Catching Query Error

Report · Jan 15, 2010

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?

Thanks,

Min

Report · Jan 16, 2010

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:

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

Report · Jan 16, 2010

<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.

For numeric types, you might want to go with IsNumeric() instead of just an empty string check. Though it is obviously not fool-proof, nor a substitute for validation.

cfqueryparam cfsqltype="cf_sql_numeric"

value="#ARGUMENTS.id#" null="#not IsNumeric(ARGUMENTS.id)#"

Report · Jan 16, 2010

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.

Report · Jan 16, 2010

Dan - I agree completely that all input should've been validated long before this point and to clarify for people - this is more of a "debug" step than a recommended use of a function.

Report · Jan 16, 2010

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

Report · Jan 16, 2010

Hi Min

Re: "I would need to know the max limit for each query parameter"

Do you not? If you're trying to create an application without knowledge of the database itself you're only ever going to run into problem after problem. If you keep a note of the table structure, column definitions and nullable columns and stick to it whilst creating your interface, you'll find yourself with far fewer issues when it comes to the database.

Presumably these input variables are coming from some kind of user interface? If so, just use a MAXLENGTH parameter on the input field.

O.

Report · Jan 16, 2010

Regarding:

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

Database errors do not come from cold fusion. They come from your db software.

Regarding:

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.

That's why it's called work and why we get paid for doing it.

Report · Jan 16, 2010

Ok, point taken about doing validation. Just wanted to check if there is a way to get more detailed message from cfquery errors.

Thanks.

Report · Jan 16, 2010

When an error is triggered in cfquery, is there a way to find out
which query parameter is causing the error?
..
I wish cfquery could give me a more precise error message.
...
Database errors do not come from cold fusion. They come
from your db software.

Exactly what is the exception/error message? If it is a cfqueryparam validation error, that error actually does come from ColdFusion and not your database. But again, the source depends on the exact error.

cold fusion.

Very retro

Report · Jan 16, 2010

Regarding:
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.
That's why it's called work and why we get paid for doing it.

Hahaha. That's a bit of a snippy comment. But it's really very true. Nice one 😉

--

Adam

Report · Jan 17, 2010

So true - half-arsed development ends in unexpected odd behaviour shocker.

Report · Jan 16, 2010

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.

<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.

Report · Jan 16, 2010

Hi BKBK,

Thanks for the tip. I've been using MS SQL and the cfcatch struct does not give such explicit error messages. I tried trigerring an error with MySQL and did get what you described -- which is very helpful in debugging. So I suppose the answer depends on the database and its settings.

Report · Jan 16, 2010

I've been using MS SQL

Then you should definitely be using cfqueryparam.

Report · Jan 18, 2010

Actually I have always used cfqueryparam, but only now do I realize that it has a maxLength attribute. I have only used cfqueryparam to validate the type of the parameter before. That's nice! Thanks!

-==cfSearching==- wrote:

Then you should definitely be using cfqueryparam.