"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:324063
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
