Omnis DAM

The OpenBase DAMs provide connectivity between Omnis Studio 3.2 (or later) or Omnis 7.3.8.1 (or later) and OpenBase SQL. This section describes the requirements, installation procedures, and basic usage of the OpenBase DAMs.

videoicon3.png Video: Programming with Omnis Studio

Three styles of DAMs are provided: v3 (new-style, nonvisual) DAMs for Omnis Studio, v2 (old-style, visual) DAMs for Omnis Studio, and O7 DAMs for Omnis 7. Debug builds of the DAMs are also provided.

Requirements v3 DAM

The OpenBase v3 DAM, like all v3 DAMs, currently requires the "Web Edition" of Omnis Studio for development. However, the standard runtime license can be used to deploy libraries that use the OpenBase v3 DAM. The OpenBase v3 DAM is the preferred way of connecting Omnis Studio to OpenBase.

The OpenBase v3 DAM runs on Carbon, Classic, and Windows versions for Omnis Studio. A Linux version is under development.

Requirements v2 DAM

The OpenBase v2 DAM requires the "Enterprise Edition" of Omnis Studio for development. However, the standard runtime license can be used to deploy libraries that use the OpenBase v2 DAM. The OpenBase v2 DAM is provided to make migration easier for existing O7 and OmnisSQL-based applications. New applications should use the v3 DAMs where possible.

The OpenBase v2 DAM runs on Carbon, Classic, and Windows versions of Omnis Studio.

Requirements O7 DAM

The OpenBase O7 DAM requires Omnis 7.3.8.1 for both development and deployment. This requirement is due to licensing terms set by Raining Data for third party developers like OpenBase providing DAMs for O7.

The OpenBase v2 DAM runs on Classic and Windows versions of Omnis Studio.

Installation v3 DAM

Look in the "Release DAMs" folder. Place either the OpenBase v3 DAM
(Carbon)file, the OpenBase v3 DAM (Classic) file, or the OpenBase v3 DAM
Win32. dll file into Omnis Studio’s xcomp directory, depending on which build of Omnis Studio you are using. No additional middleware is required.

Important: Please be sure you do not install the wrong DAM for the wrong version of Omnis Studio. Doing so may cause Omnis Studio to fail to recognize the correct DAM.

Place the CHARMAPS directory into Omnis Studio’s external directory so that Omnis Studio can map characters correctly for OpenBase.

Installation v2 DAM

Look in the "Release DAMs" folder. Place either the OpenBase v2 DAM
(Classic), the OpenBase v2 DAM (Carbon), or the dopenbase.dll (for
Windows) file into Omnis Studio’s
external directory, depending on which build of Omnis Studio you are using. No additional middleware is necessary.

Important: Please be sure you do not install the wrong DAM for the wrong version of Omnis Studio. Doing so may cause Omnis Studio to fail to recognize the correct DAM.

Place the CHARMAPS directory into Omnis Studio’s external directory so that Omnis Studio can map characters correctly for OpenBase.

Installation O7 DAM

Look in the "Release DAMs" folder. Place either the OpenBase O7 DAM (Classic) or the dopenbase.dll (for Windows) file into Omnis 7’s Omnis Extensions directory, depending on which build of Omnis 7 you are using. No additional middleware is necessary.

Place the CHARMAPS directory into Omnis Studio’s Omnis Extensions directory so that Omnis 7 can map characters correctly for OpenBase.

Installation Debug DAMs

If you experience a crash that you believe is in the DAM, install the correct DAM for your version of Omnis from the Debug DAMs folder.

Note: The names of debug and release DAMs are the same for each version of Omnis. You should not have both debug and release DAMs of the same type installed simultaneously.

What the Debug DAMs do.

The debug builds simply log the execution path within the DAM to a file over the course of an execution of Omnis 7 or Omnis Studio. If a crash occurs in the DAM, the last log point in the file will give an indication of where the crash occurred and the sequence of events in the DAM preceding the crash. This file can be quite useful to me in tracking down and fixing crashing bugs.

