This concludes my changes that restructured the code to support JDBC3.
The jdbc unit tests were also resturctured to allow different tests between
jdbc2 and jdbc3, although currently make check (aka ant test) for JDBC3 just
runs the JDBC2 tests.  Of special note the largeobject/PGblob and PGclob
classes have been moved under the jdbc2/jdbc3 specific directories as they
now differ by jdbc version.  Also note that this checkin removes the
PostgresqlDataSource and files in the xa directory.  A recent checkin has
added new datasource support that replaces the functionality provided by these
classes.

 Modified Files:
 	jdbc/build.xml
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
 	jdbc/org/postgresql/jdbc2/Array.java
 	jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/Jdbc2DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/Jdbc2PreparedStatement.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Statement.java
 	jdbc/org/postgresql/test/jdbc2/BatchExecuteTest.java
 	jdbc/org/postgresql/test/jdbc2/BlobTest.java
 	jdbc/org/postgresql/test/jdbc2/CallableStmtTest.java
 	jdbc/org/postgresql/test/jdbc2/ConnectionTest.java
 	jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java
 	jdbc/org/postgresql/test/jdbc2/DateTest.java
 	jdbc/org/postgresql/test/jdbc2/DriverTest.java
 	jdbc/org/postgresql/test/jdbc2/JBuilderTest.java
 	jdbc/org/postgresql/test/jdbc2/MiscTest.java
 	jdbc/org/postgresql/test/jdbc2/ResultSetTest.java
 	jdbc/org/postgresql/test/jdbc2/TimeTest.java
 	jdbc/org/postgresql/test/jdbc2/TimestampTest.java
 	jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
 Added Files:
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Blob.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Clob.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Blob.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Clob.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3Blob.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3Clob.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3Connection.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
 	jdbc/org/postgresql/jdbc3/AbstractJdbc3Statement.java
 	jdbc/org/postgresql/jdbc3/Jdbc3Blob.java
 	jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java
 	jdbc/org/postgresql/jdbc3/Jdbc3Clob.java
 	jdbc/org/postgresql/jdbc3/Jdbc3Connection.java
 	jdbc/org/postgresql/jdbc3/Jdbc3DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java
 	jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java
 	jdbc/org/postgresql/jdbc3/Jdbc3ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc3/Jdbc3Statement.java
 	jdbc/org/postgresql/test/TestUtil.java
 	jdbc/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
 	jdbc/org/postgresql/test/jdbc3/Jdbc3TestSuite.java
 Removed Files:
 	jdbc/org/postgresql/PostgresqlDataSource.java
 	jdbc/org/postgresql/largeobject/PGblob.java
 	jdbc/org/postgresql/largeobject/PGclob.java
 	jdbc/org/postgresql/test/JDBC2Tests.java
 	jdbc/org/postgresql/xa/ClientConnection.java
 	jdbc/org/postgresql/xa/TwoPhaseConnection.java
 	jdbc/org/postgresql/xa/TxConnection.java
 	jdbc/org/postgresql/xa/XAConnectionImpl.java
 	jdbc/org/postgresql/xa/XADataSourceImpl.java

 Modified Files:
 	jdbc/org/postgresql/Driver.java.in
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
 	jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/Jdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
 Added Files:
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc1/Jdbc1DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/Jdbc1ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc2/Jdbc2DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSetMetaData.java
 Removed Files:
 	jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/ResultSetMetaData.java
 	jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/ResultSetMetaData.java

 Modified Files:
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
 	jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/Jdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
 	jdbc/org/postgresql/jdbc2/Array.java
 	jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
 Added Files:
 	jdbc/org/postgresql/jdbc1/Jdbc1CallableStatement.java
 	jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java
 Removed Files:
 	jdbc/org/postgresql/jdbc1/CallableStatement.java
 	jdbc/org/postgresql/jdbc2/CallableStatement.java
 	jdbc/org/postgresql/jdbc2/UpdateableResultSet.java

