RE: double single quotes in cfc cfquery

Schreck, Tom Tue, 28 Dec 2004 13:19:35 -0800

Some of those problems listed by Dan are from me quickly modifying Joe’s example and not taking the time to double check my code. Sorry for the confusion.

Thanks

Tom Schreck

972-361-9943

From: Dan R Blackman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 28, 2004 1:49 PM
To: [email protected]
Subject: RE: double single quotes in cfc cfquery

Joe,

Now that I have taken a sec to look throughly through it, Isee a couple things.

First, If the user is in role 3000, your Where clause is left blank.

Secondly, there a couple of "))" in the second else if statement.

In the "else" the And statement isn't going to work with the Where clause.

Lastly, the CFRETURN is returning qGetAllDocuments not qGetAllData.

Am I seeing things here?

"Kelly, Joe" <[EMAIL PROTECTED]> wrote:

This did not fix the issue but my cfc looks a lot better. Thanks for the help!

Excellent point about separating CFC from the calling page! Darn it, I knew that!

I had stripped out the dummy clause for the example, sorry.

The ‘var’ scoping led to some interesting reading. That may make a good sub topic at Tom’s presentation, the difference between ‘var’ and ‘this’, with when and why.

I appreciate your help. The code has gone into production now, so if a solution comes up, it’ll have to wait for the next version. But it’ll come up again. It happens whenever I set those query statements as a string containing a text variable in a cfc.

Thanks,

Joe Kelly

From: Dan Blackman [mailto:[EMAIL PROTECTED]
Sent: Monday, December 27, 2004 4:04 PM
To: [email protected]
Subject: RE: double single quotes in cfc cfquery

I have never run into this. Strange.

I would pass that Username into that method. Decouple your CFC from the calling page. This CFC, now, requires there to be a session available to it.

Make the CFC independent of the calling page.

Also, notice how Tom coded with the “Dummy” where clause. That should fix the issue.

Dan

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Schreck, Tom
Sent: Monday, December 27, 2004 2:18 PM
To: [email protected]
Subject: RE: double single quotes in cfc cfquery

Try this:

            <cffunction name="GetAllData" displayName="Sort Criteria" access="public" returnType="query" output="false" roles="3000,3001">

                        <cfargument name="QueryOrderBy" type="string">

                                                                        <cfscript>

                                                                                    var qryGetAllData = "";

                                                                        </cfscript>



                        <cfquery name="qryGetAllData" datasource="mydatasource">

                                                                                    SELECT *

                                                                                    FROM   MyTable

                                                                                    WHERE 0=0

                                                                                                <cfif IsUserInRole("3001")>

                                                                                                            AND Status = 2 and UserName = '#SESSION.InfoStruct.UserName#'))

                                                                                                </cfif>

                             ORDER BY #QueryOrderBy#

                        </cfquery>

                        <cfreturn qryGetAllDocuments/>

            </cffunction>

Note: you should var all variables used within your <cffunction>, even query names. If not, you can run into issues.

Thanks -

Tom Schreck

972-361-9943

From: Kelly, Joe [mailto:[EMAIL PROTECTED]
Sent: Monday, December 27, 2004 2:00 PM
To: [email protected]
Subject: double single quotes in cfc cfquery

Has anybody run into this issue? Is there a better resolution? Or am I just doing this wrong?

Setting up variable query statements for a <cfquery> within a cfc,

when using variables containing strings,

the evaluated string ends up with double single quotes around it in when the <cfquery> is run.

There is a hot fix that addressed this issue:

http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19108

and it is supposedly contained within the updater but neither fixes this issue.

Here’s my example cfc with my workaround (notice the Replace()):

<cfcomponent displayName="GetAndSortData" hint="Accepts sort criteria, runs the query and returns the data sorted.">

            <cffunction name="GetAllData" displayName="Sort Criteria" access="public" returnType="query" output="false" roles="3000,3001">

                        <cfargument name="QueryOrderBy" type="string">



                        <cfif IsUserInRole("3000")>

                                    <cfset SecurityQryString = "">

                        <cfelseif IsUserInRole("3001")>

                                    <cfset SecurityQryString = "AND Status = 2 and UserName = '#SESSION.InfoStruct.UserName#'))">

                        <cfelse>

                                    <cfset SecurityQryString = "AND 1 = 0">

                        </cfif>



                        <cfquery name="qryGetAllData" datasource="mydatasource">

                                    SELECT *

                                    FROM   MyTable

                                    WHERE #Replace(SecurityQryString, "''", "'", "All")#

                                    ORDER BY         #QueryOrderBy#

                        </cfquery>

                        <cfreturn qryGetAllDocuments />



            </cffunction>

</cfcomponent>

I can assure you that I tried many different combinations of ideas and this is the best I solution I have found. Please let me know if you have encountered this and have found something better!

Thanks,

Joe Kelly

RE: double single quotes in cfc cfquery

Reply via email to