Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting Foxpro Tables to MySQL 2

Status
Not open for further replies.
benny7

benny7

Programmer
Oct 3, 2004
31
GB
Is is possible to convert Foxpro tables into a MySQL database. If so how?
 
Sort by date Sort by votes
I don't know of any Foxpro to MySQL conversion wizard. But in principle MySQL has the capabilities/field types to convert a database. Difficulties start with automating the 1:1 recreation of referential integrity without user intervention. Stored procedures must of course be rewritten. That may be the hardest thing compared to minor problems with field/index types, rules and such.

The simplest way to work from within VFP is with the mysql ODBC driver, sending the appropriate commands "CREATE DATABASE ...", "CREATE TABLE ..." to the MySQL server.
And to do data export/import via CSV and/or by SQL Inserts and/or remote views.

Bye, Olaf.
 
Upvote 0 Downvote

Benny,

I've never used MySQL, but I have used SQL Server, and in general I've always found it necessary to write my own code to migrate the data, despite the fact that there are good import tools available.

In addition, be aware that converting the data is only a small part of the story. You application will not work straight away with a different back end, even if you change each of the tables to a remote view. There are many details that you need to be aware of.

I also agree with Olaf's good advice.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)
 
Upvote 0 Downvote
benny7

Have a .prg which creates a MySql database if it does not exist and then creates and adds a MySql table from the currently selected VFP table to the afore mentioned MySql database.

If this is of interest, I will attempt to find it over the weekend.

Please advise.

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommandertm.net
PDFcommandertm.com
 
Upvote 0 Downvote
This is a modified code used to transfer tables to Firebird

Code: 
PROCEDURE dbf2MySQL
PARAMETERS tcTable, tnConnHnd, tlAddData
*parameters:	tcTable		table name or full path
*				tnConnHnd	valid connection handle to MySQL database returned by SQLConnect()
*				tlAddData	transfer table data?
_t1=SECONDS()
LOCAL lcCreateTableSQL, lcCreateIndexSQL, lcDropTableSQL, lcInsertDataSQL, ;
		lcVFPTypes, lcFBTypes, lcOptions, lnFields, lnNDX
LOCAL ARRAY laVFPTypes[9], laFBTypes[9], laOptions[9]

USE (tcTable)

*types translation table
*VFP
lcVFPTypes = 'C^D^L^M^N^F^I^B^Y^T'
*Firebird
lcFBTypes  = 'CHAR^DATE^SMALLINT^TEXT^NUMERIC^FLOAT^INTEGER^DOUBLE^NUMERIC^DATETIME'
lcOptions  = 'C^ ^ ^ ^N^N^ ^ ^N^ ^ '
lnFields = AFIELDS(laFlds)
ALINES(laVFPTypes,lcVFPTypes,.t.,'^')
ALINES(laFBTypes,lcFBTypes,.t.,'^')
ALINES(laOptions,lcOptions,.t.,'^')
tcTable = ALIAS()		
*build SQL statements
lcDropTableSQL = [DROP TABLE ]+tcTable				

SET TEXTMERGE on
SET TEXTMERGE TO memvar lcCreateTableSQL NOSHOW 

\\CREATE TABLE <<tcTable>> (
FOR i=1 TO lnFields
	IF i>1
		\\, 
	ENDIF
	lnFieldTypeRange = ASCAN(laVFPTypes, laFlds[i,2])
	\\<<laFlds[i,1]>> <<laFBTypes[lnFieldTypeRange]>>
	IF laOptions[lnFieldTypeRange] = 'N'
		\\(<<laFlds[i,3]>>,<<laFlds[i,4]>>)
	ENDIF
	IF laOptions[lnFieldTypeRange] = 'C'
		\\(<<laFlds[i,3]>>)
	ENDIF
	IF laFlds[i,5] = .f. and !(laFlds[i,2] = 'D' OR laFlds[i,2]='T')
		\\ NOT NULL 
	ENDIF
NEXT
\\)
SET TEXTMERGE to
SQLEXEC(tnConnHnd,lcDropTableSQL)
IF SQLEXEC(tnConnHnd,lcCreateTableSQL) < 0
	AERROR(laError)
	DISPLAY MEMORY LIKE laError
	?lcCreateTableSql
