I started converting my existing .DBF files into MYSQL. And I am using TMysql Class for conversion.
I don't know why, for a .DBF which is having 30 Fields and 20000 records is taking almost 30 mins.
Where as In tried to conver the same .DBF with "Kleyber Derick's" DBF TO SQL Converter, it took
49 seconds.
I am using the following code. Can anybody help me where I am going wrong:
- Code: Select all Expand view
*******************************************************************************
*** FUNCTION MySqlConnect(cServer, cUser, cPassword) to Connect to MySql DB ***
*******************************************************************************
FUNCTION MySqlConnect(cServer, cUserName, cPassword)
oServer := TMySQLServer():New(ALLTRIM(cServer), ALLTRIM(cUserName), ALLTRIM(cPassword))
IF oServer:NetErr()
lConnected := .F.
YesNo(oServer:Error())
ELSE
lConnected := .T.
ENDIF
RETURN lConnected
*******************************************************************************
**** FUNCTION AscToMySql(cDatabase,cServer,cUser,cPassword,oMeter1,nMeter1, ***
*** oMeter2,nMeter2) to convert ASC to MySql Db ***
*******************************************************************************
FUNCTION AscToMySql(cDataBase, cServer, cUserName, cPassword, cPort, oMeter1, ;
nMeter1, oMeter2, nMeter2, aReserved)
LOCAL aDir, aFields := {}, aField, n, cField, lFound := .F.
LOCAL cFile, aFiles1 := {}, lSelected := .F.
oStartTime:lDisColors := .F.
oStartTime:nClrTextDis := CLR_BLUE
oStartTime:Refresh()
cCurrentTime := time()
oCurrentTime:lDisColors := .F.
oCurrentTime:nClrTextDis := CLR_HBLUE
oCurrentTime:Refresh()
cLapsedTime := "00:00:00"
oLapsedTime:lDisColors := .F.
oLapsedTime:nClrTextDis := CLR_GREEN
oLapsedTime:Refresh()
IF lExportDel
SET DELETED ON
ELSE
SET DELETED OFF
ENDIF
cUserName := ALLTRIM(cUserName)
cPassword := ALLTRIM(cPassword)
CursorWait()
IF lConnected
IF lDeciAsFloat
oServer:lDeciAsFloat := .T.
ELSE
oServer:lDeciAsFloat := .F.
ENDIF
ENDIF
IF oServer:DBExist( ALLTRIM(cDatabase) ) = 0 // 0 Indicates that the database does not exist
CursorWait()
oServer:CreateDatabase( ALLTRIM(cDatabase) )
CursorArrow()
ELSE
IF lNewDatabase
CursorWait()
oServer:DeleteDatabase( ALLTRIM(cDatabase) )
oServer:CreateDatabase( ALLTRIM(cDatabase) )
CursorArrow()
ENDIF
ENDIF
oServer:SelectDB( ALLTRIM(cDatabase) )
IF ASCAN(oTree:aSelected,{|x|x[2] = .T.}) >0
lSelected := .T.
ENDIF
FOR n = 1 TO LEN(aFiles)
* Reconnect to Server for every 10 files
IF n % 10 = 0
oServer:End()
lConnected := MySqlConnect(cServer, cUserName, cPassword)
oServer:SelectDB( ALLTRIM(cDatabase) )
ENDIF
IF lSelected
IF oTree:aSelected[n,2]
cFile := UPPER(ALLTRIM(aFiles[n-1]))
IF ASCAN(aReserved, {|x|UPPER(ALLTRIM(x[2])) == cFile}) >0
LOOP
ENDIF
USE &cFile ALIAS cFile
aFields := DbStruct()
IF oServer:TableExist( STRTRAN(ALLTRIM(cFile),".ASC","") ) >0
IF lDeleteTable
IF oServer:DeleteTable(STRTRAN(ALLTRIM(cFile),".ASC",""))
*YesNo("Table "+ STRTRAN(ALLTRIM(cFile),".ASC","")+" Deleted successfully")
ELSE
*YesNo(oServer:Error())
RETURN nil //QUIT
ENDIF
ENDIF
ELSE
aFields := DbStruct() // Load the structure of the dbf in an array
FOR EACH aField IN aFields
IF HB_EnumIndex() <= 2
AADD( aField, .T. ) // Add a new column to the array of definitions
// of the first two fields. That it indicates
// that these columns are NOT NULL.
// This is to be able to include them like part
// of the primary key.
ELSE
EXIT
ENDIF
NEXT
ENDIF
AADD(aFields,{"DELETED","L",1,0})
AADD(aFields,{"RECORD_NO","N",6,0})
IF lDeleteTable
CursorWait()
IF oServer:CreateTable(STRTRAN(ALLTRIM(cFile),".ASC",""), aFields, "RECORD_NO")
*YesNo("Table : "+ STRTRAN(ALLTRIM(cFile),".ASC","")+" Created successfully")
ELSE
*YesNo(oServer:Error())
CursorArrow()
RETURN nil // QUIT
ENDIF
ENDIF
CursorArrow()
IF RECCOUNT() >0
oMeter2:SetTotal(RecCount())
* Table Population from .ASC file mechanism
PopulateTableData(cDatabase, aFields, cFile, oMeter2, nMeter2)
ELSE
oMeter2:SetTotal(100)
oMeter2:Set(100)
oMeter2:Refresh()
ENDIF
USE
oTree:SetCurSel(n)
ENDIF
ENDIF
nMeter1 := n
oMeter1:Set(nMeter1)
oMeter1:Refresh()
NEXT
I tried with/without Reconnecting the server for every 10 files and creating a new querry for every 250 records.
without the expected result.
RETURN nil
*******************************************************************************
*** FUNCTION PopulateTableData(aFields,cFile,oMeter2,nMeter2) to Pupulate ***
*** Every ASC file data in its MySql Table ***
*******************************************************************************
FUNCTION PopulateTableData(cDatabase, aFields, cFile, oMeter2, nMeter2)
LOCAL i, n, nCounter := 0, nPos := 1, x, cField, lFound := .F.
LOCAL cSource := "", oTable
nMeter2 := 0
cFile := lower(STRTRAN(ALLTRIM(cFile),".ASC",""))
oTable := oServer:Query("SELECT * FROM "+ALLTRIM(cDatabase)+"."+ALLTRIM(cFile), .F. )
IF oTable:NetErr()
YesNo(oTable:Error())
RETURN nil
ENDIF
lDisConnect := .T.
oDisConnect:Refresh()
cFile->(DbGoTop())
DO WHILE .NOT. cFile->(EOF())
* Create Query for every nRecsPerQuery
IF cFile->(RECNO()) % nRecsPerQuery = 0
IF VALTYPE(oTable) = "O"
oTable:End()
ENDIF
oTable := oServer:Query("SELECT * FROM "+ALLTRIM(cDatabase)+"."+ALLTRIM(cFile), .F. )
IF oTable:NetErr()
YesNo(oTable:Error())
EXIT
ENDIF
ENDIF
WITH OBJECT oTable
:GetBlankRow()
FOR n = 1 TO LEN(aFields)-2
oTable:FieldPut(oTable:FieldName(n),FieldGet(n))
NEXT
IF !oTable:Append()
EXIT
ENDIF
cCurrentTime := time()
oCurrentTime:Refresh()
cLapsedTime := ElapTime(cStartTime, cCurrentTime)
oLapsedTime:Refresh()
END
cFile->(DbSkip())
nMeter2++
oMeter2:Set(nMeter2)
oMeter2:Refresh()
ENDDO
cFile->(DbCloseArea())
oTable:End()
lDisConnect := .F.
oDisConnect:Refresh()
RETURN nil
Thanks,
- Ramesh Babu P
