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:
SELECT This, That FROM Those WHERE bar = 'foo' ORDER BY SortOrder
Here is the resulting error:
Error: [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:
SELECT 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.
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.