Migrating from Access to MySql
Please try this program:
#include "fivewin.ch"
#include "adodef.ch"
function Main()
local oCn, oRs
? "Connecting to mysql server"
ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE FWH USER root PASSWORD yourpassword
if oCn == nil .or. oCn:State == 0
? "Fail to connect to mysql"
return nil
endif
? "Importing CUSTOMER table from c:\fwh\samples\xbtrest.mdb"
MDB2MYSQL( oCn, "c:\fwh\samples\xbrtest.mdb", "CUSTOMER" )
? "Browse the imported table"
oRs := FW_OpenRecordSet( oCn, "CUSTOMER" )
XBROWSER oRs AUTOSORT
oRs:Close()
oCn:Close()
return nil
function MDB2MYSQL( oCnMySql, cMdb, cTable )
local oCnMDB, oRs, aRows, aStruct, lHasAutoInc, aCols
local cSql, cExecSql, nRow, n
local nBatchSize := 100
oCnMDB := FW_OpenAdoConnection( cMdb, .t. )
oRs := FW_OpenRecordSet( oCnMDB, cTable )
aStruct := FWAdoStruct( oRs )
if oRs:RecordCount() > 0
aRows := oRs:GetRows()
oRs:MoveFirst()
oRs:Close()
oCnMDB:Close()
endif
TRY
oCnMySQL:Execute( "DROP TABLE " + cTable )
CATCH
END
lHasAutoInc := ( aStruct[ 1, 2 ] == '+' )
FWAdoCreateTable( cTable, aStruct, oCnMySQL, !lHasAutoInc )
aCols := ArrTranspose( aStruct )[ 1 ]
ADel( aCols, 1, .t. )
AEval( aCols, { |c,i| aCols[ i ] := Chr(96) + c + Chr(96) } )
aCols := FW_ArrayAsList( aCols )
cSql := "INSERT INTO " + cTable + " ( " + aCols + " ) VALUES "
n := 0
for nRow := 1 to Len( aRows )
aCols := aRows[ nRow ]
if lHasAutoInc
ADel( aCols, 1, .t. )
endif
aCols := FW_ValToSQL( aCols )
if n == 0
cExecSQL := cSql + aCols
else
cExecSQL += "," + aCols
endif
if n >= nBatchSize .or. nRow >= Len( aRows )
TRY
oCnMySQL:Execute( cExecSQL )
CATCH
FW_ShowAdoError()
QUIT
END
n := 0
else
n++
endif
next nRow
return nil
In the above program, please replace your servername, database. username and password.
If the mdb file is password protected:
MDB2MYSQL( oCn, "c:\fwh\samples\xbrtest.mdb,mypassword", "CUSTOMER" )
