On Tue, Jul 21, 2009 at 06:54:35PM +0100, Ashley Sheridan wrote: > On Tue, 2009-07-21 at 13:46 -0400, Matt Neimeyer wrote: > > Has anyone come across / written a script that will convert one > > "flavor" or Dialect of SQL to another? > > > > I need to convert Visual FoxPro 6.0 style WHERE clauses to MySQL. > > > > For the most part the problems are converting VFP functions to the > > equivalent SQL. For example, Visual FoxPro has a function inlist() > > that is used like inlist(X,1,2,3) which converts to the MySQL query "X > > IN (1,2,3)". That's easy enough (relatively speaking) but VFP also has > > stuff like "EMPTY(X)" where any of Null, the Empty String (for Char), > > 0000-00-00 (or the VFP equivalent anyways for dates), False (for > > Boolean), 0 (for Numeric) are considered empty without needing to > > know the data type. So that starts getting a lot more complex since I'd > > need to check the data type of the field in the "right" table... to be > > able to convert it to something like (X is null OR X="") or (X is null > > OR x=0) etc... > > > > These are for customer "stored" queries... I've already manually > > converted "system" queries and I'm frustrated to the point of giving > > up and adding a column "untested" and let the end user figure it out > > but that seems bad from the standpoint of "lazy" and "poor customer > > experience". > > > > Thanks! > > > > Matt > > > > P.S. I'm also going to post this to the MySQL general list but my fear > > is that they MIGHT say "We only know MySQL so we can't help you with > > that other DBMS" I'm hoping that by posting here someone might say > > "well it's not to MySQL but I ran script XYZ to convert my VFP to > > PostgreSQL..." or similar. > > > > I'm not sure it's as easy as you think it might be. As you get more into > the various flavours of SQL, you notice their little idiosyncrasies that > only exist within that one particular language branch. As such, it's > often a task best left to people to try and convert from one to the > other, rather than leave it to a machine. How complex are the queries > that you are trying to convert anyway? I have to agree with Ash here. I was a FoxPro 2.6a DOS programmer (just at the beginning of VFP). We hired a programmer who did a lot of his queries in FoxPro's crippled version of SQL, but the rest of us just used regular FoxPro for queries. Remembering what FoxPro SQL was like, and now having worked with MySQL and PostgreSQL, I wouldn't wish the conversion of one SQL dialect to another on anyone. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
