FoxPro -> MySQL

I'm building a website for a client that uses a FoxPro desktop database system.

The data that should be shared between the database and website is essentially a list of members (fairly flat structure: their contact details, a few flag fields etc)

They'd like any changes that are made to these member details to be automatically synched through to the website (mysql database). It doesn't need to be totally instant but the process needs to be easy.

My question is what are the simplest approaches that could be used here? they are willing to compromise a bit to save development time/cost.

Is there an easy way for FoxPro开发者_如何学C could talk directly to my online MySQL database?

Could FoxPro create an XML or CSV type file and pass it to a web script? (which i then parse and update MySQL with)

Any suggestions appreciated.

You could use either ODBC or OLE DB from Visual FoxPro. Using ODBC is typically fairly straightforward.

• Use SQLConnect or SQLStringConnect to connect to the server.
• Some connection string examples show what might work for the SQLStringConnect call.
• Then call SQLExec to run some queries.

Foxpro has an intrinsic function CURSORTOXML, which will take a table and convert it to XML It is fairly well documented in the help files, but here is a simple example of its use

LOCAl lcXML,lnRecords

use my_table

lnRecords=CURSORTOXML('my_table','lcXML',0)

? lnRecords
? lcXML

Hope this helps

Try this:

1. Create a Shared Folder on your Web Server.
2. Create a CSV of your Data and save on the Shared Folder.
3. Create a Cron Jobs/ Scheduler to parse and dump the data to your Mysql.

This works for me.

Get the odbc driver from here: https://dev.mysql.com/downloads/connector/odbc/

You may also need Microsoft Visual C++ 2010 Redistributable Package (x86) vcredist_x86.exe

Use this code to establish the mysql connection

PUBLIC server,port,db,uid,pw
server="" & use mysql ip
port="" & port mysql is open on
db="" & db name
uid="" & user anme
pw="" & password

mysql = SQLSTRINGCONNECT('Driver={MySQL ODBC 5.3 Unicode Driver};Server=' + server + ';Port=' + port + ';Database=' + db + ';uid=' + uid + ';Pwd=' + pw + ';',.T.)
IF VARTYPE(mysql) == 'L' OR m.mysql < 0
    MESSAGEBOX('MySQL Connection Failed. Logging will be disabled for this session.',16,'MySQL Error',3500)
ENDIF

Here's an example to do an insert into a mysql table. I use it for logging user actions into mysql.

IF VARTYPE(mysql) != 'L' AND m.mysql >= 0
    SQLPREPARE (;
        m.mysql;
        ,'INSERT INTO logging (computer,operator,application,program,version,action) VALUES (;
            ?COMPUTERNAME ;
            ,?OPERATOR ;
            ,?thisApplication ;
            ,?thisProgram ;
            ,?thisVersion ;
            ,?logMessage;
            );';
        )
    SQLEXEC(m.mysql)
ENDIF

You can use XML for sending information to web application by HTTP REQUEST. to create/read XML in foxpro, you can use CURSORTOXML/XMLTOCURSOR.

In your web application, you can create a web service to receive/send XML data.

If you want another data type, you can use JSON data. Use QDFOXJSON https://qdfoxjson.codeplex.com/

I'd say your quickest/easiest bet is probably to do the following via a nightly script:

1. export your FoxPro data to XML
2. push the data into the MySQL database using something like Python or Java

In the long run though, you're probably better off totally ditching FoxPro after the initial import ;)