ENDIF
SQLCOMMIT(tnConnHnd)
*build and execute SQL statements to add data if third parameter is .T.
lcMemoConversion = ''
IF tlAddData=.t.
	z=0
	SET TEXTMERGE on
	SET TEXTMERGE TO memvar lcInsertDataSQL NOSHOW 
	\\INSERT INTO <<tcTable>> (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\<<laFlds[i,1]>>
	NEXT
	\\) VALUES (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\?m.<<laFlds[i,1]>>
	NEXT
	\\) 
	SET TEXTMERGE to
	SQLPREPARE(tnConnHnd,lcInsertDataSql)
	SCAN
		SCATTER MEMVAR 
		z=z+1
		FOR i=1 TO lnFields
			lcVarName = 'm.'+laFlds[i,1]
			DO case
			CASE TYPE(laFlds[i,1]) = 'L'	&&logical type, need conversion
				&lcVarName = IIF(EVALUATE(laFlds[i,1]) = .T.,1,0)
			CASE TYPE(laFlds[i,1]) = 'D' AND EMPTY(EVALUATE(laFlds[i,1]))
				&lcVarName = '1900-01-01'
			CASE TYPE(laFlds[i,1]) = 'T' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty date, need conversion
				&lcVarName = '1900-01-01 00:00:00'
			CASE TYPE(laFlds[i,1]) = 'M' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty date, need conversion
				&lcVarName = ' '
			ENDCASE
		NEXT
		IF SQLEXEC(tnConnHnd) < 1
			CLEAR
			AERROR(xx)
			DISPLAY MEMORY LIKE xx
			?'insert error'
			SUSPEND
		ENDIF
		IF MOD(z,100) = 0
			SET MESSAGE to STR(z)
		endif
	ENDSCAN
ENDIF
CLEAR
?
?SECONDS()-_t1

*build and execute SQL statements to create indexes
lnNDX = ATAGINFO(laIndexes)
FOR i=1 TO lnNDX
	lcIndexName = ALIAS()+'_'+laIndexes[i,1]
	IF laIndexes[i,2] = 'PRIMARY'
		lcCreateIndexSQL = [ALTER TABLE ]+tcTable+[ ADD PRIMARY KEY (]+laIndexes[i,3]+[)]
	ELSE 
		lcCreateIndexSQL = [CREATE INDEX ]+tcTable+[_]+ALLTRIM(STR(I))+[ ON ]+tcTable+[ (]+LaIndexes[i,3]+[)]
	ENDIF
	?lcCreateIndexSQL
	
	SQLEXEC(tnConnHnd,lcCreateIndexSQL)
	SQLCOMMIT(tnConnHnd)
NEXT

RETURN
 
Upvote 0 Downvote
The code for this originated in thread184-973130 and credit must go to Danko Josic for developing it and to Eguy for posting it in the VFP forum.

It has been reworked for use under VFP 9.0.
Code: 
CLEAR

SET TALK OFF
SET SAFETY OFF
SET DATE BRITISH

IF EMPTY(ALIAS())
	MESSAGEBOX([There is no table selected]	,;
		48									,;
		[No table]							,;
		3000)
	RETURN
ENDIF

lcAlias 	= LOWER(ALIAS())
lnReccount	= RECCOUNT(ALIAS())
SET TEXTMERGE ON TO (lcAlias) + [.sql]	NOSHOW

\\SET FOREIGN_KEY_CHECKS=0;
\
\DROP DATABASE IF EXISTS `test`;
\
\CREATE DATABASE `test`;
\
\USE `test`;
\
\DROP TABLE IF EXISTS `<<lcAlias>>`;	
\
\#
\# Structure for the `<<lcAlias>>` table : 
\#
\

\CREATE TABLE IF NOT EXISTS `<<lcAlias>>` (

lnFieldCount = AFIELDS(laDbf)  && Create array
*!*	Create field names and values
FOR i = 1 TO lnFieldCount
	lcField =  [`] + laDbf(i,1) + [` ]
	DO CASE
	CASE laDbf[i,2] = [C]	&& Character
		lcField = lcField 				;
			+ 'char (' 					;
			+ TRANSFORM(laDbf[i,3])		;
			+ ')'
	CASE laDbf[i,2] = [I]	&& Integer
		lcField = lcField 				;
			+ 'int (' 					;
			+ TRANSFORM(laDbf[i,3])		;
			+ ')'
	CASE laDbf[i,2] = [N]	&& Numeric
		IF EMPTY(laDbf[i,4])
			lcField = lcField 				;
				+ 'int (' 					;
				+ TRANSFORM(laDbf[i,3])		;
				+ ')'
		ELSE
			lcField = lcField 	;
				+ 'float (' 				;
				+ TRANSFORM(laDbf[i,3])		;
				+ ','						;
				+ TRANSFORM(laDbf[i,4])		;				
				+ ')'		
		ENDIF
	CASE laDbf[i,2] = [D]	&& Date
		lcField = lcField + 'date'
	CASE laDbf[i,2] = [M]	&& Memo
		lcField = lcField + 'text'
	CASE laDbf[i,2] = [L]	&& Logical
		lcField = lcField + 'char (1)'
	CASE laDbf[i,2] = [Q]	&& Varbinary
		lcField = lcField + 'longtext'			
	CASE laDbf[i,2] = [T]	&& DATETIME
		lcField = lcField + 'datetime'
	CASE laDbf[i,2] = [V]	&& Varchar
		lcField = lcField 	;
			+ 'varchar (' 				;
			+ TRANSFORM(laDbf[i,3])		;
			+ ')'
	CASE laDbf[i,2] = [W]	&& Blob
		lcField = lcField + 'longtext'	
	CASE laDbf[i,2] = [Y]	&& Currency
		&& Convert to numeric - no equivalent
	OTHERWISE				&& Field type not recognized - we have problem
		lcField = lcField + '***'   
	ENDCASE

	DO CASE
	CASE i = 1
		\	<<lcField>>,
	CASE i = lnFieldCount	
		\	<<lcField>>
		\) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	OTHERWISE
		\	<<lcField>>,	
	ENDC		