of duplicated code between the jdbc1 and jdbc2.  This checkin restructures
the code so that the duplication is removed so that the jdbc3 support
can be added without adding yet another copy of everything.  Also many
classes were renamed to avoid confusion with multiple different objects
having the same name.  The timestamp tests were also updated to add support
for testing timestamp without time zone in addition to timestamp with time zone

 Modified Files:
 	jdbc/Makefile jdbc/build.xml jdbc/example/ImageViewer.java
 	jdbc/example/basic.java jdbc/example/blobtest.java
 	jdbc/example/threadsafe.java
 	jdbc/org/postgresql/Driver.java.in
 	jdbc/org/postgresql/Field.java
 	jdbc/org/postgresql/core/QueryExecutor.java
 	jdbc/org/postgresql/fastpath/Fastpath.java
 	jdbc/org/postgresql/jdbc1/CallableStatement.java
 	jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc1/PreparedStatement.java
 	jdbc/org/postgresql/jdbc2/Array.java
 	jdbc/org/postgresql/jdbc2/CallableStatement.java
 	jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
 	jdbc/org/postgresql/jdbc2/PreparedStatement.java
 	jdbc/org/postgresql/jdbc2/UpdateableResultSet.java
 	jdbc/org/postgresql/largeobject/LargeObjectManager.java
 	jdbc/org/postgresql/largeobject/PGblob.java
 	jdbc/org/postgresql/largeobject/PGclob.java
 	jdbc/org/postgresql/test/jdbc2/BlobTest.java
 	jdbc/org/postgresql/test/jdbc2/ConnectionTest.java
 	jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java
 	jdbc/org/postgresql/test/jdbc2/TimestampTest.java
 	jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
 	jdbc/org/postgresql/util/Serialize.java
 Added Files:
 	jdbc/org/postgresql/PGConnection.java
 	jdbc/org/postgresql/PGStatement.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
 	jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
 	jdbc/org/postgresql/jdbc1/Jdbc1ResultSet.java
 	jdbc/org/postgresql/jdbc1/Jdbc1Statement.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
 	jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
 	jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
 	jdbc/org/postgresql/jdbc2/Jdbc2Statement.java
 Removed Files:
 	jdbc/org/postgresql/Connection.java
 	jdbc/org/postgresql/ResultSet.java
 	jdbc/org/postgresql/Statement.java
 	jdbc/org/postgresql/jdbc1/Connection.java
 	jdbc/org/postgresql/jdbc1/ResultSet.java
 	jdbc/org/postgresql/jdbc1/Statement.java
 	jdbc/org/postgresql/jdbc2/Connection.java
 	jdbc/org/postgresql/jdbc2/ResultSet.java
 	jdbc/org/postgresql/jdbc2/Statement.java

  Allows you to set the loglevel at runtime by adding ?loglevel=X to the connection URL, where 1 = INFO and 2 = DEBUG.
  Automatically turns on logging by calling DriverManager.setPrintWriter(new PrintWriter(System.out)) if one is not already set.
Adds a Driver.info() message that prints out the version number
Adds member variables logDebug and logInfo that can be checked before making logging methods calls
Adds a build number to the version number string.  This build number will need to be manually incremented when we see fit.

----------------------------------------------------------------------
Modified Files:
 	org/postgresql/Connection.java org/postgresql/Driver.java.in
 	org/postgresql/fastpath/Fastpath.java
 	org/postgresql/jdbc1/DatabaseMetaData.java
 	org/postgresql/jdbc2/Connection.java
 	org/postgresql/jdbc2/DatabaseMetaData.java
 	org/postgresql/largeobject/LargeObjectManager.java
 	org/postgresql/util/PSQLException.java
 	org/postgresql/util/Serialize.java
----------------------------------------------------------------------

fixes getIndexInfo throwing NullPointerException
fixes getIndexInfo improper results when multiple key indexs are used

