* Program:	VFP2MySQL_Data_Upload.prg
* Author:	Michael J. Babcock, MCP
* Date:		08-25-08
* Purpose:	To upload the VFP data to the MySQL database.
* Preconds:	You might have to make sure the PK fields are NOT set to autoincrement when this is run.  Double-check that.
* Comments:	If you make any mods/improvements, please update the ProFox downloads page so we all benefit.  Cheers!

LOCAL liHandle as Integer, lcConnection as String, lcDBC as String, lcSQL as String, lcTable as String, loRec as String, ;
		llSuccess as Logical, lcMsg as String
LOCAL ARRAY laTables[1], laError[1]

CLOSE DATABASES ALL
SQLDISCONNECT(0) 
lcConnection = [DRIVER={MySQL ODBC 3.51 Driver};SERVER=your-server-here;UID=root;PWD=password;DATABASE=yourdb;option=131609]
lcDBC = [c:\yourdatapath\yourVFPdb.DBC]
SET PROCEDURE TO makeupdatable.prg && this is Paul McNett's jewel for easily making cursors updatable for remote dbms
liHandle = SQLSTRINGCONNECT(lcConnection)
IF liHandle > 0 THEN 
	OPEN DATABASE (lcDBC) NOUPDATE 
	ADBOBJECTS(laTables,"TABLE")
	FOR EACH lcTable IN laTables
		WAIT WINDOW NOWAIT "Copying " + lcTable
		USE (lcTable) IN 0 ALIAS VFPData
		lcSQL = [select * from ] + lcTable + [ where 1=0]
		IF SQLEXEC(liHandle,lcSQL,lcTable) = 1 THEN && got the cursor...now make it updatable
			IF makeupdatable(lcTable) THEN 
				* Now cycle through the vfp data and insert into MySQL recordset
				SELECT VFPData
				SCAN 
					WAIT WINDOW NOWAIT "Copying " + lcTable + " (" + TRANSFORM(RECNO()) + " of " + TRANSFORM(RECCOUNT()) + ")"
					SCATTER MEMO NAME loRec
					INSERT INTO (lcTable) FROM NAME loRec
				ENDSCAN

				* Now update the cursor
				llSuccess = TABLEUPDATE(2,.T.,lcTable)				
				IF NOT llSuccess THEN 
					AERROR(laError)
					lcMsg = laError(2)
					SET STEP on 
					MESSAGEBOX("Unable to save changes to " + lcTable + CHR(13) + CHR(13) + TRANSFORM(lcMsg),16,"Problem")
				ENDIF 
				
			ENDIF 
		ELSE 
			MESSAGEBOX("Problem getting cursor " + lcTable,16,"Problem")
		ENDIF

		* close table/cursor 
		USE IN (SELECT("VFPData"))
		USE IN (SELECT(lcTable))
	ENDFOR 
ELSE 
	MESSAGEBOX("Problem getting handle",16,"Problem")
ENDIF && liHandle > 0