Skip site navigation (1) Skip section navigation (2)

Re: passing user defined data types to stored procedures

From: "Jay Howard" <jhoward(at)alumni(dot)utexas(dot)net>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: passing user defined data types to stored procedures
Date: 2008-11-15 17:43:00
Message-ID: 7569a8ec0811150943l62746eefm8e4f034a1d69b624@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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>
>

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-11-15 17:51:51
Subject: Generated keys support
Previous:From: Kris JurkaDate: 2008-11-15 17:25:31
Subject: Re: passing user defined data types to stored procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group