Store SQL statement in a variable to use inside <CFQUERY> tags 1

[mow]

Hi,

I generate my SQL string dynamically and store it in a variable. I tried to use that variable inside the <CFQUERY> tag, but it generates the following error.
..............
ODBC Error Code = S1000 (General error)

[Oracle][ODBC][Ora]ORA-00907: missing right parenthesis

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (134:1) to (134:44).
..................

I have tried to enclose CFQUERY inside CFOUTPUT but I get the same error message.

Please HELP!!

 

[KevinFSI]

Can you output the dynamic SQL and post it here? Kevin
slanek@ssd.fsi.com

 

[calista]

Hi!

Post your code, and I'm sure someone here can help you. I have done something similar in Access. I generated a variable and used it for my WHERE clause. Is this something like what you are trying to do?

Calista

 

[GunJack]

You may need to use the #preservesinglequotes()# function as CF will escape any single quotes inside of a <cfquery> block. This is useful when you are just using dynamic values inside of sql statements but can cause problems when the entire sql statement is dynamic and you have single quotes that need to be there. I would try this first and if it still doesn't work, post the dynamic sql statement and someone in the forums should be able to help.

<cfquery ...>
#preservesinglequotes(myDynamicSqlStatement)#
</cfquery>

GJ

 

[mow]

Hi all,

Thanks for the quick responses.
I've tried GJ's tip and it works like magic!
You just made my day.

mowunmi