Re: Need to convert all CFquery tags to lowercase
Do you need all of the query lowercased or is there part that you want to keep uppercased? If you don't care then this regex will do the job: (]+>.+?) \L\1 The first line grabs all cfquery tags and their content and the second like converts it to lower case. I'd test the regex against all of your templates first to see if there are any cases that you want to avoid. You can use just about any regex tool to do the job, including writing a fast tool in CF to do the job. -- Michael Dinowitz On Fri, Feb 26, 2010 at 4:57 AM, phil phil wrote: > > Hello, > > I do need to migrate from Access to MySQL on a Windows and shared server > environment. > All existing queries (approx 2000) are failing because they are not > lowercased. > > Does anyone know of a script that would look into every files for the CFquery > tag and lowercase its content between? > > This is the only solution that comes to me but maybe there is a easier way? > > Thanks in advance for any feedbacks. > > Phil > > ~| 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:331159 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
As far as I know there is no requirement in MySQL for queries to be lowercase. I just ran one in all uppercase and it worked fine. There has to be something else causing this problem. On Fri, Feb 26, 2010 at 1:57 AM, phil phil wrote: > > Hello, > > I do need to migrate from Access to MySQL on a Windows and shared server > environment. > All existing queries (approx 2000) are failing because they are not > lowercased. > > Does anyone know of a script that would look into every files for the CFquery > tag and lowercase its content between? > > This is the only solution that comes to me but maybe there is a easier way? > > Thanks in advance for any feedbacks. > > Phil > > ~| 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:331160 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
Thanks Michael, Ideally I would like to keep the comments in Uppercase but can do without it. > >You can use just about any regex tool to do the job, including writing >a fast tool in CF to do the job. I am not sure how to use it but will play with it. (]+>.+?) >\L\1 ~| 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:331161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
>As far as I know there is no requirement in MySQL for queries to be >lowercase. I just ran one in all uppercase and it worked fine. There >has to be something else causing this problem. > > >> Are you on Linux? It is not the case for Windows with my hosting company which is unable to change system variables in the shared environment. Or Am I missing something? I would love to be proved wrong... ~| 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:331162 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
It cost me to make the below code to work. Should I use a Rereplace expression? (]+>.+?) \L\1 Thanks in advance for your feedbacks >Do you need all of the query lowercased or is there part that you want >to keep uppercased? If you don't care then this regex will do the job: > >(]+>.+?) >\L\1 > >The first line grabs all cfquery tags and their content and the second >like converts it to lower case. I'd test the regex against all of your >templates first to see if there are any cases that you want to avoid. > >You can use just about any regex tool to do the job, including writing >a fast tool in CF to do the job. > >-- >Michael Dinowitz > > > > > >> ~| 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:331163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need to convert all CFquery tags to lowercase
If I recall right, you have to switch case sensitivity on, are you saying that is what your host has done? -Original Message- From: phil phil [mailto:arantxa...@gmail.com] Sent: Friday, 26 February 2010 8:58 PM To: cf-talk Subject: Need to convert all CFquery tags to lowercase Hello, I do need to migrate from Access to MySQL on a Windows and shared server environment. All existing queries (approx 2000) are failing because they are not lowercased. Does anyone know of a script that would look into every files for the CFquery tag and lowercase its content between? This is the only solution that comes to me but maybe there is a easier way? Thanks in advance for any feedbacks. Phil ~| 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:331164 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
It seems that the table name is case sensitive but not the columns. For e.g if I change the name of the table to 'company' from 'Company' then it cannot find the table because in MYSQL it is referred as 'Company'. BTW, we are on Linux which I understand is case sensitive. >If I recall right, you have to switch case sensitivity on, are you saying >that is what your host has done? > > > > >Hello, > >I do need to migrate from Access to MySQL on a Windows and shared server >environment. >All existing queries (approx 2000) are failing because they are not >lowercased. > >Does anyone know of a script that would look into every files for the >CFquery tag and lowercase its content between? > >This is the only solution that comes to me but maybe there is a easier way? > >Thanks in advance for any feedbacks. > >Phil ~| 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:331166 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
yes. REReplace. The pattern your trying to match is the first line. It says that we're looking to capture something that starts with a cfquery tag, has content, and then ends with a closing cfquery tag. The replace has a \L before the content returned from the pattern (the query tag). \L means that everything that comes after it will be lower cased. End result is a whole cfquery tag along with body converted to lower case. -- Michael Dinowitz On Fri, Feb 26, 2010 at 7:07 AM, phil phil wrote: > > It cost me to make the below code to work. Should I use a Rereplace > expression? > (]+>.+?) > \L\1 > > Thanks in advance for your feedbacks > > >>Do you need all of the query lowercased or is there part that you want >>to keep uppercased? If you don't care then this regex will do the job: >> >>(]+>.+?) >>\L\1 >> >>The first line grabs all cfquery tags and their content and the second >>like converts it to lower case. I'd test the regex against all of your >>templates first to see if there are any cases that you want to avoid. >> >>You can use just about any regex tool to do the job, including writing >>a fast tool in CF to do the job. >> >>-- >>Michael Dinowitz >> >> >> >> >> >>> > > ~| 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:331167 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need to convert all CFquery tags to lowercase
Have you tried talking to your hosting provider, and switch case sensitivity off for your database? -Original Message- From: phil phil [mailto:arantxa...@gmail.com] Sent: Friday, 26 February 2010 11:40 PM To: cf-talk Subject: Re: Need to convert all CFquery tags to lowercase It seems that the table name is case sensitive but not the columns. For e.g if I change the name of the table to 'company' from 'Company' then it cannot find the table because in MYSQL it is referred as 'Company'. BTW, we are on Linux which I understand is case sensitive. ~| 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:331168 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
On Fri, Feb 26, 2010 at 2:01 PM, Andrew Scott wrote: > Have you tried talking to your hosting provider, and switch case sensitivity > off for your database? That is not a per-database setting. Once you have it set to some setting and have clients on it, a hosting provider is committed and can not change it anymore. But why not do the conversion on the Access side before migrating to MySQL? In Access you can simply rename the tables to the case you use in your queries before you convert them. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:331171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
> > But why not do the conversion on the Access side before migrating to > MySQL? In Access you can simply rename the tables to the case you use > in your queries before you convert them. > IIRC there is a setting in my.cnf that will automatically lower case table names. To turn it off you have to set it to 0 --lower-case-table-names=0 I think that the default setting is to lower case table names... It was a while back so forgive me if I am hazy on the details. More details can be found here: http://www.heidisql.com/forum.php?t=667#p3653 http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html The way I got a round it was to either do a search and replace and lower case the table names in the code or to change the table names in the database using a MySQL GUI. Again, I forget the details, I was in a bit of a rage at the time. ;) HTH G! On Fri, Feb 26, 2010 at 10:05 AM, Jochem van Dieten wrote: > > On Fri, Feb 26, 2010 at 2:01 PM, Andrew Scott wrote: > > Have you tried talking to your hosting provider, and switch case > sensitivity > > off for your database? > > That is not a per-database setting. Once you have it set to some > setting and have clients on it, a hosting provider is committed and > can not change it anymore. > > But why not do the conversion on the Access side before migrating to > MySQL? In Access you can simply rename the tables to the case you use > in your queries before you convert them. > > Jochem > > -- > Jochem van Dieten > http://jochem.vandieten.net/ > > ~| 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:331173 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
Problem is that in my code over the years the table name can be as 'Company' or 'company' so just changing the table names in mysql to lowercase is not enough but yes a necessary step (can do it from the GUI: PHPadmin) Then as you wrote changing all occurrences of the table names (1500 cfquery tags) to lowercase. I choose lowercase but it could be also uppercase as long as everything on the code and db is coherent. Anyway that is the only solution that I find at this time. Changing the db to0 be case non sensitive because as someone already mentioned it is a shared environment so other clients would get in troubles. Thanks guys for the great feedbacks! >> >> But why not do the conversion on the Access side before migrating to >> MySQL? In Access you can simply rename the tables to the case you use >> in your queries before you convert them. >> > > >IIRC there is a setting in my.cnf that will automatically lower case table >names. > >To turn it off you have to set it to 0 >--lower-case-table-names=0 > >I think that the default setting is to lower case table names... It was a >while back so forgive me if I am hazy on the details. > >More details can be found here: > >http://www.heidisql.com/forum.php?t=667#p3653 >http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html > >The way I got a round it was to either do a search and replace and lower >case the table names in the code or to change the table names in the >database using a MySQL GUI. Again, I forget the details, I was in a bit of a >rage at the time. ;) > >HTH > >G! > > >On Fri, Feb 26, 2010 at 10:05 AM, Jochem van Dieten wrote: > >> ~| 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:331177 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
On 2/26/10, phil phil wrote: > Problem is that in my code over the years the table name can be as 'Company' > or 'company' so just changing the table names in mysql to lowercase is not > enough but yes a necessary step (can do it from the GUI: PHPadmin) > Then as you wrote changing all occurrences of the table names (1500 cfquery > tags) to lowercase. I choose lowercase but it could be also uppercase as > long as everything on the code and db is coherent. > Anyway that is the only solution that I find at this time. How invested are you in MySQL? If your hoster offers PostgreSQL you could use that and have your case insensitive table names? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:331181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
I tried this but as you can see I am REgex illiterate and more... REReplaceNoCase(TheFile,"(]+>.+?)","(]+>.+?\L\1)","all") It will return the content of the cfquery but I am cannot find a way to lowercase it properly. Thanks >yes. REReplace. >The pattern your trying to match is the first line. It says that we're >looking to capture something that starts with a cfquery tag, has >content, and then ends with a closing cfquery tag. >The replace has a \L before the content returned from the pattern (the >query tag). \L means that everything that comes after it will be lower >cased. End result is a whole cfquery tag along with body converted to >lower case. > >-- >Michael Dinowitz > > > > > >> ~| 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:331182 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4