Solved

Typed CFQUERY vs passed variable CFQUERY

Posted on 2008-06-10
5
335 Views
Last Modified: 2010-04-21
Hi all,

I have an issue with a query I am trying to create and run. The query is compiled based on various criteria and saved into a variable. That variable is then passed to a CFQUERY like this:

<cfquery datasource="#datasource#" name="GetReportData">
    <cfoutput>#qryFull#</cfoutput>
</cfquery>

When run, I get the error "Incorrect syntax near ','." The funny thing is that the syntax is correct. when I output the variable I get the code included at the bottom of this message. When I copy and past that exactly in Query Analyser and run it against the same MSSQL database ColdFusion is using, it returns the expected results. Even when I copy and paste it into the ColdFusion page in place of the variable, it returns the expected results.

So the issue seems to be in the difference between how ColdFusion reads the text and reads the variable containing the same text.

Any ideas? See output query below:
SELECT patient.patientAccount AS patientpatientAccount, 

	CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 

		THEN patient.lastName + ', ' + patient.firstName 

		ELSE patient.companyName 

		END AS patientpayeeName, 

	patient.address AS patientaddress, 

	patient.city AS patientcity, 

	patient.state AS patientstate, 

	patient.zip AS patientzip, 

	patient.phone AS patientphone, 

	transactions.amount AS transactionsamount, 

	transactions.transDate AS transactionstransDate, 

	CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 

		THEN patient.lastName + ', ' + patient.firstName 

		ELSE patient.companyName 

		END AS patientpayeeName, 

	patient.dateOfBirth AS patientdateOfBirth, 

	doctor.businessName AS doctorbusinessName, 

	transactions.date_SentToCollections AS transactionsdate_SentToCollections 

FROM transactions WITH (NOLOCK) 

	LEFT JOIN patient WITH (NOLOCK) ON patient.patientID = transactions.patientID 

	LEFT JOIN doctor WITH (NOLOCK) ON doctor.doctorID = transactions.doctorID 

WHERE transactions.collectorID = 46 

	AND transactions.collectorExported = 0 

	AND transactions.active = 0 

ORDER BY transactions.transDate

Open in new window

0
Comment
Question by:msfletch
  • 3
  • 2
5 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 21754499
try using

#evaluate("#de(qryFull)#")#
0
 
LVL 4

Author Comment

by:msfletch
ID: 21754589
SidFishes,

Using either:

#evaluate("#de(qryFull)#")#

or

#evaluate(DE(qryFull))#

... returns the same "Incorrect syntax near ','" error.

Any other thoughts?
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 21754841
it's the lines

THEN patient.lastName + ', ' + patient.firstName

you can either build the name in cf

<cfset name = q.lastname & ", " & q.firstname>

or do something like

I'm not 100% sure that will work since I don't have tables to test it against...but testing it threw a DB error so I think it cleared the parsing error

<cfsavecontent variable="x">

SELECT patient.patientAccount AS patientpatientAccount, 

        CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 

                THEN patient.lastName + '~' + patient.firstName 

                ELSE patient.companyName 

                END AS patientpayeeName, 

        patient.address AS patientaddress, 

        patient.city AS patientcity, 

        patient.state AS patientstate, 

        patient.zip AS patientzip, 

        patient.phone AS patientphone, 

        transactions.amount AS transactionsamount, 

        transactions.transDate AS transactionstransDate, 

        CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 

                THEN patient.lastName + '~' + patient.firstName 

                ELSE patient.companyName 

                END AS patientpayeeName, 

        patient.dateOfBirth AS patientdateOfBirth, 

        doctor.businessName AS doctorbusinessName, 

        transactions.date_SentToCollections AS transactionsdate_SentToCollections 

FROM transactions WITH (NOLOCK) 

        LEFT JOIN patient WITH (NOLOCK) ON patient.patientID = transactions.patientID 

        LEFT JOIN doctor WITH (NOLOCK) ON doctor.doctorID = transactions.doctorID 

WHERE transactions.collectorID = 46 

        AND transactions.collectorExported = 0 

        AND transactions.active = 0 

ORDER BY transactions.transDate

</cfsavecontent>
 
 

 <cfquery datasource="kdata" name="GetReportData"> 

   #evaluate(de(replace(x, "'~'", ", ", "ALL")))#

 </cfquery> 

Open in new window

0
 
LVL 4

Author Comment

by:msfletch
ID: 21754990
Thanks SidFishes!

Coming through again. That worked. So it seems as though CF has an issue passing single quotation marks from variable directly to a query. This was the same reason I was having trouble passing static variables through the query (see: QID 23462296) because they were also being passed to the cfquery through a variable.

So to recap, the final solution I used was (see attached):
<cfquery datasource="datasource" name="GetReportData">

<cfoutput>

    #evaluate(de(replace(qryFull, "', '", ", ", "ALL")))#

</cfoutput>

</cfquery>

Open in new window

0
 
LVL 4

Author Closing Comment

by:msfletch
ID: 31465844
Thanks again!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now