Previous versions did not correctly identify primary/foreign keys

fixes for compiling Jason's getImportedKeys, getExportedKeys

applied patch from Mark Lillywhite, patch was already applied to jdbc2, this applies same fix to jdbc1 code

fix for a bug in DatabaseMetaData.getIndexInfo().  This fixes a bug reported by tom_falconer@lineone.net.  On Sept 7th, he sent a test case to the list demonstrating the bug.  His test case now works successfully with this patch

DatabaseMetaData.getColumn(). I proposed a patch that would change the
number of queries to find out all columns in a table from 2 * N + 1 to 1 (N
being the number of columns reported) by using some outer joins. I also
fixed the fact that getColumns() only returned columns that had a default
defined. OTOH, I did not use to change the code required for obtaining a
column's remarks (by using col_description() for 7.2  and requested by Tom
Lane).

Finally, I have found a way to get all the column details in a single query
*and* use col_description() for 7.2 servers. A patch is attached. It
overrules Ren? Pijlman's fix for this that was committed just today, but
still used N + 1 queries (sorry Ren? ;-) )

I also fixed the return values for TABLE_CAT and TABLE_SCHEM from "" to
null, to be more standard compliant (and requested in Ren?'s mail found at
http://fts.postgresql.org/db/mw/msg.html?mid=1034253).

As always, the JDBC1 version has not been tested as I have no JDK 1.1

Jeroen van Vianen

>there is still an unpatched reference to pg_description in
>getColumns(), in both jdbc1 and jdbc2.

This was introduced by Jeroen's patch (see
http://fts.postgresql.org/db/mw/msg.html?mid=1032468). Attached
is a patch that returns getColumns() to using "select
obj_description()" instead of direct access to pg_description,
as per the request by Tom.

I've incorporated Jeroen's fix to left outer join with
pg_attrdef instead of inner join, so getColumns() also returns
columns without a default value.

I have, however, not included Jeroen's attempt to combine
multiple queries into one huge multi-join query for better
performance, because:
1) I don't know how to do that using obj_description() instead
of direct access to pg_description
2) I don't think a performance improvement (if any) in this
method is very important

Because of the outer join, getColumns() will only work with a
backend >= 7.1. Since the conditional coding for 7.1/7.2 and
jdbc1/jdbc2 is already giving me headaches I didn't pursue a
pre-7.1 solution.

Regards,
Ren? Pijlman <rene@lab.applinet.nl>

Thanks. However, I seem to have left a single debug statement in there :-(

Here's a patch to remove it.

Vianen, Jeroen van

flawed in the following ways:

1. Only returned columns that had a default value defined, rather than all
columns in a table
2. Used 2 * N + 1 queries to find out attributes, comments and typenames
for N columns.

By using some outer join syntax it is possible to retrieve all necessary
information in just one SQL statement. This means this version is only
suitable for PostgreSQL >= 7.1. Don't know whether that's a problem.

I've tested this function with current sources and 7.1.3 and patched both
jdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, as
I have no JDK 1.1 available.

Note the discussion in http://fts.postgresql.org/db/mw/msg.html?mid=1029626
regarding differences in obtaining comments on database object in 7.1 and
7.2. I was unable to use the following syntax (or similar ones):

select
     ...,
     description
from
     ...
     left outer join col_description(a.attrelid, a.attnum) description
order by
     c.relname, a.attnum;

(the error was parse error at or near '(') so I had to paste the actual
code for the col_description function into the left outer join. Maybe
someone who is more knowledgable about outer joins might provide me with a
better SQL statement.

Jeroen van Vianen

system.  Some systems did not understand the 'l' section, and in general
it wasn't entirely appropriate.

On SCO OpenServer, the man pages won't be installed at all until someone
figures out their man system.

  This patch moves the logic that looks up TypeOid, PGTypeName, and
SQLTypeName from Field to Connection.  It is moved to connection since
it needs to differ from the jdbc1 to jdbc2 versions and Connection
already has different subclasses for the two driver versions.  It also
made sense to move the logic to Connection as some of the logic was
already there anyway.

Barry Lind

> Shouldn't
>
>    throw new PSQLException("metadata unavailable");
>
> in getTypeInfo() be something like:
>
>    throw new PSQLException("postgresql.meta.unavailable");
>
> to allow translation of the error message in the
> errors*.properties files?

You're right. Attached is an updated patch that also includes a message
in error.properties. I've attempted a French message in
errors_fr.properties but beware that I haven't written French in quite a
few years. Don't know Italian, German, or Dutch so I can't do those.

Liam Stewart

reflect a mail thread that discussed our conformance (or lack thereof)
to the SQL92 spec.

Barry Lind

includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 might break existing code that doesn't follow the JDBC
spec and isn't prepared to handle a null in the REMARKS column
of getTables()/getColumns()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
Ren? Pijlman

1) improves performance of commit/rollback by reducing number of round
trips to the server
2) uses 7.1 functionality for setting the transaction isolation level
3) backs out a patch from 11 days ago because that code failed to
compile under jdk1.1

