howto? insert into with returning clause

From: <kevinh_at_computersandcontrols.com>
Date: Fri, 25 May 2001 14:33:24 +0000 (UTC)
Message-ID: <3B0E6DF2.FC4620B3@computersandcontrols.com>

Say I have the following:

   create table kk ( id integer not null );

I would like to execute an insert statement with an arbitrary value for id that returns the assigned value. So, the (Oracle) statement is:

    insert into kk (id) values (?) returning id into ?

I'm using JDBC to execute the statement but I don't know which kind of Statement to use - PreparedStatement or CallableStatement. As far as I can see, it should be a Callable.
Is the returned field an output parameter? This is what I've got so far... (but it doesn't work)

    {              
        Random rndom = new Random( System.currentTimeMillis() );
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:sid";
       
        System.out.println("Connect String is: " + url);
       
        conn = DriverManager.getConnection(url, "user", "pswd");
        conn.setAutoCommit(false);
        System.out.println("JDBC Connection opened");         {
        System.out.println("Table kk: attempt callable statement");
        CallableStatement stmt = conn.prepareCall(
                                  "insert into kk (id) values (?) returning id
into ?"
                                  );
         int idtoins = rndom.nextInt();
         stmt.setInt(1, idtoins);
        stmt.registerOutParameter(2, Types.INTEGER);
         System.out.println("Table kk: attempt to insert id = " + idtoins);
         boolean q = stmt.execute();
        ResultSet rs = stmt.getResultSet();
         System.out.println("Table kk inserted");
        System.out.println("Table kk: returning gave " + rs.getInt(1)); 
        stmt.close();
        conn.commit();
        }

    }

This gives me the following output and exception:

Table kk: attempt callable statement
Table kk: attempt to insert id = 1201013030 Exception: java.lang.NullPointerException

java.lang.NullPointerException
at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)

at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java) at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java) at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java) at KK.doit(KK.java:73)
at KK.main(KK.java:14)

Any help would be appreciated!
Thanks,
Kevin

-- 
Posted from [196.32.42.50] 
via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Fri May 25 2001 - 09:33:24 CDT