A query issue with SET QUOTED_IDENTIFIER OFF and a constructed WHERE
For a dealer locator. The idea was to randomize the results of the query. Users tend to pick the link at or near the top of the list returned. We want an easy to give everyone some chance at the top of the list. Looking up some code on the Internet gave me the solution of using “SET QUOTED_IDENTIFIER OFF” at the top of the query, and Order BY newid() at the end. We wanted users that may live on the border of say North and South Dakota to be able to choose both states from a list to search. This query worked great until we attempted to upgrade to ColdFusion 10. Then it brings the entire server down. Any page that runs a query will return an error similar to this:
| ErrorCode | 8179 |
| Message | [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1. |
Here is what I know.
This is the setup of the wherestring. I see how it could be more efficient, but it isn’t what is killing the server so let stay for now.
<cfif listLen(arguments.statelist,",") GT 1> <cfloop list="#arguments.statelist#" delimiters="," index="i"> <cfset statevar = listgetat(arguments.statelist,i,",")> <cfif len(trim(i)) EQ 2> <cfif whereString NEQ ""> <cfset whereString = whereString & " OR ibos.billSTATE = " & " '" & statevar & "'"> <cfelse> <cfset whereString = " ibos.billSTATE = " & " '" & statevar & "' "> </cfif> <cfelse> <cflocation url="/index.cfm"><!--- don't try and hack me bro, states should only be 2 characters. ---> </cfif> </cfloop> <cfelse> <cfset statevar = left(trim(arguments.statelist),2)> <cfset whereString = " ibos.billSTATE = " & " '" & statevar & "' "> </cfif>
This query used to work:
<cftry> <cfquery name="qGetIbosByStates" datasource="#request.dsn#"> SET QUOTED_IDENTIFIER OFF SELECT ibos.agreeToTerms ,ibos.billAddress1 ,ibos.billAddress2 ,ibos.billCity ,ibos.billCompany ,ibos.billEmail ,ibos.billFName ,ibos.billLName ,ibos.billPhone ,ibos.billState ,ibos.billZip ,ibos.cbwActive ,ibos.email ,ibos.homepage ,ibos.homepageFolder ,ibos.iboStatus ,ibos.latitude ,ibos.longitude ,ibos.MemberID ,ibos.Phone ,ibos.startDate ,ibos.tollfree ,ibos.url ,ibos.ContractType FROM ibos WHERE #preservesinglequotes(whereString)#<!--- This does not play well with Quoted id and newID()' ---> AND (ibos.IBOstatus = 'a' OR ibos.IBOstatus = 'i') AND ibos.ContractType != '30' AND ibos.ContractType != '32' AND ibos.ContractType != '18' AND ibos.contractType != 'EM' AND ibos.contractType != '23' <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif> <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif> Order BY newid() </cfquery> <cfcatch type="any"> <cfdump var="#cfcatch#" label="Query error line516"> <cfabort> </cfcatch> </cftry>
So to find out what the issue is I took out the multi-state option. You now can only choose one state at a time from a drop down menu.
This query does works and returns randomly ordered results.
<cfquery name="qGetIbosByStates" datasource="#request.dsn#"> SET QUOTED_IDENTIFIER OFF SELECT ibos.agreeToTerms ,ibos.billAddress1 ,ibos.billAddress2 ,ibos.billCity ,ibos.billCompany ,ibos.billEmail ,ibos.billFName ,ibos.billLName ,ibos.billPhone ,ibos.billState ,ibos.billZip ,ibos.cbwActive ,ibos.email ,ibos.homepage ,ibos.homepageFolder ,ibos.iboStatus ,ibos.latitude ,ibos.longitude ,ibos.MemberID ,ibos.Phone ,ibos.startDate ,ibos.tollfree ,ibos.url ,ibos.ContractType FROM ibos WHERE ibos.billSTATE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#statelist#"><!--- this works with Quoted ID off and newid() ---> AND (ibos.IBOstatus = 'a' OR ibos.IBOstatus = 'i') AND ibos.ContractType != '30' AND ibos.ContractType != '32' AND ibos.ContractType != '18' AND ibos.contractType != 'EM' AND ibos.contractType != '23' <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif> <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif> Order BY newid() </cfquery>
So what is the issue? It seems to be with “SET QUOTED_IDENTIFIER OFF” because this query works and returns random results:
<cfquery name="qGetIbosByStates" datasource="#request.dsn#"> <!--- SET QUOTED_IDENTIFIER OFF---><!--- commented out ---> SELECT ibos.agreeToTerms ,ibos.billAddress1 ,ibos.billAddress2 ,ibos.billCity ,ibos.billCompany ,ibos.billEmail ,ibos.billFName ,ibos.billLName ,ibos.billPhone ,ibos.billState ,ibos.billZip ,ibos.cbwActive ,ibos.email ,ibos.homepage ,ibos.homepageFolder ,ibos.iboStatus ,ibos.latitude ,ibos.longitude ,ibos.MemberID ,ibos.Phone ,ibos.startDate ,ibos.tollfree ,ibos.url ,ibos.ContractType FROM ibos WHERE #preservesinglequotes(whereString)#<!--- This does not play well with 'Quoted id ---> AND (ibos.IBOstatus = 'a' OR ibos.IBOstatus = 'i') AND ibos.ContractType != '30' AND ibos.ContractType != '32' AND ibos.ContractType != '18' AND ibos.contractType != 'EM' AND ibos.contractType != '23' <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif> <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif> Order BY newid() </cfquery>
So what does SET QUOTED_IDENTIFIER OFF do anyhow? Doesn’t seem necessary. This is one of those things where someone copies and pastes off the Internet and doesn’t really understand what they are doing.
Ok so after reading this: http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/ I just don’t think I need that at all. Someone must have been trying to do something that was solved by the use of preservesinglequotes?
So why does it bring the ColdFusion service down for the entire website? This is the first time I have been able to something like that.
Back in 2004 I bought this 98 Ford Contour for $200. Oh yeah it looks nice in the picture. But the smell!You see this guy drove it into the desert and has a stroke and dies -they don’t find him for two weeks. His body had exploded all over the inside of the car.
I am thinking “how hard could it be to take the engine out of the blue car and put in the one from the junk yard? Heh This car with a running engine would be worth about $3000.So I calls up the salvage yard and asks them how much they want for a used motor. Only $500 some bucks. . I have been fixing my cars and trucks for a while now and I have done alternators, starters and even the transmission on my truck. So I go buy this engine. -oh….. now what have I gotten myself into. This thing is REALLY heavy. Look at all this stuff hanging from it. Can I do this? I am going to do it now -I think.
Well the dude and the junk yard says his computer says it is an 11 hour job. Honestly it is going to take me that long to find my tools. But I am going to tear into and try. The worst that can happen is I get frustrated and return the new motor and sell the car to the junk yard for $200 and just be out the time I spent. 
Here is a better shot of the whole mess in my back yard. I tarped it over and hung it up for today. I am cold dirty and wet and sick of banging my knuckles for today. 
Don’t ya just Love Minnesota! I was wearing a T-shirt yesterday.I might get at this again in the Spring? or next week?
June 24th 2005. It has almost been an entire year since I started this project. I have two weeks before I want to take this car to Camp Tomahawk. If I don’t finish it I have to either take the bus or drive that nice brown 78′ F150 that gets about 5mpg. (I will be taking the bus if I fail). 
Moving the engine around on the lawn is a big chore. We hoist it up and put it in the truck, we then drive it around to the other side of the car and hoist it back out. The hoist sinks
My Sons Cody and Matthew were great help as I pretty much didn’t feed them for these two weeks because I was totally focused on getting this car put back together. Here is Cody lifting the engine out.
Here is a picture of where that motor needs to go.
Notice the Trans-axel in this picture. It is that shaft that is conected to the passenger side wheel. Yeah ahh that needs to get slid on the motor and then into the transmission as it is
After Frank helps me jocky the motor and transmission into place, Donny gives us a hand with his crane.
Donny’s Truck has this wonderful crane that made the hole project possible at all. I can’t thank him enough for his help here.
Donny, Tim and Frank were awesome help at this point in the fun.
I am not really working as fast as it looks here.
It is back together! I feel that if I want to give
See that wiring harness there? Yeah ahh that should have been put on BEFORE the engine and car went together. What a bear it was to get back behind the engine. There is a serious lack of room between the engine and the firewall.
A few more parts to put on yet here.
The “work shop”
