Re: Return Codes of BatchUpdateException in PostgreSql 9.6 - Mailing list pgsql-jdbc

Hi,

thanks for your response.

Meanwhile I found the issue, that causes my problem:

    https://github.com/pgjdbc/pgjdbc/issues/502

So the question is "if the driver should mark all the rows in int[] executeBatch() with Statement.EXECUTE_FAILED" when
asingle statement fails. 
Other drivers like oracle-jdbc work the same way like postgres does before issue #502 was released.

The fix would be to remove the code of issue #502 from the driver.

As next step for me I will create an issue on github.

Bye Tillmann









----- Ursprüngliche Message -----
Von: Jeremy Whiting <jwhiting@redhat.com>
An: Tillmann Schulz <tillmann73@yahoo.de>; "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Gesendet: 11:18 Donnerstag, 20.Oktober 2016
Betreff: Re: [JDBC] Return Codes of BatchUpdateException in PostgreSql 9.6

Hi Tillmann,
  Thank you for providing the test case. I suggest you take a look at
this class [1] in the testsuite. It may answer your question on what to
expect in updateCount.

  If you question is not answered and the tests in that class do not
exercise what is provided in your test case then I suggest the following:

a) Create an issue [2] on Github.
b) Fork the pgjdbc project.
c) Add your contributed test into [1] as a new test method. Even better
is to include a fix along with the test case.
d) Create a Pull Request (PR) to have your test merged into master. Also
update your created issue with details of the PR. So the two are linked.

  This way the project can ensure this problem you are seeing does not
reoccur again in the future. If it is indeed a defect.

Regards,
Jeremy

[1]
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java
[2]  https://github.com/pgjdbc/pgjdbc/issues


On 19/10/16 12:49, Tillmann Schulz wrote:
> Hi,
>
> thanks for the response.
>
>
>
> I have a simple test program which first creates a testtable, fills it with one record and does a batch insert of 10
records.
>
> The 5th insert causes a BatchUpdateException at stmt.executeBatch() when the data is NOT commited.
>
> The question is, which return codes should be in x.getUpdateCounts().
>
>
> For better reading I placed the test programm on      http://pastebin.com/0P1S6zEc
>
>
>
> Bye,
>
> Tillmann
>
>
>
>
>
>
>
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.util.Properties;
>
>
> public class TestUpdateCount
> {
>
> public static void main( String[] args ) throws SQLException, ClassNotFoundException, IOException
> {
> //////////////////////////////////////////////////////////////////////////////////////
> // CHANGE THIS
> String dbUrl = "jdbc:postgresql://localhost:5432/db?charSet=UTF-8";
> String dbUser = "user";
> String dbPassword = "xxxx";
> //////////////////////////////////////////////////////////////////////////////////////
>
> try
> {
> Connection con = openConnection( dbUrl, dbUser, dbPassword );
> con.setAutoCommit( false );
>
> try
> {
> // Prepare Table
> Statement stmtDrop = con.createStatement();
> stmtDrop.execute( "DROP TABLE IF EXISTS TESTTABLE " );
> con.commit();
> Statement stmtCreate = con.createStatement();
> stmtCreate.execute( "CREATE TABLE TESTTABLE (id INTEGER PRIMARY KEY)" );
> con.commit();
> Statement stmtData = con.createStatement();
> stmtData.execute( "INSERT INTO TESTTABLE VALUES (5)" );
> con.commit();
>
> // TestCase: Insert 10 statements via jdbc batch.
> // 5h statement should fail because of duplicate key error
> con = openConnection( dbUrl, dbUser, dbPassword );
> con.setAutoCommit( false );
>
> Statement stmt = con.createStatement();
> for( int i = 0; i < 10; i++ )
> {
> stmt.addBatch( "INSERT INTO TESTTABLE VALUES('" + i + "')" );
> }
>
> stmt.executeBatch();
>
> con.commit();
> }
> catch( java.sql.BatchUpdateException x )
> {
> final int[] updateCounts = x.getUpdateCounts();
> for( int i = 0; i < updateCounts.length; i++ )
> {
> System.err.println( "updateCounts[" + i + "]=" + updateCounts[i] );
> }
> // First 5 statements are successfully, so update count should be 1
> for( int i = 0; i < 5; i++ )
> {
> if( updateCounts[i] != 1 )
>                          System.err.println( "Wrong information returned by driver for update Count " + i );
> }
> // 5th statement
> if( updateCounts[5] == -3 )
>                      System.err.println( "Correct information returned by driver for update Count " + 5 );
>
> }
> }
> catch( SQLException s )
> {
>
> s.printStackTrace();
>
> if( s.getNextException() != null )
> {
> s.getNextException().printStackTrace();
> }
>
> }
> catch( Exception s )
> {
>              s.printStackTrace();
> }
> }
>
>
> private static Connection openConnection( String url, String user, String password )
> throws ClassNotFoundException, SQLException, IOException
> {
> try
> {
> Class.forName( "org.postgresql.Driver" );
> }
> catch( ClassNotFoundException e )
> {
> System.err.println( "Could not load driver!" );
> throw e;
> }
> Properties props = new Properties();
> // Activate for logging
> // props.setProperty( "loglevel", "2" );
> // FileWriter fw = new FileWriter( "C:\\temp\\test12345.txt" );
> // DriverManager.setLogWriter( new PrintWriter( fw ) );
>
> props.setProperty( "user", user );
> props.setProperty( "password", password );
> return DriverManager.getConnection( url, props );
>
> }
>
> }
>
>


--
Jeremy Whiting
Senior Software Engineer, Middleware Performance Team
JBoss, by Red Hat

------------------------------------------------------------
Registered Address: RED HAT UK LIMITED, Peninsular House, 30-36 Monument Street, 4th floor, London. EC3R 8NB United
Kingdom
Registered in UK and Wales under Company Registration No. 3798903  Directors: Michael Cunningham (US), Michael ("Mike")
O'Neill(Ireland) and Eric Shander (US)