Alter table - modifying an Enum field in MYSQL
Hi All, Have run across a VERY STRANGE behaviour in CF 8.01 / mySQL 5.0 I've got a code snippet to modify vaules in an enum field in our database. ALTER TABLE the_table MODIFY the_field ENUM(#my_new_list#) NOT NULL; ; Results in an CF/mySQL error... for SOME reason my_new_list when inside the gets transformed to "A","B","C","D".. which mySQL does not like. I have found that if use: ALTER TABLE the_table MODIFY the_field ENUM('#ListChangeDelims(my_new_list,"','")#') NOT NULL; Things work... Anyone have a reason why the first does not work.. and I hope this saves someone some grief! Cheers, Matts ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311494 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Alter table - modifying an Enum field in MYSQL
cf by default will escape single quotes in the sql statement values by doubling them. use #PreserveSingleQuotes(my_new_list)# function to counter this. Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Developer MediaDoc wrote: > Hi All, > > Have run across a VERY STRANGE behaviour in CF 8.01 / mySQL 5.0 > > I've got a code snippet to modify vaules in an enum field in our database. > > > ALTER TABLE the_table MODIFY the_field ENUM(#my_new_list#) NOT NULL; > ; > > > Results in an CF/mySQL error... for SOME reason my_new_list when inside the > gets transformed to > "A","B","C","D".. which mySQL does not like. > > I have found that if use: > > > ALTER TABLE the_table MODIFY the_field > ENUM('#ListChangeDelims(my_new_list,"','")#') NOT NULL; > > Things work... > > Anyone have a reason why the first does not work.. and I hope this saves > someone some grief! > > Cheers, > > Matts > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311497 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Alter table - modifying an Enum field in MYSQL
Wow, CF developer for too many years, and I've never come across that tag before... many thanks Azadi! Matts >cf by default will escape single quotes in the sql statement values by >doubling them. use #PreserveSingleQuotes(my_new_list)# function to >counter this. > >Azadi Saryev >Sabai-dee.com >http://www.sabai-dee.com/ > > > >Developer MediaDoc wrote: >> ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311507 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4