Thread: JDBC and arrays
Hi, I tried to find some info on how to work with arrays using PostgreSQL JDBC driver but didn't find anything useful except that I can implement java.sql.Array to get support for arrays. Can someone, please, send me a link on how to write and read array data using PostgreSQL JDBC driver or give me a brief explanation? I'm also interested how to work with inet[] arrays as they do not seem to be directly supported by JDBC API. Thanks for any info. -- Miroslav Šulc
On Fri, 2 Feb 2007, Miroslav Šulc wrote:
> I tried to find some info on how to work with arrays using PostgreSQL JDBC
> driver but didn't find anything useful except that I can implement
> java.sql.Array to get support for arrays. Can someone, please, send me a link
> on how to write and read array data using PostgreSQL JDBC driver or give me a
> brief explanation? I'm also interested how to work with inet[] arrays as they
> do not seem to be directly supported by JDBC API.
Reading arrays is pretty straightforward:
ResultSet rs = stmt.executeQuery("SELECT '{a,b}'::text[]");
rs.next();
Array arr = rs.getArray(1);
String s[] = (String)arr.getArray();
Writing arrays is not easy because prior to the JDBC4 spec there was no
way to create java.sql.Array objects without creating a class that
implements java.sql.Array yourself. Now JDBC4 offers
Connection.createArrayOf() which unfortunately we haven't implemented yet.
So while there is a light at the end of the tunnel you're still stuck
implementing java.sql.Array yourself. Check the mailing list for examples
of that.
Currently arrays of non-standard datatypes like inet are not supported.
Seems possible to do if you were willing to use PGobject for unknown
types, but no one has done it.
Also more complicated things like multi-dimensional arrays are note
supported.
Kris Jurka
Thank you for explanation. If I still want to write inet[] data, is it
sufficient to implement PGobject so it returns 'inet[]' type and '{
"ip", "ip" }' value and pass that to setObject()?
--
Miroslav Šulc
Kris Jurka napsal(a):
>
>
> On Fri, 2 Feb 2007, Miroslav Šulc wrote:
>
>> I tried to find some info on how to work with arrays using PostgreSQL
>> JDBC driver but didn't find anything useful except that I can
>> implement java.sql.Array to get support for arrays. Can someone,
>> please, send me a link on how to write and read array data using
>> PostgreSQL JDBC driver or give me a brief explanation? I'm also
>> interested how to work with inet[] arrays as they do not seem to be
>> directly supported by JDBC API.
>
> Reading arrays is pretty straightforward:
>
> ResultSet rs = stmt.executeQuery("SELECT '{a,b}'::text[]");
> rs.next();
> Array arr = rs.getArray(1);
> String s[] = (String)arr.getArray();
>
> Writing arrays is not easy because prior to the JDBC4 spec there was
> no way to create java.sql.Array objects without creating a class that
> implements java.sql.Array yourself. Now JDBC4 offers
> Connection.createArrayOf() which unfortunately we haven't implemented
> yet. So while there is a light at the end of the tunnel you're still
> stuck implementing java.sql.Array yourself. Check the mailing list
> for examples of that.
>
> Currently arrays of non-standard datatypes like inet are not
> supported. Seems possible to do if you were willing to use PGobject
> for unknown types, but no one has done it.
>
> Also more complicated things like multi-dimensional arrays are note
> supported.
>
> Kris Jurka
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
Miroslav Šulc wrote:
> Thank you for explanation. If I still want to write inet[] data, is it
> sufficient to implement PGobject so it returns 'inet[]' type and '{
> "ip", "ip" }' value and pass that to setObject()?
Miroslav,
I don't know if this will help you but awhile ago I implemented data
types for the JDBC driver for PGinet, PGmacaddr, PGcidr which have been
used successfully by a number of people including myself ;) I would
still like to get them integrated into the driver but haven't had much
time to pursue it. You can find the current version at
http://oak.cats.ohiou.edu/~rf358197/jdbc/2/
I have zero experience using them in an array but if you are using those
data types in PostgreSQL and using Java, the code might be useful to you.
Kind regards,
Russ
>
> --
> Miroslav Šulc
>
>
>
> Kris Jurka napsal(a):
>>
>>
>> On Fri, 2 Feb 2007, Miroslav Šulc wrote:
>>
>>> I tried to find some info on how to work with arrays using PostgreSQL
>>> JDBC driver but didn't find anything useful except that I can
>>> implement java.sql.Array to get support for arrays. Can someone,
>>> please, send me a link on how to write and read array data using
>>> PostgreSQL JDBC driver or give me a brief explanation? I'm also
>>> interested how to work with inet[] arrays as they do not seem to be
>>> directly supported by JDBC API.
>>
>> Reading arrays is pretty straightforward:
>>
>> ResultSet rs = stmt.executeQuery("SELECT '{a,b}'::text[]");
>> rs.next();
>> Array arr = rs.getArray(1);
>> String s[] = (String)arr.getArray();
>>
>> Writing arrays is not easy because prior to the JDBC4 spec there was
>> no way to create java.sql.Array objects without creating a class that
>> implements java.sql.Array yourself. Now JDBC4 offers
>> Connection.createArrayOf() which unfortunately we haven't implemented
>> yet. So while there is a light at the end of the tunnel you're still
>> stuck implementing java.sql.Array yourself. Check the mailing list
>> for examples of that.
>>
>> Currently arrays of non-standard datatypes like inet are not
>> supported. Seems possible to do if you were willing to use PGobject
>> for unknown types, but no one has done it.
>>
>> Also more complicated things like multi-dimensional arrays are note
>> supported.
>>
>> Kris Jurka
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
On Fri, 2 Feb 2007, Miroslav Šulc wrote:
> Thank you for explanation. If I still want to write inet[] data, is it
> sufficient to implement PGobject so it returns 'inet[]' type and '{ "ip",
> "ip" }' value and pass that to setObject()?
That should work although you'll probably need to write _inet
instead of inet[]. The underscore variant is the the backend's true
datatype name and I'm not sure if it will accept the other version in this
context.
I got writing of inet[] working through object that implements PGobject
(you were right, it works only with _inet), but I cannot read inet[]. I
thought I'll get String[] through getArray(index).getArray() but instead
I get an exception on getArray(index). What is the correct way to read
inet[]?
Caused by: org.postgresql.util.PSQLException: Method
org.postgresql.jdbc3g.Jdbc3gArray.getArrayImpl(long,int,Map) is not
implemented.
at org.postgresql.Driver.notImplemented(Driver.java:728)
at
org.postgresql.jdbc2.AbstractJdbc2Array.getArrayImpl(AbstractJdbc2Array.java:228)
at
org.postgresql.jdbc2.AbstractJdbc2Array.getArray(AbstractJdbc2Array.java:71)
at
cz.startnet.utils.java.sql.ResultSetUtils.getValue(ResultSetUtils.java:253)
--
Miroslav Šulc
Kris Jurka napsal(a):
>
>
> On Fri, 2 Feb 2007, Miroslav Šulc wrote:
>
>> Thank you for explanation. If I still want to write inet[] data, is
>> it sufficient to implement PGobject so it returns 'inet[]' type and
>> '{ "ip", "ip" }' value and pass that to setObject()?
>
> That should work although you'll probably need to write _inet instead
> of inet[]. The underscore variant is the the backend's true datatype
> name and I'm not sure if it will accept the other version in this
> context.
I got it. I read it as string and then I parse the string to get the values. -- Miroslav Šulc Miroslav Šulc napsal(a): > I got writing of inet[] working through object that implements > PGobject (you were right, it works only with _inet), but I cannot read > inet[]. I thought I'll get String[] through getArray(index).getArray() > but instead I get an exception on getArray(index). What is the correct > way to read inet[]? > > Caused by: org.postgresql.util.PSQLException: Method > org.postgresql.jdbc3g.Jdbc3gArray.getArrayImpl(long,int,Map) is not > implemented. > at org.postgresql.Driver.notImplemented(Driver.java:728) > at > org.postgresql.jdbc2.AbstractJdbc2Array.getArrayImpl(AbstractJdbc2Array.java:228) > > at > org.postgresql.jdbc2.AbstractJdbc2Array.getArray(AbstractJdbc2Array.java:71) > > at > cz.startnet.utils.java.sql.ResultSetUtils.getValue(ResultSetUtils.java:253) > > > -- > Miroslav Šulc
Russell,
thank you for the link. I have all the logic already implemented either
in my classes or I use system one (like java.net.Inet4Address) so what I
need (and have) is just a simple implementation of PGobject which
returns correct type and the value.
Best regards,
--
Miroslav Šulc
Russell Francis napsal(a):
> Miroslav Šulc wrote:
>
>> Thank you for explanation. If I still want to write inet[] data, is it
>> sufficient to implement PGobject so it returns 'inet[]' type and '{
>> "ip", "ip" }' value and pass that to setObject()?
>>
>
> Miroslav,
>
> I don't know if this will help you but awhile ago I implemented data
> types for the JDBC driver for PGinet, PGmacaddr, PGcidr which have been
> used successfully by a number of people including myself ;) I would
> still like to get them integrated into the driver but haven't had much
> time to pursue it. You can find the current version at
>
> http://oak.cats.ohiou.edu/~rf358197/jdbc/2/
>
> I have zero experience using them in an array but if you are using those
> data types in PostgreSQL and using Java, the code might be useful to you.
>
> Kind regards,
> Russ
>