Thread: UDT arrays

Hello,

I was wondering, is JDBC driver support for the combination of UDT's
and arrays planned for the near future? I couldn't find any
documentation on the JDBC drivers website.

Is there any specific reason (apart from the fact that UDT's and/or
ARRAYs are not used often) for this lack of implementation? Clearly,
the database itself supports this combination. I can easily insert and
select data from a table holding UDT arrays

Cheers
Lukas

Actully I've done support for Structs, with fixation of metdata bugs for those
types (JDBC spec is not clear, about metadata), but only in binary mode. I
will, probably, don't give support for text protocol, because I belive binary
protocol is future (but here is ?).

Currently
1. I need to give full support for SQLData, Input & Output, as well implement
last type (time with timezone).
2. I need do something with DISTINCTS/DOMAINS

Problem is with timestamap with timezone, as on this field backend is
incompatible with JDBC, so probably driver will need to extend database, or I
need to do some "unsafe" operations or throw exception for some situations.

In both of this I need to do / write full tests for many compilations of
resultset/arrays/structs.

Some functionality will temporaly go away (eg. updating result sets, arrays
returning result sets for mulitdimensional array, geometric types, and all
PG*Objects will be sleeped too).

I want to resolve some serius bugs about resultsets and SQLXML, too, before
release (this with result set may wait, if only one person found it, and not
even found full problem...), but SQLXML is important as XML is popular
solution.

Maybe "true" async notifications based on listener pattern will be included in
release.

Have a nice day

Lukas Eder <lukas.eder@gmail.com> Tuesday 08 February 2011 00:26:58
> Hello,
>
> I was wondering, is JDBC driver support for the combination of UDT's
> and arrays planned for the near future? I couldn't find any
> documentation on the JDBC drivers website.
>
> Is there any specific reason (apart from the fact that UDT's and/or
> ARRAYs are not used often) for this lack of implementation? Clearly,
> the database itself supports this combination. I can easily insert and
> select data from a table holding UDT arrays
>
> Cheers
> Lukas

I plan to sleep PGObjects, and wake for backward compatibility (maybe as
deprectaed or some parts of methodes there - there are static methods, which
causes problems with JDBC4 Exception model and some per connection specific
functionaly). Arrays parsing is different in text and in binary mode, and
binary mode requires more carefull casting. Actually current flow should allow
to read any nested type, which is supported by ResultSet, form parent object
(I putted only exception for getting result set with multidimensional arrays).

I plan to give support for specific PG objects like box, but make them more
portable, without internal connection with JDBC dirver logic (you should be
able to serialize and deserialzie those objects on clients without
postgresql.jar). I plan as well to give "plugable" support for custom PG
objects, which can't be processed as UDT (above box is example). I think about
mapping some PG objects to standard Java classes e.g. PG's box -> java.awt.Box
(but this is far future). Above must be done in descriptive way, to be usable
with DataSource and app servers, eg.

@Resource
private DataSource myPgDataSourceWithMyCustomObjects

Both of those should be designed in fast way, low memory consuption and in way
preventing writing thousend lines of code if binary format for given object
will be different per DB level or per new protocol (see e.g. problems with
bytea encoding in 9.x releases).

