Re: Need help with JDBC driver. Problem, - getExportedKeys

Hi all,

Does anyone know if we're planning on adding the Java
class/function/method/whatever called getExportedKeys? (I'm not a Java
programmer).

I've been looking at the free Java product called DbVisualizer
(http://www.ideit.com/innovations/dbvis/), which can connect to a
database and create an ER diagram from it, including RI information and
everything. Presently it supports several databases happily, but the
PostgreSQL JDBC 7.1.3 driver isn't passing information to it regarding
the RI relationships. So for PostgreSQL it gives a nice display of all
the tables, but no display of their relationships.

Here's a screenshot of what it's supposed to display, when all the RI
relationships are shown :

http://www.ideit.com/innovations/dbvis/images/dbvis-4.gif

In discussing this with Roger (the Product Manager of Innovative-IT who
has created this program), he says our JDBC driver gives a
Driver.notImplemented() exception when trying to find out the RI info.
(Email history detailed below).

Can anyone please assist with this?

Regards and best wishes,

Justin Clift

************

Justin,

Now I have a running 7.1.3 version of PostgreSQL.
Regarding the ref. integrity stuff. The driver
only implements getImportedKeys() but the getExportedKeys
throws a Driver.notImplemented() exception. Is there are
reason for this? (I mean if it is possible to return
imported keys then I assume exported keys could be returned
as well?)

Also, using a previous version of the driver just shows
functions in the tree while the 7.1.3 version shows
databases as well. Is this something that you are aware of?

Can you send me a SQL script that creates at least 2 tables and
the SQL to establish the foreign key relationsships between them
(referential integrity)? So I can manage to test this.
(I have browsed the documentation but cannot
find a word whether PostgreSQL supports ref. integrity or not).

Regards

Roger

Justin Clift wrote:
>
> Hi Roger,
>
> The place to get the latest RPM's for RedHat is :
>
> ftp://ftp.jack-of-all-trades.net/www.postgresql.org/binary/v7.1.3/RPMS/
>
> I would recommend making sure Ant and the 1.3.x JDK is installed on your
> system, then compiling PostgreSQL 7.1.3 from the source RPM or from
> source.
>
> There are JDBC drivers available from http://jdbc.fastcrypt.com, not
> sure which versions of the driver are there.
>
> One thing I did notice, is that DbVisualizer doesn't like the JDBC
> drivers for PostgreSQL 7.1.2 (which are supposed to work with any 7.1.x
> release), but it definitely DID like the JDBC drivers compiled with
> PostgreSQL 7.1.3. That's my reason behind recommending compiling
> PostgreSQL 7.1.3 yourself, as the JDBC drivers are compiled along with
> it (./configure --enable-java if you need to know the command for the
> source archive).
>
> Hope all that's helpful.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
> Roger Bjärevall wrote:
> >
> > Justin,
> >
> > If you could point me to a place where I can download the latest
> > RPM's and JDBC driver for Linux RedHat then I will give it a try
> > in a couple of days.
> >
> > Regards
> >
> > Roger
> >
> > Justin Clift wrote:
> > >
> > > Hi,
> > >
> > > Would you guys like some assistance getting DbVisualizer working with
> > > PostgreSQL? The only thing I can see that is missing is the recognition
> > > of Referential Integrity relationships in PostgreSQL databases.
> > >
> > > :-)
> > >
> > > Regards and best wishes,
> > >
> > > Justin Clift
> > >
> > > --
> > > "My grandfather once told me that there are two kinds of people: those
> > > who work and those who take the credit. He told me to try to be in the
> > > first group; there was less competition there."
> > > - Indira Gandhi
> >
> > --
> > Roger Bjärevall
> > Product Manager
> >
> > Innovative-IT
> > Södermannagatan 48
> > Box 20129
> > SE-104 60 Stockholm
> > Phone: +46 8 55602760
> > Direct: +46 8 55602775
> > Mobile: +46 709 242012
> > roger(dot)bjarevall(at)ideit(dot)com
> > http://www.ideit.com
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi

--
Roger Bjärevall
Product Manager

Innovative-IT
Södermannagatan 48
Box 20129
SE-104 60 Stockholm
Phone: +46 8 55602760
Direct: +46 8 55602775
Mobile: +46 709 242012
roger(dot)bjarevall(at)ideit(dot)com
http://www.ideit.com

On Sat, 01 Sep 2001 21:26:52 +1000, Justin Clift wrote:
>Does anyone know if we're planning on adding the Java
>class/function/method/whatever called getExportedKeys?
...
>In discussing this with Roger (the Product Manager of Innovative-IT who
>has created this program), he says our JDBC driver gives a
>Driver.notImplemented() exception when trying to find out the RI info.
>(Email history detailed below).

He's right. The DatabaseMetaData.getExportedKeys() method is
simply not yet implemented, both in the jdb1 and the jdbc2
driver. Its open for development.

If someone can write the query on the system catalogs I'll be
glad to implement the Java code and a test case. The
documentation of the info we need is available on
http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
(look for getExportedKeys).

Regards,
René Pijlman <rene(at)lab(dot)applinet(dot)nl>

Hi all.

> He's right. The DatabaseMetaData.getExportedKeys() method is
> simply not yet implemented, both in the jdb1 and the jdbc2
> driver. Its open for development.
>
> If someone can write the query on the system catalogs I'll be
> glad to implement the Java code and a test case. The
> documentation of the info we need is available on
> http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
> (look for getExportedKeys).

