Re: How do I retrive newly added records?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 02 May 1999 13:35:24 GMT
Message-ID: <372e5445.7259218@192.86.155.100>

A copy of this was sent to d96ohrn_at_ios.chalmers.se (Fredrik Öhrn) (if that email address didn't require changing) On Sat, 01 May 1999 12:51:54 GMT, you wrote:

>
>Hello!
>
>I'm working on a java applet that adds records to an Oracle database.
>
>To add a new record I use the following construct:
>statement.executeUpdate ("INSERT INTO mytable VALUES (0, myvalue1, myvalue2,
>...)");
>
>The catch is that the first 0 is a primary key column and the server
>automatically assigns a unique running number instead of the value I provide.
>How can I get hold of the server assigned primary key value? I need it to add
>records to other tables that has this key as a foreign key.
>

Use the return's clause on the insert. Consider:

create table t ( x int );
create sequence t_seq;

create or replace trigger t_trigg
before insert on t
for each row
begin

    select t_seq.nextval into :new.x from dual; end;
/

and then the java code:

import java.io.*;
import java.sql.*;
import java.util.*;

class Returning
{

public static void main(String args[])
{

    Statement statement = null;

    try
    {
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

    Connection connection =

      DriverManager.getConnection
        ("jdbc:oracle:thin:@slackdog:1521:oracle8", "scott", "tiger");

    connection.setAutoCommit(false);

    statement = connection.createStatement();

    CallableStatement cstmt = connection.prepareCall(

        "begin insert into t(x) values (0) return x into ?; end;" );

    cstmt.registerOutParameter( 1, java.sql.Types.INTEGER );     System.out.println( "prepared & registered" );     cstmt.executeUpdate();

    int newVal = cstmt.getInt( 1 );
    System.out.println( newVal + " assigned on input" );

    }
    catch( Exception e )
    {

        e.printStackTrace();
    }

}

}

will print out results like:

$ java Returning
prepared & registered
3 assigned on input

$ java Returning
prepared & registered
4 assigned on input

You can retrieve as many columns as you like this way.

>Any input is appreciated.
>
>Regards,
>Fredrik
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun May 02 1999 - 08:35:24 CDT