Lukas Eder <lukas.eder@gmail.com> Thursday 10 February 2011 10:45:17
> Hi Radoslaw,
>
> Interesting news about SQLData, Input & Output. That's clearly the way to
> go. So I assume you're going to be removing the PGobject from the JDBC
> driver in a future release? But I still don't understand whether you are
> actually going to plan to implement a combination of UDT and ARRAY, such as
>
> CREATE TYPE composite_type AS (f1 int, f2 text);
> CREATE TABLE t (array_of_composites composite_type[]);
>
> This works in Postgres, but is not supported by the JDBC driver... Are you
> going to address that as well, in the near future?
>
> Cheers
> Lukas
>
> 2011/2/9 Radosław Smogura <rsmogura@softperience.eu>
>
> > Actully I've done support for Structs, with fixation of metdata bugs for
> > those
> > types (JDBC spec is not clear, about metadata), but only in binary mode.
> > I will, probably, don't give support for text protocol, because I belive
> > binary
> > protocol is future (but here is ?).
> >
> > Currently
> > 1. I need to give full support for SQLData, Input & Output, as well
> > implement
> > last type (time with timezone).
> > 2. I need do something with DISTINCTS/DOMAINS
> >
> > Problem is with timestamap with timezone, as on this field backend is
> > incompatible with JDBC, so probably driver will need to extend database,
> > or I
> > need to do some "unsafe" operations or throw exception for some
> > situations.
> >
> > In both of this I need to do / write full tests for many compilations of
> > resultset/arrays/structs.
> >
> > Some functionality will temporaly go away (eg. updating result sets,
> > arrays returning result sets for mulitdimensional array, geometric
> > types, and all PG*Objects will be sleeped too).
> >
> > I want to resolve some serius bugs about resultsets and SQLXML, too,
> > before release (this with result set may wait, if only one person found
> > it, and not
> > even found full problem...), but SQLXML is important as XML is popular
> > solution.
> >
> > Maybe "true" async notifications based on listener pattern will be
> > included in
> > release.
> >
> > Have a nice day
> >
> > Lukas Eder <lukas.eder@gmail.com> Tuesday 08 February 2011 00:26:58
> >
> > > Hello,
> > >
> > > I was wondering, is JDBC driver support for the combination of UDT's
> > > and arrays planned for the near future? I couldn't find any
> > > documentation on the JDBC drivers website.
> > >
> > > Is there any specific reason (apart from the fact that UDT's and/or
> > > ARRAYs are not used often) for this lack of implementation? Clearly,
> > > the database itself supports this combination. I can easily insert and
> > > select data from a table holding UDT arrays
> > >
> > > Cheers
> > > Lukas

CREATE TYPE composite_type AS (f1 int, f2 text);
CREATE TABLE t (array_of_composites composite_type[]);

Excuse me for butting in, but am I right in understanding that you're
only talking about the Softperience JDBC driver, not the official one?

http://softperience.eu/pages/cmn/ngpgjdbc.xhtml

Like Lukas I'm also writing a database tool (though mine works on rather
different principles), and I'm also extremely interested in support for
UDT's and arrays of UDT's.

 - thormick

On Thu, 2011-02-10 at 11:26 +0100, Radosław Smogura wrote:
> I plan to sleep PGObjects, and wake for backward compatibility (maybe as
> deprectaed or some parts of methodes there - there are static methods, which
> causes problems with JDBC4 Exception model and some per connection specific
> functionaly). Arrays parsing is different in text and in binary mode, and
> binary mode requires more carefull casting. Actually current flow should allow
> to read any nested type, which is supported by ResultSet, form parent object
> (I putted only exception for getting result set with multidimensional arrays).
>
> I plan to give support for specific PG objects like box, but make them more
> portable, without internal connection with JDBC dirver logic (you should be
> able to serialize and deserialzie those objects on clients without
> postgresql.jar). I plan as well to give "plugable" support for custom PG
> objects, which can't be processed as UDT (above box is example). I think about
> mapping some PG objects to standard Java classes e.g. PG's box -> java.awt.Box
> (but this is far future). Above must be done in descriptive way, to be usable
> with DataSource and app servers, eg.
>
> @Resource
> private DataSource myPgDataSourceWithMyCustomObjects
>
> Both of those should be designed in fast way, low memory consuption and in way
> preventing writing thousend lines of code if binary format for given object
> will be different per DB level or per new protocol (see e.g. problems with
> bytea encoding in 9.x releases).

 I think it will be so, I want to add good support for this driver. You
 should not afraid about this, many "not offical" drivers are better then
 official releases, e.g. BEA had own driver for Oracle DB, as well I
 worked with one driver,I don't remember vendor, to IBM DB2 better then
 official.

 I currently write small user registration app, I have virtual mailboxes
 to hide true user e-mails in bugzilla. I looking for good "forum".

 Kind regards,
 Radek

 On Sun, 13 Feb 2011 19:03:29 +0100, Thor Michael Støre wrote:
