You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Karl Weber <ka...@googlemail.com> on 2011/11/08 22:43:50 UTC
UDT and java arrays
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;
?
ij does not complain. It also allows to use it in a table definition, e.g.
ij> CREATE TYPE APP.DARRAY
> EXTERNAL NAME 'double[]'
> LANGUAGE JAVA;
0 Zeilen eingefügt/aktualisiert/gelöscht
ij> create table XXX (
> ASDF APP.DARRAY
> );
0 Zeilen eingefügt/aktualisiert/gelöscht
However, how do I insert values into this table, using normal SQL and using
the JDBC API?
Or is this all nonsense, although I do not get any error message so far?
/Karl
Re: UDT and java arrays
Posted by Rick Hillegas <ri...@oracle.com>.
On 11/12/11 2:36 AM, Karl Weber wrote:
> Am Mittwoch, 9. November 2011, 11:51:43 schrieb Knut Anders Hatlen:
>> Karl Weber<ka...@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
Re: UDT and java arrays
Posted by Rick Hillegas <ri...@oracle.com>.
On 11/12/11 2:36 AM, Karl Weber wrote:
> Am Mittwoch, 9. November 2011, 11:51:43 schrieb Knut Anders Hatlen:
>> Karl Weber<ka...@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
Re: UDT and java arrays
Posted by Karl Weber <ka...@googlemail.com>.
Am Mittwoch, 9. November 2011, 11:51:43 schrieb Knut Anders Hatlen:
> Karl Weber <ka...@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?
Re: UDT and java arrays
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Karl Weber <ka...@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;
> ?
> ij does not complain. It also allows to use it in a table definition, e.g.
>
> ij> CREATE TYPE APP.DARRAY
>> EXTERNAL NAME 'double[]'
>> LANGUAGE JAVA;
> 0 Zeilen eingefügt/aktualisiert/gelöscht
> ij> create table XXX (
>> ASDF APP.DARRAY
>> );
> 0 Zeilen eingefügt/aktualisiert/gelöscht
>
> 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();
If you want to do it from ij, you'll have to create a user-defined
function that returns the array you want to insert, and call that
function in the insert statement.
class StoredFunctions {
public static double[] createArray(...) {
double[] array;
...
return array;
}
ij> create function create_array () returns APP.DARRAY
language java parameter style java
external name 'Funcs.createArray';
ij> insert into xxx values create_array(...);
Hope this helps,
--
Knut Anders