Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Message-ID: <4EC116CF.9090801@oracle.com>
Date: Mon, 14 Nov 2011 05:25:35 -0800
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: UDT and java arrays
References: <201111082243.50627.Karl.Weber99@googlemail.com>
 <wjo8ipmth8lc.fsf@oracle.com>
 <201111121136.58213.Karl.Weber99@googlemail.com>
In-Reply-To: <201111121136.58213.Karl.Weber99@googlemail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 11/12/11 2:36 AM, Karl Weber wrote:
> Am Mittwoch, 9. November 2011, 11:51:43 schrieb Knut Anders Hatlen:
>> Karl Weber<karl.weber99@googlemail.com>  writes:
>>> Hi,
>>>
>>> Derby does support UDTs. One can use any java.io.Serializable java class
>>> as a UDT.
>>>
>>> On the other hand, derby does not support SQL ARRAY types.
>>>
>>> However, every java array is an object that implements
>>> java.io.Serializable, so can one define a UTD of the form
>>>
>>> CREATE TYPE APP.DARRAY
>>>
>>> 	EXTERNAL NAME 'double[]'
>>> 	LANGUAGE JAVA;
>>>
>>> ?
> [...]
>>> However, how do I insert values into this table, using normal SQL and
>>> using the JDBC API?
>> Hi Karl,
>>
>> The easiest way is to insert values using the JDBC API, like this:
>>
>>      PreparedStatement ps = c.prepareStatement("INSERT INTO XXX VALUES ?");
>>      ps.setObject(1, new double[] { 1, 2, 3 });
>>      ps.executeUpdate();
>>
> Thank you very much, Knut, it works.
>
> I have one more question: I checked double[], double[][], float[], int[],
> short[] and byte[]. Unfortunately the last one does not work:
>
> IJ Version 10.8
> ij>  create type APP.BARRAY
>> external name 'byte[]'
>> language java;
> FEHLER 42Z10: Die an einen benutzerdefinierten Typ gebundene Java-Klasse darf
> nicht intern von Derby verwendet werden: 'byte[]'.
> ij>
>
> Why is byte[] not supported? O.k., there is VARCHAR FOR BIT DATA, which maps
> to byte[], but it's size is limited to at most 32.672 bytes, a restriction I
> do not know of for UDTs. I would need larger byte arrays. The only other way I
> know would be to use a BLOB instead.... Anyway, I do not really understand,
> why one cannot define a UDT of type byte[]. If I had to use a BLOB: does the
> specified length does have any performance or other impact?
>
Hi Karl,

As you noted, byte[] is not an allowed type because it is the backing 
class for one of Derby's builtin SQL types. You will see the same error 
if you try to bind a UDT name to java.lang.Integer, which is the backing 
class for the INT type. Off the top of my head, I don't know what would 
break if we removed this restriction.

For arbitrarily long byte arrays, you can also use LONG VARCHAR FOR BIT 
DATA. One difference between VARCHAR FOR BIT DATA and the large byte 
types (BLOB and LONG VARCHAR FOR BIT DATA) is that you can create 
indexes on VARCHAR FOR BIT DATA.

Hope this helps,
-Rick