JDBC driver - first impressions and comments

Folks:

Having used mysql for some time, I decided to try
postgres 7.3. I thought I'd post some initial jdbc
specific comments and initial impressions. Some of the
issues that I mention may be known and/or fixed in
development versions of the driver; therefore please
treat this post as a "newbie's" point of view.

Let me start by saying that I am writing a java based
db object/abstraction layer that can examine any
database and generate various java objects an
interface to those objects. Automatic validation,
application caching etc., will also be provided. For
this code to work, I need to get a lot of meta-data
about the database, tables and fields.

**NOTE**
For purposes of discussion, "md" in the following
examples will refer to reference to a
DatabaseMetaData object.

I feel there is a definite difference between the
mysql JDBC driver and the postgres one. The postgres
driver is the lastest compiled driver for JDK 1.4
against postgres 7.3

1) postgres' JDBC does not *seem* to have a "owner" or
a person finally responsible for the driver. (at least
I haven't found this going thru the archives). This
simply may be how jdbc development is done by many
people but ultimately someone has to be responsible
for it. That somone then should be mentioned as the
contact person for all JDBC development requests and
bug reports.

2) The jdbc compliance test results page has not been
updated for quite some time. It's not clear how
compliant the latest drivers are and if they are ready
for serious production use.

3) I am getting exceptions when I call the following
methods:

md.supportsSavepoints()
md.supportsGetGeneratedKeys()

4) The md.getColumnInfo() returns "int2" and "int4"
for the TYPE_NAME field in the result set
(corresponding to columns defined as smallint and
integer). These should be "smallint" and "integer"
instead.

5) The md.getColumnInfo() returns a result set in
which the columns are sorted by column *name* and not
by the ORDINAL_POSITION field. This is opposite of the
JDBC spec which requires that columns be sorted by the
ORDINAL_POSITION field. If I create a table such as:

create table foo ( zoo int, abc int, baba timestamp);

I should get my columns in the following order: [zoo,
abc, baba] but getColumnInfo() returns them as: [abc,
baba, zoo]

Note, getColumnInfo() does return the correct
ORDINAL_POSITION data each column but sorts them by
alpha - which is wrong. Moreover, all returned
columns are in all-lowercase [this may be the correct
behavior - I don't know, but if I create a column
fooBAR, JDBC returns that as foobar].

5) The md.getImportedKeys() and md.getExportedKeys()
methods take 30-50 seconds to return. There is
something very strange going on (it's not the
connection itself, I am on a private LAN and other
jdbc methods return information immediately).

6) The md.getTypeInfo() method returns every single
tablename, sequence etc. as a type. This method should
only return types like "integer", "timestamp" etc, NOT
the name of every single table !

7) There is no way to change the catalog of the JDBC
connection. What I want is:

myconnection.setCatalog("template1")
get a connection to the "template1" database
then say: myconnection.setCatalog("foo")
get a connection to the "foo" database.

This does not seem to be possible with the postgres
driver. Why does the driver not treat a postgres
database as a "catalog" or least pretend to ? The
current state of affairs is a serious deficiency.

8) It would be nice if the driver supported an auto
reconnect feature. There is no documentation or readme
file that comes with the compiled driver that talks
about connection timeautos, auto-reconnecting
connections etc.

9) The MD5 documentation is missing and the JDBC
driver does not work if passwords are used on the
server. (if we choose password authentication, then
the latest 7.3 postgres release defaults to MD5 when I
compile it out of the box). Currently, I've been
unable to connect using "password" but can only
connect using "trust".

Best regards,

--j

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com