Getting newly inserted AutoId+SELECT @@Identity

Hi  Experts

I am inserting into a table using autoid(by creating a sequence and trigger)
i need to get newly inserted id.
In SQL Server i have used SELECT @@Identity.
is there any replacement for the same in oracle 8i

Thank you
Shiju S N
LVL 14
Shiju SasidharanAssoc Project ManagerAsked:
Who is Participating?
 
rbrookerConnect With a Mentor Commented:
Hi,

Hi,

try this :

   dim v_output ...
   dim cmdSP ...

   Set cmdSP = New ADODB.Command
   With cmdSP
        ' setup command object
        .ActiveConnection = <connection>
        .CommandText = "insert into <blah>( col2, col3, col4 ) values( val2, val3, val4 ) returning col1 into val1"

        ' Appending parameters
        Set parOutput = .CreateParameter("val1", adChar, adParamOutput, 50)
        Call .Parameters.Append(parOutput)

        ' calling my sp
        Call .Execute

        ' getting the result from output parameter.
        v_output = SetStringVal(parOutput.Value)

    End With


i dont know if it will work, but it might give you something to start from.

heres a couple of links that might also shed some light:
http://www.vbcity.com/forums/topic.asp?tid=24523
http://support.microsoft.com/kb/308051/

good luck man.

:)
0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:
i am using VB6 and Oracle
0
 
rbrookerCommented:
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.

:)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
jrb1Commented:
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?
0
 
rbrookerCommented:
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.

:)
0
 
jrb1Commented:
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)
0
 
sathyagiriCommented:
After your insert, runn this

select <yoursequence>.currval from dual;
0
 
rbrookerCommented:
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.

:)
0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:

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.

0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:
i am not using stored procedures ,instead directly executing insert query from my application
0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:
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
0
 
jrb1Commented:
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;
/

0
 
jrb1Commented:
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.
0
 
rbrookerCommented:
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.

:)
0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:
how do we implement the same in vb ?
0
 
rbrookerCommented:
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.

:)
0
 
rbrookerCommented:
Hi,

how are you going on this?  got a solution?

:)
0
 
Shiju SasidharanAssoc Project ManagerAuthor Commented:
>>talks about using a bind variable in the into clause.
how can we get the value from bind variable using vb ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.