> Excuse me for butting in, but am I right in understanding that you're
> only talking about the Softperience JDBC driver, not the official
> one?
>
> http://softperience.eu/pages/cmn/ngpgjdbc.xhtml
>
> Like Lukas I'm also writing a database tool (though mine works on
> rather
> different principles), and I'm also extremely interested in support
> for
> UDT's and arrays of UDT's.
>
>  - thormick
>
> On Thu, 2011-02-10 at 11:26 +0100, Radosław Smogura wrote:
>> I plan to sleep PGObjects, and wake for backward compatibility
>> (maybe as
>> deprectaed or some parts of methodes there - there are static
>> methods, which
>> causes problems with JDBC4 Exception model and some per connection
>> specific
>> functionaly). Arrays parsing is different in text and in binary
>> mode, and
>> binary mode requires more carefull casting. Actually current flow
>> should allow
>> to read any nested type, which is supported by ResultSet, form
>> parent object
>> (I putted only exception for getting result set with
>> multidimensional arrays).
>>
>> I plan to give support for specific PG objects like box, but make
>> them more
>> portable, without internal connection with JDBC dirver logic (you
>> should be
>> able to serialize and deserialzie those objects on clients without
>> postgresql.jar). I plan as well to give "plugable" support for
>> custom PG
>> objects, which can't be processed as UDT (above box is example). I
>> think about
>> mapping some PG objects to standard Java classes e.g. PG's box ->
>> java.awt.Box
>> (but this is far future). Above must be done in descriptive way, to
>> be usable
>> with DataSource and app servers, eg.
>>
>> @Resource
>> private DataSource myPgDataSourceWithMyCustomObjects
>>
>> Both of those should be designed in fast way, low memory consuption
>> and in way
>> preventing writing thousend lines of code if binary format for given
>> object
>> will be different per DB level or per new protocol (see e.g.
>> problems with
>> bytea encoding in 9.x releases).

Sorry for late answer.

I rolled my own driver, because of

1) I added support for some functionality I was required for production system
sefety (e.g. statement timeout), and some for performance (binary mode
support), many of those was inspirated by post in this forum (better memory
usage for large objectes, UDT, etc...), I've done some bug fix of those what I
saw, just, in code, without any direct bug notify. Actually I was affected by
one of those bugs, and I didn't realasied this, I rewritten many parts of app,
because I thought it is my problem. As well many JDBC4 functionality is
missing, in few places implementation ended at JDBC2/3 and from other hand,
JDBC 4.1 will income.
2) I gave sources few monthes ago, but those were forgotten.

As well I want to add profesional support (bugzilla, etc)...

Thanks for notify about link. It is only "framework" of support system, and
actually all links there points to "ddd"- 404. I know it's realy
unprofessional..., but I was in need to "correct" glassfish too. Check in
month or two. Everything should work prefectly.

From other side future of this driver isn't for me clear, because I don't
exactly know in which direction PG will come. I choosed PG as free DB from
performance reasons when it was 7.x, It was faster then MySQL and had low
resource needs.

About Google... better is Kenai, Sourceforge, etc - G has quite interesting
licenses, especially when you read license for search engines, gmail, chrome
and connect them together.

