*
The moose likes JDBC and the fly likes getting the last inserted value Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting the last inserted value" Watch "getting the last inserted value" New topic
Author

getting the last inserted value

Karthik Veeramani
Ranch Hand

Joined: Dec 22, 2002
Posts: 132
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).


Thanks<br />Karthik<br />SCJP 1.4, CCNA.<br /> <br />"Success is relative. More the success, more the relatives."
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
<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?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919


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

Groovy
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
syntax is 'return X into ?' in oracle.
i know zero about sybase.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

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
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
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
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

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','john.doe@oracle.com',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
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
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
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

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

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

Joined: Mar 07, 2003
Posts: 16
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
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting the last inserted value