• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

getting the last inserted value

 
Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
<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?
 
Ranch Hand
Posts: 8944
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

[ August 13, 2003: Message edited by: Pradeep Bhat ]
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
syntax is 'return X into ?' in oracle.
i know zero about sybase.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic