Geek Answers Handbook

Returns the ROWID parameter from an insert statement using a JDBC connection with oracle

I cannot get the correct magic combination to make this work:


OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc:oracle:thin:app_user/pass@server:1521:sid");
DefaultContext conn = ods.getConnection();
CallableStatement st = conn.prepareCall("INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO :rowid0");
st.registerReturnParameter(1, OracleTypes.ROWID);
st.execute();

The error I get is a “protocol violation”. If I switch to registerOutParameter (), I get a notification that I have not registered all returned variables. If I complete the statement at the beginning of PL / SQL; end; block, then I get the parameter just fine using a regular registerOutParameter () call. I would prefer not to wrap all my inserts in PL / SQL - so what's missing from the top?

+5
source share
5 answers

A few things you need to do.

  • Change CallableStatement to OracleCallableStatement
  • NUMBER, : OracleTypes.Number

:

OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(
       "delete from tab1 where age < ? returning name into ?");
pstmt.setInt(1,18);

/** register returned parameter
  * in this case the maximum size of name is 100 chars
  */
pstmt.registerReturnParameter(2, OracleTypes.VARCHAR, 100);

// process the DML returning statement
count = pstmt.executeUpdate();
if (count>0)
{
  ResultSet rset = pstmt.getReturnResultSet(); //rest is not null and not empty
  while(rset.next())
  {
    String name = rset.getString(1);
    ...
  }
}

Oracle JDBC:

+6

. OraclePreparedStatement CallableStatement.

CallableStatement statement = connection.prepareCall("{call INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO ? }");
statement.registerOutParameter( 1, Types.VARCHAR );

int updateCount = statement.executeUpdate();
if (updateCount > 0) {
   return statement.getString(1);
}
+15

, , , .

Oracle Metalink:

< >

JDBC 10.1.0.x DML :

FAQ JDBC: "10.1.0 (10g r1)   DML? . Post 10.1.0. ".

JDBC, .

JDBC 10.2.0.x, FAQ JDBC 10.2.0.x, :

"10.2.0 (10g r2) DML? ! . . . "

JDBC Oracle, , :

 // Create Oracle DatabaseMetaData object
  DatabaseMetaData meta = conn.getMetaData();

  // gets driver info:
  System.out.println("JDBC driver version is " + meta.getDriverVersion());

JDBC 10.2.0.x , , , ...

+3
source

Try to use ? instead : rowid0 in your SQL row. I used to have problems with named parameters and Oracle.

+1
source 
PreparedStatement prepareStatement = connection.prepareStatement("insert...",
            new String[] { "your_primary_key_column_name" });

    prepareStatement.executeUpdate();

    ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
    if (null != generatedKeys && generatedKeys.next()) {
         Long primaryKey = generatedKeys.getLong(1);
    }

I found the answer, this works great. I can insert from JAVA and return it using a key.

Full version:

CREATE TABLE STUDENTS
(
   STUDENT_ID   NUMBER NOT NULL PRIMARY KEY,
   NAME         VARCHAR2 (50 BYTE),
   EMAIL        VARCHAR2 (50 BYTE),
   BIRTH_DATE   DATE
);


CREATE SEQUENCE STUDENT_SEQ
   START WITH 0
   MAXVALUE 9999999999999999999999999999
   MINVALUE 0;

And Java code

String QUERY = "INSERT INTO students "+
               "  VALUES (student_seq.NEXTVAL,"+
               "         'Harry', 'harry@hogwarts.edu', '31-July-1980')";

// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");

// get database connection from connection string
Connection connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:sample", "scott", "tiger");

// prepare statement to execute insert query
// note the 2nd argument passed to prepareStatement() method
// pass name of primary key column, in this case student_id is
// generated from sequence
PreparedStatement ps = connection.prepareStatement(QUERY,
        new String[] { "student_id" });

// local variable to hold auto generated student id
Long studentId = null;

// execute the insert statement, if success get the primary key value
if (ps.executeUpdate() > 0) {

    // getGeneratedKeys() returns result set of keys that were auto
    // generated
    // in our case student_id column
    ResultSet generatedKeys = ps.getGeneratedKeys();

    // if resultset has data, get the primary key value
    // of last inserted record
    if (null != generatedKeys && generatedKeys.next()) {

        // voila! we got student id which was generated from sequence
        studentId = generatedKeys.getLong(1);
    }

}

source: http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/

+1
source

More articles:


All Articles