Index ... Oktober 2008: Foxpro to Servoy again
Index ... Oktober 2008: Foxpro to Servoy again |
|
When we started the conversion of our Foxpro data to Servoy, we tried to do it 'the Servoy way' using a JDBC driver to pull the fox data in. Our problem was however that the tables were so big that the JDBC driver choked on it. Trying to do it several thousand records at a time didn't help because the JDBC driver didn't know how to use the Foxpro indexes. It tried to pull in whole tables in memory for every query, not so smart if you're working on tables several Gigs big.
So now what?
Sybase has some commands to import data from other databases including Foxpro, problem: it's not scriptable. As an ISV, we are facing the conversion of several thousand tables so that was a dead end too.
Fortunately, Visual Foxpro can talk to any other database you can find an ODBC driver for and that saved the day. We now convert our raw Foxpro tables to our target database, Sybase or MySql, and than we do some conversion processing from within Servoy. Here is a small Foxpro tool to get you started on this road, in real live, you most probably want to change it a bit to suit your needs.
The program converts a table to another database (pick one from your available ODBC connections) using t-sql. I tried to stay away from proprietary t-sql commands. It works for SyBase and MySQL and probably for MS Sql Server but I didn't try that yet. It might be worth while exploring because Foxpro's MS SQL Server upsizing wizard is not something you'd want to use manually over and over again.
CLEAR ALL
CLOSE ALL
CLEAR
SET EXCLUSIVE OFF
SET DATE YMD
gnConn = SQLCONNECT() && Open an ODBC connection
gcTablePrefix = "_FOX_" && All upsized tablenames will be prefixed with this
gcFieldPrefix = "_" && All upsizes fieldnames will be prefixes with this
glTrimChar = .T. && TRIM trailing spaces in character fields
DO SizeUp WITH GetFile('dbf')
* DO SizeUp WITH GetFile('dbf'), .T. && Adding .T. as 2nd parameter will encode non-ascii character fields. Significantly slower,use only when needed
=SQLDISCONNECT(gnConn)
**************************************************************************************
PROCEDURE SizeUp
PARAMETERS fTable, lEncode
PRIVATE lnErrors, lnFieldCount, lcCommand
lnErrors = 0
SELECT 0
USE (fTable)
fTable = JustStem(fTable)
lnFieldCount = AFIELDS(gaMyArray)
lcCommand = "CREATE TABLE " + gcTablePrefix + fTable+ " ("
lcCommand = lcCommand + gcFieldPrefix+ 'PK INTEGER,'
FOR nCount = 1 TO lnFieldCount
IF gaMyArray(nCount,2) = 'G' && SKIP GENERAL FIELDS
LOOP
ENDIF
tField = gcFieldPrefix+ gaMyArray(nCount,1)+ ' '
t = gaMyArray(nCount,2)
DO CASE
CASE t = 'C'
ut = 'CHAR(' + ALLTRIM(STR(gaMyArray(nCount,3)))+ ')'
CASE t = 'N'
ut = 'NUMERIC(' + ALLTRIM(STR(gaMyArray(nCount,3)))+ ',' + ALLTRIM(STR(gaMyArray(nCount,4))) + ')'
CASE t = 'D'
ut = 'DATE'
CASE t = 'L'
ut = 'BIT'
OTHERWISE
ut = 'TEXT'
ENDCASE
lcCommand = lcCommand + tField
lcCommand = lcCommand + ut
IF nCount <> lnFieldCount
lcCommand = lcCommand + ','
ENDIF
ENDFOR
lcCommand = lcCommand + ')'
? 'SqlExec for DROP TABLE '+ gcTablePrefix + fTable+ ' returned ', SQLEXEC(gnConn, 'DROP TABLE '+ gcTablePrefix + fTable)
x = SQLEXEC(gnConn, lcCommand)
? 'SqlExec for CREATE TABLE '+ gcTablePrefix+ fTable+ ' returned ', x
IF x<0
DO disperror
ENDIF
SCAN ALL
lcCommand = "INSERT INTO "+ gcTablePrefix+ fTable + "("
lcCommand = lcCommand + gcFieldPrefix+ 'PK,'
FOR nCount = 1 TO lnFieldCount
IF gaMyArray(nCount,2) = 'G' && SKIP GENERAL FIELDS
LOOP
ENDIF
lcCommand = lcCommand + gcFieldPrefix + gaMyArray(nCount,1)
IF nCount <> lnFieldCount
lcCommand = lcCommand + ','
ENDIF
ENDFOR
lcCommand = lcCommand + ") VALUES ("
lcCommand = lcCommand + ALLTRIM(STR(RECNO()))+ ','
FOR nCount = 1 TO lnFieldCount
IF gaMyArray(nCount,2) = 'G' && SKIP GENERAL FIELD
LOOP
ENDIF
t = gaMyArray(nCount,2)
ut = EVALUATE(gaMyArray(nCount,1))
RawUt = ut
DO CASE
CASE t = 'N'
ut = ALLTRIM(STR(ut))
CASE t = 'D'
ut = IIF(EMPTY(ut), [null], [']+ STRTRAN(DTOC(ut), [/], [-])+ ['])
CASE t = 'L'
ut = IIF(ut, '1', '0')
OTHERWISE
IF lEncode
ut = encode(ut)
ENDIF
ut = STRTRAN(ut, [\], [\])
ut = STRTRAN(ut, ['], [''])
IF ! glTrimChar
ut = [']+ ut + [']
ELSE
ut = [']+ RTRIM(ut) + [']
ENDIF
ENDCASE
lcCommand = lcCommand + ut
IF nCount <> lnFieldCount
lcCommand = lcCommand + ','
ENDIF
ENDFOR
lcCommand = lcCommand + ")"
x = SQLEXEC(gnConn, lcCommand)
IF x < 0
DO disperror
lnErrors = lnErrors + 1
ENDIF
ENDSCAN
USE
IF lnErrors > 0
? lnErrors, ' errors'
ENDIF
PROCEDURE disperror
? lcCommand
= AERROR(aErrorArray) && Data from most recent error
? 'The error provided the following information' && Display message
FOR n = 1 TO 7 && Display all elements of the array
? aErrorArray(n)
ENDFOR
WAIT
RETURN
****************************************************************************************************
PROCEDURE encode
PARAMETERS tString
PRIVATE len, x ,r, y, z
tString = STRTRAN(tString, [\], [\\])
len = LEN(tString)
r = []
FOR i = 1 TO len
x = SUBSTR(tString,i,1)
z = ASC(x)
IF z > 127 OR z < 32
r = r + [\x]+ BaseX(16,z)
ELSE
r = r + x
ENDIF
ENDFOR
RETURN r
*****************************************************************************************************
*
* Convert lnX to base lnBase.
* Returns result as string
*
PROCEDURE BaseX
*
* FUNCTION BaseX
*
* Convert lnX to base lnBase.
* Returns result as string
*
PARAMETER lnBase, lnX
PRIVATE lcRes, lnRem
IF lnX=0
RETURN '0'
ENDIF
lcRes = ''
lnRem = lnX
DO WHILE lnRem <> 0
lcRes = BaseD(lnRem % lnBase)+ lcRes
lnRem = INT( lnRem / lnBase)
ENDDO
RETURN lcRes
*****************************************************************************************************
FUNCTION BaseD
PARAMETER lnX
IF lnX <= 9
RETURN CHR(lnX+ 48)
ELSE
RETURN CHR(lnX + 55)
ENDIF