Exporting DBF to MySQL

CCH : Have you ever wondered whether we can export our DBFs to MySQL ?
Well Sudip with the help of Rathinagiri (owner of HMG Forum) came out with this working version of DBF2MYSQL using Harbour+HMG as built by HMG IDE. BTW, this working prg also clearly demonstrates how we can connect to MySQL via Harbor + HMG.

BTW, the original codes for connecting to MySQL may have come from Mitja Podgornik





#Include "MiniGui.ch"
#include "DbStruct.ch"


Procedure Main()
Private Dbo := Nil
Private cHostName:= "localhost"
Private cUser:= "root"
Private cPassWord:= ""
Private cDatabase:= ""
Private cDbf:= ""
Private cTable:= ""
Private lOpened := .f.
Private lLogin:= .F.

DEFINE WINDOW frmMain ;
AT 5,5 ;
WIDTH 640 ;
HEIGHT 480;
TITLE "DBF To 'New' MySql Table (Created with help from Rathinagiri)" ;
MAIN ;
NOSIZE ;
ON INIT MySQL_Login() ;
ON RELEASE MySQL_Logout()


@ 30, 30 label lblDatabase value "Database:" ;
width 100
@ 30, 100 textbox txtDatabase ;
width 150

@ 30, 300 listbox lstDatabase width 200 height 200 ;
on dblclick { frmMain.txtDatabase.value := frmMain.lstDatabase.item(frmMain.lstDatabase.value)}


@ 200, 100 button cmdSelectDbf caption "Select &Dbf" width 150 ;
on click SelectDbf()

@ 240, 100 label lblDbf value "" width 150

@ 280, 100 button cmdExport caption "&Export to MySql" width 150 ;
on click ExportToMySql()

@ 350, 100 PROGRESSBAR pgbExport ;
VALUE 0 ;
RANGE 0 , 100 ;
WIDTH 300 ;
HEIGHT 26 ;


DEFINE STATUSBAR
STATUSITEM " "
END STATUSBAR

DEFINE MAIN MENU
POPUP "&File"
Item "E&xit" action thiswindow.release()
end popup
END MENU

END WINDOW

frmMain.pgbExport.visible := .f.
CENTER WINDOW frmMain
ACTIVATE WINDOW frmMain

Return Nil



static function MySql_login()
DEFINE WINDOW frmLogon ;
AT 0,0 ;
WIDTH 280 HEIGHT 190 ;
TITLE 'Login MySql' ;
MODAL ;
NOSYSMENU

@ 20,30 LABEL lblHostName ;
VALUE "HostName/IP" ;
WIDTH 150 ;
HEIGHT 35

@ 20,120 TEXTBOX txtHostName ;
HEIGHT 25 ;
VALUE cHostName ;
WIDTH 120 ;
ON ENTER iif( !Empty(frmLogon.txtHostName.Value), frmLogon.txtUser.SetFocus, frmLogon.txtHostName.SetFocus )

@ 50,30 LABEL lblUser ;
VALUE "User" ;
WIDTH 120 ;
HEIGHT 35

@ 50,120 TEXTBOX txtUser ;
HEIGHT 25 ;
VALUE cUser ;
WIDTH 120 ;
ON ENTER iif( !Empty(frmLogon.txtUser.Value), frmLogon.txtPassword.SetFocus, frmLogon.txtuser.SetFocus )

@ 80,30 LABEL lblPassword ;
VALUE "Password" ;
WIDTH 120 ;
HEIGHT 35

@ 80,120 TEXTBOX txtPassword ;
VALUE cPassWord ;
PASSWORD ;
ON ENTER frmLogon.cmdLogin.SetFocus

@ 120,30 BUTTON cmdLogin ;
CAPTION '&Login' ;
ACTION SQL_Connect()

@ 120,143 BUTTON cmdLogoff ;
CAPTION '&Cancel' ;
ACTION frmMain.Release

END WINDOW

CENTER WINDOW frmLogon
ACTIVATE WINDOW frmLogon