Installing the Debug Builds

The debug builds are in the Debug DAMs folder. Replace the release builds of the same name in the appropriate Omnis directory.

Running with Debug Builds

Simply run through a (preferably short) sequence of events that leads to a crash in the DAM. When Omnis crashes, look for a file corresponding to the crashing DAM in the directory containing the Omnis application:

Filename OpenBase DAM
OpenBase v3 Trace (OpenBase v3 DAM for Omnis Studio) OpenBase v2 Trace (OpenBase v2 DAM for Omnis Studio) OpenBase O7 Trace (OpenBase DAM for Omnis 7.3.8)

Note: For the Mac OS X version of Omnis Studio, these files will actually be within the Omnis Studio package (aka Bundle). Control-click on the application icon, and choose Show Package Contents.

Reporting a Crash

With a trace log in hand, you’re ready to report a crash and we’ll be ready to fix it. Simply send an e-mail describing the query you were trying to execute, version of Omnis, etc. to:
moc.esabnepo|troppus#moc.esabnepo|troppus

Attach the trace log so we can narrow down where the problem is.

Do Not Deploy Debug DAMs! !!

Writing all that data out to files and flushing the files so that crash information is preserved is a real time waster. Do not deploy the debug DAMs.

Connecting with v3 DAM in Omnis Studio 3.2

Using the SQL Browser in Omnis Studio, define a new session:

Session Name:
DBMS Vendor: OpenBase
Data Access Module: OpenBaseDAM
Host Name: @ e.g. TestDB@example.com
User Name: e.g. admin
User Password: e.g. whatever

When opening the session, the SQL Browser is not always helpful when things go wrong and the session cannot be opened. It will complain of a generic middleware error. Be sure that the OpenBase server is running, that you have set up the user and password, etc. Please note that due to how Mac OS Classic networking works, you will not be able to connect to an OpenBase database running on localhost in Mac OS X.

Connecting with v2 DAM in Omnis Studio 3.2

Using the SQL Browser in Omnis Studio, define a new session:

Session Name:
DBMS Vendor: OpenBase
Data Access Module: DOPENBASE
Host Name: @ e.g. TestDB@example.com User Name: e.g. admin User Password: e.g. whatever

When opening the session, the SQL Browser is not always helpful when things go wrong and the session cannot be opened. It will complain of a generic middleware error. Be sure that the OpenBase server is running, that you have set up the user and password, etc. Please note that due to how Mac OS Classic networking works, you will not be able to connect to an OpenBase database running on localhost in Mac OS X.

Connecting with O7 DAM in Omnis 7

The SQL Browser in Omnis 7 does not know about the OpenBase O7 DAM, so connection templates may not be created. However, once a session is established, it will show up in the SQL Browser.

Programmatically, you can do something like the following. Note that the hostname is of the form database@host.

Set current session {Testing}
Start session {dopenbase}
If flag true
Set character mapping {OpenBase Latin1} If flag true
Set hostname { database@wherever.com } Set username { admin}
Set password {whatever}
Logon to host
End If
End If

Augmented host name parameter

As of version 1.1.3, the host name parameter for v3, v2, and O7 DAMs has been augmented to optionally allow for software ID and client name. The software ID is used by OpenBase to restrict access by applications based on user. The client name shows up in the Process Viewer window of OpenBase Manager and can help sort out actual client machines with connections. The valid formats for the host name are:
@;;
e.g. moc.elpmaxe|BDtseT#moc.elpmaxe|BDtseT ;My Application;Joe's Computer
e.g. moc.elpmaxe|BDtseT#moc.elpmaxe|BDtseT ;My Application
@;;< client name >
e.g. moc.elpmaxe|BDtseT#moc.elpmaxe|BDtseT ;;Joe's Computer
@
e.g. moc.elpmaxe|BDtseT#moc.elpmaxe|BDtseT

v3 DAM Methods & Props

The v3 DAM implements two types of objects that Omnis Studio developers will use: the session object and the statement object. The v3 DAM may define methods and properties for each.

