Re: JDBC driver - first impressions and comments

On Wed, 4 Dec 2002, j.random.programmer wrote:

> 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.

Barry Lind and Dave Cramer are two developers with CVS commit rights who
focus solely on the JDBC driver.  The main contact point for development
requests and bug reports is this list which is actively followed by a
significant number of developers and users.

> 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.

The driver is what it is.  People use it in production environments all
the time.  I don't know what other options are out there, but the only way
you'll know if it's suitable for your production environment is by doing
testing which it appears you have done.

> 3) I am getting exceptions when I call the following
> methods:
>
> md.supportsSavepoints()
> md.supportsGetGeneratedKeys()

These are JDBC3 methods which do not have support in the driver yet.
They have been added solely to allow the JDBC driver to be compiled under
the 1.4 JDK

> 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.

The smallint and integer types are simply aliases for the int2 and int4
types which are resolved upon table creation.  The spec does indicate that
this is a data source dependent type name.  Perhaps you could use
DATA_TYPE for your application.


> 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].

For case sensitivity see storesXXXXXCaseIdentifiers() where XXXXX is one
of Upper,Lower,Mixed.

For column ordering, if you are referring to getColumns(), I have already
supplied a patch to fix this.

> 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).

This is a known issue related to the complexity of retrieving the desired
info.  I am working on a solution to this.

> 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 !

Tables always have a first class type associated with them consisting of a
field for each column, so they are in fact types.

>
> 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.

The database server itself does not support changing the database on a
given connection.  This is related to how the backend starts up and is not
likely to change anytime soon.  You must use multiple connections to
connect to multiple databases.  With 7.3 and the addition of schemas this
problem is not so severe as it once was.


> 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".

I believe "crypt" is the correct setting for MD5 passwords.


Kris Jurka