Getting newly inserted AutoId+SELECT @@Identity

i am using VB6 and Oracle

Hi,

what about :
insert into table (col2, col3, col4) values( 'val2', 'val3', val4' ) returning col1 into val1

not sure about how you would tie all this together in vb, you would have to have an out parameter added to teh statement i would assume.

good luck.

:)

I don't know of a returning parm on an insert.  Also, when you say you have an autoid, what do you mean?  Do you have an Oracle trigger to set the value?

Hi,

Oracle insert statement syntax: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_913a.htm
details how a returning clause works on an insert statement.

:)

That's the main reason I'm here...I learn new stuff all the time.  Thanks!  (for what it's worth...I concur with rbooker's solution)

After your insert, runn this

select <yoursequence>.currval from dual;

Sathy:

Hi,

this will work, but what about this : if it is a web app that is inserting into the table, there may be a point when more than one insert happens at the exact same time.  in this scenario, the time taken between the insert and the reading of the sequence current value ( definately milliseconds as program control would have to go back to the web server, a new db request built and then executed ), there may have been more inserts.  you would end up reading an incorrect value from the sequence.

with an insert into returning value, you know the value returned is going to be the correct value associated with the insert, and not a value that is 3 inserts down the track.

:)

Thank u all for the valuable comments

Could u please provide with a sample example

suppose my table is Table1 with fields F1,F2,F3 and F4 where F1 is autogenerated
my query is

INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )

i just need to get newly inserted F1 field value.

i am not using stored procedures ,instead directly executing insert query from my application

now what i do is
after this insert
INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )

i am using

SELECT MAX(F1)  FROM  TABLE1

before committing transaction
i dont know whether this will lead to the scenario rbrooker was discussing

What kind of program are you using?  Like rbooker said, you do it all in one statement:

declare
   v_f1 table1.f1%type;
begin
   INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )  returning F1 into v_f1;
end;
/

oh yeah, vb6.  run the sql with the returning statement.  send another variable to the SQL call, and it will be set to the value.

also, when using select max( uncommitted value ), this does not guarentee correct-ness ( is that a word?? )
imagine a slow and fast connection.

slow - inserts a record, does not commit
fast - inserts a record, does not commit
fast - reads max value - commits
slow - reads max value ( from fast connection, not correct slow value ) - commits

slow connection has read the incorrect value.

"insert into returning" completes both operations in one single database call. you will never get the wrong value.

:)

how do we implement the same in vb ?

Hi,

had a quick google, and this is the only thing i can find...

http://www.orafaq.com/forum/t/10872/0/

talks about using a bind variable in the into clause.
i would then assume that you would been to set the command up with an output parameter and have the bind variable automagically populate the output parameter.

good luck.

:)

Hi,

how are you going on this?  got a solution?

:)

>>talks about using a bind variable in the into clause.
how can we get the value from bind variable using vb ?