You may request additional properties and methods for the v3 DAM by sending your requests to moc.esabnepo|troppus#moc.esabnepo|troppus

Note: The OpenBase v2 and O7 DAMs do not define custom methods and properties.

v3 DAM Session Object Methods

$newid(, )

Description: This method retrieves a unique key from the database for the specified table and column. It returns a numeric value, floating decimal point corresponding to a 64 bit integer.

Discussion: Using $newid is the equivalent to creating a statement object and executing a statement like: "newid for ". Calling $newid may destroy active cursors (result sets) in statement objects, but should not otherwise interfere with statement objects.

Example:

lUniqueID = tOpenBaseSession.$newid('ANIMAL', 'ANIMAL_ID')

v3 DAM Session Object Properties

$obHost

Description: This read-only character property contains the DNS host name of the server to which the session is connected.

$obDatabase

Description: This read-only character property contains the name of the database to which the session is connected.

$obVersion

Description: This read-only character property contains the version of the OpenBase database to which this session is connected.

$obPassiveTransaction

Description: This read-write boolean property contains the setting for passive transactions. The default value for new sessions is true. Passive transactions generally help you avoid lockups when multiple clients access the same table within the context of open transactions.

$obTimezoneOffset

Description: This read-only character property contains the timezone offset for the client, suitable for appending to date time literals before inserting into the database. For Pacific Standard Time, this property would contain Ò -0800Ó (no quotes, note leading space).

v3 DAM Statement Object Properties

$obPreparedStatement

Description: This read-only character property contains the last statement prepared. This may be different from what your library prepared if the statement has been adjusted by the DAM to be syntactically more correct.

$obExecutedStatement

Description: This read-only character property contains the last statement actually sent to the OpenBase server. All variables are bound on the client-side, so this will have bind variables substituted.

$obFilterRowID

Description: This read-write boolean property determines whether the statement object should filter out the _rowid column when executing select * SQL statements. The default value for new sessions is true. If you use the _rowid column as your primary key (as many OpenBase developers do) and you perform select * queries, you may want to set this property to false for each new statement object.

V2 & O7 DAM Properties

The old-style DAM architecture does not implement a specific property mechanism like the new-style DAM architecture does. The OpenBase v2 and O7 DAMs intercept SQL statements to set their properties. We still have not decided how to implement getting the properties.

Setting a Property

To set a property, issue a set dam property SQL statement. The v2 and O7 DAMs will intercept these statements and handle them accordingly.

v2 and O7 DAM Properties

$obPassiveTransaction

Description: This read-write boolean property contains the setting for passive transactions. The default value for new sessions is true. Passive transactions generally help you avoid lockups when multiple clients access the same table within the context of open transactions.

Example: To set this property, use a set dam statement SQL statement:
set dam property $obPassiveTransaction true set dam property $obPassiveTransaction false

$obFilterRowID

Description: This read-write boolean property determines whether the statement object should filter out the _rowid column when executing select * SQL statements. The default value for new sessions is true. If you use the _rowid column as your primary key (as many OpenBase developers do) and you perform select * queries, you may want to set this property to false when you establish a connection.

Example: To set this property, use a set dam statement SQL statement:

set dam property $obFilterRowID true set dam property $obFilterRowID false

Type Mapping

When using the OpenBase DAMs to connect Omnis 7 and Omnis Studio to OpenBase in real world applications, it is helpful to understand how Omnis Studio types are mapped to OpenBase types and vice versa. Note that the mapping is not one-to-one, as there is not a perfect correspondence among types in both systems. When copying tables between OpenBase sessions using the SQL Browser, you may have to edit column types in the destination table to get exact copies. The OpenBase server is pretty forgiving, and will automatically do things like converting floating point values to longlong if needed. So it is possible to get exact copies of tables with the SQL Browser by giving it a few hints.

The OpenBase DAM's type mapping scheme depends on some small fixes in OpenBase SQL 7.0.5. Version 1.1 and later of the OpenBase DAMs also depend on OpenBase SQL 7.0.7. As such, the OpenBase DAMs should be used with OpenBase SQL 7.0.7 or later.

