Thread: pg JDBC driver feature question

pg JDBC driver feature question

From
Erik Price
Date:
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


Re: pg JDBC driver feature question

From
Thomas Kellerer
Date:
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


Re: pg JDBC driver feature question

From
Erik Price
Date:

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


Re: pg JDBC driver feature question

From
Barry Lind
Date:
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




Re: pg JDBC driver feature question

From
Erik Price
Date:

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


Re: pg JDBC driver feature question

From
Kris Jurka
Date:
> > 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