ADO: Obtain generated key 1

[RobV]

I'm experienced with SQL server 7 and Access, but not with Oracle, and I need to do some maintenance on an Oracle 8i server. I stumbled across a problem:

A particular table has an insert trigger so that new ID's are being generated by a sequence at insert. However, after insert, that key cannot be obtained in ADO.

oRecordset.Open "tblUser", oConnection
oRecordset.AddNew
oRecordset.Fields("sName") = strMyName
oRecordset.Update
lngNewKey = oRecordset.Fields(&quot;lUserID&quot;) '<-- fails!
oRecordset.Close

The second last statement (lngNewKey = ..) does not fail but only returns an empty value. I need that value!

I'm wondering if there are ways to solve this problem. But please: if possible, without any database changes!

Help me, please.. Bye,

Rob.

 

[Dagon]

Try getting it from the CURRVAL of the sequence which generated it. Immediately after inserting the record (without disconnecting and re-connecting), do:

select <seq>.currval
into lUserID
from dual

 

[RobV]

thanks for replying, dragon. Is this operation multithread safe?

I mean: Am I sure that that is the correct value given to the record? Or is there a possibility with multiple inserts that I'm getting an incorrect value, given the fact that the connection is only used for the insert of one record within that sequence?
Bye,

Rob.

 

[Dagon]

Yes, the operation would be multi-thread safe. If you have two different processes inserting records at the same time, they will each get different values from the sequence. The currval will be specific to each session and will show the last value retrieved in that session only.

 

[SBGibson]

I've the same problem of RobV but that sintax doesn't work. I created a sequence with this syntax:

CREATE SEQUENCE [SEQ_TABNAME] START WITH 1

and then a trigger

CREATE TRIGGER [SEQ_TABNAME]
BEFORE INSERT ON [TABNAME]
FOR EACH ROW
BEGIN
SELECT [SEQ_TABNAME].NEXTVAL INTO :new.[IDNAME] FROM DUAL;
END;

if I run

select [SEQ_TABNAME].currval into [IDNAME] from dual;

I obtain:

select [SEQ_TABNAME].currval into [IDNAME] from dual
*
ERRORE on row 1:
ORA-00905: missing keyword

You've some idea? ([SEQ_TABNAME] and [IDNAME] are placeolders for the realname of sequences and ids)

Thank You
Stevie B. Gibson

 

[SBGibson]

excuse me, I noticed one error due to html format. The * of the error is under [IDNAME].
Tnx one more time. Stevie B. Gibson

 

[RobV]

Try

select [SEQ_TABNAME].currval AS [IDNAME] from dual

instead of INTO Bye,

Rob.

 

[Dagon]

The &quot;INTO&quot; only works in a programming language like PL/SQL. In ADO it would be something similar but may not be quite the same syntax.

 

[SBGibson]

OK, now it works, thanks to everybody.
Just another question, you confirm to me that I've to use a adOpenkeyset for inserting the record otherwise I got an error when I try to retrieve the ID?

I'm trying with different settings. Stevie B. Gibson