| 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".
