cfquery not returning results with apostrophe

hi, we are running a cfquery with the following sql statement: SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING categoryname IN ('s) ORDER BY viewname ASC LIMIT 0, 300 if we type this directly

Re: cfquery not returning results with apostrophe

Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what you're looking for is #preserveSingleQuotes()# coldfusion automatically escapes any single quotes in variables that are within a cfquery tag so if your query SQL is a variable cfquery

Re: cfquery not returning results with apostrophe

I'm with Rick. You should really change it up a wee bit so you can use cfqueryparam. It has many advantages. On Thu, Sep 16, 2010 at 8:54 AM, Rick Root rick.r...@gmail.com wrote: Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what

Re: cfquery not returning results with apostrophe

yea so don't forget to do your own SQL injection protection as well. On Thu, Sep 16, 2010 at 1:54 PM, Rick Root rick.r...@gmail.com wrote: Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what you're looking for is

Re: cfquery not returning results with apostrophe

thanks for the replies, although the preserveSingleQuotes doesnt work hi, we are running a cfquery with the following sql statement: SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING

Re: cfquery not returning results with apostrophe

On 9/16/2010 6:44 AM, Richard White wrote: thanks for the replies, although the preserveSingleQuotes doesnt work You should be looking at the actual SQL that is being sent to the database. It would be provide clear evidence of what is wrong. Looking at this line: cfset havingClause =

Re: cfquery not returning results with apostrophe

Or instead of using a variable, if you can just put the clause in the SQL statement, let the list param do the work for you: HAVING categoryname IN ( cfqueryparam cfsqltype=cf_sql_varchar value=#arrayToList(value)# list=Yes / )

Re: cfquery not returning results with apostrophe

Wrap your actual cfquery with cftry and then cfoutput the variable cfcatch.sql cftry cfquery.../cfquery cfcatch type=Databasecfoutputpre#cfcatch.sql#/pre/cfoutput/cfcatch /cftry Then post that sql here. Rick ~| Order the

Re: cfquery not returning results with apostrophe

thanks, although the problem is there is no error to catch. when we run a cfdump of the query, the sql is as follows: SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0 AND categoryname IN

Re: cfquery not returning results with apostrophe

If it was MSSQL, you would want single quotes, so more like this: SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0 AND categoryname IN ('''s') ORDER BY viewname ASC LIMIT 0, 300 Instead

Re: cfquery not returning results with apostrophe

thanks for everyones help, its now solved! the reason i am building it through a string is because it is interacting with various extjs grids, which apply filters. i have a generic function that creates the havingclause to filter the data. i ran the havingclause through a loop printing out 1