Passing querytext as a function argument OR When is a string not a string?

Posted By : Dan Wilson March 22, 2007 5:33 PM

Today I wrote a function that added in default rows to a query. Part of the function needed the text of a query to find the default values. I attempted to pass in the query text and use the same text in a <cfquery> tag.

This did not work.

Here is the query I passed in:

view plain print about

1SELECT This, That FROM Those WHERE bar = 'foo' ORDER BY SortOrder

Here is the resulting error:

view plain print about

1Error: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'Foo'.

After a few minutes, I figured out the query worked fine if I left the criteria out. This worked:

view plain print about

1SELECT This, That FROM Those ORDER BY SortOrder

Well, it kinda worked. It parsed and executed fine but I obviously got more records than I wanted. I expected it was a wierd parsing error and then used chr() function to replace the single quotes AND the equal sign. Nada.

The final product that worked with the criteria was to use toString(). <cfsavecontent> would probably work also.

view plain print about

1<cfquery name="ColumnQuery" datasource="#DSN#">
2 #toString( arguments.SQLForRowTypes)#
3 </cfquery>

It was not obvious this wouldn't work without the toString(). I could dump the arguments scope and see the query text VERBATIM. I could also copy the cfdumped text and paste it directly in QueryAnalyzer and it also ran. I am still scratching my head over this one.

2384 Views