Return Nil






Function SQL_Connect()
cHostName:= AllTrim( frmLogon.txtHostName.Value )
cUser:= AllTrim( frmLogon.txtUser.Value )
cPassWord:= AllTrim( frmLogon.txtpassword.Value )

Dbo := TMySQLServer():New(cHostName, cUser, cPassWord )
If Dbo:NetErr()
MsGInfo("Error connecting to SQL server: " + Dbo:Error() )
Release Window ALL
Quit
Endif
lLogin := .T.
frmLogon.Release
frmMain.StatusBar.Item(1) := "MySql Server Connected!!!"

frmMain.lstDatabase.DeleteAllItems()
AEVAL( dbo:ListDbs(), { | a | frmMain.lstDatabase.AddItem( a ) } )

Return Nil




Function SelectDbf()
cDbf := alltrim(GetFile({{"DBF Files","*.dbf"}},"Select a dbf file",,.f.,.f.))
frmMain.lblDbf.caption := cDbf
if len(alltrim(cDbf)) > 0
if file(cDbf)
use &cDbf
lOpened := .t.
endif
endif
RETURN NIL



Function ExportToMySql()
local aStruct := {}, cSql, i, mFldNm, mFldtype, mFldLen, mFldDec, mSql

cDatabase := frmMain.txtDatabase.value
if empty(cDatabase)
MsgInfo("Please specify Database name")
frmMain.txtDatabase.setfocus
return nil
endif

if !DbExist(dbo, cDatabase)
if !miscsql(dbo, "CREATE DATABASE "+cDatabase)
return nil
endif
endif

if !miscsql(dbo, "USE "+cDatabase)
return nil
endif

if empty(cDbf)
MsgInfo("Please select a Dbf table")
frmMain.cmdSelectDbf.setfocus
return nil
endif

