for the changed files and a few new files:
- test/jdbc2/BatchExecuteTest.java
- util/MessageTranslator.java
- jdbc2/PBatchUpdateException.java

As an aside, is this the best way to submit a patch consisting
of both changed and new files? Or is there a smarter cvs command
which gets them all in one patch file?

This patch fixes batch processing in the JDBC driver to be
JDBC-2 compliant. Specifically, the changes introduced by this
patch are:

1) Statement.executeBatch() no longer commits or rolls back a
transaction, as this is not prescribed by the JDBC spec. Its up
to the application to disable autocommit and to commit or
rollback the transaction. Where JDBC talks about "executing the
statements as a unit", it means executing the statements in one
round trip to the backend for better performance, it does not
mean executing the statements in a transaction.

2) Statement.executeBatch() now throws a BatchUpdateException()
as required by the JDBC spec. The significance of this is that
the receiver of the exception gets the updateCounts of the
commands that succeeded before the error occurred. In order for
the messages to be translatable, java.sql.BatchUpdateException
is extended by org.postgresql.jdbc2.PBatchUpdateException() and
the localization code is factored out from
org.postgresql.util.PSQLException to a separate singleton class
org.postgresql.util.MessageTranslator.

3) When there is no batch or there are 0 statements in the batch
when Statement.executeBatch() is called, do not throw an
SQLException, but silently do nothing and return an update count
array of length 0. The JDBC spec says "Throws an SQLException if
the driver does not support batch statements", which is clearly
not the case. See testExecuteEmptyBatch() in
BatchExecuteTest.java for an example. The message
postgresql.stat.batch.empty is removed from the language
specific properties files.

4) When Statement.executeBatch() is performed, reset the
statement's list of batch commands to empty. The JDBC spec isn't
100% clear about this. This behaviour is only documented in the
Java tutorial
(http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html).
Note that the Oracle JDBC driver also resets the statement's
list in executeBatch(), and this seems the most reasonable
interpretation.

5) A new test case is added to the JDBC test suite which tests
various aspects of batch processing. See the new file
BatchExecuteTest.java.

Regards,
Ren? Pijlman

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

public ResultSet getTables(String catalog, String schemaPattern, String

Jeroen van Vianen

Ola Sundell

Fri Feb 17 15:11:00 GMT 2001 peter@retep.org.uk
        - Reduced the object overhead in PreparedStatement by reusing the same
          StringBuffer object throughout. Similarly SimpleDateStamp's are alse
          reused in a thread save manner.
        - Implemented in PreparedStatement: setNull(), setDate/Time/Timestamp
          using Calendar, setBlob(), setCharacterStream()
        - Clob's are now implemented in ResultSet & PreparedStatement!
        - Implemented a lot of DatabaseMetaData & ResultSetMetaData methods.
          We have about 18 unimplemented methods left in JDBC2 at the current
          time.

Tue Feb 13 16:33:00 GMT 2001 peter@retep.org.uk
        - More TestCases implemented. Refined the test suite api's.
        - Removed need for SimpleDateFormat in ResultSet.getDate() improving
          performance.
        - Rewrote ResultSet.getTime() so that it uses JDK api's better.

Tue Feb 13 10:25:00 GMT 2001 peter@retep.org.uk
        - Added MiscTest to hold reported problems from users.
        - Fixed PGMoney.
        - JBuilder4/JDBCExplorer now works with Money fields. Patched Field &
          ResultSet (lots of methods) for this one. Also changed cash/money to
          return type DOUBLE not DECIMAL. This broke JBuilder as zero scale
          BigDecimal's can't have decimal places!
        - When a Statement is reused, the previous ResultSet is now closed.
        - Removed deprecated call in ResultSet.getTime()

Thu Feb 08 18:53:00 GMT 2001 peter@retep.org.uk
        - Changed a couple of settings in DatabaseMetaData where 7.1 now
          supports those features
        - Implemented the DatabaseMetaData TestCase.

Wed Feb 07 18:06:00 GMT 2001 peter@retep.org.uk
        - Added comment to Connection.isClosed() explaining why we deviate from
          the JDBC2 specification.
        - Fixed bug where the Isolation Level is lost while in autocommit mode.
        - Fixed bug where several calls to getTransactionIsolationLevel()
          returned the first call's result.

Anders Bengtsson

the versioning works. There's also a new utils directory used by Makefile

couldn't produce a full patch using cvs diff -c this time since I have
created new files and anonymous cvs usage doesn't allow you to
adds. I'm supplying the modified src/interfaces/jdbc as a tarball at :
http://www.candleweb.no/~gunnar/projects/pgsql/postgres-jdbc-2000-10-05.tgz

The new files that should be added are :

? org/postgresql/PGStatement.java
? org/postgresql/ObjectPool.java
? org/postgresql/ObjectPoolFactory.java

There is now a global static pool of free byte arrays and used byte arrays
connected to a statement object. This is the role of the new PGStatement
class. Access to the global free array is synchronized, while we rely on
the PG_Stream synchronization for the used array.

My measurements show that the perfomance boost on this code is not quite as
big as my last shot, but it is still an improvement. Maybe some of the
difference is due to the new synchronization on the global array. I think I
will look into choosing between on a connection level and global level.

I have also started experimented with improving the performance of the
various conversions. The problem here is ofcourse related handle the
various encodings. One thing I found to speed up ResultSet.getInt() a lot
was to do custom conversion on the byte array into int instead of going
through the getString() to do the conversion. But I'm unsure if this is
portable, can we assume that a digit never can be represented by more than
one byte ? It works fine in my iso-latin-8859-1 environment, but what about
other environments ? Maybe we could provide different ResultSet
implementations depending on the encoding used or delegate some methods of
the result set to an "converter class".

Check the org/postgresql/jdbc2/FastResultSet.java in the tarball above to
see the modified getInt() method.

Regards,

        Gunnar

one-line change necessary. Due to the Mark Holloman "New Relkind for
Views" patch, my support for views in the driver will need to be updated
to match. The change to DatabaseMetaData.getTableTypes[][] is as
follows:

-    {"VIEW",           "(relkind='r' and relhasrules='t' and relname !~
'^pg_' and relname !~ '^xinv')"},
+    {"VIEW",           "(relkind='v' and relname !~ '^pg_' and relname
!~ '^xinv')"},

Christopher Cain

ALTER TABLE <tablename> OWNER TO <username>

Only a superuser may execute the command.

--
Mark Hollomon
mhh@mindspring.com

This is a patch which lets the DatabaseMetaData return the object type
when getTables(....) is called.  It does not really fix any bug, but it
fills in some functionality that should be there anyway.  The diff
included here is off of the CVS as of just now :)

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

encountered while getting my reporting tool up and running with the
driver. All changes are in the DatabaseMetaData class.

Problem: The getDatabaseProductVersion() method was returning "6.5.2"
Resolution: Changed it to return "7.0.2"

Problem: A call to getTables() with an unsupported table type (in the
String array) resulted in a malformed SQL statement and subsequent
parsing error
Resolution: Unsupported table types are now ignored without error

Problem: In a getTables() call, tables and views were both returned by
the "TABLE" table type, and the "VIEW" table type was unsupported
Resolution: Changed the "TABLE" type to return only physical tables and
added support for the "VIEW" table type (returning only views)

Problem: The getIdentifierQuoteString() method was returning null
Resolution: This method now returns a double-quote

Christopher Cain

into it.

file containing the latest version of the JDBC driver, allowing it to be
compiled and used under JDK 1.2 and later.

NB: None (well almost none) of the new methods actually do anything. This
release only handles getting it to compile and run. Now this is done, I'll
start working on implementing the new stuff.

Now this tar file replaces everything under src/interfaces/jdbc. I had to
do it this way, rather than diffs, because most of the classes under the
postgresql subdirectory have moved to a new directory under that one, to
enable the support of the two JDBC standards.

Here's a list of files in the tar file. Any file not listed here (in the
postgresql directory) will have to be deleted, otherwise it could cause
the driver to fail:

Peter Mount

From: Peter T Mount <patches@maidast.demon.co.uk>

Bug fixes:

        PreparedStatement.setObject didn't handle short's

        ResultSet.getDate() now handles null dates (returns null rather
        than a NullPointerException)

        ResultSetMetaData.getPrecision() now returns 0 for VARCHAR

New features:

        Field now caches the typename->oid in a Hashtable to speed things
        up. It removes the need for some unnecessary queries to the
        backend.

        PreparedStatement.toString() now returns the sql statement that
        it will send to the backend. Before it did nothing.

        DatabaseMetaData.getTypeInfo() now does something.

1) DatabaseMetaData.getPrimaryKeys() would fail saying that there
is no
   table t.

2) PreparedStatement.getObject() was missing some break statements,
which
   was causing updates not to work with JBuilder (supplied by Aaron
   Dunlop).


jdbc fixes from Peter.

of -1 for varchar's.

From: CNT systemen BV <cntsys@cistron.nl>