Thread: JDBC Statement.executeBatch patch

JDBC Statement.executeBatch patch

From
Rene Pijlman
Date:
Attached is a patch for current CVS, consisting of a cvs diff -c
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

Attachment

Re: [JDBC] JDBC Statement.executeBatch patch

From
Bruce Momjian
Date:
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> Attached is a patch for current CVS, consisting of a cvs diff -c
> 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

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC Statement.executeBatch patch

From
Barry Lind
Date:
Rene,

The patch looks good.  Thanks for your time and effort.

thanks,
--Barry

Rene Pijlman wrote:
> Attached is a patch for current CVS, consisting of a cvs diff -c
> 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
>
>
> ------------------------------------------------------------------------
>
> Index: src/interfaces/jdbc/org/postgresql/errors.properties
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors.properties,v
> retrieving revision 1.6
> diff -c -r1.6 errors.properties
> *** src/interfaces/jdbc/org/postgresql/errors.properties    2001/08/21 00:37:23    1.6
> --- src/interfaces/jdbc/org/postgresql/errors.properties    2001/08/26 18:20:15
> ***************
> *** 61,67 ****
>   postgresql.serial.noclass:No class found for {0}.
>   postgresql.serial.table:The table for {0} is not in the database. Contact the DBA, as the database is in an
inconsistentstate. 
>   postgresql.serial.underscore:Class names may not have _ in them. You supplied {0}.
> - postgresql.stat.batch.empty:The batch is empty. There is nothing to execute.
>   postgresql.stat.batch.error:Batch entry {0} {1} was aborted.
>   postgresql.stat.maxfieldsize:An attempt to setMaxFieldSize() failed - compile time default in force.
>   postgresql.stat.noresult:No results were returned by the query.
> --- 61,66 ----
> Index: src/interfaces/jdbc/org/postgresql/errors_de.properties
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors_de.properties,v
> retrieving revision 1.1
> diff -c -r1.1 errors_de.properties
> *** src/interfaces/jdbc/org/postgresql/errors_de.properties    2001/07/09 20:25:44    1.1
> --- src/interfaces/jdbc/org/postgresql/errors_de.properties    2001/08/26 18:20:15
> ***************
> *** 65,71 ****
>   postgresql.serial.noclass:Keine Klasse für Typ »{0}« gefunden
>   postgresql.serial.table:Keine Tabelle für Typ »{0}« in der Datenbank gefunden. Die Datenbank ist in einem
unbeständigenZustand. 
>   postgresql.serial.underscore:Zu serialisierende Klassennamen dürfen keine Unterstriche (_) enthälten. Der
angegebeneName war »{0}«. 
> - postgresql.stat.batch.empty:Der Anweisungs-Batch ist leer.
>   postgresql.stat.batch.error:Batch-Anweisung Nummer {0} ({1}) wurde abgebrochen.
>   postgresql.stat.maxfieldsize:setMaxFieldSize() is nicht möglich; die Grenze ist fest eingebaut.
>   postgresql.stat.noresult:Die Abfrage ergab kein Ergebnis.
> --- 65,70 ----
> Index: src/interfaces/jdbc/org/postgresql/errors_it.properties
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors_it.properties,v
> retrieving revision 1.1
> diff -c -r1.1 errors_it.properties
> *** src/interfaces/jdbc/org/postgresql/errors_it.properties    2000/10/12 08:55:24    1.1
> --- src/interfaces/jdbc/org/postgresql/errors_it.properties    2001/08/26 18:20:15
> ***************
> *** 60,66 ****
>   postgresql.serial.noclass:Nessuna classe trovata per {0}.
>   postgresql.serial.table:La tabella per {0} non è nel database. Contattare il DBA, visto che il database è in uno
statoincosistente. 
>   postgresql.serial.underscore:Il nome di una classe non può contenere il carattere ``_''. E` stato fornito {0}.
> - postgresql.stat.batch.empty:La sequenza di operazioni è vuota. Non c'è niente da eseguire.
>   postgresql.stat.batch.error:L'operazione {0} {1} della sequenza è stata annullata.
>   postgresql.stat.maxfieldsize:Fallito un tentativo a setMaxFieldSize() - verrà utilizzato il valore predefinito a
tempodi compilazione. 
>   postgresql.stat.noresult:Nessun risultato è stato restituito dalla query.
> --- 60,65 ----
> Index: src/interfaces/jdbc/org/postgresql/errors_nl.properties
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors_nl.properties,v
> retrieving revision 1.1
> diff -c -r1.1 errors_nl.properties
> *** src/interfaces/jdbc/org/postgresql/errors_nl.properties    2000/04/26 05:39:32    1.1
> --- src/interfaces/jdbc/org/postgresql/errors_nl.properties    2001/08/26 18:20:15
> ***************
> *** 54,60 ****
>   postgresql.serial.noclass:Geen class gevonden voor {0}.
>
>   postgresql.serial.table:De tabel voor {0} is niet in de database. Neem contact op met de DBA, omdat de database in
eeninconsistente staat verkeert. 
>
>   postgresql.serial.underscore:Class namen mogen geen _ in zich hebben. Jij voerde {0} in.
>
> - postgresql.stat.batch.empty:De batch is leeg. Er is niets om uit te voeren.
>
>   postgresql.stat.batch.error:Batch invoer {0} {1} werd afgebroken.
>
>   postgresql.stat.maxfieldsize:Een poging om setMaxFieldSize() faalde - compiletime standaardwaarde van kracht.
>
>   postgresql.stat.noresult:Geen resultaten werden teruggegeven door de query.
>
> --- 54,59 ----
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
> retrieving revision 1.31
> diff -c -r1.31 DatabaseMetaData.java
> *** src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java    2001/08/24 16:50:17    1.31
> --- src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java    2001/08/26 18:20:15
> ***************
> *** 2836,2842 ****
>       }
>
>       /**
> !      * New in 7.1 - If this is for PreparedStatement yes, ResultSet no
>        */
>       public boolean supportsBatchUpdates() throws SQLException
>       {
> --- 2836,2842 ----
>       }
>
>       /**
> !      * Indicates whether the driver supports batch updates.
>        */
>       public boolean supportsBatchUpdates() throws SQLException
>       {
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java,v
> retrieving revision 1.11
> diff -c -r1.11 Statement.java
> *** src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java    2001/08/10 14:42:07    1.11
> --- src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java    2001/08/26 18:20:17
> ***************
> *** 179,198 ****
>
>       public int[] executeBatch() throws SQLException
>       {
> !     if(batch==null || batch.isEmpty())
> !         throw new PSQLException("postgresql.stat.batch.empty");
> !
>       int size=batch.size();
>       int[] result=new int[size];
>       int i=0;
> -     this.execute("begin"); // PTM: check this when autoCommit is false
>       try {
>           for(i=0;i<size;i++)
>           result[i]=this.executeUpdate((String)batch.elementAt(i));
> -         this.execute("commit"); // PTM: check this
>       } catch(SQLException e) {
> !         this.execute("abort"); // PTM: check this
> !         throw new PSQLException("postgresql.stat.batch.error",new Integer(i),batch.elementAt(i));
>       }
>       return result;
>       }
> --- 179,204 ----
>
>       public int[] executeBatch() throws SQLException
>       {
> !     if(batch==null)
> !         batch=new Vector();
>       int size=batch.size();
>       int[] result=new int[size];
>       int i=0;
>       try {
>           for(i=0;i<size;i++)
>           result[i]=this.executeUpdate((String)batch.elementAt(i));
>       } catch(SQLException e) {
> !         int[] resultSucceeded = new int[i];
> !         System.arraycopy(result,0,resultSucceeded,0,i);
> !
> !         PBatchUpdateException updex =
> !             new PBatchUpdateException("postgresql.stat.batch.error",
> !                 new Integer(i), batch.elementAt(i), resultSucceeded);
> !         updex.setNextException(e);
> !
> !         throw updex;
> !     } finally {
> !         batch.removeAllElements();
>       }
>       return result;
>       }
> Index: src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java,v
> retrieving revision 1.4
> diff -c -r1.4 JDBC2Tests.java
> *** src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java    2001/07/21 18:52:11    1.4
> --- src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java    2001/08/26 18:20:17
> ***************
> *** 205,210 ****
> --- 205,214 ----
>       suite.addTestSuite(TimestampTest.class);
>
>       // PreparedStatement
> +     suite.addTestSuite(BatchExecuteTest.class);
> +
> +     // BatchExecute
> +
>
>       // MetaData
>
> Index: src/interfaces/jdbc/org/postgresql/util/PSQLException.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/util/PSQLException.java,v
> retrieving revision 1.4
> diff -c -r1.4 PSQLException.java
> *** src/interfaces/jdbc/org/postgresql/util/PSQLException.java    2001/01/25 09:16:36    1.4
> --- src/interfaces/jdbc/org/postgresql/util/PSQLException.java    2001/08/26 18:20:17
> ***************
> *** 2,9 ****
>
>   import java.io.*;
>   import java.sql.*;
> - import java.text.*;
> - import java.util.*;
>
>   /**
>    * This class extends SQLException, and provides our internationalisation handling
> --- 2,7 ----
> ***************
> *** 12,20 ****
>   {
>       private String message;
>
> -     // Cache for future errors
> -     static ResourceBundle bundle;
> -
>       /**
>        * This provides the same functionality to SQLException
>        * @param error Error string
> --- 10,15 ----
> ***************
> *** 86,122 ****
>       translate(error,argv);
>       }
>
> !     /**
> !      * This does the actual translation
> !      */
> !     private void translate(String id,Object[] args)
> !     {
> !     if(bundle == null) {
> !         try {
> !         bundle = ResourceBundle.getBundle("org.postgresql.errors");
> !         } catch(MissingResourceException e) {
> !                 // translation files have not been installed.
> !                 message = id;
> !         }
>       }
>
> -         if (bundle != null) {
> -     // Now look up a localized message. If one is not found, then use
> -     // the supplied message instead.
> -         message = null;
> -         try {
> -             message = bundle.getString(id);
> -         } catch(MissingResourceException e) {
> -             message = id;
> -         }
> -         }
> -
> -     // Expand any arguments
> -     if(args!=null && message != null)
> -         message = MessageFormat.format(message,args);
> -
> -     }
> -
>       /**
>        * Overides Throwable
>        */
> --- 81,90 ----
>       translate(error,argv);
>       }
>
> !     private void translate(String error, Object[] args) {
> !         message = MessageTranslator.translate(error,args);
>       }
>
>       /**
>        * Overides Throwable
>        */
> ***************
> *** 140,144 ****
>       {
>       return message;
>       }
> -
>   }
> --- 108,111 ----
>
>
> ------------------------------------------------------------------------
>
> package org.postgresql.test.jdbc2;
>
> import org.postgresql.test.JDBC2Tests;
> import junit.framework.TestCase;
> import java.sql.*;
>
> /**
>  * Test case for Statement.batchExecute()
>  */
> public class BatchExecuteTest extends TestCase {
>
>     private Connection con;
>     private Statement stmt;
>
>     public BatchExecuteTest(String name) {
>         super(name);
>     }
>
>     // Set up the fixture for this testcase: a connection to a database with
>     // a table for this test.
>     protected void setUp() throws Exception {
>         con = JDBC2Tests.openDB();
>         stmt = con.createStatement();
>
>         // Drop the test table if it already exists for some reason. It is
>         // not an error if it doesn't exist.
>         try {
>             stmt.executeUpdate("DROP TABLE testbatch");
>         } catch (SQLException e) {
>              // Intentionally ignore. We cannot distinguish "table does not
>              // exist" from other errors, since PostgreSQL doesn't support
>              // error codes yet.
>         }
>
>         stmt.executeUpdate("CREATE TABLE testbatch(pk INTEGER, col1 INTEGER)");
>         stmt.executeUpdate("INSERT INTO testbatch VALUES(1, 0)");
>
>         // Generally recommended with batch updates. By default we run all
>         // tests in this test case with autoCommit disabled.
>         con.setAutoCommit(false);
>     }
>
>     // Tear down the fixture for this test case.
>     protected void tearDown() throws Exception {
>         con.setAutoCommit(true);
>         if (stmt != null) {
>             stmt.executeUpdate("DROP TABLE testbatch");
>             stmt.close();
>         }
>         if (con != null) {
>               JDBC2Tests.closeDB(con);
>         }
>     }
>
>     public void testSupportsBatchUpdates() throws Exception {
>         DatabaseMetaData dbmd = con.getMetaData();
>         assertTrue(dbmd.supportsBatchUpdates());
>     }
>
>     private void assertCol1HasValue(int expected) throws Exception {
>         Statement getCol1 = con.createStatement();
>
>         ResultSet rs =
>             getCol1.executeQuery("SELECT col1 FROM testbatch WHERE pk = 1");
>         assertTrue(rs.next());
>
>         int actual = rs.getInt("col1");
>
>         assertEquals(expected, actual);
>
>         assertEquals(false, rs.next());
>
>         rs.close();
>         getCol1.close();
>     }
>
>     public void testExecuteEmptyBatch() throws Exception {
>         int[] updateCount = stmt.executeBatch();
>         assertEquals(0,updateCount.length);
>
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 1 WHERE pk = 1");
>         stmt.clearBatch();
>         updateCount = stmt.executeBatch();
>         assertEquals(0,updateCount.length);
>     }
>
>     public void testClearBatch() throws Exception {
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 1 WHERE pk = 1");
>         assertCol1HasValue(0);
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 2 WHERE pk = 1");
>         assertCol1HasValue(0);
>         stmt.clearBatch();
>         assertCol1HasValue(0);
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 4 WHERE pk = 1");
>         assertCol1HasValue(0);
>         stmt.executeBatch();
>         assertCol1HasValue(4);
>         con.commit();
>         assertCol1HasValue(4);
>     }
>
>     public void testSelectThrowsException() throws Exception {
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 1 WHERE pk = 1");
>         stmt.addBatch("SELECT col1 FROM testbatch WHERE pk = 1");
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 2 WHERE pk = 1");
>
>         try {
>             stmt.executeBatch();
>             fail("Should raise a BatchUpdateException because of the SELECT");
>         } catch (BatchUpdateException e) {
>             int [] updateCounts = e.getUpdateCounts();
>             assertEquals(1,updateCounts.length);
>             assertEquals(1,updateCounts[0]);
>         } catch (SQLException e) {
>             fail( "Should throw a BatchUpdateException instead of " +
>                   "a generic SQLException: " + e);
>         }
>     }
>
>     public void testPreparedStatement() throws Exception {
>         PreparedStatement pstmt = con.prepareStatement(
>             "UPDATE testbatch SET col1 = col1 + ? WHERE PK = ?" );
>
>         // Note that the first parameter changes for every statement in the
>         // batch, whereas the second parameter remains constant.
>         pstmt.setInt(1,1);
>         pstmt.setInt(2,1);
>         pstmt.addBatch();
>         assertCol1HasValue(0);
>
>         pstmt.setInt(1,2);
>         pstmt.addBatch();
>         assertCol1HasValue(0);
>
>         pstmt.setInt(1,4);
>         pstmt.addBatch();
>         assertCol1HasValue(0);
>
>         pstmt.executeBatch();
>         assertCol1HasValue(7);
>
>         con.commit();
>         assertCol1HasValue(7);
>
>         con.rollback();
>         assertCol1HasValue(7);
>
>         pstmt.close();
>     }
>
>     /**
>     */
>     public void testTransactionalBehaviour() throws Exception {
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 1 WHERE pk = 1");
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 2 WHERE pk = 1");
>         stmt.executeBatch();
>         con.rollback();
>         assertCol1HasValue(0);
>
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 4 WHERE pk = 1");
>         stmt.addBatch("UPDATE testbatch SET col1 = col1 + 8 WHERE pk = 1");
>
>         // The statement has been added to the batch, but it should not yet
>         // have been executed.
>         assertCol1HasValue(0);
>
>         int[] updateCounts = stmt.executeBatch();
>         assertEquals(2,updateCounts.length);
>         assertEquals(1,updateCounts[0]);
>         assertEquals(1,updateCounts[1]);
>
>         assertCol1HasValue(12);
>         con.commit();
>         assertCol1HasValue(12);
>         con.rollback();
>         assertCol1HasValue(12);
>     }
> }
>
> /* TODO tests that can be added to this test case
>    - SQLExceptions chained to a BatchUpdateException
>    - test PreparedStatement as thoroughly as Statement
>  */
>
>
> ------------------------------------------------------------------------
>
> package org.postgresql.util;
>
> import java.util.*;
> import java.text.*;
>
> /**
>  * A singleton class to translate JDBC driver messages in SQLException's.
>  */
> public class MessageTranslator {
>
>     // The singleton instance.
>     private static MessageTranslator instance = null;
>
>     private ResourceBundle bundle;
>
>     private MessageTranslator() {
>         try {
>             bundle = ResourceBundle.getBundle("org.postgresql.errors");
>         } catch(MissingResourceException e) {
>             // translation files have not been installed.
>             bundle = null;
>         }
>     }
>
>     // Synchronized, otherwise multiple threads may perform the test and
>     // assign to the singleton instance simultaneously.
>     private synchronized final static MessageTranslator getInstance() {
>         if (instance == null) {
>             instance = new MessageTranslator();
>         }
>         return instance;
>     }
>
>     public final static String translate(String id, Object[] args) {
>
>         MessageTranslator translator = MessageTranslator.getInstance();
>
>         return translator._translate(id, args);
>     }
>
>     private final String _translate(String id, Object[] args) {
>         String message;
>
>         if (bundle != null && id != null) {
>             // Now look up a localized message. If one is not found, then use
>             // the supplied message instead.
>             try {
>                 message = bundle.getString(id);
>             } catch(MissingResourceException e) {
>                 message = id;
>             }
>         } else {
>             message = id;
>         }
>
>         // Expand any arguments
>         if (args != null && message != null) {
>             message = MessageFormat.format(message,args);
>         }
>
>         return message;
>     }
> }
>
>
> ------------------------------------------------------------------------
>
> package org.postgresql.jdbc2;
>
> import org.postgresql.util.*;
> import java.sql.*;
>
> /**
>  * This class extends java.sql.BatchUpdateException, and provides our
>  * internationalisation handling.
>  */
> class PBatchUpdateException extends java.sql.BatchUpdateException {
>
>     private String message;
>
>     public PBatchUpdateException(
>         String error, Object arg1, Object arg2, int[] updateCounts ) {
>
>         super(updateCounts);
>
>         Object[] argv = new Object[2];
>         argv[0] = arg1;
>         argv[1] = arg2;
>         translate(error,argv);
>     }
>
>     private void translate(String error, Object[] args) {
>         message = MessageTranslator.translate(error,args);
>     }
>
>     // Overides Throwable
>     public String getLocalizedMessage()
>     {
>         return message;
>     }
>
>     // Overides Throwable
>     public String getMessage()
>     {
>         return message;
>     }
>
>     // Overides Object
>     public String toString()
>     {
>         return message;
>     }
> }
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> patch.diff
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> BatchExecuteTest.java
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> MessageTranslator.java
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> PBatchUpdateException.java
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> Part 1.6
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>



Re: [JDBC] JDBC Statement.executeBatch patch

From
Bruce Momjian
Date:
Patch applied and files added.  Thanks.

> Attached is a patch for current CVS, consisting of a cvs diff -c
> 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

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026