Problem with JdbcTemplate.batchUpdate() --> Always returning -2

[bijithkumar]

Hi All,

I am using Spring JDBCTemplate.batchUpdate() with PreparedStatement as shown below

 String[][] myData = {{"param1", "value1"}, {"param2", "value2"}, 
         {"param3", "value3"}}; 
         
         String sql = "update myTable set param_value=? where id=? and param_name=?";
         
         JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
         MyUpdateBatchSetter setter = new MyUpdateBatchSetter(myData);
         int[] rowCounts = jdbcTemplate.batchUpdate(sql, setter);

Following is my Batch Setter class

public class MyUpdateBatchSetter implements BatchPreparedStatementSetter
{
	private String[][] data; 
	public MyUpdateBatchSetter(String[][] data)
	{
		this.data = data; 
	}

//	 this is called for each row 
	public void setValues( PreparedStatement ps, int i )
	{ 
		System.out.println("data = " + data[i][1] + "\n" + data[i][0]);
		try {
			ps.setString( 1, data[i][1] ); // set first value 
			ps.setInt( 2, 1000 ); // set second value
			ps.setString( 3, data[i][0] ); // set third value 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	} 
	
	public int getBatchSize() { 
		return data.length; 
		} 
}

This code is updating the database but the problem is that the number of affcted rows returned (rowCounts) is -2 for all the statements.

I need correct rowCounts for inserting the data if that was not updated as shown below

for (int i = 0; i < rowCounts.length; i++)
         {        	 
        	 if (rowCounts[i] <= 0)
            {            
            	// row wasn't updated we should insert it   
             // Code for inserting goes here....           

             }
         }

It would be great if anybody can help.

[trisberg]

You can't rely on getting an accurate count in this scenario. Some JDBC drivers return -2 which is a valid return value -- it just means that the driver doesn't know the exact count. I don't think there is much you could do other than switch to another database/jdbc driver that does provide accurate counts.

[bijithkumar]

Hi Thomas,
Thank you so much for the information.
If the problem is with JDBC driver, I wonder why it works fine and returns correct no. of rows when code does not use PreparedStatement and setter class but sql string as shown below

String[][] myData = {{"InterfaceName", "cs2kCfgMgrIf2"}, {"Version", "4"}, 
         {"Usn", "2"}}; 
for (int i = 0; i < myData.length; i++)
         {            
            sqlString[i] =
               new String(
                  "Update DEVICE_TYPE_CONFIG " + " set param_value= '" +
                  myData[i][1] + "'" + " where " +
                  " DEVICE_TYPE_ID = " + 1000 + " AND PARAM_NAME = '" +
                  myData[i][0] + "'");
         }

JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
int[] rowCounts = jdbcTemplate.batchUpdate(sqlString);
for (int i = 0; i < rowCounts.length; i++)
         {
        	 System.out.println("rowCounts[" + i + "] = " + rowCounts[i]);
         }

The output is

rowCounts[0] = 1
rowCounts[1] = 1
rowCounts[2] = 1

NOTE: I need to use PreparedStatement (since sql string fails when the input data contains " or ' characters).
I am using Oracle JDBC Driver version - 9.0.2.0.0

[Andreas Senft]

NOTE: I need to use PreparedStatement (since sql string fails when the input data contains " or ' characters).
I am using Oracle JDBC Driver version - 9.0.2.0.0

Have a look here. Maybe this could be helpful in solving problems with escaping quotation characters. Although I agree that a solution using prepared statements would be preferrable. Maybe you could try out another jdbc driver version.

Regards,
Andreas

[bijithkumar]

Thank you Senft,
But this does not solve my problem .
I have tried changing oracle driver version to latest (10.1.0.2.0) but that too didn't work.
and using {escape } syntax also didn't work.

If anybody can think of any other logic, My requirement is as given below

I need to update some raws in DB (or insert if raw does not exist).
and I need to use
1) Spring JDBCTemplate

2) with PreparedStatement and BatchPreparedStatementSetter. I already have this setter class implemented in my project).

3) and Oracle DB

[Andreas Senft]

As of the PreparedStatement issue I'm at a loss. However, concerning escaping you might try to escape the apostrophe by duplication. That should work for Oracle.
E.g.: UPDATE test set val='xxx''yyy';

Regards,
Andreas

[trisberg]

You should be able to avoid the -2 by not using a batch update. The preformance will suffer, but you should get your counts.

[bijithkumar]

Thank you Senft,

For time being I have fixed this issue with duplication.

This is what I did.

for (int i = 0; i < myData.length; i++)
         {            
            sqlString[i] =
               new String(
                  "Update DEVICE_TYPE_CONFIG " + " set param_value= '" +
                  myData[i][1].replaceAll("'","''") + "'" + " where " +
                  " DEVICE_TYPE_ID = " + 1000 + " AND PARAM_NAME = '" +
                  myData[i][0] + "'");
         }

The response I got from this forum was amazing.
Thanks to everyone for the support.
Will be back soon with new issues