An initial version for get(Imported|Exported)Keys and getCrossReference is
available in jxDBCon (http://jxbdcon.sf.net) the file can be extracted
automatically from CVS under
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/~checkout~/jxdbcon/src/org/sourceforge/jxdbcon/postgresql/PGDatabaseMetaData.java?rev=1.21&content-type=text/plain
Feel free to use it for a quick start.

Initial means:
- not tested
- don't like its algorithm
-> needs to be improved
But nevertheless it works.

Regargs,

Keve

Hi,

I've tried the jxdbcon driver now and it gives another view
of the actual database... I am not a PostgreSQL guy so I am
quite confused here. Anyway, this one seems to give the most
appropriate view of the database since the system tables
are listed which the pgsql drivers did not list. However, I do not
get anything back from any table when issuing the getExported/Imported
keys in DatabaseMetaData. This might be an effect of me not
having any tables with primary-foreign key mappings. Could anyone
point me to a place where I can download/get some simple SQL
statements to create at least relationsships between two tables?

I really would like to verify DbVisualizer with PostgreSQL since
more and more people requires support for it.

Best regards

Roger

Keve Müller wrote:
>
> Hi all.
>
> > He's right. The DatabaseMetaData.getExportedKeys() method is
> > simply not yet implemented, both in the jdb1 and the jdbc2
> > driver. Its open for development.
> >
> > If someone can write the query on the system catalogs I'll be
> > glad to implement the Java code and a test case. The
> > documentation of the info we need is available on
> > http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
> > (look for getExportedKeys).
>
> An initial version for get(Imported|Exported)Keys and getCrossReference is
> available in jxDBCon (http://jxbdcon.sf.net) the file can be extracted
> automatically from CVS under
> http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/~checkout~/jxdbcon/src/org/sourceforge/jxdbcon/postgresql/PGDatabaseMetaData.java?rev=1.21&content-type=text/plain
> Feel free to use it for a quick start.
>
> Initial means:
> - not tested
> - don't like its algorithm
> -> needs to be improved
> But nevertheless it works.
>
> Regargs,
>
> Keve

* Keve Müller <keve(at)keve(dot)hu> wrote:
|
| An initial version for get(Imported|Exported)Keys and getCrossReference is
| available in jxDBCon (http://jxbdcon.sf.net) the file can be extracted

Do you only concentrate on compliance or is performance important as well ?

Have you done any performance test of your driver vs. the driver shipped
with the PostgreSQL distribution ?

There was also another driver posted here for PostgreSQL about 1.5 years
ago by Sverre Huseby, that had interesting performance characteristics.

On Sat, 01 Sep 2001 17:43:39 +0200, you wrote:
>I've tried the jxdbcon driver now and it gives another view
>of the actual database... I am not a PostgreSQL guy so I am
>quite confused here. Anyway, this one seems to give the most
>appropriate view of the database since the system tables
>are listed which the pgsql drivers did not list.

Yes, I noticed that DatabaseMetaData.getColumns() in the
standard PostgreSQL driver returns the columns of the system
tables, whereas getTables() does not return those tables. I
guess that should be fixed. So documented on
http://lab.applinet.nl/postgresql-jdbc/

>Could anyone point me to a place where I can download/get some
>simple SQL statements to create at least relationsships between
>two tables?

See the section "Reference constraint" on
http://www.postgresql.org/users-lounge/docs/7.1/reference/sql-createtable.html

Regards,
René Pijlman <rene(at)lab(dot)applinet(dot)nl>

Hi René,

Did you see my email to the JDBC mailing list earlier today, in response
to someone question regarding finding out RI information from the
PostgreSQL system catalogs?

From that email, the SQL needed to find out the RI triggers is :

SELECT c.relname as "Trigger Table",
substr(f.proname, 9) as "Trigger Function",
t.tgconstrname as "Constraint Name",
c2.relname as "Constraint Table",
t.tgdeferrable as "Deferrable?",
t.tginitdeferred as "Initially Deferred?",
t.tgargs as "Trigger Arguments"
FROM pg_trigger t,
pg_class c,
pg_class c2,
pg_proc f
WHERE t.tgrelid=c.oid
AND t.tgconstrrelid=c2.oid
AND tgfoid=f.oid
AND t.tgenabled = 't'
AND tgname ~ '^RI_'
ORDER BY t.oid;

The returned "Trigger Arguments" column is a bytea field, and you'll
need to post-process this in the driver and extract the correct field
names (shouldn't be too hard).

The only other thing you'll probably need to do, apart from the
necessary JDBC driver related stuff, is do something with the "Trigger
Function" column. It shows what the action is, and what the causing
event is. i.e. "noaction_upd" shows a trigger for taking no action on
updates, "check_ins" shows a trigger which checks inserts (for UNIQUE
columns I think), etc.

Hope this is what you want.

:-)

Regards and best wishes,

Justin Clift

Rene Pijlman wrote:
>
> On Sat, 01 Sep 2001 21:26:52 +1000, Justin Clift wrote:
> >Does anyone know if we're planning on adding the Java
> >class/function/method/whatever called getExportedKeys?
> ...
> >In discussing this with Roger (the Product Manager of Innovative-IT who
> >has created this program), he says our JDBC driver gives a
> >Driver.notImplemented() exception when trying to find out the RI info.
> >(Email history detailed below).
>
> He's right. The DatabaseMetaData.getExportedKeys() method is
> simply not yet implemented, both in the jdb1 and the jdbc2
> driver. Its open for development.
>
> If someone can write the query on the system catalogs I'll be
> glad to implement the Java code and a test case. The
> documentation of the info we need is available on
> http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
> (look for getExportedKeys).
>
> Regards,
> René Pijlman <rene(at)lab(dot)applinet(dot)nl>

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi