Solved

whow to capture SQL errors in Coldfusion

Posted on 2014-11-06
2
95 Views
Last Modified: 2014-11-29
I have a bunch of queries which insert data into my SQL server and sometimes I get a duplicate record which
throws an error like this

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PK_tbl_CNR_D'. Cannot insert duplicate key in object 'dbo.tbl_CNR_D'. The duplicate key value is (C2491035056, 01, 1428310095, ). The specific sequence of files included or processed is: C:\inetpub\wwwroot\parse.cfm, line: 452

how can I capture this in my code and act accordingly ?

I want to avoid to have to fire of a query to see if recordcount GT 0 to avoid this
0
Comment
Question by:AlexPonnath
2 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 40433265
Well you can parse the error in cfcatch.detail, evaluate if it's the duplicate error and then handle it however you want but that really is no "better" than handling it properly in the first place, by checking to see if the id already exists in a query.

There's going to be 2 hits to the db in either case

Call with error > call with corrected id
Call with id check > call to insert

To me it doesn't make sense (and it's a lot more complex) to try to handle the error than avoiding it in the first place.
0
 
LVL 28

Expert Comment

by:Pravin Asar
ID: 40434199
With insert/update query you can add result attribute to check number of rescords being updated.
To avoid duplicates, you may check the existence of record matching the pk or fk.

If record exists, may be you want to run update query.

for example

<cfquery datasource="#request.dsn#" name="saveChanges" result="updateResult">
UPDATE table_name
SET
column1 = '#value1#',
column2 = '#value2#'
WHERE column3 = 1
</cfquery>

<cfset recordsChanged = updateResult.recordCount>
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

13 Experts available now in Live!

Get 1:1 Help Now