Re: passing user defined data types to stored procedures

Interesting. I did some experimenting with this last night, and it led to a
followup question:

Is it possible to return (and pass) arrays using java.sql.Array?

I wrote a test proc that returns an array of integers. I can retrieve it on
the java side as a Jdbc3Array, which implements java.sql.Array.

Unfortunately, getArray() (and its variants) seem not to have been
implemented yet by Jdbc3Array.

Any way to pass arrays back and forth? I could always have the proc return
a refcursor and handle it in Java as a ResultSet, but I was looking for a
convenient way to avoid that.

On Sat, Nov 15, 2008 at 11:25 AM, Kris Jurka <books(at)ejurka(dot)com> wrote:

>
>
> On Fri, 14 Nov 2008, Jay Howard wrote:
>
> Does the driver support passing UDTs as arguments to stored procs?
>>
>>
> Sort of, but not in the standard fashion. SQLData is not supported, but it
> is possible to do it using PGobject (a postgresql specific extension). If
> you make your class Foo extend PGobject [1] and make getType return the type
> name and getValue return the text representation that the server expects it
> will work for sending data to the server. To get objects of this type back
> from the server, you must register them via PGConnection.addDataType [2].
>
> Sample text representation of a complex type:
>
> jurka=# create type mytype as (a int, b text, c date);
> CREATE TYPE
> jurka=# select '(3,"a,b",2008-11-20)'::mytype;
> mytype
> ----------------------
> (3,"a,b",2008-11-20)
> (1 row)
>
> Kris Jurka
>
> [1]
> http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html
> [2]
> http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGConnection.html#addDataType(java.lang.String,%20java.lang.Class)<http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGConnection.html#addDataType%28java.lang.String,%20java.lang.Class%29>
>