Using variables in queries 1

[ilovetorun]

Many times I use WHERE clauses in queries which are quite long and complex. Sometimes it becomes necessary to use the same lengthy WHERE clause in my main query and several subqueries, and I've found that in certain circumstances, I can use a variable (set up using CFSET) to represent my WHERE clause.

The difficult arises when I need to use single-quotes in the WHERE clause - for some reason, the CF interpreter turns all of the single-quotes in my CFSET command into double-quotes when I insert that variable into my query.

Is there a way to suppress this function, and get the interpreter to put in single-quotes when it interprets the variable?

 

[GunJack]

I believe the function "preserveSingleQuotes()" is what you're looking for. It's designed primarily for working with dynamic sql statements.

Hope this helps,
GJ

 

[ilovetorun]

Thanks a lot for your help! I'll try that directly!

Kyle

 

[MorganGreylock]

I'm having the same problem, however I am using a dynamic INSERT clause instead of a SELECT clause. Apparently this isn't a common thing since I can't find any documentation about it anywhere. Anyway, heres a snippet:

<cfset strSQLValues = &quot;VALUES (#PreserveSingleQuotes(FirstName)#, #PreserveSingleQuotes(LastName)#&quot;>

This is what I want the string to look like:

VALUES ('Bob', 'Smith')

(The trailing paren isn't listed here, since its appended at the end. )
Instead, heres what I get:

VALUES (''Bob'', ''Smith'')


Notice that the quotes are not double quotes. There are two (2) single quotes next to each other. PreserveSingleQuotes doesn't seem to actually do anything, since
1) I cant put the quotes in the variable before it gets to this page, and
2) If I put the single quotes outside of the PreserveSingleQuotes() it doesnt work at all.

Please help!!

Thanks in advance,

MG

 

[GunJack]

Hey MG,

I think this is all you need.

<cfset strSQLValues = &quot;VALUES ('#FirstName#','#LastName#')&quot;>

<cfquery .....
insert into table1 (fName,lName)
#preserversinglequotes(strSqlValues)#
</cfquery>

If firstname or lastname can contain a single quote, you'll need to do the escaping yourself like this,

<cfset strSQLValues = &quot;VALUES ('#replace(FirstName,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#','#replace(LastName,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#')&quot;>

It's a little more messy but will make sure the code still works in the unlikely event someone has a single quote in their name.

Unless you really need to use that exact value string elsewhere, it's much easier to code it this way

<cfquery .....
insert into table1 (fName,lName)
values('#firstname#','#lastName#')
</cfquery>

Good luck,
GJ

 

[MorganGreylock]

Thanks for the reply... I thought I tried that, but now that I put it that way, I'm not getting that error, however I'm getting another error.... how exactly do I execute the SQL string when its already inside a variable? This was my first try:

<cfquery name=&quot;update_assignments&quot; datasource=&quot;test_source&quot;>

#strSQL#

</cfquery>

Apparently that wasn't it... any suggestions?

Thanks again,
MG

 

[MorganGreylock]

I guess I should be more clear... I need to create the INSERT statement dynamically.. heres a sample:

<cfif #twomoinst1_assign# EQ &quot;Yes&quot;>
<!--- twomoinst1 checkbox was checked --->

<cfset strSQLFields = strSQLFields & &quot;, #twomoinst1_month# &quot;>
<cfset strSQLValues = strSQLValues & &quot;, #PreserveSingleQuotes(twomoinst1)#&quot;>

</cfif>

Does that mean anything to ya? I can't use CFINSERT since the form names aren't hte same as the field names... (Theres no way to make them the same either, at least not without rewriting the entire project, and that's not happening...) =)

Thanks again

MG

 

[MorganGreylock]

Nevermind... I was using the functions in the wrong places.. I got it all figured out and now it seems to work fine. Thanks a ton for the help, it definitely got me in the right direction!!!


Morgan