Re: meta data information returned for domains

Hi guys,

I'm hoping someone can give me some advice for handling postgres domains
withing JDBCs metadata...

I'm using the JDBC database metadata facilities to get information about
tables for the purposes of auto generating program code. This has all been
working fine until I got to testing it on a database that uses domains as
the datatypes for the columns.

I havn't had a chance to investigate it thoroughly yet, but it I'm getting a
datatype code of 1111 when the columns datatype is a domain. , I don't know
if this has any special meaning or not, but it doesn't correspond to any of
the java.sql types that I was comparing it to, so for now my code is broken.

I had a look around for information on this but didn't come up with
anything... (yet) so I'm hoping someone here could point me in the right
direction. Most essentially, I'd like to know if there is a standard way to
get the base datatype information as that is what I need for code
generation.

regards
Iain

An update.

So far I've tried using the "SOURCE_DATA_TYPE" column of the result set
returned by getColumns but this column doesn't exist.

Next I tried calling getUDTs but apparently this method isn't implemented
yet.

I noticed some discussion about implementing the getUDTs method in the lists
and references to it in the change log so maybe this has been done already
and I just need a more recent version. I'm using pg74.215.jdbc3.jar (on
7.4.6).

...regards
Iain
----- Original Message -----
From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Friday, December 03, 2004 12:13 PM
Subject: [JDBC] meta data information returned for domains

> Hi guys,
>
> I'm hoping someone can give me some advice for handling postgres domains
> withing JDBCs metadata...
>
> I'm using the JDBC database metadata facilities to get information about
> tables for the purposes of auto generating program code. This has all been
> working fine until I got to testing it on a database that uses domains as
> the datatypes for the columns.
>
> I havn't had a chance to investigate it thoroughly yet, but it I'm getting
> a datatype code of 1111 when the columns datatype is a domain. , I don't
> know if this has any special meaning or not, but it doesn't correspond to
> any of the java.sql types that I was comparing it to, so for now my code
> is broken.
>
> I had a look around for information on this but didn't come up with
> anything... (yet) so I'm hoping someone here could point me in the right
> direction. Most essentially, I'd like to know if there is a standard way
> to get the base datatype information as that is what I need for code
> generation.
>
> regards
> Iain
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

On Fri, 3 Dec 2004, Iain wrote:

> I'm using the JDBC database metadata facilities to get information about
> tables for the purposes of auto generating program code. This has all been
> working fine until I got to testing it on a database that uses domains as
> the datatypes for the columns.
>
> I havn't had a chance to investigate it thoroughly yet, but it I'm getting a
> datatype code of 1111 when the columns datatype is a domain. , I don't know
> if this has any special meaning or not, but it doesn't correspond to any of
> the java.sql types that I was comparing it to, so for now my code is broken.

1111 = Types.OTHER. We could consider resolving domains to their base
types and reporting that as the sql type. That seems like it would be
helpful in this situation.

Kris Jurka

On Fri, 3 Dec 2004, Iain wrote:

> So far I've tried using the "SOURCE_DATA_TYPE" column of the result set
> returned by getColumns but this column doesn't exist.

This is an addition in the JDBC 3 spec that we haven't gotten around to
adding as right now we only return JDBC 2 info. This obviously makes my
previous suggestion obsolete and we should just implement this. This
looks like a pretty easy thing to do except for the fact that we haven't
yet dealt with how to return different information for JDBC 2/3 drivers
without a lot of code duplication.

> Next I tried calling getUDTs but apparently this method isn't implemented
> yet.
>
> I noticed some discussion about implementing the getUDTs method in the lists
> and references to it in the change log so maybe this has been done already
> and I just need a more recent version. I'm using pg74.215.jdbc3.jar (on
> 7.4.6).
>

This has indeed been implemented in the 8.0 series of drivers.

Kris Jurka

Hi Kris

> 1111 = Types.OTHER. We could consider resolving domains to their base
> types and reporting that as the sql type. That seems like it would be
> helpful in this situation.

I figured this one out sometime later :-) I ignored Types.OTHER when I
originaly wrote the code on the basis that I didn't know what it was for -
always a mistake.

>> So far I've tried using the "SOURCE_DATA_TYPE" column of the result set
>> returned by getColumns but this column doesn't exist.
>
> This is an addition in the JDBC 3 spec that we haven't gotten around to
> adding as right now we only return JDBC 2 info. This obviously makes my
> previous suggestion obsolete and we should just implement this. This
> looks like a pretty easy thing to do except for the fact that we haven't
> yet dealt with how to return different information for JDBC 2/3 drivers
> without a lot of code duplication.

If I can use getUDT I'll be satisfied with that for the time being, but
SOURCE_DATA_TYPE would certainly be much more convenient.

I expect this question has been asked before but is there any reason why I
shouldn't use the newer driver (for v8) with 7.4.6? I assume that it would
allow me to use the getUDT method if I did that.

Thanks
Iain

On Mon, 6 Dec 2004, Iain wrote:

> I expect this question has been asked before but is there any reason why I
> shouldn't use the newer driver (for v8) with 7.4.6? I assume that it would
> allow me to use the getUDT method if I did that.
>

There are potentially serious performance problems with using the 8.0
driver on a 7.4 server. The 8.0 driver uses a server prepared statement
for every PreparedStatement object. On the server side prepared
statements usually don't consider the actual parameter values to generate
a query plan. This means an inferior query plan can be generated when the
parameter values are known, but unused, and you don't intend to reuse the
prepared statement. The 8.0 server has a mechanism to use parameter
values for planning in certain situations that the 7.4 server does not.

Kris Jurka

OK, thanks for the explanation.

Performance won't be an issue for me in this case as I'm not curently doing
any runtime code generation, it's just for development and the code
generator only ever uses meta data.

regards
Iain
----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Monday, December 06, 2004 7:44 PM
Subject: Re: [JDBC] meta data information returned for domains

>
>
> On Mon, 6 Dec 2004, Iain wrote:
>
>> I expect this question has been asked before but is there any reason why
>> I
>> shouldn't use the newer driver (for v8) with 7.4.6? I assume that it
>> would
>> allow me to use the getUDT method if I did that.
>>
>
> There are potentially serious performance problems with using the 8.0
> driver on a 7.4 server. The 8.0 driver uses a server prepared statement
> for every PreparedStatement object. On the server side prepared
> statements usually don't consider the actual parameter values to generate
> a query plan. This means an inferior query plan can be generated when the
> parameter values are known, but unused, and you don't intend to reuse the
> prepared statement. The 8.0 server has a mechanism to use parameter
> values for planning in certain situations that the 7.4 server does not.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html