Passionate Development From Journeyman to Master

building your SQL outside CFQUERY gotcha

This is the other gotcha that I had today.

I have encountered this before (spent hours on it) but I guess I just forgot about it.

I have encountered a situation today when I need to generate the SQL statement (using CFSAVECONTENT) and this is perhaps the only 2nd time that I need to do this. The main reason why I need to do this is because I want to not only run the query but to actually either save it to file or display it on the browser (although I suppose I can use cfquery result struct maybe?).

So below is the code:

[sourcecode language="coldfusion"] <cfsavecontent> INSERT INTO tableName ( <cfset counter = 1> <cfloop list=lFieldNames index="fieldName"> <cfif counter gt 1>,</cfif> #fieldName# <cfset counter = coounter + 1> </cfloop> )VALUES( <cfset counter = 1> <cfloop list=lFieldNames index="fieldName"> <cfif counter gt 1>,</cfif> '#StructFind( stFieldValue , fieldName )#' <cfset counter = coounter + 1> </cfloop> ) </cfsavecontent> <cfquery name="qInsert" datasource="#thisDSN#"> #strSQL# </cfquery> [/sourcecode]

Running the code I got a database exception. Looking at the exception I found out that all the quotes has been doubled. So this when I remembered the ColdFusion's function PreservesSingleQuotes. Wrapping the your generated SQL with the function solves the problem.

[sourcecode language="cf"] #PreservesSingleQuotes( strSQL )# [/sourcecode]