Board index » delphi » How to return auto-increment key??

How to return auto-increment key??

What is the most elegant/reliable method for returning the value of an
auto-incrementing key created by an SQL INSERT statement to the application?

If it makes any difference I'm using SQL Anywhere 5.5

TIA

Stephen Bertram
Trade & Exchange

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

 

Re:How to return auto-increment key??


Stephen

I use the following syntax:

qry.SQL.Text := 'insert into myTable (Field1, Field2, Field3, etc) values
(value1, value2, value3, etc);'#13"Select @@identity';
qry.Open

Hopefully, this helps.

Quote
Stephen Bertram wrote in message <6ubuir$fj...@nnrp1.dejanews.com>...
>What is the most elegant/reliable method for returning the value of an
>auto-incrementing key created by an SQL INSERT statement to the
application?

>If it makes any difference I'm using SQL Anywhere 5.5

>TIA

>Stephen Bertram
>Trade & Exchange

>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

Re:How to return auto-increment key??


I prefer to write a procedure that returns the value. The new fields are
passed as parameters. Matter of style, I guess, but this method also lets me
consolidate all activity related to an insert in the same place.

(following is used in INTERBASE, should work on any server)

CREATE PROCEDURE NEWTRAN(parm1 INTEGER,parm2 INTEGER,
   etc other parameters
                           RETURNS (NEWID INTEGER)
AS
  DECLARE VARIABLE TEMPID INTEGER;
BEGIN
  TEMPID = GEN_ID(GEN_TRID, 1);
   NEWID = TEMPID;
  INSERT INTO TRANS
   (id, field1, field2, etc)
   VALUES(:NEWID, :parm1, :parm2, etc)
   SUSPEND;
END!!

Lee A. James Jr. wrote in message ...

Quote
>Stephen

>I use the following syntax:

>qry.SQL.Text := 'insert into myTable (Field1, Field2, Field3, etc) values
>(value1, value2, value3, etc);'#13"Select @@identity';
>qry.Open

>Hopefully, this helps.

>Stephen Bertram wrote in message <6ubuir$fj...@nnrp1.dejanews.com>...
>>What is the most elegant/reliable method for returning the value of an
>>auto-incrementing key created by an SQL INSERT statement to the
>application?

>>If it makes any difference I'm using SQL Anywhere 5.5

>>TIA

>>Stephen Bertram
>>Trade & Exchange

>>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>>http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

Other Threads