SQL statements inside of cfquery tags 1

[jl8789]

My question is can I have more than one statement executed inside of a cfquery tag. I am using an Oracle database and I need to insert a row, and get the current order_id for that row that I just inserted. I was told if I do this all in the same session\transaction that it would work. I would do this with two queries, but my worry of course is that I will insert a row, and then another row will be inserted before I get to do my select of the CURRVAL of the row I just inserted.

Is there a way to do this in Cold Fusion? I can do it in Embarcadero's RapidSQL.


The following code does not work.

<cftransaction action=&quot;commit&quot;/>
<cfquery name=&quot;getOrderID&quot; datasource=&quot;#datasource#&quot; username=&quot;#username#&quot; password=&quot;#password#&quot;>
INSERT INTO QADS_ORDERS(QO_ORDER_ID, QO_INSERT_EMPL_ID)
VALUES (qo_order_id_seq.NEXTVAL, 'jl8789')
/
SELECT qo_order_id_seq.CURRVAL as OrderID from dual
</cfquery>

 

[csteinhilber]

I don't know of a way to execute two SQL statements in a single CFQUERY tag, no. But you could certainly do a named CFLOCK around as many CFQUERY tags as you need and you're guaranteed that simultaneous threads won't step on each other.

<CFLOCK TIMEOUT=&quot;30&quot; THROWONTIMEOUT=&quot;No&quot; NAME=&quot;SQL_INSERT_LOCK&quot; TYPE=&quot;EXCLUSIVE&quot;>
    <CFQUERY name=&quot;getOrderID&quot; datasource=&quot;#datasource#&quot; username=&quot;#username#&quot; password=&quot;#password#&quot;>
       INSERT INTO QADS_ORDERS(QO_ORDER_ID, QO_INSERT_EMPL_ID)
       VALUES (qo_order_id_seq.NEXTVAL, 'jl8789')
   </CFQUERY>
   <CFQUERY name=&quot;getOrderID&quot; datasource=&quot;#datasource#&quot; username=&quot;#username#&quot; password=&quot;#password#&quot;> 
       SELECT qo_order_id_seq.CURRVAL as OrderID from dual
   </CFQUERY>
</CFLOCK>

The other thing you might do is do a CFDUMP of getOrderID after just your INSERT statement... I can't remember if CFQUERY returns anything useful after an INSERT or not... but there's a possibility it does.


-Carl

 

[philhege]

I do it with MS SQL Server, but am not sure about Oracle. You should be able to write a procedure that returns the key value.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[jl8789]

Great.

Thanks again Carl, your advice, comments, suggestions, pointers, and most importantly code have really come in handy.