jack niekerk
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?
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?
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.
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.
ASKER
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?
what would be the syntax to get the value XXXX out off the call?
ASKER
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?
how to do this?
Hmmmm... Been a while since playing with pervasive...
What about creating a stored procedure and executing that ??
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;
ASKER
what would be the syntax to get the indentity value back in VB6 language?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
sSQL = "Select @@IDENTITY as NewValue"
adoRecordset.Open sSQL, adoConnection, adOpenForwardOnly
MyNewValue = adoRecordset!NewValue
adoRecordset.Close
Set adoRecordset = Nothing
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
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
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...