howto? insert into with returning clause

[kev...@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");
// DriverManager.registerDriver(new 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

[AV]

There was [somewhere in 816 oradocs]
direct statement that "insert.... returning"
is not supported. I hope it will soon...

AlexV

<kev...@computersandcontrols.com> wrote in message
news:3B0E5447...@computersandcontrols.com...