[Last Call] Learn how to a build a cloud-first strategyRegister Now


CFQUERY Insert / Delete Issue

Posted on 2007-10-10
Medium Priority
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

ID: 20049653
Sry the BLID and DocID are the same column...I forgot to change that for the example
LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 20049745
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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