On 11/12/11 2:36 AM, Karl Weber wrote:
Am Mittwoch, 9. November 2011, 11:51:43 schrieb Knut Anders Hatlen:
Karl Weber<[email protected]> 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
