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

Just to add to the issue,
This happens only when the DB Charset is UTF8

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:_at_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 Thu Nov 18 2004 - 05:00:04 CET