OpenBase —> Omnis 7 and Studio

The following table describes how OpenBase column types are mapped to Omnis Studio variable types by the OpenBase DAM.
OpenBase Column Omnis 7 and Omnis Studio Variable
char (varchar) character
float (double) Number - floating dp
int Number - long integer
long Number - long integer
long long Number - 0 dp
money Number - 2 dp
date Date time - Date
time Date tme - Short time
datetime Date time - Date time
object Binary
boolean Boolean
binary character

The OpenBase binary type, new to version 7, is primarily intended for WebObjects keys and is probably not very useful to Omnis developers.

Omnis 7 and Omnis Studio —> OpenBase

The following table describes how Omnis Studio variables are mapped to OpenBase columns by the OpenBase DAM. This mapping occurs primarily when tables are created in OpenBase after repeated calls to the OpenBase v3 DAM's session method, $dColText, or the OpenBase v2 DAM's $createname method.

Omnis 7 and Omnis Studio Variable OpenBase Column
character(n), n <= 4096 char
character(n), n > 4096 object
Boolean boolean
Date - date (any format) Date date
- time (any format) Date - time
date time (any format) datetime
Sequence integer
Integer integer
Number float
Picture object
Binary object
List object

The OpenBase DAMs will quote the literals generated by numeric (floating point) bind variables, making it safe to insert such variables into char, int, long, longlong, and float columns in your OpenBase database.

Date & Time Types

This section describes the date, time, and datetime OpenBase column types and the implications these types have for Omnis 7 and Omnis Studio programmers working the OpenBase DAMs. Of particular interest is how clients and servers in different time zones work together. Commented sample code shows how various things work.

OpenBase Date Columns

An OpenBase date column contains a calendar date, e.g. November 3, 1970. A date is reported the same regardless of the time zone settings of the server or client. If you insert a date value from Omnis 7 or Omnis Studio using bind variables or by rolling your own SQL statement, the same date will be stored on the server and the same date will be returned when selected later.

OpenBase Time Columns

An OpenBase time column contains a wall-clock time, e.g. Ò15:17Ó. A time is reported the same regardless of the time zone settings of the server or client. If you insert a time value from Omnis 7 or Omnis Studio using bind variables or by rolling your own SQL statement, the same time will be stored on the server and the same time will be returned when selected later.

OpenBase Datetime Columns

An OpenBase datetime column contains a UTC timestamp, e.g. Ò1988-07-15 17:04:00 +0000Ó. Inside the server, the timestamps are normalized to GMT. A timestamp is, however, reported by client software in the client's local timezone. So a client in California (PST) reading the preceding datetime example would display Ò1988-07-15 09:04:00 -0800Ó. Semantically, this makes sense because a timestamp defines a specific instance in time that spans time zones. A timestamp does not just define a calendar date and a wall-clock time. How that instance is time is referenced depends on which time zone the reference is made.

Date time values in Omnis 7 and Omnis Studio do not contain timezone offset information. Care needs to be taken when inserting and retrieving these values to ensure that you get the results you want. When the DAM reports a datetime value from OpenBase, it adjusts the value to reflect the client’s time zone. So, an Omnis 7 or Omnis Studio client in California will report the preceding datetime example as 1988-07-15 09:04:00. Notice that the time zone offset is dropped because a datetime value in Omnis 7 or Omnis Studio does not contain that information.

When "date time" values are inserted through the DAM using bind variables, the DAM will append the appropriate time zone offset to the OpenBase datetime literal.

However, if you roll your own SQL statements, you are responsible for appending the appropriate timezone offsets. Otherwise, the server will interpret the datetime literal as GMT. Fortunately, the OpenBase v3 DAM can help you append the client’s timezone information when you create a datetime literal. An offset string for the client’s timezone is provided by the session object’s $obtimezoneoffset read-only property:

