The SQL statement looks fine to me, but I don't know how to execute this statement
from JDBC.
If a use a PreparedStatement I don't know how I can retrieve the bind variable in
the RETURNING clause. Is it possible and how do it look like in code?
I can use an output parameter in a CallableStatement, but how do I execute the SQL
statement?
If anybody have a solution and could give some JDBC code for Steve's example I would
be very grateful.
Thanks,
Dennis
Steve Muench wrote:
> | I was hoping that I could avoid using a stored procedure, but maybe that's the
> | only way to get the generated primary key back to the java code in one JDBC
> | call.
>
> Not sure about other databases, but if you're using Oracle, you
> can do an insert *and* retrieve the generated key in a single roundtrip
> using a statement like the following:
>
> INSERT INTO yourtable(pk,y,z)
> VALUES(yoursequence.nextval,?,?)
> RETURNING pk INTO ?
>
> you can bind values for the first two bind varibles before executing,
> then retrieve the value of the third bind variable (the one in the
> RETURNING clause) after executing the statement, just as if it
> were an OUT parameter from a stored procedure.
>
> Rather than mentioning "yoursequence.nextval" in the statement,
> some folks prefer to allow a database trigger to assign the
> sequence value. So if you had a trigger like:
>
> CREATE TRIGGER assign_pk_yourtable
> BEFORE INSERT ON yourtable FOR EACH ROW
> BEGIN
> SELECT yoursequence.nextval INTO :new.pk FROM DUAL;
> END;
>
> Then your insert statement could look like this:
>
> INSERT INTO yourtable(y,z) /* pk column not supplied by client */
> VALUES(yoursequence.nextval,?,?)
> RETURNING pk INTO ? /* pk value still can be retrieved by client */
>
> ____________________________________________________________________________
> Steve Muench, Oracle9i JDeveloper Dev Team
> Author,"Building Oracle XML Applications" - www.oreilly.com/catalog/orxmlapp
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
