Catching Query Error

Hi, When an error is triggered in cfquery, is there a way to find out which query parameter is causing the error?  For instance, I may be doing a CF query insert statement with 20 parameters, and CF gives me an error that one of the parameters that is supposed to be a numeric is not, but the error message does not tell me which parameter is the culprit.  I would normally have to inspect each parameter to figure out the problem.  Is there a way to easily find out which parameter is causing the error?

Hi Min,

Good question! And there is an answer, too. Funnily enough, your title, "Catching Query Error", hints at the answer!

The answer is to use try-catch, with a type setting of "database". If there then is a datatype error during insert, the resulting cfcatch structure will contain information about the offending parameter. The database type also tells you the exception you are catching has been thrown by the database server.

The following example illustrates what I mean. The animals table contains two columns, id and name, of respective type integer and varchar. I have intentionally attempted to insert a row in which the id is the string 'x'. As id must be an integer value, this will result in an error.

<cfset air_animal = "eagle">

<cfset land_animal = "elephant">

<cfset sea_animal = "dolphin">

<cftry>
    <cfquery name="insertAnimals" datasource="testDSN">
    insert into animals(id,name) values(27,'#air_animal#'),(28,'#land_animal#'),('x','#sea_animal#')
    </cfquery>
<cfcatch type="database">
    cfdump var="#cfcatch#">    
</cfcatch>
</cftry>

My database is MySQL 5. The cfcatch struct contains no less than 4 keys telling me the parameter causing the error, the type of error, and the row where the error occurred. The keys,

cfcatch.cause.message
cfcatch.detail
cfcatch.rootCause.message
cfcatch.queryError

each had the value "Incorrect integer value: 'x' for column 'id' at row 3". In addition, the key, cfcatch.sql, gives literally the query that caused the error, namely "insert into animals(id,name) values(27,'eagle'),(28,'elephant'),('x','dolphin')". This can assist you greatly in debugging, particularly when the original query contains dynamic Coldfusion variables, as in this example.

The information in the cfcatch structure will depend on the brand and version of database server. However, the wording might be different across brands and versions, but I would expect the content to mostly be the same.

Well, another type of error I often encountered is when a text input variable is longer than the maximum character allowed in the database table.  If I were to validate these variables, I would need to know the max limit for each query parameter, and doing that can be quite a pain in a large database table.

I wish cfquery could give me a more precise error message.

Min

The "proper" way is to validate every input before you even get to your cfquery tag.  Then if you get letters instead of numbers, you can handle the situation more gracefully than having your page crash.

For empty strings in fields where you accept nulls, you can set a ThisFieldIsNull variable as you inspect the input.  Set it to true or false and then use it in your cfqueryparam.  It's a variation of the previous suggestion.

If it's not already, wrap your query up in a function. Then, before the query, take a CFDUMP of the ARGUMENTS scope so you can see exactly what you're passing it.

Bear in mind that passing an empty string as a numeric will cause this error, so check for that. If you're passing in empty strings, this might help:

<cfqueryparam cfsqltype="cf_sql_numeric" value="#ARGUMENTS.id#" null="#iif(ARGUMENTS.id EQ "",de('TRUE'), de('FALSE')#" />

Which will set the column to NULL rather than trying to enter an empty string.