moving empty timestamp fields to postgresql

[markftwain]

Hi,

After establishing a connection to a postgresql database, I am unabe to insert a new record when the source vfp record has an empty timestamp value. The sql syntax of ?varname seems to pass an empty string to the backend postgresql database resulting in postgresql message of wrong data type. Replacing empty timestamps in the source foxpro table with .null., results in ? still failing.

How do I send empty timestamps values through sqlexec to posgresql?

Thanks for any help with this.

 

[Olaf Doschke]

Seem postgresql like many other databasese doesn't know an empty date or datetime. That IS very foxpro specific. SQL Server doesn't know that, too.

Changing to NULL is one solution. You need nullable fields of course. Naturally field are not nullable, buit most databases offer to store NULL, and for SQL Server this works.

You maya change to the lowest date suported by postgresql, instead. Which also is a solution often used for SQL Server.

Bye, Olaf.

 

[markftwain]

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) +"'"

 

[Mike Lewis]

Marktwain,

I haven't studied your code in detail, but at first glance, it looks like it should do what you want. If it is not successfully inserting NULLs into the datetime fields, I can only assume there is some syntax issue that is specific to Postgres.

I suggest you take a slightly different approach. In your Visual FoxPro database, do a global update to replace all blank dates and datetimes with NULL. You can so that with a simple UPDATE or REPLACE statement.

Then, your code should work. Or, if it doesn't, change the fx and gx functions so that they simply return the dates or datetimes unchanged.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

First of all if the solution works, it's at least a grade D solution, you pass

Well, not a bad solution for a novice.

The upsizing wizard of foxpro might have done the same job for you without any code. To comfort you: It might also fail. But it's worth a try before writing a program, even such a short and general one.

What I like is the general approach. For 1:1 the same structure and a set of simple field types this should work out satisfactory. If some tables need special care this can be extended, if it get's more complex you might be better off with a general approach of calling a procedure taking care of each source or destination table seperately.

It's not the most straight forward approach to create insert statements, you'd rather create CSV or other text output and import those files from postgresql than going through ODBC via SQL Passthrough.

I am a total rookie in postgresql and don't know what it offers in that respect. So maybe going through sql passthrough is easier. I don't know any database unable to work on text files at all, so it should be possible to go through text, but enough said.

Going through SQL Passthrough there would be a performancewise better approach of making a foxpro cursor updatable and use tableupdate() to upload all data.

General recipe for each empty postgresql table:

* 1. create an empty foxpro SPT cursor for the postgresql table
sqlexec(lnH,"Select * from postgresqltable")
* 2. do several cursorsetprop for Tables, Keyfieldlist,
* Updatablefieldlist, Updatenamelist and Sendupdates.
* 3. put all data into the SPT cursor
append from foxtable.dbf
* 4. upload that data into the postgresql database:
tableupdate()

And that's it besides a loop over all tables. Also you'll need to make needed updates, eg replacing empty date/datetimes or whatever else needed for the destination database.

A list of all tables can also be achieved by SQLTables() and a list of fields by SQLColumns(). And retrieving that for a DBC and it's DBFs you can use ADBOBJECTS() and AFIELDS(). As you have the idenetical database in foxpro and postgresql it's your choice, I'd change from Display Tables to SQLTables() perhaps. Set("Fields",1) is quite elegant to get the comma seperated field name list on the other side.

What's questionable in your apporoach is leaving out all recid primary key fields. Any deleted record leaves a gap in record IDs you won't get back in the postgresql database, if you have an autoincrementing id type there, too. Therefor all your foreign keys will get out of whack. Relations of all your child records is likely broken.

Bye, Olaf.

 

[markftwain]

Thank you,

I tried replacing the empty date/datetime fields with NULL by update, but it seems the "?" evaluator within the sqlexec statement still passed an empty string "" to the postgresql backend, not NULL.

The use of cursors looks very promising.

Thanks again, bye.

 

[Olaf Doschke]

Passing NULL via ?parameter works in general. I do this with SQL Server on the remote side. It's just the table structure of the database must also allow NULLs. By default a field is not nullable, you need to allow NULLs.

Bye, Olaf.

 

[Mike Lewis]

Well, another option might be to identify the date that gets inserted in place of the empty date. (In SQL Server, this would be something like 1/1/1900, but might not be the same in Postgres.)

Then, after you have done the import, use UPDATE on the Postgres side to change all instances of the date in question to NULL.

Of course, that wouldn't work if the base date might genuinely be present in your data.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy