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

Peter Suter pete.suter at aol.com
Thu Mar 5 16:20:19 EST 2015

Hi Paul,
Based on my experience with Studio 6 and the Postgres DAM

The key concept when working with Postgres is to "write your own". A large chunk of the default methods and sql functions just don't.
Check out the ManaulsOmnis60ProgrammingPage314 and 315 for Postgres specific data types to tweak your MySQL code. That MySQL code you mentioned should be pretty close for Postgres, 

Best Regards

Peter Suter
pete.suter at aol.com

-----Original Message-----
From: Paul Mulroney <pmulroney at logicaldevelopments.com.au>
To: omnisdev-en <omnisdev-en at lists.omnis-dev.com>
Sent: Thu, Mar 5, 2015 4:26 pm
Subject: O$: Postgres, $createnames() and date fields

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)

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
vsSchemaName as 'scActivity' ;; as an example
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.
vrOldOmnisSession.$makeschema(vrSchema,vsTableName) ;; Get the definition based
on the Omnis datafile
Do vlDestinationData.$definefromsqlclass(vsSchemaName) 
;; vsSchema name is the Omnis schema class
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,')')

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

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

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

All help greatly appreciated.


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 

your list subscriptions at http://lists.omnis-dev.com


More information about the omnisdev-en mailing list