Thread: arrays of composite types, and client drivers like JDBC
Hi,
I'm experimenting with Java client libraries (the usual JDBC and some other async projects, eg [1]). So far, I'm not finding ways to select/read composite types without ugly string parsing. The simple cases are okay, but if I have a column that is an array of composites, the client library might give me a string for a column value like the following, with no builtin way to parse it.
{"(10,\"abc \"\" \"\" \"\"\",\"2019-06-14 18:16:48.067969\",t)","(11,foo,\"2019-06-14 18:16:48.067969\",f)"}
Maybe I'm missing the part of the JDBC API that I can use here.
If not, then I'm wondering: is there something inherent in the underlying PG protocol that makes this difficult for all these client/driver libraries? In other words, maybe the protocol is sending strings meant for display, not for parsing as data?
I was hoping I'd find an API like...
create type foo as (age int, color text);
create table t (a foo, b foo[]);
....
var resultSet = conn.prepareStatement("select a,b from t").executeQuery()
var foos = resultSet.getArray("b")
var foo = foos.getElement(0)
var age = foo.getInt(1)
var color = foo.getString(2)
thanks,
Rob
On Sat, 15 Jun 2019 at 02:33, Rob Nikander <rob.nikander@gmail.com> wrote:
Hi,I'm experimenting with Java client libraries (the usual JDBC and some other async projects, eg [1]). So far, I'm not finding ways to select/read composite types without ugly string parsing. The simple cases are okay, but if I have a column that is an array of composites, the client library might give me a string for a column value like the following, with no builtin way to parse it.{"(10,\"abc \"\" \"\" \"\"\",\"2019-06-14 18:16:48.067969\",t)","(11,foo,\"2019-06-14 18:16:48.067969\",f)"}Maybe I'm missing the part of the JDBC API that I can use here.If not, then I'm wondering: is there something inherent in the underlying PG protocol that makes this difficult for all these client/driver libraries? In other words, maybe the protocol is sending strings meant for display, not for parsing as data?I was hoping I'd find an API like...create type foo as (age int, color text);create table t (a foo, b foo[]);....var resultSet = conn.prepareStatement("select a,b from t").executeQuery()var foos = resultSet.getArray("b")var foo = foos.getElement(0)var age = foo.getInt(1)var color = foo.getString(2)thanks,Rob
Basically because java would have to create a type dynamically to parse the data into.
There's nothing inherently difficult about parsing the data, the problem is what do we put it into ?
> On Jun 15, 2019, at 1:47 PM, Dave Cramer <pg@fastcrypt.com> wrote: > > Basically because java would have to create a type dynamically to parse the data into. > There's nothing inherently difficult about parsing the data, the problem is what do we put it into ? (I accidentally replied off-list, so resending this.) It could parse it to strings and convert only when I call `getInt`, `getTimestamp`, etc. But, like you said, I see that theparsing isn't that difficult, so I wrote something that seems to work. Probably that code should be in the library, notmy application. If I test this out some more and it still seems to work, I’ll ask again about contributing to https://github.com/pgjdbc/pgjdbc. Rob