How to execute multiple cfquery one after another in coldfusion 8?

Question

How to execute multiple cfquery one after another. First execute and completion the first cfquery then proceed to next cfquery execution and move to another cfquery. All the below cfquery are in one single page.

//--------first cfquery--------//
<cfquery name="first" datasource="test">
   Delete from tab where id='123'
</cfquery>

//--------second cfquery--------//
<cfquery name="first" datasource="test">
   insert into tab (id,name,age) values ('123','xxx','37')
</cfquery>

//--------Third cfquery--------//
<cfquery name="first" datasource="test">
   select * from tab where id='123'
</cfquery>

Answer 1

Queries are executed subsequently in ColdFusion. Your issue is rather related to the script being executed several times in parallel.

Executing the script multiple times in parallel causes concurrency issues. That means, the queries get sent to the database server from different script calls at the same time and there is no guaranteed order in which they are executed.

There are two things you should do to avoid these concurrency problems.

1. Put all of them in one <cfquery>

   Putting them all in one <cfquery> the queries get sent to the server all at once. This puts the database server in charge of executing them. And it executes them in order.

   <cfquery name="query" datasource="test">
      delete from tab where id='123';
      insert into tab (id,name,age) values ('123','xxx','37');
      select * from tab where id='123';
   </cfquery>
   
   

2. Wrap them in a <cftransaction>

   Wrapping your queries in a <cftransaction> block allows the database engine to execute all of them as a single transaction. You can imagine that as an "all or nothing" action.
   Besides that it allows you to catch errors and revert your changes if something goes wrong.

   <cftransaction>
      <cftry>
         //--------first cfquery--------//
         <cfquery name="first" datasource="test">
            Delete from tab where id='123'
         </cfquery>
   
         //--------second cfquery--------//
         <cfquery name="second" datasource="test">
            insert into tab (id,name,age) values ('123','xxx','37')
         </cfquery>
   
         //--------Third cfquery--------//
         <cfquery name="third" datasource="test">
            select * from tab where id='123'
         </cfquery>
   
         <cftransaction action="commit" />
   
      <cfcatch type="any">
         <cftransaction action="rollback" />
      </cfcatch>
   </cftransaction>
   

Answer 2

Thanks everyone for the time...much appreciated. Well, I fixed this issue by separating in two page....first page executes delete operation and second page for insert. Cheers