Thread: 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@mst.co.jp> To: <pgsql-jdbc@postgresql.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@ejurka.com> To: "Iain" <iain@mst.co.jp> Cc: <pgsql-jdbc@postgresql.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