cTable := ALLTRIM(substr(cDbf,Rat('\',cDbf)+1))
cTable :=substr(cTable,1,len(cTable)-4)

select &cTable
aStruct = DbStruct()

mSql := "CREATE TABLE IF NOT EXISTS "+cTable+" ("

for i := 1 to len(aStruct)
mFldNm := aStruct[i, DBS_NAME]
mFldType := aStruct[i, DBS_TYPE]
mFldLen := aStruct[i, DBS_LEN]
mFldDec := aStruct[i, DBS_DEC]

if i > 1
mSql += ", "
endif
mSql += alltrim(mFldnm)+" "

do case
case mFldType = "C"
mSql += "CHAR("+LTRIM(STR(mFldLen))+")"
case mFldType = "D"
mSql += "DATE"
case mFldType = "T"
mSql += "DATETIME"
case mFldType = "N"
mSql += "DECIMAL("+LTRIM(STR(mFldLen))+", "+LTRIM(STR(mFldDec))+")"
case mFldType = "F"
mSql += "FLOAT"
case mFldType = "I"
mSql += "INTEGER"
case mFldType = "B"
mSql += "DOUBLE"
case mFldType = "Y"
mSql += "DECIMAL(20, 4)"
case mFldType = "L"
mSql += "SMALLINT(1)"
case mFldType = "M"
mSql += "TEXT"
case mFldType = "G"
mSql += "BLOB"
otherwise
msginfo("Invalid Field Type: "+mFldType)
return nil
endcase
next

mSql += ")"

//msginfo(mSql)

if !miscsql(dbo, mSql)
return nil
endif

frmMain.pgbExport.value := 0
frmMain.pgbExport.visible := .t.
select &cTable
go top
do while !eof()
do events
frmMain.pgbExport.value := recno()/reccount()*100

mSql := "INSERT INTO "+cTable+" VALUES ("

for i := 1 to len(aStruct)
mFldNm := aStruct[i, DBS_NAME]

if i > 1
mSql += ", "
endif

mSql += c2sql(&mFldNm)
next

mSql += ")"

if !miscsql(dbo, mSql)
msgbox("Problem in Query: "+mSql)
return nil
endif

select &cTable
skip
enddo
frmMain.pgbExport.value := 0
frmMain.pgbExport.visible := .f.

return nil










Function MySQL_Logout()
if Dbo != Nil
Dbo:Destroy()
Dbo := Nil
EndIf
Return Nil



Function MySQL_Connect()
If Dbo != Nil
Return Nil
Endif
Dbo := TMySQLServer():New(cHostName, cUser, cPassWord )
If Dbo:NetErr()
MsGInfo("Error connecting to SQL server: " + Dbo:Error() )
Release Window ALL
Quit
Endif
return nil



Function DbExist(dbo, cDatabase)
local aDbList := {}
cDatabase := lower(cDatabase)
aDbList := dbo:ListDbs()
if dbo:neterr()
msginfo("Error in varifying database list: "+dbo:error())
return .f.
endif
return (ascan(aDbList, {|x| lower(x) == lower(cDatabase)}) > 0)



Function TableExist(dbo, cTable)
local aTableList := {}
cTable = lower(cTable)
aTableList := dbo:ListTables()
if dbo:neterr()
msginfo("Error in varifying table list: "+dbo:error())
return .f.
endif
return (ascan(aTableList, {|x| lower(x) == lower(cTable)}) > 0)



//dbo is a public variable holding the database object
FUNCTION connect2db(host,user,password,dbname)
dbo := tmysqlserver():new(AllTrim(host),AllTrim(user),AllTrim(password))
IF dbo:NetErr()
msginfo(dbo:ERROR())
RETURN .f.
ENDIF
dbo:selectdb(dbname)
IF dbo:NetErr()
msginfo(dbo:ERROR())
RETURN .f.
ENDIF
//msginfo("Successfully Connected to the MySQL Server")
RETURN .t.



function sql(dbo1,qstr)
local i, j
local table := nil
local currow := nil
local tablearr := {}
local rowarr := {}
local curdateformat := set(_SET_DATEFORMAT)
set date ansi
table := dbo1:query(qstr)
set(_SET_DATEFORMAT,curdateformat)
if table:neterr()
msgstop(table:error())
table:destroy()
return tablearr
else
if table:lastrec() > 0
asize(tablearr,0)
for i := 1 to table:lastrec()
asize(rowarr,0)
currow := table:getrow(i)
for j := 1 to table:fcount()
aadd(rowarr,currow:fieldget(j))
next j
aadd(tablearr,aclone(rowarr))
next i
endif
table:destroy()
return tablearr
endif
return tablearr



function miscsql(dbo,qstr)
local curdateformat := set( _SET_DATEFORMAT)
set date ansi
table := dbo:query(qstr)
set( _SET_DATEFORMAT,curdateformat)
if table:NetErr()
msgstop(table:ERROR())
table:destroy()
return .f.
endif
table:destroy()
return .t.



function C2SQL(Value)

local cValue := ""
local cFormatoDaData := set(4)
do case
case Valtype(Value) == "N"
cValue := AllTrim(Str(Value))

case Valtype(Value) == "D"
if !empty(Value)
cValue := "'"+DTOS(value)+"'"
else
cValue := "'00000000'"
endif
case Valtype(Value) $ "CM"
IF Empty( Value)
cValue="''"
ELSE
cValue := "'"
Value:=DATATOSQL(value)
cValue+= value+ "'"
ENDIF

case Valtype(Value) == "L"
cValue := AllTrim(Str(iif(Value == .F., 0, 1)))

otherwise
cValue := "''" // NOTE: Here we lose values we cannot convert

endcase

return cValue


/*
So, our insert query may be like this.
qsuccess := miscsql(dbo,"insert into table1 (name, address1,city) values ("+c2sql(form1.name1.value)+","+c2sql(form1.address1.value)+","+c2sql(form1.city.value)+")")
*/


/*
Select query may be like this.
tablearray := sql(dbo,"select * from table1 where city = "+c2sql(form1.city.value))
*/