8 Replies Latest reply on Feb 21, 2007 5:22 PM by 563194

    Oracle / JDBC Error when Returning values from an Insert

    527953
      I have a table with a auto-incrementing id. From time to time I want to insert rows to this table, but want to be able to know what the pk of the newly inserted row is. One way I could do this is:

      SQL> variable var1 number;

      SQL> insert into test (name) values ('test value') returning id into :var1;
      1 row created.

      SQL> print var1;
      13

      As best as I can write it, that in java should be:

      String query = "insert into test (name) values ('test') returning id into :var1";
      OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
      cs.registerOutParameter(1, OracleTypes.NUMBER );
      cs.execute();
      System.out.println(cs.getInt(1));


      The problem is that when I run it, I get an error:

      java.sql.SQLException: Protocol violation
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:764)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
      at restitution.shared.Sandbox2.run(Sandbox2.java:25)
      at restitution.shared.Sandbox2.main(Sandbox2.java:11)

      According to their website, this is a bug:

      What does "Protocol Violation" mean?
      The Thin driver throws this exception when it reads something from the RDBMS that it did not expect. This means that the protocol engine in the Thin driver and the protocol engine in the RDBMS are out of synch. There is no way to recover from this error. The connection is dead. You should try to close it, but that will probably fail too.
      If you get a reproducible test case that generates this error, please file a TAR with Oracle Global Support. Be sure to specify the exact version numbers of the JDBC driver and the RDBMS, including any patches.

      Can someone tell me what I'm doing wrong? Is there any other ways to do a insert / get key in one sql query ?

      EDIT:
      I was able to fix it by adding:
      query = "BEGIN " + query + "; END;"
      before execution
        • 1. Re: Oracle / JDBC Error when Returning values from an Insert
          Avi Abrami
          Achille,
          I'm only going from memory, so check the Oracle JDBC FAQ, but "returning into" is only supported with the latest version of Oracle's JDBC driver and database, specifically 10.2 (for both).

          Alternatively, use a stored function which does the insert and returns the ID.

          Good Luck,
          Avi.
          • 2. Re: Oracle / JDBC Error when Returning values from an Insert
            527953
            I am indeed using the 10.2 database, as well as the latest jdbc driver. I would really dislike having to use a stored procedure -- I'd much rather have all the queries in one place.
            • 3. Re: Oracle / JDBC Error when Returning values from an Insert
              Avi Abrami
              Achille,
              After perusing the JDBC Developer's Guide and Reference,
              I believe you need to use OraclePreparedStatement and executeUpdate()
              (instead of OracleCallableStatment and execute()).

              Please refer to the Developer's Guide for more details (as mentioned in the FAQ).

              Good Luck,
              Avi.

              Message was edited by:
              Avi Abrami
              • 4. Re: Oracle / JDBC Error when Returning values from an Insert
                543945
                CallableStatement should be used to execute stored procedures,
                for simple queries you should use the PreparedStatement.

                Statement should ideally be used for DDL and not DML,
                PreparedStatement s handle DML better than Statement
                • 5. Re: Oracle / JDBC Error when Returning values from an Insert
                  414247
                  Hi. Your code is fine, and you should use a callable statement
                  if you are going to return a value not as a query. Please show
                  the DDL of the table so we can duplicate this, but regardelss
                  of what statement type you use, it is a clear Oracle bug for
                  the driver to throw a protocol exception during execute().

                  Joe Weinstein at BEA Systems
                  • 6. Re: Oracle / JDBC Error when Returning values from an Insert
                    527953
                    Please show the DDL of the table so we can duplicate this
                    Joe, I had created a table for testing this
                    OPS$GTG798X@public> desc test
                    Name                    Null?    Type
                    ----------------------- -------- ----------------
                    ID                      NOT NULL NUMBER
                    NAME                             VARCHAR2(30)
                    (on that I then had a simple increment by 1 sequence and a trigger that inserted seq.nextval into id upon a new insert)

                    I was able to get it to work by amending the query like this:
                    String query = "BEGIN insert into test (name) values ('test') returning id into :?; END; ";
                    OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
                    cs.registerOutParameter(1, OracleTypes.NUMBER );
                    cs.execute();
                    System.out.println(cs.getInt(1));


                    Thanks
                    • 7. Re: Oracle / JDBC Error when Returning values from an Insert
                      563194
                      I tried your solution, but it didn't work. I get an error (incorrect column number):

                      Caused by: java.sql.SQLException: Niepoprawny indeks kolumny
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
                      at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:121)
                      at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:283)
                      at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:363)
                      at (...).Row$1.createCallableStatement(Row.java:82)
                      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:836)
                      ... 69 more


                      The code is following:

                                          String query = "BEGIN insert into movement (doc_number) values ('abc') returning id into :?; END;";
                                          
                                          OracleCallableStatement cs = (OracleCallableStatement) c.prepareCall(query);
                                          cs.registerOutParameter(1, OracleTypes.NUMBER);
                                          cs.execute();
                                          LOG.debug("result:"+ cs.getInt(1));

                      I used Oracle database 10.2.0.1 with jdbc thin driver ojdbc14.jar. Could you point out your configuration?

                      Regards,
                      Rafal Rusin
                      www.mimuw.edu.pl/~rrusin
                      • 8. Re: Oracle / JDBC Error when Returning values from an Insert
                        563194
                        Ok, it worked now. The thing was that ":" was not necessary. So the following statement worked:
                        String query = "BEGIN insert into movement (doc_number) values ('abc') returning id into ?; END;";
                        Regards,
                        Rafal Rusin
                        www.mimuw.edu.pl/~rrusin