0

I have a table that is storing SQL as one of its elements. I need to be able to run that stored SQL query, but can't figure out how to do it. So far, I'm using the following method:

<cfquery name="qDynamicTag" datasource="#myDSN#">
#PreserveSingleQuotes(arguments.sql)#
</cfquery>

If I dump the value of #PreserveSingleQuotes(arguments.sql)# before executing the cfquery I see the correct SQL statement. However, when I try to run the above cfquery I get a SQLException with the message:

Syntax error at token 0, line 0 offset 0

An example of one of the SQL statements that I'm trying to run:

select productid from catalog.producttag where tag = 'case' or tag = 'cases'

Any ideas on what I'm doing wrong?

stmcallister
  • 1,682
  • 1
  • 12
  • 21
  • 2
    Forget about what the SQL looks like when you output it in CF; the debugging output of DB activity should show what's being passed to the DB... it's that you need to look at for what's wrong. On the face of it, though, your code is fine. – Adam Cameron Jul 13 '13 at 07:09
  • 1
    PS: you should probably parametrise those values in the query, not hard-code them in the SQL. – Adam Cameron Jul 13 '13 at 07:11
  • 2
    In terms of syntax, I do not see anything obviously wrong it either. You mention the above is *example*. What is the *actual* sql when the error occurs? As mentioned, use the debugging output (or result attribute of cfquery). – Leigh Jul 13 '13 at 13:01
  • Ha! Thanks for all your comments. The suggestion to check the debugging output of the DB activity made me realize that I wasn't seeing the DB activity in the debug section of the page because the query was executing through an AJAX call. After calling the page with the query directly, the DB debug output revealed that the code was good, but the data in the database was bad. One of the records with SQL didn't exist. Thus, cfquery was trying to run a blank string. – stmcallister Jul 13 '13 at 17:05
  • 2
    Ah, I wondered if that might be the cause. (Empty statement was one of the possibilities google turned up for the "Syntax error at token 0, line 0 offset 0" error message). Be sure to write that up as an "answer", so it is easier for others to find in the future. – Leigh Jul 13 '13 at 18:25

1 Answers1

1

Ha! Thanks for all your comments. The suggestion to check the debugging output of the DB activity made me realize that I wasn't seeing the DB activity in the debug section of the page because the query was executing through an AJAX call. After calling the page with the query directly, the DB debug output revealed that the code was good, but the data in the database was bad. One of the records with SQL didn't exist. Thus, cfquery was trying to run a blank string.

stmcallister
  • 1,682
  • 1
  • 12
  • 21