Moving from FoxPro to MySql

It's been a while since I posted and I thought I'd get some more notes down. I work with some software written in FoxPro and am trying to update it since I it is now dead.

The first step in moving over to a new system is getting the databases moved over to a new database system. I chose MySQL since there are quite a few free tools you can use such as the MySQL Workbench. MySQL Workbench handles the database administration, you can enter queries into it, and you can map the databases using the EER tool. The last is what I am looking to use. The reason being that the tables weren't created with normal database rules and I'll need to abstract some of the data and move it around.

While looking for an easy way to do this, I came across a post by someone named Martin. Well great that has everything I need.

To go this route, you need to do a few things (assuming MySQL is already installed).

1. Set up a new schema


2. Install the MySQL ODBC connector


3. Set up a new System DSN


4. Perform some magic

Okay, setting up a new schema is simple enough. It can be empty. Just make sure you have a user that has "create" and "drop" permissions.

Now just run over to download the newest and greatest MySQL ODBC connector from the MySQL website. The install is a breeze.

To set up a new System DSN on Windows XP, just navigate to the Start Menu->Settings->Control Panel->Administrative Tools->Data Sources (ODBC). From there just click on the System DSN tab, go to add, find the MySQL Connector you installed, and click finish. Give it a name that's easy to remember and add in your database information and you're all set.

Now there is that magic part. Oh yeah just look down. Walking through the code, it will go through all the tables in a directory, and add them into the MySQL database. Most of this code is straight from "Martin" modified slightly with a few extra datatypes that will keep your program from freezing up.

GLOBAL gnConn
LOCAL lcPath, lcTable

lcPath = "g:\careplanet\data\" && This is the path

ON ERROR DO errhand     && errhand is the error handler procedure

gnConn = SQLCONNECT('MYSQLDSN', .T.)
IF gnConn <= 0
    MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
    DO errhand
ELSE
    MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
    CD (lcPath)
    ADIR(laFiles, '*.DBF')
    FOR i=1 TO ALEN(laFiles,1)
        lcTable = RTRIM(laFiles[i,1], 1, '.DBF')
        IF !CreateTable(lcPath + lcTable, lcTable)
            MESSAGEBOX('Unable to create table ' + lcTable, 16, 'Table Create Error')
            EXIT
        ENDIF
    ENDFOR
ENDIF

SQLDISCONNECT(gnConn)

***************************************
* CreateTable
***************************************
FUNCTION CreateTable
PARAMETERS lc_sourcetable, lc_desttable

USE (lc_sourcetable) AGAIN ALIAS a_conv

ln_c = AFIELDS(la_f)

lc_sql = "DROP TABLE IF EXISTS `"+lc_desttable + "`"

IF !MYSQLEXEC(lc_sql, .f., "Cannot drop table")
    RETURN .f.
ENDIF

lc_sql = "CREATE TABLE `"+lc_desttable + "` ("

FOR ln = 1 TO ln_c
    IF ln > 1
        lc_sql = lc_sql + ", "
    ENDIF

    lc_sql = lc_sql + "`" + la_f(ln, 1) + "` "

    DO CASE
    CASE INLIST(la_f(ln, 2), "C", "V")
        lc_sql = lc_sql + "VARCHAR(" + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + ")"
    CASE la_f(ln, 2) = "D"
        lc_sql = lc_sql + "DATE"
    CASE la_f(ln, 2) = "T"
        lc_sql = lc_sql + "DATETIME"
    CASE la_f(ln, 2) = "M"
        lc_sql = lc_sql + "TEXT"
    CASE la_f(ln, 2) = "L"
        lc_sql = lc_sql + "BIT"
    CASE INLIST(la_f(ln, 2), "N", "B", "F")
        lc_sql = lc_sql + "DECIMAL(" + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + ", " + ALLTRIM(STR(la_f(ln, 4), 10, 0)) + ")"
    CASE la_f(ln, 2) = "I"
        lc_sql = lc_sql + "INTEGER"
    CASE la_f(ln, 2) = "G"
        lc_sql = lc_sql + "MEDIUMBLOB"
    CASE INLIST(la_f(ln, 2), "W", "Q")
        lc_sql = lc_sql + "BLOB"
    CASE la_f(ln, 2) = "Y"
        lc_sql = lc_sql + "DECIMAL(19,4)"
    ENDCASE

    IF !la_f(ln, 5)
        lc_sql = lc_sql + " NULL"
    ELSE
        lc_sql = lc_sql + " NOT NULL"
    ENDIF

    IF !EMPTY(la_f(ln, 9))
        lc_sql = lc_sql + " DEFAULT `" + la_f(ln, 9) + "`"
    ENDIF
NEXT
lc_sql = lc_sql + ") ENGINE = INNODB"
IF !MYSQLEXEC(lc_sql, .f., "Cannot create table")
    RETURN .f.
ENDIF
RETURN .t.

***************************************
* mysqlexec
***************************************
FUNCTION mysqlexec
PARAMETERS lcQuery, llSmthing, lcMessage

LOCAL lcReturn

IF gnConn <= 0
    MESSAGEBOX('No active SQL Connection', 16, 'SQL Connect Error')
ELSE
    lcReturn = SQLEXEC(gnConn, lcQuery)
    IF lcReturn<0
        MESSAGEBOX(lcMessage + CHR(13) + lcQuery, 16, 'mysqlexec Error')
    ELSE
        RETURN .T.
    ENDIF
ENDIF
RETURN .F.

***************************************
* errhand
***************************************
PROCEDURE errhand

= AERROR(aErrorArray)  && Data from most recent error
CLEAR

? 'The error provided the following information'  && Display message

FOR n = 1 TO 7  && Display all elements of the array
    ? aErrorArray(n)
ENDFOR