Link to home
Start Free TrialLog in
Avatar of jack niekerk
jack niekerkFlag for Netherlands

asked on

AFTER A INSERT WITH SQL IN A DATABASE WITH A IDENTITY KEY, FINDING THE GENERATED VALUE FOR THIS KEY

I a loop I insert new records with a identity
like
 strSQL= " INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0) "
 dbconn.Execute (strSQL)
I have a need to know after this call what the value is in the indentity key after each call
how to retrieve this?
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

which database system are you using?  (version and edition)

for sql server (from sql 2000)

then
strSQL= " INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0) ;select scope_identity() as idValue;"
 dbconn.Execute (strSQL)

although ideally you should use a stored procedure to do the insert(s) and have that return the identity values...
Avatar of jack niekerk

ASKER

for testing on laptop I use Pervasive 2000I  at end user location Pervasive V10
Immediately after the INSERY statement, issue the statement "SELECT @@IDENTITY;"

This system variable holds (and allows you to retrieve) the value of the last inserted Identity value.
It may or may not work in PSQL2000, but will definitely work in PSQLv10.
oK, found out that 2000I has no support for this , it does work (no error returned,) on V10,
what would be the syntax to get the value XXXX out off the call?
I 'stuck for now,  using this call I  can 't figure out how to get the returned value for the identity in some variable?
how to do this?
Hmmmm... Been a while since playing with pervasive...

What about creating a stored procedure and executing that ??

CREATE PROCEDURE spPutMyData(IN :p1 int, IN :p2 char(20), IN :p3 char(20))         --or whatever it really is for those columns. could also create "out" param
RETURNS(:NewIDValue Int);
Begin 
INSERT INTO WEBSHOP VALUES(:p1,:p2,:p3) ;
select :NewIDValue = @@identity;
End;

Open in new window

what would be the syntax to get the indentity value back in VB6 language?
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I assumed that you were using SQL for other purposes.  This is a simple SELECT statement that looks like all of your others -- just open the record set and read the value out.

     sSQL = "Select @@IDENTITY as NewValue"
    adoRecordset.Open sSQL, adoConnection, adOpenForwardOnly
    MyNewValue = adoRecordset!NewValue
    adoRecordset.Close
    Set adoRecordset = Nothing
what I do is inserting a new identity record, then i need to know right after that call the "created'  value , due that this need to be added to a grd
it looks like
INSERT INTO WEBSHOP VALUES(0, 'JACK        110324 ', '         ', '1' , 'JACK        ', '17.0170       ', '110324',  11);SELECT @@IDENTITY AS 'Identity'
Set RST = Cn.Execute(strSQL)
After this caal i need this value
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As a passing observation, it has been shown that, under certain circumstances, @@IDENTITY may result in an invalid ID being returned.  For instance, if there is a trigger that also inserts into another table (e.g. a history table), then the @@IDENTITY value may well be the value of the identity column of the table in the trigger's activity.
true , I have read that on some sites, for this application I don 't worry, due to normaly I only use vaccess (btrieve) direct statements.
I did run in to this now (SQL)  because I write a demo in VB6 for an web programmer, who has to "translate" whats happening into a web interface connected to the Pervasive server , and only Sql calls are then possible. So I give him SQL calls in the  VB6 demo.
Thus in the aplication are orders lines inserted , and then the identity values "given"line by line to the backoffice program during insert, so one table is active for inserting only
thanks