CFQUERY Insert / Delete Issue

Posted on 2007-10-10
Last Modified: 2013-12-24
In Coldfusion I have an action page that completes some database actions for moving some rows of data from a temp table to a final table.

In short, the user edits a bunch of line items (rows in a table). This is done, by having CF copy all the FINAL data from a TEMP table, allowing the user to edit the TEMP data, and then the old FINAL data is deleted and the new TEMP data is written as the FINAL data.

Here is my code example:

<!--- ITEMS  COPY --->
<cfquery name="getItems" datasource="#DSN#">
SELECT TempTableItem, Col1, Col2, Col3, Col4, Col5, Col6, Col7
FROM ItemsTemp
WHERE TempTableItem = '#form.TempTableItem#'

<!--- Begin Audit --->
<cfloop query="getItems">
<cfquery name="AuditItems" datasource="#DSN#">
INSERT INTO ItemsAudit (DocID, Col1, Col2, Col3, Col4, Col5, Col6, Col7)
VALUES ('#form.BLID#','#getItems.Col1#','#getItems.Col2#','#getItems.Col3#','#getItems.Col4#','#getItems.Col5#','#getItems.Col6#','#getItems.Col7#')
<!--- End Audit --->

<cfquery name="deleteOldFinalItems" datasource="#DSN#">

<cfloop query="getItems">
<cfquery name="CopyItems" datasource="#DSN#">
INSERT INTO ItemsFinal (BLID, Col1, Col2, Col3, Col4, Col5, Col6, Col7)
VALUES ('#form.BLID#','#getItems.Col1#','#getItems.Col2#','#getItems.Col3#','#getItems.Col4#','#getItems.Col5#','#getItems.Col6#','#getItems.Col7#')

<cfquery name="deleteTempItems" datasource="#DSN#">
WHERE TempTableItem LIKE '#form.TempTableItem#'

I added the  AUDIT table at some point to try and track this.  the AUDIT table does capture the new FINAL data, it just doesn't get placed in the FINAL table (the FINAL table looks empty for the new data)

To me, it is as if the DELETE Final items is executed AFTER I copy the new final items to the table thus deleting both the old and new final data. This happens very intermittently...90% of the time this code works as intended. Is it possible that due to server slowdown or some other issue, that the SQL queries could actually be executed on top of each other or backwards so that delete comes after insert instead of the way it is intended, delete then insert?

Maybe I should try moving both delete and insert into the same CFQUERY tag?
Question by:garyttu

    Author Comment

    Sry the BLID and DocID are the same column...I forgot to change that for the example
    LVL 51

    Accepted Solution

    Since you're not using transactions, its entirely possible that other threads could be executing the code at the same time. Meaning another thread could peform a delete in the middle of your insert loop. You should always use transactions when multiple sql statements must be treated as a single unit.  

    On a side note, an INSERT INTO / SELECT ..FROM statement would be more efficient than looping and inserting the records one at a time.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    754 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