In PL/SQL:
DECLARE
new_user_id NUMBER;
BEGIN
INSERT INTO users ( user_id, user_name ) VALUES ( user_seq.NEXTVAL,
'userName' ) RETURNING user_id INTO new_user_id;
END;
So, similarly (though a little bit of a hack), in JDBC (since
generatedKeys() or whatever they call it isn't supported, though been
promised since Oracle 7-8?):
// context: "con" is a java.sql.Connection
CallableStatement stmt = con.prepareCall( "{call "
+ "INSERT INTO users ( user_id, user_name ) "
+ "VALUES ( user_seq.NEXTVAL, ? ) "
+ "RETURNING user_id INTO ?}" );
stmt.registerOutParameter( 2, java.sql.Types.NUMERIC );
stmt.setString( 1, "userName" );
stmt.executeUpdate();
long newId = stmt.getLong( 2 );
A few things,
- Whitespace can be detrimental to the prepareCall statement. Don't
put any spaces in "{call".
- Specify the column list before "VALUES"--Oracle won't guarantee
column order across databases.
- Get yourself a nice persistence layer (Hibernate?) :).
HTH,
Scott
Daniel Chacón Sánchez wrote:
Hi I know this the struts mail list, but I have a question and I hope
that
someone know the answer
I'm using an oracle database, I have a sequence to obtain the nextval
that
will be the key of the row that I will insert, after the insert I need to
know which is that value so I can tell the user with which value the
row was
inserted. Is there a sure fire way to do this, or am I stuck with "select
max(id) from table" and hope nothing else has been inserted in that few
milliseconds it takes to go from my insert statement to my select
statement?
Some code, for better explanation:
CREATE SEQUENCE user_seq INCREMENT 1 MINVALUE 0 NOMAXVALUE START WITH
0 NOCACHE
NOCYCLE
insert into users values (user_seq .nextval, 'userName');
Then I need to know the value with which the row was inserted, how can
I do
that
Thanks! Sorry fot the out of topic question
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
