Re: Converting SQL Dialects

On Tue, Jul 21, 2009 at 2:13 PM, Matt Neimeyer<matt@neimeyer.org> wrote: > Um... It depends? :) These are customer entered queries and vary based > on the end user and the customizations they have. It could be as > simple as WHERE inlist(SalesPerson,"Bob","Bill","Fred") OR it > could be > something 12 lines long that pulls in criteria from multiple tables > each of those with their own criteria. > > And by easy I meant simply that I would feel comfortable hacking > together something to handle inlist(x,1,2,3) and change it to "X in > (1,2,3)" it's the "rest" that worries me. > > Having slept on it, I'm probably going to write something that > converts IN, checks for a list of "forbidden" words and then flags the > resulting updated query if it contains any of those words. This way I > can catch some of the low hanging fruit. > > My hope is that if X% of queries don't contain foxpro specific > functions, and IN auto-conversions covers another X% of upgrades, and > auto-convert FunctionX (whatever it is...) gives us another X% of > upgrades... that this will result in a hopefully small number of saved > queries that are flagged for manual upgrading. (And not be so painful > in development that it still nets us saved time) > > Matt You might even be able to convert EMPTY(X) to COALESCE(X, '') = ''. MySQL seems to be pretty forgiving with its implicit type-casting. Andrew