Lukas Eder <lukas.eder@gmail.com> Monday 14 February 2011 13:20:56
> Aaaah, thanks for asking that, Thormick. Good to know. I didn't realise
> Radek was talking about any other JDBC implementation. I'll double check if
> that Softperience driver will fix my problem. But then again, Radek, you
> should have said so. I doubt that the pgsql-jdbc@postgresql.org user group
> should be about *YOUR *JDBC driver. I suggest you roll your own free user
> group at google groups or elsewhere... Then again, may I ask you, why do
> you roll your own driver? Why don't you contribute to the official one?
>
> While I'm writing you, please have a look at your documentations link. It
> seems to be broken:
> http://softperience.eu/pages/cmn/ddd
>
> I get a 404...
>
> Cheers
> Lukas
>
> 2011/2/14 rsmogura <rsmogura@softperience.eu>
>
> > I think it will be so, I want to add good support for this driver. You
> > should not afraid about this, many "not offical" drivers are better then
> > official releases, e.g. BEA had own driver for Oracle DB, as well I
> > worked with one driver,I don't remember vendor, to IBM DB2 better then
> > official.
> >
> > I currently write small user registration app, I have virtual mailboxes
> > to hide true user e-mails in bugzilla. I looking for good "forum".
> >
> > Kind regards,
> > Radek
> >
> > On Sun, 13 Feb 2011 19:03:29 +0100, Thor Michael Støre wrote:
> >> Excuse me for butting in, but am I right in understanding that you're
> >> only talking about the Softperience JDBC driver, not the official one?
> >>
> >> http://softperience.eu/pages/cmn/ngpgjdbc.xhtml
> >>
> >> Like Lukas I'm also writing a database tool (though mine works on rather
> >> different principles), and I'm also extremely interested in support for
> >> UDT's and arrays of UDT's.
> >>
> >>  - thormick
> >>
> >> On Thu, 2011-02-10 at 11:26 +0100, Radosław Smogura wrote:
> >>> I plan to sleep PGObjects, and wake for backward compatibility (maybe
> >>> as deprectaed or some parts of methodes there - there are static
> >>> methods, which
> >>> causes problems with JDBC4 Exception model and some per connection
> >>> specific
> >>> functionaly). Arrays parsing is different in text and in binary mode,
> >>> and binary mode requires more carefull casting. Actually current flow
> >>> should allow
> >>> to read any nested type, which is supported by ResultSet, form parent
> >>> object
> >>> (I putted only exception for getting result set with multidimensional
> >>> arrays).
> >>>
> >>> I plan to give support for specific PG objects like box, but make them
> >>> more
> >>> portable, without internal connection with JDBC dirver logic (you
> >>> should be
> >>> able to serialize and deserialzie those objects on clients without
> >>> postgresql.jar). I plan as well to give "plugable" support for custom
> >>> PG objects, which can't be processed as UDT (above box is example). I
> >>> think about
> >>> mapping some PG objects to standard Java classes e.g. PG's box ->
> >>> java.awt.Box
> >>> (but this is far future). Above must be done in descriptive way, to be
> >>> usable
> >>> with DataSource and app servers, eg.
> >>>
> >>> @Resource
> >>> private DataSource myPgDataSourceWithMyCustomObjects
> >>>
> >>> Both of those should be designed in fast way, low memory consuption and
> >>> in way
> >>> preventing writing thousend lines of code if binary format for given
> >>> object
> >>> will be different per DB level or per new protocol (see e.g. problems
> >>> with
> >>> bytea encoding in 9.x releases).

On Feb 8, 12:26 am, Lukas Eder <lukas.e...@gmail.com> wrote:
> I was wondering, is JDBC driver support for the combination of UDT's
> and arrays planned for the near future? I couldn't find any
> documentation on the JDBC drivers website.

After the rather confusing input about a third-party JDBC driver, I
can confirm that access to UDT arrays is possible via the standard
JDBC driver, if done like this:

    // This returns a serialised array. Not very convenient
    ResultSet rs =...
    String serialised = rs.getObject(index);

Or like this

    // This allows for iterating over array elements
    // and assembling it manually. I'll use this method
    // in jOOQ
    ResultSet rs =...
    ResultSet iterator = rs.getArray(index).getResultSet();

But not like this

    // This would be most convenient
    ResultSet rs =...
    Object[] array = rs.getArray(index).getArray();

> Is there any specific reason (apart from the fact that UDT's and/or
> ARRAYs are not used often) for this lack of implementation? Clearly,
> the database itself supports this combination. I can easily insert and
> select data from a table holding UDT arrays

The question was answered in another thread. I can live with the above
workaround...