Uploading Visual FoxPro Data to MySQL

[Neil Toulouse]

Hi there!

I currently have a routine written in VFP that enables me to select a dataset and upsize it to MS SQL Server and then populate the various tables. I am now in the process of converting that routine for use with MySQL.

With the MS SQL Sever version, I populated the tables by building a string of 200 INSERT INTO statements, sending that string to the server then build the next string of 200 INSERTs, etc. Each INSERT statement is separated with a semi-colon, and all works fine.

If I do the same thing with MySQL, however, I get an error on the second INSERT statement which I have narrowed down to the semi-colon!!

Basically if I issue this command to MySQL:

INSERT INTO table VALUES ( 1 );INSERT INTO table VALUES ( 2 );

... I recevive the error 'there is a problem with your SQL before INSERT', which I have narrowed down to the second INSERT statement.

If I issue them as two separate commands, ie:

Send this command:
INSERT INTO table VALUES ( 1 );

and then send this command:
INSERT INTO table VALUES ( 2 );

...it works fine but I get an overall major performance drop due to the amount of records that need to be uploaded!

I have tried the multiple line INSERT commands with a .SQL file and that also works OK!

Anyone any ideas?

I am *VERY* new to MySQL so any help would be much appreciated!! (is there a flag that needs to be set?)

 

[WSanders]

You have a few choices...
If you are using remote views...
do a scan..endscan with your inserts..

IF YOU have a lot of data..
export to csv, ftp the lot, then import into mysql.

expecting a semi-colon to be a CRLF separator between spt commands in hazardous, at best...
William Sanders, Electronic Filing Group

http://window.to/vfoxpro