Thank you Olaf.
I am a novice, but wrote this to solve my problem. I tested it with several hundred thousand records over 30 files, and all went well. I would be interested in any comments or improvements that the community can make
Thanks again.
Platform: Windows 7 64-bit
PostgreSQL: 9.1
Vfp: 9.0 SP2
* copy vfp to postgreSQL
* October 30, 2011
*
* copy peewee data into the postgreSQL database.
*
* Program Structure:
* a. create connection
* b. run sql statements
* c. disconnect
* set step on <--to debug
#define crlf chr(13)+chr(10)
* create connection
close databases all
open database c:\peewee\data\peewee
local lnconn as integer
lnconn = sqlconnect("PostgreSQL","postgres","Left out intentionally",.t.)
if lnconn < 0
error message()
return
endif
* insert data from vfp to postgreSQL
* vfp must be run as administrator
* create text file, c:\aaa.txt
set safety off
if file("c:\aaa.txt")
erase c:\aaa.txt
endif
* get table list to file.
display tables to file c:\aaa noconsole
*
local xstr,k,tablename,sourcelist,sourcelist2,sourcelist3,j,h,p,ff,gg,lnretval
local lcSQLSTRING,aflds[1,18],atables[1,1],nlines
select 0
m.xstr = filetostr("c:\aaa.txt")
m.nlines = alines(atables,m.xstr,1+4+8,crlf)
for m.k = 3 to m.nlines
* each line starts with the table name. each name ends with a space.
tablename = strextract(m.atables[m.k],""," ",1,1)
* "sourcetable" is the actual alias name.
use (tablename) alias sourcetable
* build field list
select sourcetable
set fields on
set fields local
* the first field in all my tables is "recid" of type Integer (autoincrement)
set fields to all except recid && bug in vfp. must have "except"
m.sourcelist = set("Fields",1) && get field list
set fields off
* build insert string. use ? for sqlexec to evaluate memory variables.
m.sourcelist2 = "?"+strtran(m.sourcelist, ", " , " ,?" )+space(1)
* tokenize datetime and date fields with fx(field_name) or gx(field_name)
afields(aflds,"sourcetable")
for m.j= 1 to alen(m.aflds,1)
do case
case m.aflds[m.j,2]='T'
m.sourcelist2 = strtran(m.sourcelist2, "?"+m.aflds[m.j,1]+" " , "fx("+m.aflds[m.j,1]+") ")
case m.aflds[m.j,2]='D'
m.sourcelist2 = strtran(m.sourcelist2, "?"+m.aflds[m.j,1]+" " , "gx("+m.aflds[m.j,1]+") ")
endcase
endfor
m.creccount = alltrim(str(reccount()))
m.sourcelist3 = m.sourcelist2
m.tablemsg = "Table: ("+ ltrim(str(m.k-2)) +"/"+ ltrim(str(m.nlines-2))+") "+m.tablename
scan
wait window ""+m.tablemsg+" .... Appending record "+ltrim(str(recno()))+" of " + m.creccount nowait
m.sourcelist2 = m.sourcelist3
* fix sourcelist2 datetime values to literal or null
for m.p = 1 to occurs("fx(",m.sourcelist2)
m.ff = strextract(m.sourcelist2,"fx(",")",1,1+4)
m.gg = evaluate(m.ff)
m.sourcelist2 = strtran(m.sourcelist2,m.ff,m.gg)
endfor
* fix sourcelist2 date values to literal or null
for m.p = 1 to occurs("gx(",m.sourcelist2)
m.ff = strextract(m.sourcelist2,"gx(",")",1,1+4)
m.gg = evaluate(m.ff)
m.sourcelist2 = strtran(m.sourcelist2,m.ff,m.gg)
endfor
m.lcSQLSTRING = " INSERT INTO " + m.tablename + " (" + m.sourcelist + ") VALUES ( "+m.sourcelist2+" )"
m.lnretval = sqlexec(m.lnconn,m.lcSQLSTRING)
if m.lnretval <0
error message()
exit
endif
endscan
endfor
* clean up and disconnect
lResult=sqldisconnect(lnconn)
if file("c:\aaa.txt")
erase c:\aaa.txt
endif
return
* call fx() for datetime variables
function fx ( datetimefield as timestamp ) as character
if empty( m.datetimefield)
return 'NULL'
endif
return "'"+ ttoc(m.datetimefield) +"'"
* call gx() for date variables
function gx ( datefield as date ) as character
if empty( m.datefield)
return 'NULL'
endif
return "'"+ dtoc(m.datefield) +"'"