0 Replies Latest reply: Jan 22, 2015 4:47 AM by 663190 RSS

    jdbc bug in executeUpdate(sql, int[]) when table name is case-sensitive;

    663190

      I have found a bug in oracle's jdbc (ojdbc6.jar), can someone tell me how to submit it so that it can be fixed?

      The BUG: using executeUpdate(insert_sql_stmt, int[]) to retrieve the generatedKey generated by before-insert trigger using a sequence results in error when the tablename is case-sensitive (but OK if table name in uppercase).

      Steps to reproduce:
      1a. create table "mixCase" (f1 integer, f2 varchar2(20));
      1b. create table upperCase (f1 integer, f2 varchar2(20));
      2a. create sequence mixCase_seq start with 1;
      2b. create sequence upperCase seq start with 1;
      3a. create or replace trigger mixCase_trigger before insert on "mixCase"
      bq. for each row \\ begin \\ select mixCase_seq.nextval into :new.f1 from dual; \\ end;
      3b. create or replace trigger upperCase_trigger before insert on upperCase
      bq. for each row \\ begin \\ select upperCase_seq.nextval into :new.f1 from dual; \\ end;
      4a. String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
      bq. conn=DriverManager.getConnection(url,user,password); \\ Statement stmt = conn.createStatement (); \\ int rc=stmt.executeUpdate("insert into \"mixCase\"(f2) values('aa')",new int[]{1});
      4b. String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
      bq. conn=DriverManager.getConnection(url,user,password); \\ Statement stmt = conn.createStatement (); \\ int rc=stmt.executeUpdate("insert into upperCase(f2) values('aa')",new int[]{1});
      When you run 4a or 4b in a java jdbc program:
      4b runs OK and rset=stmt.getGeneratedKeys() returns the correct f1 value of 1.
      4a results in error:
      bq. h6. java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist \\ + at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)+ \\ + at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)+ \\ + at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)+ \\ + at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)+ \\ + at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:406)+ \\ + ...+
      +Notes:</</em>
      # If Statement.RETURN_GENERATEDKEYS is used instead of "new int[]{1}" in 4b, there is no error but the rset=stmt.getGeneratedKeys() returned in a ROWID such as 'AAARUyAAEAAAAGQAAL', not what is expected. Other database's jdbc return the correct generated integer value.
      # Same 4b error if new String[]{"f1"} is used as 2nd argument for executeUpdate.
      # The only difference in 4a and 4b is that 4a has case-sensitive table name. All sequence names, trigger names, column names are implicitly deemed to be uppercase by oracle in both cases.
      bq.

      Edited by: user10343198 on Oct 2, 2008 5:50 PM

      Edited by: user10343198 on Oct 2, 2008 6:34 PM