Calculate lDatetimeLiteral as con('''1988-07-15 09:04:00', tOBSession.$obtimezoneoffset, '''')

Note: the single quotes that surround the datetime literal.

One other issue to note is that OpenBase datetime values do not support hundredths of a second. The DAM drops fractional second information when converting bind variables. Your code should ignore the fractional second portion of date time values when creating datetime literals for
OpenBase.

OpenBase v2 DAM Issue

When using the SQL Browser in Omnis Studio to copy a table from an OpenBase database accessed by the v2 DAM, the suggested column types for long, longlong, and double types in a destination OpenBase database will be ÒOBJECTÓ. This happens because the SQL Browser does not understand the column names returned by the v2 DAM. You should manually change these to the source column type.

Table and Column Names

Identifiers are used for table names and column names, among other things. Identifiers in OpenBase can be any combination of letters, numbers, and the underscore character. OpenBase identifiers are currently case sensitive, so "MyTable" (no quotes) is different from "MYTABLE" (no quotes). This may change in a future version of OpenBase.

You may even use SQL keywords as OpenBase identifiers. For example, "name" (no quotes) is a perfectly legitimate column name in OpenBase. Your mileage may vary with other database systems.

Quotes

Some database systems allow you to use SQL keywords by enclosing them in double quotes, e.g. "name" (with quotes). OpenBase does not currently recognize quotes around identifiers. The Omnis Studio SQL Browser does not currently generate SQL statements that include quoted identifiers, so there are no problems using it with OpenBase.

Be cautious with Omnis Studio Schema objects! They allow you to use quoted identifiers as server table and server column names. While this makes other databases systems happy, it does not play well with OpenBase. When you drag a schema that includes quoted identifiers to an OpenBase session in the SQL Browser, an error will be reported about an identifier being expected. Remove the quotes from the table and column names in the schema and the problem should be solved.

Understanding Transactions

OpenBase Transactions

There are two important things to understand about OpenBase transactions:

If you want to have multiple SQL statements in a single transaction, you must explicitly start the transaction. If you haven't started a transaction, the server commits each statement as it receives and executes it.

OpenBase has an option for transactions to be passive. Normally, transactions do not allow dirty reads. When one connection performs an update, the change is visible within the transaction immediately. If another connection performs an update on the same table, the server will block until there is a COMMIT or ROLLBACK of the transaction in the first connection. This is to ensure serialization and avoid unnecessary deadlocks.

A passive transaction, by contrast, does not obtain locks while a transaction is in process and makes no changes visible to any connection until a COMMIT is issued.

Passive transactions allow dirty reads until the COMMIT occurs, and do not block. Yet, passive transactions are still guaranteed to be atomically correct and serialized correctly if they succeed.

Implications for v3 DAM and Omnis Studio

Omnis Studio supports three transaction modes for v3 DAMs: server, automatic, and manual. In both server and automatic modes, OpenBase will commit each statement as it receives and executes it. In manual mode, OpenBase will commit each statement as it receives and executes it unless you have begun a transaction with $begin and have not concluded it with either $rollback or $commit.

The OpenBase DAM defaults to using passive transactions on each new connection. This allows your clients to update the same tables without locking up and without any need for you to dive into transaction semantics. You can change this by setting the $obPassiveTransactions property of the OpenBaseSESS object to false.

Implications for v2/O7 DAM and Omnis Studio or Omnis 7

Omnis Studio and Omnis 7 support three transaction modes for v2/O7 DAMs: server, automatic, and generic. In both server and automatic modes, OpenBase will commit each statement as it receives and executes it. In generic mode, the DAM will start a transaction implicitly if you issue an "insert" statement, an "update" statement, or a "select … for update" statement. Note that generic mode in v2/O7 DAMs is a bit different from manual mode in v3 DAMs.

The OpenBase DAM defaults to using passive transactions on each new connection. This allows your clients to update the same tables without locking up and without any need for you to dive into transaction semantics. You can change this using the set dam property SQL statement, which is intercepted by the DAMs:

set dam property $obPassiveTransaction true set dam property $obPassiveTransaction false
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License