Details:

1)  The old code was doing the following for each commit:
   commit
   begin
   set transaction isolation level xxx
thus a call to commit was performing three round trips to the database.
  The new code does this in one round trip as:
   commit; begin; set transaction isolation level xxx

In a simple test program that performs 1000 transactions (where each
transaction does one simple select inside that transaction) has the
following before and after timings:

Client and Server on same machine

old         new
---         ---
1.877sec    1.405sec   25.1% improvement

Client and Server on different machines
old         new
---         ---
4.184sec    2.927sec   34.3% improvement

(all timings are an average of four different runs)


2)  The driver was using 'set transaction isolation level xxx' at the
begining of each transaction, instead of using the new 7.1 syntax of
'set session characteristics as transaction isolation level xxx' which
only needs to be done once instead of for each transaction.  This is
done conditionally (i.e. if server is 7.0 or older do the old behaviour,
else do the new behaviour) to not break backward compatibility.  This
also required the movement of some code to check/test database version
numbers from the DatabaseMetaData object to the Connection object.

3) Finally while testing, I discovered that the code that was checked in
  11 days ago actually didn't compile.  The code in the patch for
Connection.setCatalog() used Properties.setProperty() which only exists
in JDK1.2 or higher.  Thus compiling the JDBC1 driver failed as this
method doesn't exist.  Thus I backed out that patch.


Barry Lind

value for each column. Here is a context diff of CVS which should
fix it.

Jason Davies

* NULLs are sorted differently in 7.2
* table correlation names are supported
* GROUP BY, ORDER BY unrelated is supported since 6.4
* ESCAPE/LIKE only supported since 7.1
* outer joins only since 7.1
* preferred term for procedure is "function"
* preferred term for catalog is "database"
* supports SELECT for UPDATE since 6.5
* supports subqueries
* supports UNION; supports UNION ALL since 7.1
* update some of the max lengths to match reality
* rearrange some functions to match the order in the spec
  for easier maintenance

Dave Cramer

Here's what I came up with. The biggest difference api between JDK1.x and
later versions is the support for collections. The problem was with the
Vector class; in jdk1.x there is no method called add, so I changed the
calls to addElement. Also no addAll, so I rewrote the method slightly to not
require addAll. While reviewing this I notices some System.out.println
statements that weren't commented out. So I commented them out in both
versions.

The upshot of all of this is that I have clean compile, but no idea if the
code works ;(

Dave Cramer

(http://www.ideit.com/products/dbvis/) to work with Postgresql and I found
out the following bug: if database has views then getTables() gets the null
pointer exception ('order by relname' makes the listing tree in
DbVisualizer a lot useful !!)

This patch should propably be applied to the the jdbc1's
DatabaseMetaData.java, too.

Panu Outinen