Today I'd like to help save someone from pulling their hair out when they encounter a Coldfusion error similar to the following:
Error Executing Database Query. Incorrect string value: '\xC2\x96 OTH...' for column 'Comments' at row 1
This error would occur during a database insert or update, and very likely when trying to execute a query that does NOT use the CFQUERYPARAM tags.
TANGENT BEGIN
I'll just indulge the tangent for a moment since I mentioned doing something that ought not to be done and toss in at least one occasion when it is acceptable NOT to use them. When building a dynamic sql statement that you then want to execute, you cannot include cfqueryparam tags and have it work; CF will barf on it. Here's a rough example of what I'm talking about:
<cfset thisSQL = "INSERT INTO MYTABLE ('field1','field2',field3') VALUES (" />
<cfset thisSQL = "#thisSQL# '#val1#','#val2#','#val3#')" />
<cfquery name="qryInsert" datasource="#dsn#">
#preserveSingleQuotes(thisSQL)#
</cfquery>
In that scenario, you can't use CFQUERYPARAM.
TANGENT END
Okay, back to the point. So you've built this dynamic sql statement, values being gotten by parsing some incoming text file, let's say, and when you try to execute your statement you get the "Error Executing Database Query. Incorrect string value: '\xC2\x96 OTH...' for column 'Comments' at row 1" error. Thinking the statement is malformed, you paste it directly into your favorite query analyzer to try and execute it directly in the database and it works fine. You then feed the same incoming data file to your test server to see if the error is consistent, but it works FINE on your test server.
(skip a lot of hair pulling and frustration...)
THE SOLUTION
The problem turns out to be that the incoming file contains high ascii values, or characters outside of the "safe zone", and for whatever reason, your server barfs on it (even though your test server and the db itself doesn't). The solution then is to strip out all high ascii values before trying to use the string in your sql statement. Like so:
<cfset val1 = rereplace(val1,"[^\x00-\x7F]","","all") />
That'll get rid of that pesky irritating useless error, for ya! You're welcome. No charge.
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
The code you've posted doesn't need to be done as a dynamic string, and even when people think they have got a situation which "must" be done that way, there's a way to do it as a normal query.
Using cfqueryparam works, and doesn't require screwing about with the data.
SO, when you DO have to build a query string, for WHATEVER reason, and encounter the error I described, my solution does address it.