Hi,
I need to build up a complex dynamic query statement. I have built methods
to add queryParam statements, and built up valid SQL.
If I do:
<cfset sqlStatement = "SELECT DISTINCT tableName.ID FROM tableName
LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID
WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND
tableName.otherID IN
(SELECT otherID FROM thirdTable WHERE (firstName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" /> OR lastName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" />))
)" />
And then do:
<cfquery name="foo">
#sqlStatement#
</cfquery>
I get an error from the SQL Server driver that points to the first part of
the client ID - "Incorrect syntax near 'D35DAF11'" and a "nextException"
that says "Incorrect syntax near '<'."
Yet if I paste the above statement directly into the cfquery tag, it runs
perfectly.
Can anyone suggest where I'm going wrong, please? I suppose it could be a
single quotes problem of some sort. But certainly preserveSingleQuotes has
no affect. And if I wrap the first varchar (the clientID) in a queryParam
statement, it simply eliminates the first error and immediately shows the
"Incorrect syntax near '<'." error.
--
Thanks,
Tom
Tom McNeer
MediumCool
http://www.mediumcool.com
1735 Johnson Road NE
Atlanta, GA 30306
404.589.0560
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354006
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
