Developers

Omnis Technical Note TNSQ0033 October 2013

Creating Server Tables Automatically

for Omnis Studio 5/6.x
by Andreas Pfeiffer and Omnis Engineering

Introduction
For some applications it might make sense to create missing server tables automatically after successfully logging onto a database. For example, the benefit would be that new tables in a customer database could be created when deployed for the first time or a new version of your library is delivered. When you are using schema classes while developing your application you can compare them with the existing server tables and, where necessary, create new server classes based on your schema class definitions, but this may not be so easy in a remote location, or at a customer site.

Let's assume you have a Task Variable called 'tSessObj' that has a valid connection to your database. Add a Statement Object which you can later use to send your SQL to the database into the variable lStatObj (type Object - no subtype).

Do tSessObj.$newstatement(#CT) Returns lStatObj

The Object Variable lStatObj now has methods and properties which you can examine using the Interface Manager (right mouse on the variable) when debugging your code.

You can then use the following code to list the server tables of the database in the list variable lDBTableList, as follows:

Do lStatObj.$tables(kStatementServerTable)

Please note that you can only debug the code when you have got a valid instance. So you should debug the code of the Startup_Task by setting a breakpoint – you need to close and re-open Startup_Task by Right-clicking on it and selecting Close/Open Task. Then the code should stop at the breakpoint, and you can debug it.

Do lStatObj.$fetch(lDBTableList,kFetchAll)

The second step is to create another list that contains the schema classes of the current library. Two local variables are used to define this list:

Do lTablesForAppList.$define(lTableName,lSchemaClassName) Do $schemas.$appendlist(lTablesForAppList,$ref.$servertablename,$ref.$name)

Now you need to step through using a For loop the create a list of schema classes – new server tables are created as required:

For lTablesForAppList.$line from 1 to lTablesForAppList.$linecount() step 1   Do lTablesForAppList.$loadcols()   ; loads the current line in the corresponding fields lTableName and lSchemaClassName   Do lDBTableList.$search(lDBTableList.TableOrViewName=lTableName,kTrue,kFalse,kFalse,kFalse)   If not(lDBTableList.$line) ;; when the table could not be found     Do lRow.$definefromsqlclass(lSchemaClassName)     ; define a local Row Variable with the schema class name just loaded     Do lRow.$sessionobject.$assign(tSessObj) ;; important for $createnames to work correctly!     ; now we are going to create the new server table:     Do lStatObj.$execdirect(con('CREATE TABLE ',lRow.$servertablenames(),'       (',lRow.$createnames(),') ')) Returns lStatus     If not(lStatus)         Breakpoint -> implement the error handler here     End If    End If End For

When you call this code after opening the database from the $construct of the Startup_Task it will be checked automatically whether there is a library in the server table for each existing schema class. If required it will be created automatically.