Oracle 10g OCI driver - setString/executeBatch malfunctions

From: Raja S <itssraja_at_gmail.com>
Date: 15 Nov 2004 20:13:14 -0800
Message-ID: <26bbdfa2.0411152013.28785ddf@posting.google.com>

Hi all,

We use batch updates feature of JDBC 2.0 in our application. The application was running fine with Oracle 8i (8.1.7.4) & 9i (9.2.0.1). Currently we are upgrading the application to work with Oracle 10g and the application fails when executing batch updates with PreparedStatements.

Platform used
OS: W2k sp4
DB: Oracle 10.1.0.2
Driver: 10.1.0.2 (Type 2)
jar/zip file: tried both
classes12.zip + jdk 1.3
ojdbc14.jar + jdk1.4.2_02

IMPORTANT:
- The problem occurs when using Type 2 driver but it works fine when using Type 4 driver.
- The problem occurs in both Standard batch update and Oracle batch update. With Oracle batch update it works fine if the batch count is set to 1 (in which case we will not get any performance improvement) - The problem occurs when we deal with columns of VARCHAR type. We have used all combinations of setXXX but the problem persists. - The same program runs fine in Oracle 8i & Oracle 9i

Following is a sample program that we are using to test the batch update feature

---

import java.sql.*;
public class BatchUpdates {
public static void main(String[] args) { Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
int i = 0;

try {
String url = "jdbc:oracle:oci:@kct64";
Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url, "kctuser", "kana"); stmt = conn.createStatement();
try { stmt.execute(
"create table mytest_table (col1 number, col2 varchar2(20))");
} catch (Exception e1) {

System.out.println("Exception when creating Table :"+e1);
}

pstmt = conn.prepareStatement("insert into mytest_table values (?, ?)");

pstmt.setInt(1, 1); 
pstmt.setString(2,"row1"); 
pstmt.addBatch(); 

pstmt.setInt(1, 2); 

pstmt.setString(2,"row2");
pstmt.addBatch();

pstmt.setInt(1, 3); 
pstmt.setString(2,"row3"); 
pstmt.addBatch(); 

pstmt.setInt(1, 4); 

pstmt.setString(2,"row4");
pstmt.addBatch();

pstmt.setInt(1, 5); 
pstmt.setString(2,"row5"); 
pstmt.addBatch(); 
pstmt.executeBatch(); 

rset = stmt.executeQuery("select * from mytest_table"); while (rset.next()) {
System.out.println(rset.getInt(1) + ", " + rset.getString(2));
}
}

catch (Exception e) {
e.printStackTrace();
} finally {

if (stmt != null) {
try {
stmt.execute("drop table mytest_table");
} catch (Exception e) {

System.out.println("Error when dropping table mytest_table :" + e);
}

try { stmt.close(); } catch (Exception e) { System.out.println("Error when closing statement :" + e);
}
}

if (pstmt != null)
{
try { pstmt.close(); } catch (Exception e) { System.out.println("Error when closing PreparedStatement :" + e);
}
}

if (conn != null)
{
try { conn.close(); } catch (Exception e) { System.out.println("Error when closing connection :" + e);
}
}
}
}
}

---

The output of this program when executed is as follows

1, row1 
2, row3 
3, row5 
4, null 
5, null 

Whereas the expected result is

1, row1 
2, row2 
3, row3 
4, row4 
5, row5 

Note: We get this correct result when we run the program with Thin driver(Type 4).

Thanks for the help,
Raja.S Received on Mon Nov 15 2004 - 22:13:14 CST