Thread: passing user defined data types to stored procedures
Does the driver support passing UDTs as arguments to stored procs?
Suppose I have a class Foo that implements SQLData.
On the server side, I've created a composite type called "foo", along with a proc that accepts a single "foo" argument and returns a "foo".
On the client side, I acquire a connection and set its type map such that "foo" maps to Foo.class.
When I try to setObject(2, new Foo()) on a CallableStatement, I get that the driver can't infer the SQL type to use for an instance of class Foo.
Have I screwed something up, or is this just not supported? The driver docs didn't have much to say, that I could find.
Suppose I have a class Foo that implements SQLData.
On the server side, I've created a composite type called "foo", along with a proc that accepts a single "foo" argument and returns a "foo".
On the client side, I acquire a connection and set its type map such that "foo" maps to Foo.class.
When I try to setObject(2, new Foo()) on a CallableStatement, I get that the driver can't infer the SQL type to use for an instance of class Foo.
Have I screwed something up, or is this just not supported? The driver docs didn't have much to say, that I could find.
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)
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.
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@ejurka.com> wrote:
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].
On Fri, 14 Nov 2008, Jay Howard wrote:Does the driver support passing UDTs as arguments to stored procs?
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)
On Sat, 15 Nov 2008, Jay Howard wrote: > 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. > getArray is implemented and should work. Perhaps you're getting confused because you're expecting to receive int[] and you're getting Integer[] instead? It's tough to say what's going wrong without some example code. Kris Jurka
Actually it may not have been an array of integers. I was doing stuff with composite types, so that may be the problem. The message I got from getArray() indicated that the "full" version of that method, which it must call internally, wasn't implemented. I'll verify tonight and respond with some sample code.
On Sat, Nov 15, 2008 at 12:02 PM, Kris Jurka <books@ejurka.com> wrote:
getArray is implemented and should work. Perhaps you're getting confused because you're expecting to receive int[] and you're getting Integer[] instead? It's tough to say what's going wrong without some example code.
On Sat, 15 Nov 2008, Jay Howard wrote: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.
Kris Jurka
Turns out I was returning an array of composites after all. Everything works as expected when the array elements are simple types.
That begs the question, though- is supporting arrays of composite types on the list of "things to do"?
Here's what I get when I call java.sql.Array.getArray() and the array contains composites:
Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented.
That begs the question, though- is supporting arrays of composite types on the list of "things to do"?
Here's what I get when I call java.sql.Array.getArray() and the array contains composites:
Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented.
On Sat, Nov 15, 2008 at 12:20 PM, Jay Howard <jhoward@alumni.utexas.net> wrote:
Actually it may not have been an array of integers. I was doing stuff with composite types, so that may be the problem. The message I got from getArray() indicated that the "full" version of that method, which it must call internally, wasn't implemented. I'll verify tonight and respond with some sample code.On Sat, Nov 15, 2008 at 12:02 PM, Kris Jurka <books@ejurka.com> wrote:getArray is implemented and should work. Perhaps you're getting confused because you're expecting to receive int[] and you're getting Integer[] instead? It's tough to say what's going wrong without some example code.
On Sat, 15 Nov 2008, Jay Howard wrote: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.
Kris Jurka
On Sat, 15 Nov 2008, Jay Howard wrote: > Turns out I was returning an array of composites after all. Everything > works as expected when the array elements are simple types. > > That begs the question, though- is supporting arrays of composite types on > the list of "things to do"? Sure. The problem here is our poor support of user defined types, not specifically arrays of composite types. Kris Jurka