Thread: pg JDBC driver feature question
Is there a place that I can find out all of the features supported by the PostgreSQL JDBC driver, or the ones that are not yet ready? In short, I am curious about two things -- 1. The DatabaseMetaData.supportsIntegrityEnhancement method returns false for me using the pg73jdbc2ee.jar driver. Perhaps I just don't fully understand what this method actually means, but I would have expected it to return true, since PostgreSQL (the database) supports referential integrity constraints? Or am I confused about two different things. 2. Is the DatabaseMetaData.getExportedKeys method fully implemented in the Driver? Specifically, I would like to identify all of the foreign key tables that depend on a specified table's primary key using the FKTABLE_NAME column. I'm just trying to nail down whether the bug I'm experiencing in my development is something I've overlooked or if it could possibly be an unfinished implementation of the driver. Thanks very much if you can give me any hints at all. Erik
Erik Price schrieb: > 2. Is the DatabaseMetaData.getExportedKeys method fully implemented in > the Driver? Specifically, I would like to identify all of the foreign > key tables that depend on a specified table's primary key using the > FKTABLE_NAME column. > > I'm just trying to nail down whether the bug I'm experiencing in my > development is something I've overlooked or if it could possibly be an > unfinished implementation of the driver. > To my knowledge this works pretty well. I have a SQL tool which display the foreign key constraints for a table. It is using plain JDBC calls, and works well with postgres (7.2.x and 7.3.x) I have to admit that I haven't analyzed really complex databases with this. Although I did see some quirks with older JDBC drivers such as: the full FK definition appeared the FK_NAME column (something like fk_name\000fk_colum_base_table\000fk_column_target or something similar, I can't remember now) But as far as I can tell these problems went away with the current build 110 of the JDBC driver. Thomas
Thomas Kellerer wrote: > Erik Price schrieb: > >> 2. Is the DatabaseMetaData.getExportedKeys method fully implemented in >> the Driver? Specifically, I would like to identify all of the foreign >> key tables that depend on a specified table's primary key using the >> FKTABLE_NAME column. >> >> I'm just trying to nail down whether the bug I'm experiencing in my >> development is something I've overlooked or if it could possibly be an >> unfinished implementation of the driver. >> > To my knowledge this works pretty well. I have a SQL tool which display > the foreign key constraints for a table. It is using plain JDBC calls, > and works well with postgres (7.2.x and 7.3.x) Thanks for confirming that for me Thomas. Frankly it makes me feel better that it's not a problem with a driver, even if it means that there's some hidden problem in my application! :) Erik
Erik, thanks, --Barry Erik Price wrote: > Is there a place that I can find out all of the features supported by > the PostgreSQL JDBC driver, or the ones that are not yet ready? > > In short, I am curious about two things -- > > 1. The DatabaseMetaData.supportsIntegrityEnhancement method returns > false for me using the pg73jdbc2ee.jar driver. Perhaps I just don't > fully understand what this method actually means, but I would have > expected it to return true, since PostgreSQL (the database) supports > referential integrity constraints? Or am I confused about two different > things. If IntegrityEnhancementFacility = integrity constraints then yes the driver should return true. But I really don't know what IntegretyEnhancementFacility means. It isn't discussed in either the jdbc javadoc or the jdbc specs. It is entirely possible that it implies a lot more than just plain integrity constraints in which case returning false for this method may be the correct thing to do. > > 2. Is the DatabaseMetaData.getExportedKeys method fully implemented in > the Driver? Specifically, I would like to identify all of the foreign > key tables that depend on a specified table's primary key using the > FKTABLE_NAME column. > > I'm just trying to nail down whether the bug I'm experiencing in my > development is something I've overlooked or if it could possibly be an > unfinished implementation of the driver. I believe this has already been answered, but the short answer is it should work. It would be a bug if it didn't. thanks, --Barry
Barry Lind wrote: >> 1. The DatabaseMetaData.supportsIntegrityEnhancement method returns >> false for me using the pg73jdbc2ee.jar driver. Perhaps I just don't >> fully understand what this method actually means, but I would have >> expected it to return true, since PostgreSQL (the database) supports >> referential integrity constraints? Or am I confused about two >> different things. > > > If IntegrityEnhancementFacility = integrity constraints then yes the > driver should return true. But I really don't know what > IntegretyEnhancementFacility means. It isn't discussed in either the > jdbc javadoc or the jdbc specs. It is entirely possible that it implies > a lot more than just plain integrity constraints in which case returning > false for this method may be the correct thing to do. The most authoritative thing I could find was this document: <http://www.itl.nist.gov/div897/ctg/dm/sql_info.html> <snip> The SQL Integrity Enhancement facility offers additional tools for referential integrity, CHECK constraint clauses, and DEFAULT clauses. Referential integrity allows specification of primary and foreign keys with the requirement that no foreign key row may be inserted or updated unless a matching primary key row exists. Check clauses allow specification of inter-column constraints to be maintained by the database system. Default clauses provide optional default values for missing data. </snip> This is a little ambiguous. Does Sun offer a guide for writing JDBC drivers, which might explain how a driver should respond when the various DatabaseMetaData methods are called? Erik
> > 1. The DatabaseMetaData.supportsIntegrityEnhancement method returns > > false for me using the pg73jdbc2ee.jar driver. Perhaps I just don't > > fully understand what this method actually means, but I would have > > expected it to return true, since PostgreSQL (the database) supports > > referential integrity constraints? Or am I confused about two different > > things. > > If IntegrityEnhancementFacility = integrity constraints then yes the > driver should return true. But I really don't know what > IntegretyEnhancementFacility means. It isn't discussed in either the > jdbc javadoc or the jdbc specs. It is entirely possible that it implies > a lot more than just plain integrity constraints in which case returning > false for this method may be the correct thing to do. Looking at the javadoc I can't see anything else about integrity constraints, so I'd have to believe that this is what they mean. It seems if this was for something above and beyond regular constraints they'd have something for the base level as well. I think we should return true. > > 2. Is the DatabaseMetaData.getExportedKeys method fully implemented in > > the Driver? Specifically, I would like to identify all of the foreign > > key tables that depend on a specified table's primary key using the > > FKTABLE_NAME column. It works for foreign keys which reference a primary key, but does not work for columns referencing a unique index. Kris Jurka