getting the last inserted value

[Karthik Veeramani]

this is more of a database question... im using sybase ASA... theres an 'autonumber' field in a table, that is used as primary key. i have a method that should insert the given data into this table, and return the value of this primary key.
can any one tell me if theres some sql function that'll get me the last inserted record's primary key (autonumber).

[Lasse Koskela]

<Disclaimer: I am not a DBA>
Would it be possible to use a stored procedure with an OUT parameter that would contain the newly inserted primary key?

[Pradeep bhatt]

[ August 13, 2003: Message edited by: Pradeep Bhat ]

[SJ Adnams]

syntax is 'return X into ?' in oracle.
i know zero about sybase.

[Avi Abrami]

Simon,
Apart from the fact that your syntax is wrong (and incomplete, so even if Karthik was using Oracle, I don't think it would help him), is your reply supposed to be helpful -- or did you just take the opportunity to announce to the world that you:

know zero about sybase

Good Luck,
Avi.

[SJ Adnams]

Avi,
my reply was supposed to be helpful for three reasons.
1) SQL is actually a standard, so there is a chance sybase is the same as oracle.
2) 2 other > 1000 posts ranchers had a go at answering the question .
3) my syntax is 100% correct.
I've now been to google & I realise 1) was not a valid assumption, for a better answer go to google and enter "sybase return sql callablestatement registerOutParameter". Luck? I make my own, cheers

[Avi Abrami]

Simon,

3) my syntax is 100% correct.

Excuse me, but I must be missing something. You wrote:

syntax is 'return X into ?' in oracle.

But the following example is taken from the Oracle9i SQL Reference

Inserting Using Bind Variables: Example
The following example returns the values of the inserted rows into output
bind variables :bnd1 and :bnd2. (The bind variables must first be
declared.)
INSERT INTO employees
(employee_id, last_name, email, hire_date, job_id, salary)
VALUES
(employees_seq.nextval,'Doe','[e-mail]',SYSDATE,'SH_CLERK',2400)
RETURNING salary*12, job_id INTO :bnd1, :bnd2;

Your syntax doesn't look the same as Oracle's, although when I tested your syntax, it worked. So why is RETURN allowed when the documentation uses RETURNING?
(Since you "make your own", I won't wish you...)
Good Luck,
Avi.

[SJ Adnams]

you are missing something.
CallableStatement stmt = connection.prepareCall("begin insert into foo(bar_key, bar) values (bar_seq.nextval,?) return bar_key into ?; end;");
stmt.setWhatever(1,whatever);
stmt.registerOutParameter(2,Types.INTEGER);
stmt.execute();
int key = stmt.getInt(2);
n.b. i know zero about if this will work in sybase

[Avi Abrami]

Simon,
Obviously I am having trouble expressing myself in a way that you will understand. So rather than repeat my question (that you failed to answer), I will try to rephrase it (using your -- unformatted -- code snippet).

insert into foo(bar_key, bar) values (bar_seq.nextval,?) [B]return[/B] bar_key into ?

Why does return work, when the Oracle documentation uses returning?
Good Luck,
Avi.

[Amit Da]

since when did oracle start supporting a return value alongwith an insert statement like the way Simon you've mentioned?
begin insert into foo(bar_key, bar) values (bar_seq.nextval,?) return bar_key into ?; end
Would this work only in context of callable statement(thus making work like a pl*sql block ? as in procedure) or work even in a simple statement while executing it with st.execute(sql) ?
I don't think this is still in the standard SQL, right?
Anyway, that's cool! In sql server we can use something similar. Using "select @@identity" returns the value of identity generated after executing an insert statement if the table has an identity column.

[SJ Adnams]

Avi, the documention you link to is for PL/SQL code on the oracle side. My code is different - i send and anonymous block over the wire, the syntax is different, why? who cares, it works.
dalAm, since 8.1.5, maybe before that even.
Ok, so who's gonna answer Karthiks original question