Solved

Getting newly inserted AutoId+SELECT @@Identity

Posted on 2006-07-20
18
1,252 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:Shiju Sasidharan
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility
i am using VB6 and Oracle
0
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
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
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
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
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
After your insert, runn this

select <yoursequence>.currval from dual;
0
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
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
 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility

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
 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility
i am not using stored procedures ,instead directly executing insert query from my application
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility
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
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
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
 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility
how do we implement the same in vb ?
0
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
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
 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
Hi,

how are you going on this?  got a solution?

:)
0
 
LVL 14

Author Comment

by:Shiju Sasidharan
Comment Utility
>>talks about using a bind variable in the into clause.
how can we get the value from bind variable using vb ?
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 500 total points
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now