O$: Postgres, $createnames() and date fields

[Paul Mulroney]

Hi $All,

We're moving one of our systems from DML to PostgreSQL, and I'm grappling with the $createnames() function. (Studio 5.2, Mac and Windows)

The documentation says that you can use $createnames to generate the SQL needed to create the database tables, but some of the field types translate in a weird way.  The ones that are causing me grief are date and time fields.

I have code like this, which converts an Omnis File class into a Schema class, and then generates the SQL to create the table:

Calculate vsTableName as 'fActivity' ;; As an example, this is a file class in the current datafile
Calculate vsSchemaName as 'scActivity' ;; as an example
Do vrOldOmnisSession.$logon(<datafile pathname>,'','','Old_Omnis') ;; vrOldOmnisSession is an OMSQLSESS object
Do $clib.$schemas.$add(vsSchemaName) returns vrSchema ;; Create the schema class, nothing in it yet.
Do vrOldOmnisSession.$makeschema(vrSchema,vsTableName) ;; Get the definition based on the Omnis datafile
Do vlDestinationData.$definefromsqlclass(vsSchemaName)  ;; vsSchema name is the Omnis schema class
Calculate vlDestinationData.$sessionobject as clSession ;; clSession is our logged on Postgres session
Calculate vsCreateNames as vlDestinationData.$createnames() ;; When linked to the destination, this should generate SQL to create the table specific to the target (ie Postgres).
Calculate vsQuery as con('CREATE TABLE ',vsTableName,' (',vsCreateNames,')')
Do ioStatement.$execdirect(vsQuery)

Now, for this particular table I have two fields: one called "dateEntered" which is a Date (D/M/y) field, and "timeEntered" which is a time (H:N) field.  When you get to the $createnames() method, both fields are type "TIMESTAMP".

The problem is this: If I try to save data into the timeEntered field (eg 15:45) Postgres says "Invalid date: 0000-00-00 15:45".  The timeEntered field really needs to be of type "TIME", not "TIMESTAMP".

If I use notation to look at the dateEntered and timeEntered field in the Schema class, they are both $coltype kDate and $colsubtype kDateTime.  If I mess with the subtype, and change it to kTime or kDate2000, it somehow changes back to kDateTime and I end up with TIMESTAMP in the $createnames.

The last time I did a conversion like this (to MySQL), I wrote my own $createnames() method, but it was pretty chunky and I was hoping to use the built-in methods to get a cleaner result.

So, how do I get the $createnames() method to recognise time fields as a Time, and date fields as a Date?


All help greatly appreciated.

Regards,
Paul.


A will is a dead giveaway.
-- 
Paul W. Mulroney                                                   We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au        Trading as Logical Developments
www.logicaldevelopments.com.au			   ACN 161 009 374 
Ph: +61 8 9458 3889						   86 Coolgardie Street
Fax: +61 8 9458 2169                       			   BENTLEY  WA  6102