Yes we have set the Auto Commit to true. Also we are not sure how many Queries we can execute as part of the Batch. We are trying to execute the Batch after 1000 Queries and then clear the batch and process the rest till we have none left. Thanks Sateesh Fabian Groffen wrote:

On 04-03-2010 09:27:31 -0800, sateesh wrote:

...

Hi All,

When we try to execute a Set of SQL Statements using executeBatch() and if one of the SQL Query fails then MonetDB seems to be ignoring the rest.

Is there a way we can configure MonetDB to execute the remaining SQL Queries in the Batch ??

Are you using autocommit mode?

------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- View this message in context: http://old.nabble.com/Java-executeBatch%28%29-behavior-tp27783879p27786287.h... Sent from the monetdb-users mailing list archive at Nabble.com.

Hi Fabian, Here i am including a Sample test program which i wrote to test the batch Execution Script to create the test table [SQL] CREATE TABLE TEST.instance_test ( c_208 BIGINT , c_207 BIGINT , property SMALLINT , proptype BOOLEAN , ns SMALLINT , CONSTRAINT uuid_key PRIMARY KEY(c_208,c_207,property,ns) ); [/SQL] [CODE] import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class BatchInsertTests { /** * @param args */ private boolean autoCommitOn = true; public static void main(String[] args) { BatchInsertTests test = new BatchInsertTests(); try { test.testBatch(); // test.setAutoCommitOn(false); } catch (Exception e) { System.out.println("Exception =[" + e + "]"); e.printStackTrace(); } } private void testBatch() throws Exception { System.out.println("In testBatch()"); Connection connection = getConnection(); if(!this.autoCommitOn) { connection.setAutoCommit(false); } System.out.println("In testBatch() con=["+connection+"]"); PreparedStatement statement = null; String query = getInstanceTableInsertQuery(); statement = connection.prepareStatement(query); int[] updatedValues = null; try { printTableCount(connection); statement.setLong(1, new Long("3751548521200044480").longValue()); statement.setLong(2, new Long("-9164647932261081852").longValue()); statement.setInt(3, 12); statement.setInt(4, 115); statement.setBoolean(5, false); statement.addBatch(); System.out.println("After adding the First Query "); statement.setLong(1, new Long("3751548521200044480").longValue()); statement.setLong(2, new Long("-9164647932261081852").longValue()); statement.setInt(3, 12); statement.setInt(4, 115); statement.setBoolean(5, false); statement.addBatch(); System.out.println("After adding the Second Query "); statement.setLong(1, new Long("3751548521200044481").longValue()); statement.setLong(2, new Long("-9164647932261081853").longValue()); statement.setInt(3, 12); statement.setInt(4, 117); statement.setBoolean(5, false); statement.addBatch(); System.out.println("After adding the Third Query "); updatedValues = statement.executeBatch(); System.out .println("After executing the First Batch................"); System.out.println("updatedValues =[" + updatedValues + "]"); printUpdatedValues(updatedValues); commitTransaction(connection); statement.clearBatch(); } catch (BatchUpdateException bue) { System.out.println("BatchUpdateException =["+bue+"] and updatedValues=["+updatedValues+"]"); System.out.println("NextException =["+bue.getNextException()+"]"); printUpdatedValues(updatedValues); statement.clearBatch(); commitTransaction(connection); } try { statement.setLong(1, new Long("3751548521200044482").longValue()); statement.setLong(2, new Long("-9164647932261081853").longValue()); statement.setInt(3, 12); statement.setInt(4, 119); statement.setBoolean(5, false); statement.addBatch(); System.out.println("After adding the Fourth Query "); updatedValues = statement.executeBatch(); System.out .println("After executing the Second Batch................"); printUpdatedValues(updatedValues); commitTransaction(connection); } catch (BatchUpdateException bue) { System.out.println("BatchUpdateException =["+bue+"]"); commitTransaction(connection); } printTableCount(connection); statement.close(); connection.close(); } private void printTableCount(Connection connection) { Statement statement = null; ResultSet rs = null; try{ statement = connection.createStatement(); rs = statement.executeQuery(getInstanceTableSelectQuery()); if(rs.next()){ System.out.println("No.of Records in the Table =["+rs.getString("NO_OF_RECORDS")+"]"); } } catch(SQLException sqle){ sqle.printStackTrace(); } } private void commitTransaction(Connection connection){ if(!this.autoCommitOn) { try { connection.commit(); } catch(SQLException sqle){ sqle.printStackTrace(); } } } public void printUpdatedValues(int[] updatedValues) { if (updatedValues != null && updatedValues.length > 0) { for (int i = 0; i < updatedValues.length; i++) { System.out.println("Updated Values[" + i + "] =" + updatedValues[i]); } } else { System.out.println("No Updated Values !!"); } } public Connection getConnection() { Connection con = null; String url = "jdbc:monetdb://localhost:50000/MYDB"; try { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); con = DriverManager.getConnection(url, "monetdb", "monetdb"); } catch (Exception e) { e.printStackTrace(); } return con; } private String getInstanceTableInsertQuery() { StringBuilder strQuery = null; strQuery = new StringBuilder(); strQuery.append("INSERT INTO "); strQuery.append(" TEST.instance_test"); strQuery.append("(c_208,c_207,ns,property,proptype) VALUES(?,?,?,?,?)"); return strQuery.toString(); } private String getInstanceTableSelectQuery() { StringBuilder strQuery = null; strQuery = new StringBuilder(); strQuery.append("SELECT COUNT(*) AS NO_OF_RECORDS "); strQuery.append(" FROM TEST.instance_test"); return strQuery.toString(); } public boolean isAutoCommitOn() { return autoCommitOn; } public void setAutoCommitOn(boolean autoCommitOn) { this.autoCommitOn = autoCommitOn; } } [/CODE] When auto commit is On then none of the records are getting inserted into the Table (instance_test) [OUTPUT] In testBatch() In testBatch() con=[MonetDB Connection (jdbc:monetdb://localhost:50000/MYDB) disconnected] No.of Records in the Table =[0] After adding the First Query After adding the Second Query After adding the Third Query BatchUpdateException =[java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details] and updatedValues=[null] NextException =[java.sql.SQLException: SQLException:assert:INSERT INTO: PRIMARY KEY constraint 'instance_test.uuid_key' violated current transaction is aborted (please ROLLBACK)] No Updated Values !! After adding the Fourth Query After executing the Second Batch................ Updated Values[0] =0 No.of Records in the Table =[0] [/OUTPUT] When the Auto Commit is set to False then also i am getting the same results!!.. -- View this message in context: http://old.nabble.com/Java-executeBatch%28%29-behavior-tp27783879p27824321.h... Sent from the monetdb-users mailing list archive at Nabble.com.

Hi sateesh, On 08-03-2010 09:06:55 -0800, sateesh wrote:

Here i am including a Sample test program which i wrote to test the batch Execution

I executed your example here. I noticed the following from the log: TX 1269345025845: exec 0(3751548521200044480, -9164647932261081852, 12, 115, false); exec 0(3751548521200044480, -9164647932261081852, 12, 115, false); exec 0(3751548521200044481, -9164647932261081853, 12, 117, false); RD 1269345025870: read final block: 149 bytes RX 1269345025910: &2 1 -1 !SQLException:assert:INSERT INTO: PRIMARY KEY constraint 'instance_test. uuid_key' violated !current transaction is aborted (please ROLLBACK) What I see here is correct, as the first and second argument are the same, so a rollback is performed.

From the JDBC specification:

Disabling auto-commit allows an application to decide whether or not to commit the transaction in the event that an error occurs and some fo the commands in a batch cannot be processed successfully. For this reason, auto-commit should always be turned off when batch updayes are done. The commit behaviour of executeBatch() is always implementation-defined when an error occurs and auto-commit is true. In MonetDB, each batch execution is a transaction. As such, when one statement in the batch fails, the entire batch is invalidated and rollbacked. The interface should allow you to find the statement in your batch that caused the problem iirc. Hope this helps