Hmm.. yeah I can see your point. There are instances where it would be ok to feed in an entire sql statement through cf variables. I think my point was just that this shouldn't be a regular practice as it's unnecessary. It also makes editing the sql statement more difficult and it's more bulky and more difficult to read.
Also, as Claude stated in his email there are instances where you could have a custom tag or a component that is really scalable and you may need to feed in sql statements through to it. I can see the benefit of that. -----Original Message----- From: Stephane Vantroyen [mailto:s...@emakina.com] Sent: Tuesday, June 30, 2009 8:55 AM To: cf-talk Subject: Re: SQL Help "it's not good practice in ColdFusion to do this. " I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple <cfquery> (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ";", in the string variable that you create). If you encounter the problem of the quotes (number of extra single quotes in the statement generated upon your variable), just use the PreserveSingleQuotes() function; basically, your query will look like : ('mystring' is the variable that contains your statements) <cfquery name="myName" datasource="myDsn"> #PreserveSingleQuotes(mystring)# </cfquery> >As Dominic said, putting the entire sql statement in as a variable in >ColdFusion isn't necessary. Many languages like php that sort of thing is >necessary but in ColdFusion, as Dominic said, it's just redundant. > >However, just for future knowledge sake, the reason your receiving this >error is because when you feed in a variable with single quotes, for some >reason they become duplicated when you feed it in sql. I've actually never >seen this before mostly because it's not good practice in ColdFusion to do >this. > >However, Just FYI though... the below query should work. > ><cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username, >eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', >'#FORM.eventdate#')"> ><cfquery name="addpersonaleventtome" dataSource="cf_WikiData"> > #replace(sqlToRun,"''","'","ALL")# ></cfquery> > >Paul Alkema > > >Basically, the <cfset sqlToRun = "..." /> is kind of redundant and >negates the benefit of the cfquery tag. Put all you SQL inside the >cfquery tag. cfqueryparam is only valid within cfquery tags. > >Dominic >> >> CF 8.01 OS X. >> >> I have: >> >> <cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username, >eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', >'#FORM.eventdate#')"> >1, 'jason', '2009-06-27') >> >> Which runs fine in a SQL Editor, but running that in the above <cfquery> I >get: >> >> You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near '1'', ''1'', >''jason'', ''2009-06-27'')' at line 1 >> >> There are a number of extra single quotes that I dont see why. >> >> If I try converting to <crqueryparam> I have something messed up because I >get an error: >> >> I did: >> <cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username, >eventdate) VALUES(<cfqueryparam value="#FORM.eventidentity#" >cfsqltype="CF_SQL_INTEGER">, <cfqueryparam value="#FORM.whois#" >cfsqltype="CF_SQL_INTEGER">, <cfqueryparam value="#FORM.juser#" >cfsqltype="CF_SQL_VARCHAR">, <cfqueryparam value="#FORM.eventdate#" >cfsqltype="CF_SQL_DATE">)"> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
