Re: howto? insert into with returning clause

Hi Kevin,

   I can hear the collective groans from the dba's in this group...

   Question 1: what's wrong with this picture?     Did you notice that you are passing in the value that you want returned? Why go to the trouble of asking for the value back that you gave to the db?

    Since you provided the value, you have it.

   Question 2: Do you know when/why to use a Statement, PreparedStatement, CallableStatement?

    So, get a book or browse Oracle Documentation on PL/SQL,

    Get a JDBC book:

        Reference Book: ISBN: 1-56592-270-0. Might be a later version out now.

    Go to the site where all java developers should start:     Reference Site URL: www.javasoft.com Browse through their tutorial section and learn about using JDBC.

good luck,
cindy

kevinh_at_computersandcontrols.com wrote:

> 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 - 19:13:02 CDT