Re: how can I dynamically build field names string in a sql update statement?
Hi there.
I played around with the concept of what you're doing, and it is sound. I built
this simple template to prove it:
cfquery name=getbits datasource=mydatasource
select * from bittest
/cfquery
cfdump var=#getbits#
cfset column_string = bool1=1,bool2=0,bool3=1
cfquery name=updatebit datasource=mydatasource
update bittest set #column_string# where id=1
/cfquery
cfquery name=getbits datasource=dugzsqlblog
select * from bittest
/cfquery
cfdump var=#getbits#
and it did indeed correctly place my set string within the sql and execute it
properly. Which leads me to one logical conclusion about your code:
The column_string value you're building isn't sound.
Dump what you believe to be a properly build column_string, try pasting it
directly into an update statement, and see what error you get then. Better yet,
just examine it and see that it is properly formed. However you troubleshoot it
from here though, I do believe that the issue is solely with the way your
'column-string' is being constructed.
Doug :0)
I'm trying to construct a dynamic sql server update statement on a
table with a large number of fields, most of them type bit. First I
evaluate all the yes/no fields and convert them to 1/0. Then I
build a string of column (field) names and values, e.g, {name of
field1}=0, {name of field2}=1, etc., iterating through all of the
fields for the table.
Then I try to execute the query:
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
However, Coldfusion 6.0 bombs on #column_string#. How can I
accomplish this?
~|
Create Web Applications With ColdFusion MX7 Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282666
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: how can I dynamically build field names string in a sql update statement?
define bombs and do a cfoutput on #column_string# to see exactly
what SQL you're generating.
On 6/28/07, david cowen [EMAIL PROTECTED] wrote:
Hi,
I'm trying to construct a dynamic sql server update statement on a table with
a large number of fields, most of them type bit. First I evaluate all the
yes/no fields and convert them to 1/0. Then I build a string of column
(field) names and values, e.g, {name of field1}=0, {name of field2}=1, etc.,
iterating through all of the fields for the table.
Then I try to execute the query:
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
However, Coldfusion 6.0 bombs on #column_string#. How can I accomplish this?
Thanks much for your help!
Here is the code:
cfset numvar=0
cfset charvar=
cfset column_namestring=
cfset column_valstring=
cfset column_string=
cfset query_string=
cfquery name=bitfields datasource=rhcaccesssql
select column_name, data_type from information_schema.columns where
table_name='Requests' and data_type='bit'
/cfquery
!---
cfloop query=bitfields startrow=2
cfset column_namestring=, #column_name# =
cfif data_type eq varchar or data_type eq nvarchar
cfset charvar = evaluate(#column_name#)
cfset column_valstring=' charvar '
cfelse
cfset numvar=evaluate(#column_name#)
cfif data_type eq bit
cfif numvar eq yes
cfset numvar = 1
/cfif
cfif numvar eq no
cfset numvar = 0
/cfif
/cfif
cfset column_valstring=numvar
/cfif
cfset column_string = column_string column_namestring
column_valstring
/cfloop
cfset column_string=mid(column_string,2,len(column_string)-1)
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282515
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: how can I dynamically build field names string in a sql update statement?
Well, without trying the code out or looking real hard at it.. I see that
you have quotes around #column_strings# in your cfquery. Remove those and
see what happens first.
The next obvious step would be to take a look at the outputted (is that a
word?) SQL statement in the error message.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: david cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:44 PM
To: CF-Talk
Subject: how can I dynamically build field names string in a sql update
statement?
Hi,
I'm trying to construct a dynamic sql server update statement on a table
with a large number of fields, most of them type bit. First I evaluate
all the yes/no fields and convert them to 1/0. Then I build a string of
column (field) names and values, e.g, {name of field1}=0, {name of
field2}=1, etc., iterating through all of the fields for the table.
Then I try to execute the query:
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
However, Coldfusion 6.0 bombs on #column_string#. How can I accomplish
this?
Thanks much for your help!
Here is the code:
cfset numvar=0
cfset charvar=
cfset column_namestring=
cfset column_valstring=
cfset column_string=
cfset query_string=
cfquery name=bitfields datasource=rhcaccesssql
select column_name, data_type from information_schema.columns where
table_name='Requests' and data_type='bit'
/cfquery
!---
cfloop query=bitfields startrow=2
cfset column_namestring=, #column_name# =
cfif data_type eq varchar or data_type eq nvarchar
cfset charvar = evaluate(#column_name#)
cfset column_valstring=' charvar '
cfelse
cfset numvar=evaluate(#column_name#)
cfif data_type eq bit
cfif numvar eq yes
cfset numvar = 1
/cfif
cfif numvar eq no
cfset numvar = 0
/cfif
/cfif
cfset column_valstring=numvar
/cfif
cfset column_string = column_string column_namestring
column_valstring /cfloop cfset
column_string=mid(column_string,2,len(column_string)-1)
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282516
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: how can I dynamically build field names string in a sql update statement?
If I remove the quote marks, then the program fails on line 0 of this
routine; i.e., this code does not execute. With the quote marks, the
routine gets executed but fails and there is a sql statement in the
resulting error message. The sql statement has quote marks around the set
field1=0, set field2=1, set field3=0 portion.
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:20 PM
To: CF-Talk
Subject: RE: how can I dynamically build field names string in a sql update
statement?
Well, without trying the code out or looking real hard at it.. I see that
you have quotes around #column_strings# in your cfquery. Remove those and
see what happens first.
The next obvious step would be to take a look at the outputted (is that a
word?) SQL statement in the error message.
...:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: david cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:44 PM
To: CF-Talk
Subject: how can I dynamically build field names string in a sql update
statement?
Hi,
I'm trying to construct a dynamic sql server update statement on a table
with a large number of fields, most of them type bit. First I evaluate
all the yes/no fields and convert them to 1/0. Then I build a string of
column (field) names and values, e.g, {name of field1}=0, {name of
field2}=1, etc., iterating through all of the fields for the table.
Then I try to execute the query:
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
However, Coldfusion 6.0 bombs on #column_string#. How can I accomplish
this?
Thanks much for your help!
Here is the code:
cfset numvar=0
cfset charvar=
cfset column_namestring=
cfset column_valstring=
cfset column_string=
cfset query_string=
cfquery name=bitfields datasource=rhcaccesssql
select column_name, data_type from information_schema.columns where
table_name='Requests' and data_type='bit'
/cfquery
!---
cfloop query=bitfields startrow=2
cfset column_namestring=, #column_name# =
cfif data_type eq varchar or data_type eq nvarchar
cfset charvar = evaluate(#column_name#)
cfset column_valstring=' charvar '
cfelse
cfset numvar=evaluate(#column_name#)
cfif data_type eq bit
cfif numvar eq yes
cfset numvar = 1
/cfif
cfif numvar eq no
cfset numvar = 0
/cfif
/cfif
cfset column_valstring=numvar
/cfif
cfset column_string = column_string column_namestring
column_valstring /cfloop cfset
column_string=mid(column_string,2,len(column_string)-1)
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282520
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: how can I dynamically build field names string in a sql update statement?
If I remove the quote marks, then the program fails on line 0 of this routine; i.e., this code does not execute. With the quote marks, the routine gets executed but fails and there is a sql statement in the resulting error message. The sql statement has quote marks around the set field1=0, set field2=1, set field3=0 portion. Definitely remove the quotes. The sql should work if fields 1,2 and 3 are bit type. But CF will automatically escape the single quotes here: cfset column_valstring=' charvar ' Causing a syntax error. You would need to use PreserveSingleQuotes(). Though it does pose a sql injection risk if you haven't scrubbed your params. Btw, you don't need # signs here and do you really need to use evaluate()? cfset charvar = evaluate(#column_name#) ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282524 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: how can I dynamically build field names string in a sql update statement?
You do not want those quotes in your resulting SQL statement
Run this and give use the results
cfoutput
Update Requests Set br
#column_string#br
Where RequestID = #FindCurrent.requestID#
/cfoutput
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: David S. Cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 9:03 PM
To: CF-Talk
Subject: RE: how can I dynamically build field names string in a sql update
statement?
If I remove the quote marks, then the program fails on line 0 of this
routine; i.e., this code does not execute. With the quote marks, the
routine gets executed but fails and there is a sql statement in the
resulting error message. The sql statement has quote marks around the set
field1=0, set field2=1, set field3=0 portion.
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:20 PM
To: CF-Talk
Subject: RE: how can I dynamically build field names string in a sql update
statement?
Well, without trying the code out or looking real hard at it.. I see that
you have quotes around #column_strings# in your cfquery. Remove those and
see what happens first.
The next obvious step would be to take a look at the outputted (is that a
word?) SQL statement in the error message.
:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: david cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:44 PM
To: CF-Talk
Subject: how can I dynamically build field names string in a sql update
statement?
Hi,
I'm trying to construct a dynamic sql server update statement on a table
with a large number of fields, most of them type bit. First I evaluate
all the yes/no fields and convert them to 1/0. Then I build a string of
column (field) names and values, e.g, {name of field1}=0, {name of
field2}=1, etc., iterating through all of the fields for the table.
Then I try to execute the query:
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
However, Coldfusion 6.0 bombs on #column_string#. How can I accomplish
this?
Thanks much for your help!
Here is the code:
cfset numvar=0
cfset charvar=
cfset column_namestring=
cfset column_valstring=
cfset column_string=
cfset query_string=
cfquery name=bitfields datasource=rhcaccesssql
select column_name, data_type from information_schema.columns where
table_name='Requests' and data_type='bit'
/cfquery
!---
cfloop query=bitfields startrow=2
cfset column_namestring=, #column_name# =
cfif data_type eq varchar or data_type eq nvarchar
cfset charvar = evaluate(#column_name#)
cfset column_valstring=' charvar '
cfelse
cfset numvar=evaluate(#column_name#)
cfif data_type eq bit
cfif numvar eq yes
cfset numvar = 1
/cfif
cfif numvar eq no
cfset numvar = 0
/cfif
/cfif
cfset column_valstring=numvar
/cfif
cfset column_string = column_string column_namestring
column_valstring /cfloop cfset
column_string=mid(column_string,2,len(column_string)-1)
cfquery name=FindRequest datasource=rhcaccesssql
Update Requests Set
#column_string#
Where RequestID =
#FindCurrent.requestID#
/cfquery
~|
ColdFusion MX7 and Flex 2
Build sales marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282529
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
