Omnis Technical Note TNSQ0037a August 2019

Automatic Conversion to PostgreSQL

for Omnis Studio 10.0 or above
by Gary Ashford, Omnis Engineering

This feature allows applications that use legacy Omnis data files and DML commands to convert automatically into a PostgreSQL database. Once converted, the application continues to run almost entirely without modification, using PostgreSQL in place of OmnisSQL.

Why Convert?
We are taking steps to allow easy migration away from Omnis data files to more mainstream relational databases. This will ensure the future longevity and portability of your applications. Converting to a system which emulates DML commands also allows us to address several limitations and long term issues that affect the aging code base that sits behind the Omnis Data Manager. Moving this functionality out of the Omnis Studio "core" and "componentising" it also facilitates easier code maintenance.

Converting to PostgreSQL
A new add-on tool in Studio 10 allows you to convert a single or multi-segment Omnis data file into a PostgreSQL database. The conversion process copies tables, data and indexes and also preserves any Omnis File class connections (emulating them as foreign keys).

We recommend that you load your application before converting to PostgreSQL. This will ensure that the conversion utility has access to your File classes and any connections.

When converting to PostgreSQL, you need to specify a logon configuration file (e.g. pgsql.dfp) as the conversion target. This is a simple text file which contains logon parameters and other session-specific properties. For Example:


After conversion, you can remove the Omnis data file(s) and retain them as backup if required.

Emulating DML Commands
To enable emulation, you will need to change a root preference, namely $root.$prefs.$mapdmltodam. For example:

Do $root.$prefs.$mapdmltodam.$assign('PGSQLDAM')

It is recommended that you re-start Omnis if you change modes as this allows the emualtor to re-initialse. To enable a specific library for DML emulation, set its $clib.$prefs.$dmlemulation property to kTrue:

Do $clib.$prefs.$dmlemulation.$assign(kTrue)

Your Open data file or Prompt for data file commands need to specify the PostgreSQL logon configuration file described above. If you name this to match your existing Omnis data file, then no code changes are necessary. For example, use:

Open data file /Users/myUser/data/myDatafile.dfp,myDatafile     ## or
Open data file /Users/myUser/data/myDatafile.df1,myDatafile    

When you start or re-load your library, it will then use the DML emulation features built into the PostgreSQL DAM. The accompanying omsqlconv library may still be called upon to perform certain tasks including dialog boxes and prompts.

The following DML commands and functions are emulated:

Data files: Close data file, Close lookup file, Create data file, Floating default data file, Open data file, Open lookup file, Prompt for data file, Set current data file, Set default data file, lookup().
Data management: Build indexes, Delete data, Drop indexes, Open runtime data file browser, Rename data.
Changing data: Cancel prepare for update, Delete, Delete with confirmation, Do not flush data, Do not wait for semaphores, Flush data, Flush data now, Prepare for edit, Prepare for insert, Prepare for insert with current values , Test for only one user, Update files, Update files if flag set, Wait for semaphores.
Files: Clear all files, Clear main & connected, Clear main file, Clear range of fields, Clear selected files, Set main file.
Finding data: Clear find table, Disable relational finds, Enable relational finds, Find, Find first, Find last, Load connected records, Next, Previous, Prompted find, Single file find, Test for a current record, Test for a unique index value.
Searches: Clear search class, Reinitialize search class, Set search as calculation, Set search name, Test data with search class.
Sort fields: Clear sort fields, Set sort field.
Lists: Build list from file.
Others: Begin reversible block, End reversible block, Quit all methods, $root.$getodbfilelist(), $datas.$makelist(), various sys() calls including sys(11..20), sys(82), sys(83), sys(139), sys(3000) & sys(3001).

Aside from setting the root and library preferences, it should not be necessary to make changes to the library code since the emulated commands will automatically execute against the PostgreSQL database.

Logging DML Commands
Inside the config file, the special value logging directs logging information to the Omnis trace log. For basic logging, use 1. For additional logging, add 2. For performance monitoring and SQL logging, add 4, for example:


See also
Automatic Conversion to SQLite: TNSQ0037


Search Omnis Developer Resources


Hit enter to search