Insert Into Returning 1

[JonAustin1905]

Hi,
I've inherited an app riddled with

insert into x.

select max(id) from X

insert maxid into Y...


I'm looking to execute inserts into Oracle, returning the ID, using

"Insert into <table> (<cols>) values(<vals>) returning ID into <cfqueryparam...>"

Although it doesn't seem to allow me to do that..

has anyone done that in CF code?

TIA!

~ Jon

 

[philhege]

You should be able to return the identity from the database. (Sorry I'm MSSQL-centric, so I don't know the specific word for the identity). So anyway, for example in MSSQL we would put a statement at the end of the procedure, immediately after the insert, "SELECT SCOPE_IDENTITY as NewRecID" and that would return the ID to the query request.

YMMV with Oracle, but I'm sure there's a way to do that.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[webmigit]

A short google for:
oracle, getting, inserted, record

gave me this

http://www.experts-exchange.com/Databases/Oracle/Q_21068087.html

Its the best I could find... Seems last row is a weakness of oracle.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

oracle's "autonumbering" implementation, called a sequence, is significantly different from sql server's IDENTITY

see Comparing Autonumbering Methods in Different Relational Database Management Systems

you use the NEXT_VAL and CURR_VAL reserved words

in fact, you can actually grab the next value first, before you do the insert



rudy

http://r937.com/

SQL Consulting

 

[philhege]

Smooth, Rudy, thanks for the clarification!

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?