Re: Oracle 10g OCI driver - setString/executeBatch malfunctions

itssraja_at_gmail.com (Raja S) wrote in message news:<26bbdfa2.0411152013.28785ddf_at_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

I tested your code and it is reproducable. I tested it in 9.2.0.5.0 and 10.1.0.2.0.

1) Against 9i database using 9i jdbc drivers. Both oci and thin works.
2) Against 10g database using 10g jdbc drivers. Thin works but oci does not.
3) Against 10g database using 9i jdbc drivers. Both oci and thin works.
4) Against 9i database using 10g jdbc drivers. Thin works but oci does not.

So I suspect something going on with 10g oci driver. The only way I could get it to work against 10g using 10g oci driver was to,

stmt.addBatch("insert into mytest_table values (1,'row1')");
stmt.addBatch("insert into mytest_table values (2,'row2')");
stmt.addBatch("insert into mytest_table values (3,'row3')");
stmt.addBatch("insert into mytest_table values (4,'row4')");
stmt.addBatch("insert into mytest_table values (5,'row5')");
stmt.executeBatch();

My 10g charset is also UTF8.
I suggest you check the metalink for any bugs in 10g oci driver.

Regards
/Rauf Received on Thu Nov 18 2004 - 06:14:48 CST