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: (view raw, whole thread or download thread mbox)
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);
> jurka=# select '(3,"a,b",2008-11-20)'::mytype;
>        mytype
> ----------------------
>  (3,"a,b",2008-11-20)
> (1 row)
> Kris Jurka
> [1]
> [2]

In response to


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-2017 The PostgreSQL Global Development Group