NEXT

\
\#
\# Data for the `<<lcAlias>>` table : 
\#
\
\INSERT INTO `<<lcAlias>>` (<<field_names()>>) VALUES

SCAN
	IF lnReccount = RECNO(ALIAS())
		\	(<<field_content()>>);
	ELSE
		\	(<<field_content()>>),	
	ENDI
ENDSCAN

\
\COMMIT;

SET TEXTMERGE OFF
SET TEXTMERGE TO				

MODI COMM (lcAlias + '.sql')

RETURN

*----------------------------------------------------------------------------
* Reads record data, modifying "'" to "`" and ',' to '.' in numeric fields
* This function is a good place to escape characters, add some field definitions etc...
*----------------------------------------------------------------------------
FUNCTION field_content
PRIVATE lcReturnValue, i, lnFieldcount, laDbf
DIMENSION laDbf[1]
lnFieldcount = AFIELDS(laDbf)  && Create array
lcReturnValue = []

FOR i = 1 TO lnFieldcount
	lcValue = EVAL(laDbf[i,1])
	DO CASE
	CASE EMPTY(lcValue)		&& Empty field
		lcField= 'NULL'
	CASE laDbf[i,2] = 	[C]	&& Character
		lcField= "'" + CHRTRAN(ALLTRIM(lcValue),"'","`") + "'"
	CASE laDbf[i,2] = 	[D]	&& Date
		lcField= '"' + DTOS(lcValue) + '"'
	CASE laDbf[i,2] = 	[I]	&& Integer	
		lcField= TRANSFORM(lcValue)
	CASE laDbf[i,2] = 	[N]	&& Numeric
		lcField= CHRTRAN(PADR(lcValue,laDbf(i,3)),',','.')
	CASE laDbf[i,2] = 	[M]	&& Memo
		lcField= "'" + CHRTRAN(ALLTRIM(lcValue),"'","`") + "'"
	CASE laDbf[i,2] = 	[L]	&& Logical
		lcField= IIF(lcValue,'1','0')
	CASE laDbf[i,2] = 	[Q]	&& Varbinary
		lcField= "'" + CHRTRAN(ALLTRIM(lcValue),"'","`") + "'"	
	CASE laDbf[i,2] = 	[T]	&& DateTime
		lcField= '"' + LEFT(DTOS(lcValue) + CHRTRAN(TIME(lcValue),[:],[]),14) + '"'		
	CASE laDbf[i,2] = 	[V]	&& Varchar
		lcField= "'" + CHRTRAN(ALLTRIM(lcValue),"'","`") + "'"
	CASE laDbf[i,2] = 	[W]	&& Blob
		lcField= "'" + CHRTRAN(ALLTRIM(lcValue),"'","`") + "'"
	CASE laDbf[i,2] = 	[Y]	&& Currency
		&& Convert to numeric - no equivalent	
	OTHERWISE
		lcField= '***'   	&& We have a problem
	ENDCASE
	lcReturnValue = lcReturnValue + lcField
	IF i < lnFieldcount
		lcReturnValue = lcReturnValue + ','
	ENDIF
NEXT
RETURN ALLTRIM(lcReturnValue)

*----------------------------------------------------------------------------
* Returns field names of selected table
*----------------------------------------------------------------------------
FUNCTION field_names
PRIVATE lcReturnValue, i, lnFieldcount, laDbf
DIMENSION laDbf[1]
lnFieldCount 	= AFIELDS(laDbf)  && Create array
lcReturnValue 	= []

FOR i = 1 TO lnFieldCount
	lcReturnValue = lcReturnValue + [`] + laDbf[i,1] + [`]
	IF i < lnFieldCount
		lcReturnValue = lcReturnValue + [,]
	ENDIF
NEXT
RETURN lcReturnValue
Substitute your own names, etc for your own version.





FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommandertm.net
PDFcommandertm.com
 
Upvote 0 Downvote
This has currently been put on hold. Will come back to it. However thank you all for your assistance
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmerlinn
  • Locked
  • Question
FoxPro button status
2
Replies
32
Views
3K
Scott24x7
Scott24x7
A_Radiolog_inFoxPro
  • Locked
  • Question
Python code execution though Foxpro environment
Replies
11
Views
1K
Chris Miller
Chris Miller
Premal Vala
  • Locked
  • Question
VFP with MYSQL - mySQL server gone away
Replies
8
Views
904
Chris Miller
Chris Miller
ravicoder
  • Locked
  • Question
OCR with visual foxpro
Replies
3
Views
1K
Gerrit Broekhuis
Gerrit Broekhuis
gbcpastor
  • Locked
  • Question
Export Foxpro 9 DBF to Dbase 5 File 1
2
Replies
22
Views
2K
Don Child
Don Child

Part and Inventory Search

Sponsor

Back
Top