Re:How To Convert between FoxPro .DBF and MySql Database
Posted by: harsha kiran
Date: October 31, 2009 01:52AM

hi everyone i m having a foxpro application and i want to convert foxpro dbf files into mysql db files. I got this program which will do the above task.But i dont know how to use it, if it works or not.can any one help me in achieveing my task.

The way to use it is:
in foxpro

do programname with dbf-filename-no-ext

it will read the structure and create the mysqldump file

to import into mySql

mysql -u user -ppassword database < dbf-filename-no-ext.sql

and bingo it's in there



**start of program**

PARAMETERS mdbf

mdbffile = mdbf + ".dbf"
* where you want mysqldump files to go
mexportfile = "c:\sumcd\sql\" + mdbf + ".sql"

* where dbf files are
SET DEFAULT TO c:\sumcd

IF !FILE(mdbffile)
messagebox("not a valid file")
CANCEL
ENDIF

set century on
set hours to 24

mjunk = 0

*
* open/create file to hold sql commands
* target is a linux box so only lf "chr(10)" at end of line
*

*delete sql dump file if exists (important)
DELETE FILE &mexportfile

IF FILE(mexportfile) && Does file exist?
mimporthandle = FOPEN(mexportfile,11) && If so, open write only
ELSE
mimporthandle = FCREATE(mexportfile) && If not, create it
ENDIF

IF mimporthandle < 1
messagebox("could not open output file")
CANCEL
ENDIF

use &mdbf
set safety off
*remove deleted records
pack
go top

*
* setup array to hold field attributes
*

mfield_cnt = AFIELDS(marray) && Create array

*
* Create text to build create table commands
*

mtext = "DROP TABLE IF EXISTS `" + mdbf + "`;" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)

mtext = "CREATE TABLE IF NOT EXISTS `" + mdbf + "`(" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)


FOR nCount = 1 TO mfield_cnt
mtext = ""
mtext = "`" + marray(nCount,1) + "` " && field name

DO CASE
CASE marray(nCount,2) = "C"
mtext = mtext + "varchar(" + alltrim(str(marray(nCount,3))) + ") "
CASE INLIST(marray(nCount,2), 'N', 'F', 'B')
mtext = mtext + "decimal(" + alltrim(str(marray(nCount,3))) + "," + alltrim(str(marray(nCount,4))) + ") "
CASE marray(nCount,2) = "D"
mtext = mtext + "date "
CASE marray(nCount,2) = "L"
mtext = mtext + "tinyint(1) "
CASE marray(nCount,2) = "I"
mtext = mtext + "int(" + alltrim(str(marray(nCount,3))) + ") "
CASE marray(nCount,2) = "T"
mtext = mtext + "datetime "
CASE marray(nCount,2) = "M"
mtext = mtext + "text "
OTHERWISE
mtext = mtext + "unknown data type "
ENDCASE

if !marray(nCount, 5)
mtext = mtext + "NOT NULL "
endif

if nCount < mfield_cnt
mtext = mtext + "default '" + marray(nCount,9) + "'," + chr(10)
else
mtext = mtext + "default '" + marray(nCount,9) + "'" + chr(10)
endif

mjunk = FWRITE(mimporthandle, mtext)

ENDFOR

*
* final line for create table section
*

mtext = ")TYPE=MyISAM COMMENT='" + mdbf + "';" + chr(10) + chr(10)
mjunk = FWRITE(mimporthandle, mtext)

go top

*
* start data loading section
*

do while !eof()

*
*beginning of line
*
mtext = "INSERT INTO " + mdbf + " VALUES ("

FOR nCount = 1 TO mfield_cnt

*
*build values
*

if nCount > 1 && don't put a comma in the first time
mtext = mtext + ", "
endif

DO CASE
CASE marray(nCount,2) = "C" && characters
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + strtran(alltrim(&marray(nCount,1)), "'", "\'") + "'"
else
mtext = mtext + "'NULL'"
endif
CASE INLIST(marray(nCount,2), 'N', 'F', 'B') && numeric,float,double
if !isnull(&marray(nCount,1))
mlen = marray(nCount,3)
mdec = marray(nCount,4)
mtext = mtext + "'" + alltrim(str(&marray(nCount,1),mlen,mdec)) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "I" && Integer
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + alltrim(str(&marray(nCount,1))) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "D" && date
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + substr(dtos(&marray(nCount,1)), 1, 4)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 5, 2)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 7, 2) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "T" && date time
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + substr(dtos(&marray(nCount,1)), 1, 4)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 5, 2)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 7, 2)
mtext = mtext + " " + substr(ttoc(&marray(nCount,1)), 12, 8) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "L" && logical
if !isnull(&marray(nCount,1))
if (&marray(nCount,1))
mtext = mtext + "'1'"
else
mtext = mtext + "'0'"
endif
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "M" && memo
if !isnull(&marray(nCount,1))
if memlines(&marray(nCount,1)) = 0
mtext = mtext + "''"
else
STORE MEMLINES(&marray(nCount,1)) TO gnNumLines
mtext = mtext + "'"
FOR gnCount = 1 TO gnNumLines
mtext = mtext + strtran(alltrim(MLINE(&marray(nCount,1), gnCount)), "'", "\'")
NEXT
mtext = mtext + "'"
endif
else
mtext = mtext + "'NULL'"
endif
OTHERWISE
mtext = mtext + "unknown data type "
ENDCASE
if isnull(mtext)
set step on
endif
*
*end of data load line
*
ENDFOR

mtext = mtext + ");" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)
skip

enddo

use
fclose(mimporthandle)



Edited 1 time(s). Last edit at 11/03/2009 01:18AM by harsha kiran.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re:How To Convert between FoxPro .DBF and MySql Database
14910
October 31, 2009 01:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.