-2

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>
user11662447
  • 59
  • 8
  • 1
    For your specific example, the first two queries can be replaced by a single update query. – Dan Bracuk Jan 18 '22 at 15:04
  • 1
    The names for all the queries are the same. Furthermore, depending on what you want to achieve in the end you may not necessarily need the third query because you already have the data when inserting it. – Sebastian Zartner Jan 18 '22 at 16:14
  • 2
    The code posted does exactly what was asked. Voting to close as it's unclear what the actual problem is... – SOS Jan 19 '22 at 23:07

2 Answers2

2

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>
Sebastian Zartner
  • 18,808
  • 10
  • 90
  • 132
  • Yes....i have multiple insert row, so that's the problem....How do I do that? – user11662447 Jan 18 '22 at 12:31
  • 1
    Depending on your database engine multiple inserts can be made at once by separating them with commas. Please edit your question to include the info about your database system and describe in more detail what you actually want to achieve. – Sebastian Zartner Jan 18 '22 at 16:10
  • 1
    @user11662447 - I realize it's probably habit to give a cfquery a `name`, but it's pointless when used with a DELETE, INSERT and UPDATE. Those sql statements don't return query objects, so the `name` variable will never contain anything. For those, you must use the `result` attribute instead. – SOS Jan 18 '22 at 19:15
  • 3
    (Edit) `Though to ensure they definitely get executed in order..` Not sure what you mean by "in order". The DBMS will execute the statements in declared order regardless of whether a single cfquery or multiple cfquery's are used. If you're talking about *concurrent access* behavior (i.e. multiple threads) that's a completely different ball of wax... and a much more complicated answer (: – SOS Jan 18 '22 at 20:25
  • 1
    Respectfully, concurrency handling is a LOT more complicated than the description above implies (:. Databases are multi-threaded too so simply submitting multiple statements in one go - i.e. as a single cfquery or even a single query tab in SSMS - doesn't guarantee *other* threads can't also execute the same statements at the same time. Even transactions can allow concurrent access in certain cases, depending on the operation, rows accessed and isolation level (cont'd) – SOS Jan 24 '22 at 05:13
  • 1
    The OP didn't mention a specific dbms, but this article https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15 explains concurrent access in more detail and how statement order is just one element (see transaction isolation level, intent locks, lock compatibility, etc). – SOS Jan 24 '22 at 05:13
-1

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

user11662447
  • 59
  • 8