Chapter 9—Server-Specific Programming

This chapter contains server-specific information for each of the proprietary databases and middleware configurations that can be accessed in Omnis Studio.

The type of database you can access in Omnis Studio will depend on the edition of Omnis Studio you have. All editions allow you to access:

• PostgreSQL

• SQLite

In addition to those above, other editions, including the Professional Edition, may provide access to:

• Oracle

• Sybase

• DB2

• MySQL

• ODBC

• Amazon SimpleDB

The following are for legacy or existing Omnis applications only and should not be used for new applications:

• OmnisSQL DAM
  provided for backwards compatibility for legacy apps only, should not be used for new apps

• JDBC
  support for JDBC has been removed in Studio 10 or above, but the supporting files can be obtained by contacting Omnis Support

Every DBMS has its own specific, extra features that are not part of the SQL standard. Some of these features are supported by sending a specific database command to the server using the standard $prepare(), $execute(), and $execdirect() methods. Others are implemented as special session and statement object properties and methods.

In addition to the DAMs provided with Omnis Studio, FrontBase Inc also produce and maintain DAMs for Omnis Studio, see: http://www.frontbase.com

Server and Clientware Compatibility

Some aspects of functionality and compatibility are subject to frequent change with new versions of server software or clientware. Check the Omnis Developer website for details of software versions supported and middleware configurations at: www.omnis.net/developers/resources/dams/

There you can view the latest information about the Clientware supported by the different server databases supported in the current version Omnis Studio.

64-bit DAMs

The DAMs provided with the 64-bit version of Omnis Studio 8.0 or higher use 64-bit architecture. This means that you will need to install separate 64-bit clientware where appropriate. The 64-bit DAMs are not interoperable with 32-bit client libraries and vice-versa. For single-tier and embedded DAMs, including DAMSQLITE, DAMOMSQL, DAMMYSQL, DAMPGSQL and DAMAZON, all necessary changes have been made. The 64-bit ODBC DAM requires the 64-bit ODBC Administrator library and should be used with 64-bit ODBC Drivers to ensure compatibility.

PostgreSQL

This section contains the additional information you need to access a PostgreSQL database, including server-specific programming, data type mapping to and from PostgreSQL, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

For additional information on changes to the PostgreSQL DAM, refer to the readme file which accompanies your Omnis download.

PostgreSQL Client Libraries

This section discusses the PostgreSQL client library, which must be present on the library search path before the PostgreSQL DAM can be used.

Win32 platforms

For Win32 platforms, the library search path includes the Windows\System32 folder or any location in the PATH environment variable, including the folder containing omnis.exe. The Win32 client library is named libpq.dll.

Linux and macOS platforms

The Linux and macOS ports of the PostgreSQL DAM look for libpq.so or liqpq.dylib respectively.

In most cases the library present on your system will be labelled according to the version you have installed. For example on Linux, libpq.so might be a symbolic link to the target library libpq.so.5.0. A detailed directory listing shows this relationship, e.g.

12 2007-01-15 10:20 libpq.so -> libpq.so.5.0
117338 2007-01-15 10:20 libpq.so.5.0

Under Linux and macOS therefore, it is essential that the target library and symbolic link to it both exist either in the library search path or in the same folder as the Omnis executable. For macOS, the library search path also includes the Omnis.app/Contents/Frameworks folder.

Properties and Methods

In addition to the “base” properties and methods documented in the SQL Programming chapter, the PostgreSQL DAM provides the following additional features.

Session Properties

Session Methods

Statement Properties

Logging on to PostgreSQL

In addition to the hostname, username and password parameters provided by the $logon() method, the PostgreSQL DAM provides several session properties which enable additional logon parameters to be set. These should be set before calling $logon().

• $database is used to specify the dbname connection parameter.

• $port is used to specify the port connection parameter.

• $logontimeout is used to specify the connect_timeout parameter.

• $options is used to specify further optional connection parameters.

• $service is used to specify a service (filename) to use for additional parameters.

The $logon() hostname parameter can either be specified as an IPv4 (e.g. 192.168.1.100) or an IPv6 IP address or as a machine name. If prefixed with a slash, name refers to a Unix domain name.

Metadata Functions

• $indexes()

The DamInfoRow for $indexes() is defined with a single column containing the SQL text used to define the index.

• $tables()

The PostgreSQL DAM implements $tables() slightly differently. In particular, only the kStatementServerTable and kStatementServerView parameters are supported. This is because the processes for querying tables are incompatible with those for querying views. (kStatementServerAll defaults to kStatementServerTable).

The DamInfoRow for $tables() is defined with three Boolean columns with additional information on the table or view: HasIndexes, HasRules & HasTriggers.

Transactions

PostgreSQL supports two transaction isolation levels: Read Committed (the default) and Serializable. Using Read Committed mode, a statement can only see rows that were committed before the current transaction began. Using Serializable mode, all statements in the current transaction can only see rows that were committed before the first query or data-modification statement was executed in this transaction.

Transactions can also be instantiated as read-only if required. This enables significant performance improvements for read operations. When a transaction is read-only, the following SQL commands are disallowed: INSERT,UPDATE,DELETE and COPY FROM if the table they would write to is not a temporary table; all CREATE,ALTER and DROP commands; COMMENT,GRANT,REVOKE,TRUNCATE,EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. Please refer to the PostgreSQL documentation on transactions for further details.

When using manual transaction mode (kSessionTranManual), the transaction isolation level can be switched between Read Committed and Serializable using the $serializable session property.

The access mode can be changed using the $readonly session property.

The PostgreSQL DAM treats the kSessionTranAutomatic and kSessionTranServer transaction modes identically. In either of these modes the server automatically begins and commits read/write transactions.

Remote Procedure Calls

PostgreSQL does not support the concept of stored procedures but supports functions instead. This has a few implications as described below.

• $rpcprocedures()
  The DamInfoRow returned by $rpcprocedures is defined with the following columns:

• $rpc()
  Calling $rpc() is similar to executing a SQL SELECT statement of the form:

SELECT * from proc_name (param1, param2, … )

with the exception that $rpc() will also set any InputOutput or Output parameters.

Any return value generated by the function will be available via $rpcreturnvalue although in the case where the function generates a result set, it may be preferable to retrieve the entire set by calling $fetch(). The value returned by $rpcreturnvalue is also returned as the first row of this result set.

Notification Channels

PostgreSQL supports asynchronous notification channels via its LISTEN, UNLISTEN and NOTIFY SQL commands. You can register the session object as a listener for a notification channel using the $listen() method, specifying the channel name to listen for. $unlisten() removes the listener.

Once registered, if any client executes a NOTIFY for that channel name, the listener calls the session object’s $notify() method with parameters that indicate the channel name, the notifier’s process ID and an optional ‘payload’/ text string.

In the following example, oPgSQL is an object class with $superclass .PGSQLDAM.PGSQLSESS:

Do oPgSQL.$logon('192.168.0.96','postgres','postgres','session1') Returns #F
Do oPgSQL.$listen('charliex') Returns #F
Do oPgSQL.$newstatement() Returns statObj
Do statObj.$execdirect("notify charliex,'This is an important message'") Returns #F

Create method oPgSQL.$notify() with a single parameter of type Row to be called whenever a notification is received. For example:

OK message Notification received {Channel=[pRow.Channel]//PID=[pRow.PID]//Message=[pRow.Message]}

As of Studio 10.2, $listen() automatically encloses the channel name in double quotes when $quotedidentifier is kTrue.
Unquoted channel names containing illegal characters now cause $listen() to return kFalse with $nativeerrortext; “Malformed unquoted identifier”.

Valid unquoted identifiers are case-insensitive, commence with a-z or _ and can contain a-z, 0-9 as well as _ and $. Quoted identifiers are case-sensitive and can contain any characters. PostgreSQL identifiers have a maximum length of 63 characters. As of Studio 10.2, the message size can be up to 8000 ANSI characters.

Handling Dates

When kTrue, the $defaultdateisempty tells the DAM to convert retrieved datetimes to empty when they match $defaultdate.

UUID, ENUM and XML Column Types

Support for the following data types is available in Omnis Studio 4.3.1 and above.

UUID

The PostgreSQL DAM is able to read and write Universally Unique Identifiers. An example of a UUID in standard form might be:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 (36 characters)

but the DAM also accepts UUIDs formatted without hyphens and/or encapsulated using curly braces.

Output from UUID columns is always in the standard form.

To allow input binding of UUIDs and to make $createnames() return UUID types, it is necessary to set $char38touuid to kTrue. Once set, the Omnis Character 38 data subtype maps to UUID.

Note: there is no facility either in the PostgreSQL client library or in the DAM to create UUID values. This must be implemented by the Omnis application.

ENUM

Enumerated types are created by executing CREATE TYPE statements, for example:

Do statObj.$execdirect("CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')") Returns #F ##creates the enumerated type

To make Omnis map certain character sub types to ENUMs, the $addcustomtype() method is provided.

The following example maps the Omnis Character 2001 data subtype to the “mood” enumerated type:

Do sessObj.$addcustomtype(2001,'mood') Returns #F

Once set, this mapping affects the text generated by $createnames() as well as input binding.

To clear previously defined enumerated type mappings, the $clearcustomtypes() method is provided.

XML

The $addcustomtype() method can also be used to force an Omnis Character subtype to map to the XML data type, for example:

Do sessObj.$addcustomtype(10001,'xml') Returns #F

As above, this mapping affects the text generated by $createnames() as well as input binding and remains in effect until $clearcustomtypes() is called.

Large Object Support

As of Omnis Studio 5.1.1, the PostgreSQL DAM supports additional session object methods for manipulating large objects stored in the database.

The methods; $lobimport(), $lobexport() and $lobunlink() complement their SQL equivalents (lo_import(), lo_export() & lo_unlink()) with the exception that the client-side methods operate on files in the client machine’s file system. The SQL functions operate on files in the database server’s file system. In other respects, the operation of these methods is comparable:

• $lobimport() – creates a large object and imports data into it from a local file.

• $lobexport() – retrieves data from a large object and writes it to a local file.

• $lobunlink() – removes a large object from the database.

There is an additional client-side method for creating a large object:

• $lobcreate() – creates a new (empty) large object and returns the OID value.

The large objects are identified by their OID values, which can subsequently be stored and retrieved in database Oid columns in a similar fashion to standard integers.
Once created, the following methods can be used to manipulate data inside large objects:

• $lobopen() – opens a large object (OID) and returns a large object descriptor.

• $lobclose() – closes a large object descriptor.

• $lobread() – reads zero or more bytes from a descriptor into a Binary variable.

• $lobwrite() – writes zero or more bytes to a descriptor from a Binary variable.

• $lobseek() – repositions the read/write pointer within a large object.

• $lobtell() – reports the current pointer position within a large object.

• $lobtruncate() – resizes a large object to the desired size (in bytes).

To use these methods, it is important to note that large object operations must be performed within a single transaction, i.e. in manual transaction mode. Any open large object descriptors are automatically closed upon $commit(). For example:

Do cSess.$transactionmode.$assign(kSessionTranManual)
Do cSess.$begin()
Do cSess.$lobcreate() Returns lOid ## create new oid

Do cSess.$commit() 
Do cSess.$begin()
Do cSess.$lobopen(lOid) Returns fileDesc
Calculate lBinary as 'Some Unicode character dat
Do cSess.$lobwrite(fileDesc,lBinary) Returns lNumBytes ## write data into the large object
Do cSess.$lobseek(fileDesc,8,kPgSqlSeekSet) Returns lFilePos ## move the read/write pointer to byte 8/character position 3
Do cSess.$lobread(fileDesc,lCharValue) Returns lNumBytes ;;=> 'me Unicode character data' 

Do cSess.$commit() ## commit closes the descriptor

For further information on the behavior of these methods and the parameter values that may be applied, please refer to the Session Methods section above.

JSON Column Types

As of Studio 8.0.3 you can select and insert JSON strings into PostgreSQL JSON and JSONB columns. In earlier versions of Studio, there was partial support for insertion of JSON strings and it was possible to select from JSON columns using the CAST(… as VARCHAR(n)) operator.

The client library will parse and validate text before insertion into JSON/JSONB columns. You can optionally use this feature to validate JSON strings, for example:

Do cStat.$execdirect("select '5'::json as myCol") Returns #F

Do cStat.$fetchinto(lResult) ## Returns 5
Do cStat.$execdirect("select '{""col1"":1,""col2"":""mostly cloudy"",""col3"":true}'::json as myJSON") Returns #F

Do cStat.$fetchinto(lResult) ## Returns {"col1":1,"col2":"mostly cloudy","col3":true}
Do cStat.$execdirect("select '{""col1"":1,""col2"":""600 meters"",""col3"":[[""one"",""two"",""three""]}'::json as myValue") Returns #F

Do cStat.$fetchinto(lResult) ## Returns {"col1":1,"col2":"600 meters","col3":["one","two","three"]}

Any of the resulting strings can then be inserted into the database:

Do cStat.$execdirect('create table jsontest(col1 int, col2 jsonb)') Returns #F
Do cStat.$execdirect('insert int jsontest values(1,@[lResult])') Returns #F

Note that JSON string literals must be suitably escaped in respect of quotes and square brackets, as shown above.

To insert JSON strings using bind variables, the $addcustomdatatype() method should be used. This tells the DAM to map Omnis character fields of a specific fieldlength to JSON and/or JSONB columns, and also allows $createnames() to generate JSON and/or JSONB columns. For example:

Do cSess.$addcustomtype(1000,'JSON') Returns #F
Do cSess.$addcustomtype(1001,'JSONB') Returns #F
Do lList.$definefromsqlclass('scTest')
Calculate lSql as cSess.$createnames(lList)

Where scTest defines Character columns of length 1000 or 1001, the ‘JSON’ and ‘JSONB’ column type will be returned accordingly.

PostgreSQL Data Type Mapping

Omnis to PostgreSQL

_{[1] Numeric precision for Number (dp) columns uses the value of $numericprecision.
[2] The mapping used for the Omnis Sequence type depends on the value of $sequencetoint.
[3] This mapping occurs only if $char38touuid is set to kTrue
[4] Use the $addcustomtype() method to add additional mappings, e.g. for XML and JSON
*Time zone data types are used when session.$usetimezone is set to kTrue}

PostgreSQL to Omnis

_{⁽¹⁾ DAM will map decimal values to the Omnis Number dp data type where column scale is <=14
⁽²⁾ Supported in Studio 8.0.3 and later}

PostgreSQL Troubleshooting

The following points may help in resolving programming issues encountered using PostgreSQL session and statement objects.

• $rpcparameters()
  When calling $rpcparameters(), the DAM uses defaults for the column precision and/or scale since this information is not provided by the pg_proc system table.
  For this reason, the API may report parameter-matching problems when calling certain functions and the list (passed to $rpcdefine()) may need to be manually coerced.

• Error Messages
  The following additional error messages may be returned via the session or statement $errortext property:
  

  • "Native error text could not be retrieved". No connection currently exists to the server or there is no message corresponding to the current error code.

  • "Unsupported client protocol version". The protocol version reported by the client API is too low. The DAM cannot use this version and you should upgrade to a newer version of the client library. Use the PostgreSQL access library supplied with Omnis Studio.

  • "Client or interface function not available". The most likely cause of this error is that the client library (or one of its dependencies) was not found and has not been loaded. Can also occur if the client library being used does not provide a required interface function.

  • "server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request." This error can occur when logging on with a username other than “postgres”. The client library uses the username for the database name unless the database name is specified. Set $database to the required database name (e.g. “postgres”) and try again.

  • "SCRAM authentication requires libpq version 10 or above" or "authentication method 10 not supported".
    The PostgreSQL client library shipped with Omnis Studio does not support the requested authentication method (scram-sha-256) which was introduced in PostgreSQL v10. For connection to PostgreSQL version 10 and later, we have a technote that discusses the replacement of the PostgreSQL Client library: TNSQ0031. The relevant client library and any OpenSSL dependencies can be copied from the PostgreSQL server installation.

• Linux Terminal Messages. On Linux, NOTICE and/or WARNING messages are sent to stderr, (normally the terminal window behind Omnis). To avoid these, refer to the server configuration parameter: client_min_messages and set this to a higher level.

• Chunking and Batch Fetching. Chunking of large character/binary data is not handled by DAMPGSQL but is handled automatically by the API. Such data is effectively returned to the DAM as single chunks. $lobthreshold , $lobchunksize and $blobsize therefore have no effect.

Batch fetching of data is also not handled by DAMPGSQL. The API automatically manages transfer of the data and presents the DAM with the entire result set. Hence setting $batchsize has no effect.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at: https://www.omnis.net/developers/resources/technotes/

SQLite

This section contains the additional information you need to access a SQLite database, a very popular database which is embedded into a whole range of applications on desktop and mobile devices. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

SQLite implements a self-contained, server-less, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to a disk file which can contain multiple tables, indices, triggers, and views. For more information about SQLite and to download it, please go to the website: www.sqlite.org (portions of this text are taken from the SQLite website).

This section contains the additional information you need to access a SQLite database, including server-specific programming, trouble-shooting and data type mapping to and from the database. For additional information on changes to the SQLite DAM, refer to the readme file which accompanies your Omnis download.

Server-specific Programming

Logging on to SQLite

To connect using the SQLite DAM, create an object variable of subtype “SQLITESESS”.

You connect to a SQLite data file using the $logon() method. The hostname parameter should be the full path to the data file.

SQLite does not require a username or password, but you can specify a session name that will appear in the SQL Browser and in the Notation Inspector under $sessions.

SQLite expects a DOS-style pathname under Windows and an absolute POSIX-style path under macOS and Linux. For example:

Do mySession.$logon('C:\mydata\mydatafile.db','','','session1') Returns #F ## on Windows

Do mySession.$logon('/Users/MyUser/mydatafile.db','','','session1') Returns #F ## on macOS / Linux

Additionally, you can force SQLite to create the specified data file if it does not exist. To do this, set the $opencreate session property to kTrue before logging on.

To open a read-only connection, set the $readonly session property to kTrue before logging on. (It is not possible to create a data file if the connection is read-only).

If the hostname is ":memory:", then a private, temporary in-memory database is created for the connection. This in-memory database will be deleted when the database connection is closed. Filenames beginning with “:” should be considered reserved for future SQLite extensions and avoided to remove ambiguity. For in-memory databases, $version will be set to “:memory:” following $logon().

If the hostname is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed. For temporary databases, $version will be set to “:temporary:” following $logon().

For standard data file connections, $version is read directly from the file header information and reflects the file format version that the data file supports.

Transaction Support

SQLite supports both automatic and manual SQL transactions.

To invoke manual transaction mode, the $transactionmode session property should be set to kSessionTranManual.

In this mode you must commence each transaction by calling the $begin() session method and terminating each transaction either by calling $commit(), $rollback(), by switching back to kSessionTranAutomatic or by logging off.

The SQL text that is submitted each time $begin() is called may be augmented using the $transactiontype session property as shown below. The different transaction types affect the way in which SQLite acquires row locks on tables:

The $commit() and $rollback() methods, invoke the COMMIT and ROLLBACK commands respectively.

$commitmode and $rollbackmode are set to kSessionCommitClose and kSessionRollbackClose respectively for the SQLite DAM. Statement objects are closed upon $commit() / $rollback(). Any pending result set is discarded and the statement is returned to its prepared state ready for re-execution if desired.

Incremental BLOB I/O

SQLite supports incremental Input/Output to BLOB columns in database tables. This means that you effectively bind a placeholder for the BLOB at bind time, then write the data to it later. Similarly, you can open a handle to a BLOB which already exists in the database and read/modify its contents without the need to perform a SELECT statement.

To create a placeholder for a BLOB, you should bind the binary variable inside the SQL statement as normal, but set its contents to #NULL. On execution, this creates a zero-blob of size $blobsize- bytes padded with zeros.

The session object provides several methods for accessing and modifying BLOBs:

• $blobopen()
  Opens a handle to a BLOB column, identified by its database name, table name, column name and row number, optionally as read-only

• $blobclose()
  Closes a BLOB handle; you have to close any BLOB handles opened during the session, but any handles left open when the session ends are closed automatically

• $blobcloseall()
  Closes all BLOB handles

• $blobbytes()
  Returns the size in bytes that was allocated to a BLOB column when it was created

• $blobhandles()
  Returns a list of all open BLOB handles including the corresponding database, table name, column name and row number

• $blobreopen()
  Moves a BLOB handle to a new row within the same table

• $blobwrite()
  Writes binary data to a BLOB column

• $blobread()
  Reads binary data from a BLOB column

See the Session Methods section for more details and syntax for these methods.

Calculated Columns, Functions and Sub-Queries

A column returned from SELECT statement that is the result of an expression or sub-query cannot be described automatically by the DAM because there is no corresponding entry in the sqlite_master table. In this situation, you can provide a “hint” using a column alias name containing the intended SQL data type, for example:

select ’2014-07-27’ as mydate from table
select substr(col1,1,2)||':'||substr(col1,3,2) as id_char from test
select 1 as boolval, col5+3 as intval from test

Other data types recognised include; “timestamp, time, national, tinyint, serial, sequence, dec, float”. When used the emulate OmnisSQL, the DAM also recognises various OmnisSQL function names such as upper(), lower(), ascii(), charindex(), length(), mod(), round(), cos(), sin(), etc. The DAM also recognises literal numeric and integer values, so there is no need to provide alias names for such columns, e.g.

select 3.14159, 33*3, col3+6, sin(0.6), length(col1) from test

Note that there is currently no way to specify the data sub-type when using aliased column names, hence Character data will be Character 100000000 and numeric data will be Number floating dp. If the data type of a calculated column cannot be determined, it will be fetched as binary.

Similarly, result columns generated using operators have no SQLite "type affinity". Using the UNION operator for example, it is necessary to CAST the entire operation:

select CAST(amount as FLOAT) as myfloat from (select 2.35 as amount 
UNION ALL 
select 2.46 as amount)

To avoid using column alias names, you can pre-define the fetch list or table instance using a schema, in which case the fetched data will be coersed into the required column types.

Additional Functions

In order to better support Omnis SQL emulation, the SQLite DAM supports the following scalar functions in addition to the SQLite core functions:

SQLite Encryption

As of Studio 8.0.3, the SQLite DAM supports native datafile encryption. When enabled, all data written to the SQLite datafile is encrypted and can only be read and decrypted using the SQLite DAM with the appropriate encryption key.

Encryption is enabled by setting the session object $encryptkey property before logging on to the SQLite datafile. $encryptkey accepts a string of hexadecimal characters. The string should be of even length and should be no longer than 32 characters. The key value will be truncated if it does not meet either of these criteria. The accepted key value is then used to seed an internal private key which is subsequently used by all statement objects belonging to that session object.

To create a new encrypted datafile, the $opencreate property should also be set to kTrue before logging on. For example:

Do sessObj.$opencreate.$assign(kTrue)   ## create a new datafile if it does not exist
Do sessObj.$encryptkey.$assign('1a2b3c4d5e6f') Returns #F
Do sessObj.$logon('/Users/user1/Desktop/sqlite.db','','','session1') Returns #F

Once encrypted, $logon() will fail unless the correct $encryptkey is supplied. $encryptkey will be ignored (cleared) if the DAM detects a connection to a non-encrypted datafile. Please note that you cannot change the $encryptkey property while the DAM is logged on. Errors encountered during assignment of $encryptkey are written to session.$nativeerrorcode and session.$nativeerrortext.

The DAM provides two session methods that facilitate encryption/decryption of existing SQLite datafiles:

• $encrypt(filename)
  opens a non-encrypted datafile and encrypts it using the $encryptkey. A backup copy of the non-encrypted datafile is created at the file location named filename.bak

• $decrypt(filename)
  opens a previously encrypted datafile and decrypts it using the $encryptkey. A backup copy of the encrypted datafile is created at the file location named filename.bak

$encrypt() and $decrypt() return kTrue on success but will fail, unless the DAM is logged off, if the process cannot get exclusive read/write access to the specified datafile or if filename.bak already exists and cannot be overwritten. Once encrypted, connection via third-party tools should be avoided as this may result in undefined behaviour and cause datafile corruption.

Important note: Your attention is drawn to the terms of the Omnis End User License Agreement and to the following excerpt pertaining to the use of this encryption mechanism and subsequent loss of data and/or of the encryption key(s):

Omnis Software disclaims any responsibility for or liability related to the use of this software. IN NO EVENT WILL OMNIS SOFTWARE BE LIABLE FOR ANY INDIRECT, PUNITIVE, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES HOWEVER THEY MAY ARISE AND EVEN IF OMNIS SOFTWARE HAS BEEN PREVIOUSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Session Properties

Session Methods

Data Type Mapping

Omnis to SQLite

The SQLite DAM creates custom data types in order to preserve information about Omnis subtypes, notably: DATE(n) as well as PICTURE, LIST, ROW, OBJECT and OBJECTREF. There are also single mappings for Omnis Character and National data, implying that CHAR(n) and NCHAR(n) can store up to the maximum field length supported by Omnis (10000000 characters). This is contrary to other relational databases which impose a fixed size on such columns.

Although this greatly improves compatibility between Omnis and SQLite, if portability of the data file is of concern, then it may be preferable to avoid using $createnames() / $coltext() in favor of manual statements that use standard SQL types, e.g. VARCHAR(n), DATE, TEXT and BLOB.

SQLite to Omnis

The SQLite DAM recognises several additional SQL data types in order to maximise compatibility with externally generated data files as well as those generated by Omnis.

Troubleshooting

The following points may help in resolving programming issues encountered using SQLite session and statement objects.

For additional updated troubleshooting issues, refer to the readme file which accompanies the installation media.

For a detailed explanation of the SQL syntax supported by SQLite, please refer the SQLite website: www.sqlite.org

• SQLite does not currently support dynamic creation of SQL stored procedures or functions. The associated methods; $rpcprocedures(), $rpcparameters() & $rpc() therefore return kFalse.

• The SQLite API handles the transfer of binary data automatically. The $blobsize, $lobchunksize and $lobthreshold properties are therefore ignored.

• For performance reasons, journaling mode is set to PERSIST for the SQLite DAM. For optimum performance, especially on Linux it may be desirable to turn off journaling, (“PRAGMA journal_mode = OFF”). Note: in this mode however it will not be possible to rollback manual transactions.

• You may experience slow performance during certain INSERT operations. Each INSERT and UPDATE operation is normally committed to the disk drive so as to preserve integrity of the data in the event of a crash or power failure. Executing "PRAGMA synchronous=OFF" tells SQLite not to wait for data to reach the disk surface between writes which results in much faster performance. This risks data loss or corruption in the event of a crash however. Alternatively, you can use manual transaction mode (kSessionTranManual) to commit several INSERT operations at once.

• A column returned from SELECT statement that is the result of an expression or sub-query may require an additional column name alias containing the intended data type. Refer to Calculated Columns and Sub-Queries above for further details.

Oracle

This section contains additional information you need to access an Oracle database, including server-specific programming, PL/SQL, data type mapping to and from Oracle, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

Properties and Methods

In addition to the “base” properties and methods described in the SQL Programming chapter, the Oracle DAM provides the following additional features.

Session Methods

Session Properties

Statement Methods

Statement Properties

Connecting to your Database

To connect to your database, you need to create a session object with a subtype ORACLE8SESS (or the legacy subtype: ORACLE7SESS). In order to log on to the database using the SessObj.$logon() method, the hostname must contain a valid Oracle host alias previously generated by the Oracle client tools.

Mixing Unicode and Non-Unicode Data Types

This section summarises recent changes made to the Unicode Oracle Object DAM designed to enable insertion and retrieval of mixed ANSI and Unicode character types.

In the case of Oracle 8i and later, these data types are:

By default, the Unicode Oracle DAM maps all Omnis character data to the NVARCHAR2 and NCLOB data types, dependent on the field length of the Omnis bind variable. However, the Oracle DAM provides session properties which affect the Omnis to Oracle data type mappings:

• $nationaltonvarchar
  If set to kTrue, Character and National data types are treated differently when being inserted to VARCHAR2 / NVARCHAR2 columns.

• $nationaltonclob
  If set to kTrue, large Character and National data types are treated differently when being inserted to CLOB / NCLOB columns.

• $maxvarchar2
  Sets the byte limit above which Omnis character fields will be mapped to CLOB/NCLOB data types as opposed to VARCHAR2 / NVARCHAR2 columns. The maximum value is 4000 bytes.

• $longchartoclob
  If set to kTrue (the default), Omnis large character fields > $maxvarchar2 in byte length will be mapped to the CLOB/NCLOB data type. If set to kFalse, the LONG data type is used.

Reading Unicode and Non-Unicode Data

The Oracle DAM automatically detects the data type of retrieved character columns and converts the data accordingly. There is no need to modify any properties in order to retrieve mixed ANSI and/or Unicode Data.

Insertion/Update of CHAR and VARCHAR2 data

To write short character data to ANSI columns it is necessary to set $nationaltonvarchar to kTrue. In this mode, Omnis Character fields will be mapped to VARCHAR2 and National fields will be mapped to NVARCHAR2.

When set to kFalse (the default), both Character and National types will be mapped to NVARCHAR2.

Insertion/Update of CLOB data

Where the Omnis field length exceeds $maxvarchar2, the DAM will map to either CLOB, NCLOB or LONG dependent on the value of the $nationaltonclob and $longchartoclob properties. To write long character data to ANSI CLOB columns, it is necessary to set $nationaltonclob to kTrue. In this mode, Omnis Character fields will be mapped to CLOB and National fields will be mapped to NCLOB. When set to kFalse (the default), both Character and National types with byte sizes exceeding $maxvarchar2 will be mapped to NCLOB.

Note that where Omnis fields are mapped to NCLOB columns, $maxvarchar2 is interpreted as the length in bytes. Thus when set to 4000, this mapping will be applied for Character and/or National fields with a field length > 2000 characters

Fetching Very Large Objects

The Oracle DAM has the ability to fetch very large objects (up to 2GB) directly to the local file system. Two new properties have been added to the session object:

• $filethreshold
  the file threshold which is initially set to 50MB

• $filedirectory
  the directory to receive the file which is initially set from the USERPROFILE environment variable on Windows or HOME on macOS and Linux

Any CLOB, NCLOB, BLOB or BFILE column which exceeds $filethreshold will now be fetched in chunks using $lobchunksize directly to $filedirectory. The filename used will conform to “colname_xxxxxx.BIN” where xxxxxx is a unique identifier (based on #CT). Any character data written to file will be converted to UTF8, otherwise raw data will be written. For BFILEs this means that changing the file extension later (e.g. from .BIN to .AVI) will result in a facsimile of the original file. When a VLOB is written to file, its filename is returned into the result list column. Since the result column was previously described as binary it is necessary to extract the filename using the utf8tochar() function, e.g. Calculate filename as utf8tochar(lResult.1.colLOB).

Using Worker objects to fetch VLOBs

Fetching VLOBs on the main thread can cause Omnis to pause while the data is being transferred. Therefore, for large transfers it may be preferable to SELECT and FETCH each VLOB using an Oracle worker object. The main thread is then free to continue and will be notified when the VLOB has been fetched.

Insertion/Update of LONG data

When $longchartoclob is set to kFalse, Omnis Character and National fields which would otherwise map to CLOB or NCLOB will be mapped to the LONG data type. Since Oracle tables may contain only one column of type LONG, this may lead to problems if not used judiciously.

PL/SQL

Prior to Studio 10.2, the Oracle DAM does not support the remote procedure call methods such as $rpc() which are described in the SQL Programming chapter. Server procedures can be executed via PL/SQL. The Oracle DAM fully supports Oracle PL/SQL; a procedural language that the server executes.

You create a PL/SQL script and send it to Oracle in a similar way as any SQL statement and the server executes it. The statement object method $plsql() should be used instead of $prepare() when you want to call server procedures that contain bound OUT or IN/OUT parameters. Any PL/SQL bind variables being passed to Oracle should be passed as Omnis bind variables i.e. @[Xvar]. When $plsql() is called with bind variables, the DAM will check to see whether any return values are present after execution and will return them back into the Omnis variables. This does not happen after a $prepare(). If you are creating stored procedures or executing a server procedure for which you do not expect a return value, $prepare() will be sufficient.

# lEmpId is local Integer 32-bit with initial value of 0
Do StatObj.$plsql("begin select empno into @[lEmpId] from scott.emp where ename = 'JONES'; end;")
Do StatObj.$execute()
If lEmpId <>7566
  # Incorrect value returned from procedure 
End If 

After the PL/SQL executes, the Omnis variable lEmpId has the value associated with the column id from the row with the name ‘Jones’.

The Oracle DAM supports select tables returned through PL/SQL procedures. However Oracle can only return single column tables (arrays). The statement object method, $plsql(), has optional parameters that follow the cPLSQLtext parameter. These are necessary when calling server procedures that return single column tables. To bind a column of an Omnis list to the select table being returned requires a list variable to be bound in the SQL statement and the list column number to be passed as an additional parameter. If more than one select table is being returned, multiple lists will need to be bound and a column number parameter passed for each list, in the same order as the lists are bound. The bound lists do not have to be different lists, the same list can be bound more than once in the PL/SQL statement, but you must take care to specify a different column number for each occurrence of the bound list. If no column number parameter is passed for a bound list, the first column of the list is used by default.

Consider the following example table and PL/SQL package:

create or replace package test as
  type account_name is table of accounts.name%TYPE index by binary_integer;
  type account_balance is table of accounts.balance%TYPE index by binary_integer;
end test;
create or replace procedure credit(accnum IN number, amount IN number,pname out test.account_name, pbalance out test.account_balance)
is
  cursor c1 is select name,balance from accounts where balance > limit;
  row_count BINARY_INTEGER;
begin
  row_count := 1;
  update accounts set balance = balance + amount where id = accnum;
  open c1;
  LOOP
    FETCH c1 INTO pname(row_count),pbalance(row_count);
    row_count := row_count + 1;
    exit when c1%NOTFOUND;
  end LOOP;
  close c1;
end;

# Local variables:
# lName=Character 256, lBalance=Number2dp, lCreaditList=List 
Do SessObj.$blobsize.$assign(32767) ## Prevents ORA-06505
Do lCreditlist.$define(lName,lBalance)
Do StatObj.$plsql('begin credit(2,490,@[lCreditlist], @[lCreditlist]); end;',1,2) Returns #F 
Do StatObj.$execute() Returns #F
If Creditlist.$linecount<>6
  # Incorrect data returned from stored procedure 

End If 

In the above example, the same list has been bound twice, the first bind variable binds the first column of the list and the second bind variable binds the second column as defined by the second and third parameters of the $plsql() method.

After the PL/SQL procedure executes, lCreditList should contain 6 rows as follows:

The balance of account id 2 (Sally) has been increased by 490. The procedure returns details of those accounts where the balance column is greater than the limit column.

$rpc() Support

As of Studio 10.2 (rev 30204), there is support for $rpcprocedures(), $rpcparameters(), $rpcdefine() and $rpc(). $rpc() basically executes a PL/SQL begin… end statement block that calls the stored procedure or function. Operation is as described in the SQL Programming chapter with one exception. When bindng single-column SELECT tables as in the previous example, it is necessary to pass the required list column numbers along with the parameter definitions. To do this, the DAM uses column 5 of the list returned by $rpcparameters(). For example:

Do cStat.$rpcparameters('credit') Returns #F
Do procList.$define()
Do cStat.$fetch(procList,kFetchAll)     ## returns 4 rows
Do procList.3.5.$assign(1)   ## Assign the list column number to 1

Do procList.4.5.$assign(2)   ## Assign the list column number to 2
Do cSess.$rpcdefine('credit',procList) Returns #F

Do lCreditList.$define(lName,lBalance)
Do cStat.$rpc('credit',1,10,lCreditList,lCreditList) Returns #F  

The additional values assigned to procList correspond to the column numbers that would otherwise be passed via the $plsql() method in the previous example.

You can also call a stored function using the $rpc() method and the return value will be written to the statement object’s $rpcreturnvalue property. For example:

Begin statement 
Sta: CREATE OR REPLACE FUNCTION test_function
Sta: RETURN VARCHAR2 IS
Sta: BEGIN
Sta: RETURN 'This is being returned from a function';
Sta: END test_function;
End statement 

Do cStat.$execdirect() Returns #F
Do cStat.$rpcparameters('test_function') Returns #F
Do procList.$define()
Do cStat.$fetch(procList,kFetchAll)

Do cSess.$rpcdefine('test_function',procList) Returns #F
Do cStat.$rpc('test_function') Returns #F   ## now check the value of $rpcreturnvalue

Alternatively, could can invoke functions in SQL statements. For example:

Do cStat.$execdirect('select test_function() from dual') Returns #F

Do cStat.$fetchinto(lCharVar)

$rpc() is limited to calling a single stored procedure or function. To execute more complex PL/SQL constructs, you can continue to use the $plsql() method.

Positioned Updates and Deletes

You can use positioned updates and deletes to update and delete specific rows from the select table you are fetching. To enable positioned updates and deletes the statement object method, $prepareforupdate(), should be used. This method creates and prepares a 'select for update' statement for specific use with positioned updates and deletes. A 'select for update' SQL statement can be prepared using the $prepare() method. However, it will not store the current ROWID in the statement object.

You can use $prepareforupdate() in conjunction with $posupdate() and $posdelete() to update or delete a row, which is determined by the current row in the specified statement. The 'select for update' statement is built based on the parameters passed. The columns will be derived from the list or row passed as the first parameter. If the cTablename parameter is omitted, the name of the list/row is assumed to be the name of the table. A SQL WHERE clause is appended to the select statement if it has been specified. After this statement has been executed the last row fetched will be seen to be the current row. If the statement does not perform a fetch, there will not be a current row.

Note: For all position update and delete functionality the transaction mode must be kSessionTranManual.

# lEmpSt,lEmpUpSt,lEmpDelSt are Statement Object instances derived from the same session
# lTableName is a Character(32) variable 
# iTableList and lTempList are list variables defined from schema class scPos1 
# lDataRow is a row defined from schema class scPos1 
# Fetch the row 
Calculate lTableName as $classes.scPos1.$servertablename()
Do lEmpSt.$prepareforupdate(iTableList,lTableName)
Do lEmpSt.$execute()
Do lEmpSt.$fetch(lDataRow,1)
# Update this row 
Calculate lDataRow.vala as 5 ## change the value of one of the columns
Do lEmpUpSt.$posupdate(lEmpSt,lDataRow)
Do lEmpUpSt.$execute()
# Fetch the next row 
Do lEmpSt.$fetch(lTempList,2)
# delete this row 
Do lEmpDelSt.$posdelete(lEmpSt)

Do lEmpDelSt.$execute()

Oracle 8 Data types

The Oracle8 DAM (DAMORA8) supercedes the older Oracle7 DAM (DAMORA7, which is no longer in development). DAMORA8 has been specifically written to connect to Oracle8 (and later) databases but can be used against an Oracle7 server using the recommended clientware. In this case, the DAM will encounter restrictions as described below, mainly concerned with data type mapping of large objects.

Large Objects (LOBS))

CLOBs, NCLOBs and BLOBs are data types introduced for Oracle 8 that deal with large objects. Internal LOBs (BLOBs, CLOBs, NCLOBs) are stored in the database tablespaces in a way that optimizes space and provides efficient access. These LOBs have the full transactional support of the database server. The maximum length of a LOB/FILE is 4 gigabytes. Internal LOBs have copy semantics. Thus, if a LOB in one row is copied to a LOB in another row, the actual LOB value is copied, and a new LOB locator is created for the copied LOB.

The Oracle8 DAM uses locators to point to the data of a LOB or FILE. These locators are invisible as the DAM performs operations on the locator to insert, update, delete and fetch the values. This means that you are only ever dealing with the values of the LOBs and not the locators.

You can work with the locators rather than just the values using PL/SQL in conjunction with the dbms_lob package. Further information can be found in the Oracle8i Supplied Packages Reference.

External LOBs (FILEs) are large data objects stored in the server's operating system files outside the database tablespace. FILE functionality is read-only. Oracle currently supports only binary files (BFILEs). The Oracle8 DAM uses locators to point to the data of a FILE. The FILE locator will be invisible, as the DAM will return the value of the external file and not the locator when performing transactions with the BFILE data type. Even though the BFILE data type is read-only you can insert a directory alias and filename into the column. These values are assigned to a single Omnis binary variable and separated by the '&' symbol. The DAM will assign these values to the locator so that when a fetch is performed on the locator the binary representation of the external file corresponding to the alias and filename will be returned. An example is shown below.

# A Directory alias needs to be created on the server that points
# to an OS folder 
myStat.$execdirect("create or replace directory sound as 'c:\bfiles'")
# BFILE1 and BFILE2 are Omnis variables of type Binary. 
# The variable is calculated as 
# '<DirectoryAlias>&<Filename>'. 
Calculate BFILE1 as 'sound&wav2.wav'
Calculate BFILE2 as 'sound&wav3.wav'
myStat.$execdirect('insert int bfiletest values(1,@[BFILE1],@[BFILE2])')
# You can now select the data back and this time you can receive the binary representation of the file. 
myStat.$execdirect('select * from bfiletest')
myStat.$fetch(myRow)
# The values contained in col2 and col3 of myRow can now be 
# written to the local drive using the Omnis Fileops commands. 
Calculate File as 'c:\windows\desktop\wavtest.wav'
Do Fileops.$createfile(File) Returns lErr
Do Fileops.$openfile(File) Returns lErr
Do Fileops.$writefile(myRow.2) Returns lErr

Do Fileops.$closefile() Returns lErr

Ref Cursor Data Types

The REF CURSOR is an Oracle 8 data type that is used to point to a set of results from a multi-row query. When executing a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use a variable of type REF CURSOR, which points to the work area. To create cursor variables, you define a REF CURSOR type and then declare cursor variables of that type.

A REF CURSOR type can be defined in an Oracle Package object. For example:

create or replace package OmnisPackage
as
type cursorType is ref cursor;
end;

This data type can be used in other Oracle objects, such as procedures and functions in order to process result sets. An example of a Stored function follows:

create or replace function OmnisFunction return OmnisPackage.cursorType
as
l_cursor OmnisPackage.cursorType;
begin
open l_cursor for select * from scott.dept;
return l_cursor;
end;

An example of a stored procedure that uses the defined REF CURSOR type follows:

create or replace procedure OmnisProcedure
( p_cursor in out OmnisPackage.cursorType )
as
begin
open p_cursor for select ename, empno from scott.emp order by ename;
end;

Cursor variables are like pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item.

REF CURSOR data types can be returned in three different ways; via a PL/SQL block, an Oracle Stored Function or an Oracle Stored Procedure. The REF CURSOR type is a pointer to a result set. The SQL statement that returns the REF CURSOR must be prepared using the $plsql() method. The Oracle 8 DAM maps the REF CURSOR type to an Omnis Oracle8 Statement Object*. The statement object will be created by the DAM and will belong to the same session object as the Statement object that prepared the initial SQL. It will have a $state of kStatementStateExecuted and, assuming that there is data in the result set, will have $resultspending set to kTrue. Therefore, the statement object will be in a ‘Ready-For Fetch’ state. Below are examples of the three ways to return and use a REF CURSOR in Omnis. The connection code and the creation of initial Statement Object (myStatement) have been removed for clarity.

*As of Studio 5.2, the Object variable used to return the REF CURSOR result set may instead be passed as an Object reference if preferred.

PL/SQL Block

The PL/SQL method does not require any SQL objects created on the server. All the PL/SQL code can be encapsulated in an Omnis Statement block.

# declare vars: cursor1 (Object), myList1 (List)
Begin statement 
Sta: begin
Sta: OPEN @[cursor1] FOR SELECT * FROM scott.emp;
Sta: end;
End statement 
If myStatement.$plsql()
  If myStatement.$execute()
    Do cursor1.$fetch(myList1,kFetchAll)
    # myList1 will contain the rows of the result set. 
  Else
    OK message Error {[ myStatement.$nativeerrortext]}
  End If 
Else
  OK message Error {[ myStatement.$nativeerrortext]}

End If 

Stored Functions

Returning a REF CURSOR from a Stored Function requires an Oracle Stored Function on the database. The Function must have a return type that has been defined as a REF CURSOR. For this example we will assume that the example Oracle Stored Function described above has been created on the server.

# declare vars: cursor2 (Object), myList2 (List)
If myStatement.$plsql('begin @[cursor2] := OmnisFunction; end;')
  If myStatement.$execute()
    Do cursor2.$fetch(myList2,kFetchAll)
    # myList2 will contain the rows of the result set. 
  Else
    OK message Error {[ myStatement.$nativeerrortext]}
  End If 
Else
  OK message Error {[ myStatement.$nativeerrortext]}
End If 

Stored Procedures

Returning a REF CURSOR from an OUT or IN OUT parameter of a Stored Procedure requires an Oracle Stored Procedure on the database. The Procedure must have an OUT or IN OUT parameter type that has been defined as a REF CURSOR. For this example we will assume that the example Oracle Stored Procedure described above has been created on the server.

# declare vars: cursor3 (Object), MyList3 (List)
If myStatement.$plsql('begin getemps(@[cursor3]); end;') 
  If myStatement.$execute()
    Do cursor3.$fetch(myList3,kFetchAll)
    # myList3 will contain the rows of the result set. 
  Else
    OK message Error {[ myStatement.$nativeerrortext]}
  End If 
Else
  OK message Error {[ myStatement.$nativeerrortext]}
End If 

Oracle 9i Data types

The Oracle 8 Object DAM includes support for data types added for Oracle 9i, namely the XML and URI data types. The XML data type lets you store native XML documents directly in an Oracle database and eliminates the need to parse the documents coming into and out of the database. Server specific properties and methods have been added to the DAM to support these enhanced database operations.

Oracle 9i also introduced several new Universal Resource Identifier (URI) types. These are used to identify resources such as Web content anywhere on the Web and can be used to point to data either internally or externally from the database itself. In addition to support for URIs, the Oracle DAM includes support for querying and other abstract functions provided for the URI types.

These changes were introduced with Omnis Studio version 3.2. The old-style, single threaded DAM (DORACLE8) can connect to Oracle 9i databases, but does not support the XML and URI data types.

XMLType

The XMLType is a system defined data type with predefined member functions to access XML data. You can perform the following tasks with XMLType:

• Create columns of XMLType and use XMLType member functions on instances of the type.

• Create PL/SQL functions and procedures, with XMLType as argument and return parameters.

• Store, index, and manipulate XML data in XMLType columns.

URIType

The URIType is an abstract object type that can store instances of HttpUriType or DBUriType. Universal Resource Indicator references can point to XML, HTML and custom internet content which can be located either locally within the database, externally to the database but local to the server or remotely across an internet or network connection.

DBUriType

The DBUriType can obtain data pointed to by a DataBaseUri-reference. A DBUri-Ref is a database relative URI that is a special case of the Uri-ref mechanism, where ref is guaranteed to work inside the context of a database and session. This ref is not a global ref like the HTTP URL, instead it is local ref (URL) within the database.

HttpUriType

The HttpUriType implements the HTTP protocol for accessing remote pages.

UriFactoryType

It is not possible to generate table columns using the UriFactoryType. Rather, this is a PL/SQL package containing factory methods that can be used to generate the appropriate instance of the Uri types without having to hard code the implementation in the program. Custom URI types can be defined and registered using this package.

For further information on the application of these data types, refer to the Oracle9i Application Developer's Guide – XML.

Retrieving XML and URI data

In Oracle9i version 9.1, it is not possible to directly SELECT data from columns defined using these types. Instead, the appropriate accessor functions should be used.

XMLType provides the extract(), getClobVal(), getStringVal(), and getNumberVal() functions for data query and retrieval. The extract() function has to be used in conjunction with one of the data type conversion functions, since it returns an object of type XMLType. The following example SQL statement can be used to extract an XML document from an XMLType column:

SELECT a.xmlcol.extract(‘*’).getStringVal() AS mycol FROM mytable a

If required, the XPath expression parameter to the extract() function can be supplied using a character bind variable. For further information on the extract() function and the supported Xpath syntax, refer to the Oracle9i Application Developer's Guide – XML.

URITYPE and its derivatives provide the getClob(), getUrl() and getExternalUrl() functions for data retrieval. When getClob() is executed, the URL stored in the database column is read. The document pointed to by the URL is then accessed and returned as CLOB data which can be read into Omnis:

SELECT a.myuri.getclob() AS mycol FROM mytable a

The getUrl() and getExternalUrl() functions return the URL contained in the database column. (getExternalUrl() differs from getUrl in that it escapes the URL so that it better conforms to the URL specification):

SELECT a.myuri.geturl() AS myurl FROM uritest a

The getClob(), getUrl() and getExternalUrl() functions can be overridden when creating custom URI types as defined using the UriFactoryType. For information on the UriFactoryType, refer to the Oracle9i Application Developer's Guide – XML.

Inserting XML and URI data

To insert XML data into an XMLType column, the data must be a valid XML document or fragment. This is because XMLType validates the XML before storing it. A simple insert statement would be of the form:

INSERT INTO xmltable VALUES(…,sys.XMLType.createXML (‘<DOC> </DOC>’),…)

If required, the XML text can be supplied using a character bind variable.

XML data can also be supplied as CLOB data by inlining a SELECT statement (or some other expression which returns a CLOB):

INSERT INTO xmltable SELECT id, sys.XMLType.createXML(myclob) FROM clobtable;

Inlining ensures that createXML() receives a CLOB field, which is not possible from Omnis since CLOBs are converted into Omnis character strings when fetched.

To insert a URL into a URIType column or one of its derivatives, use the createUri() function. For example:

INSERT INTO uritest VALUES (…,sys.httpUriType.createUri(‘http://www.omnis.net’),…)

If required, the URL can be supplied using an Omnis character bind variable.

Updating XML and URI data

In version 9.1, Oracle9i stores XMLType internally using the CLOB data type. Updates on CLOBs have to be performed on the entire column and for this reason, when updating an XMLType column, it is necessary to re-insert the XML data.

UPDATE xmltest
SET xmlcol = sys.XMLType.createXml(@[iXMLText]) WHERE IDcol = @[iRecordNum]
Similarly, with URITypes, updates are performed as follows:
UPDATE uritable
SET uricol = sys.httpUriType.createUri(@[iHTMLURL]) WHERE IDcol = @[iRecordNum]

Oracle Data Type Mapping

Omnis to Oracle

_{[1] Dependant on the values of $nationaltonvarchar, $nationaltonclob and $maxvarchar2
[2] Dependant on the value of $booltonum
[3] Dependant on the value of $binarytoblob}

Oracle to Omnis

_{[1] $fetch64bitints must be kTrue}

Oracle Troubleshooting

• When performing transactions that use the new LOB data types the transaction mode must be set to either kSessionTranAutomatic or kSessionTranManual. This is because LOB and file locators cannot be used across transactions. The DAM performs functionality on these locators and when the transaction mode is either automatic or manual, the DAM can control when to commit the command, which would be after all the LOB functionality has been performed. When the transaction mode is server, Oracle commits (or rollbacks) after every statement and any LOB functionality performed by the DAM would result in an error.

• Oracle has a client character set and a server character set. If the two are not the same character set, Oracle will convert between the two. If a character in the client character set also exists in the server character set, Oracle will store that character on the server. If the character doesn’t exist in the server character set, Oracle will do one of two things. Firstly, Oracle will see if there is a close-fit character. For example, if you are trying to insert the character ‘à’, but that particular character is not in the server character set, Oracle will store that character as an ‘a’ as it is a close-fit. If there is not a close-fit character, Oracle will store that character as an ‘unknown’ character on the server. This ‘unknown’ character is usually ‘¿’. If the client character set is the same as the server character set, no conversion takes place and all ASCII values of characters are preserved. On retrieval of characters Oracle will again convert but this time from the characters stored in the Oracle database to the client’s character set being used to retrieve the text.

• As with inserting Oracle will convert a character in the server character set to the same character in the client character set if it exists. If not, it will try a ‘best fit’ solution before returning its ‘unknown’ character. To guarantee that Oracle doesn’t convert the data in the database, the client character set should be the same as the server character set. It is possible to use Omnis’ character maps to change some of the characters which aren’t correct to ones that are preferred (Non-Unicode session objects only). This is useful when Oracle returns a ‘best fit’ character and not the original character.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at:

Sybase

This section contains the additional information you need to access a Sybase database, including server-specific programming, data type mapping to and from Sybase, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

Properties and Methods

In addition to the “base” properties and methods documented in the SQL Programming chapter, the Sybase DAM provides the following additional features.

Session Properties

Session Methods

Statement Properties

Statement Methods

Connecting to your Database

To connect to your database, you need to create a session object with a subtype of SYBASESESS. In order to log on to the database using the SessObj.$logon() method, the hostname must contain a valid Sybase host alias previously generated by the Sybase client tools (see your Sybase documentation for more details).

Multiple Select Tables

create proc multi_select as
SELECT firstName, lastName FROM Agents
SELECT id, name FROM Customers

To execute this from an Omnis method and to fetch the results:

Do tStatement.$execdirect('exec multi_select') Returns #F
Do My_List1.$define()
Do tStatement.$fetch(My_List1,kFetchAll) Returns #1
If #1=kFetchFinished & tStatement.$resultspending=kTrue
  Do My_List1.$define()
  Do tStatement.$fetch(My_List1,kFetchAll) Returns #1

End If 

The Sybase DAM reports the select tables exactly as Open Client reports them. If a select result set has no rows, $fetch() will return kFetchFinished the first time it is invoked for that set.

Program Name

The Sybase sysprocesses table (in the master database) has a program_name column that stores a separate name for each connection to the server. The session $programname property lets you put a name into this column for the current session so that you can use it to distinguish multiple sessions.

The default name is the name of the current library, i.e. $clib().$name If you wish to change this you must set this property before logging on to the server, because the value gets set at logon. If the value is set after logon it does not take effect until the session is logged on again. The value persists across logons and logoffs, and $clear() does not reset it.

If the string assigned to the property is too long, the DAM truncates it without reporting an error. The DAM can store a maximum of 255 characters but the program_name field in the sysprocesses table currently only allows 16.

Error Handling

If an error is raised on either a Sybase session or statement object, the $errorcode and $errortext properties associated with the object will provide the generic error code and error text. If there is an associated native Sybase server or client error this will be returned in the $nativeerrorcode and $nativeerrortext properties associated with the object.

It is possible for an Omnis command to generate multiple Sybase server and client error messages. If this is the case, the object’s $nativeerrorpending property will be set to kTrue. To retrieve the next set of error information the application can use the $nextnativeerror() method. The DAM will return server errors and then client errors in the order in which they were generated. Any informational messages returned from Sybase are ignored.

If a new Sybase DAM command is issued or a Sybase property is set, the current error information for that object is cleared. The error set for the session is shared between the session and all statements in that session. If a session or statement clears the current error set, any other statement with multiple errors pending will only be able to retrieve the last cached error since the error set will have been cleared.

You should be aware that the Sybase server and client errors reported may have codes and messages that sometimes differ between the macOS and Windows Sybase clients.

The Sybase DAM defines several of its own internal error codes. These are returned in the $nativeerrorcode and $nativeerrortext properties of the session and statement.

If a Sybase session or statement object generates an Open Client error, the error code is decoded into the $cterrorlayer, $cterrorseverity, $cterrororigin and $cterrornumber. Sybase Open Client errors use these 4 error components to provide more detail about the error raised.

If a timeout error occurs and the session’s $failtimeout property is kTrue, a timeout error will be raised, the connection will be marked as dead and the session will be logged off. If the $failtimeout is set to kFalse (the default) the connection or query will be re-tried.

Large Objects (LOBs)

The Sybase Object DAM can send and retrieve text and image fields which are referred to as LOBs or “large objects”. You can insert, update and fetch the fields using the standard SQL object methods or you can use the $writelob() method to update a text or image field on the DBMS, with the implicit functionality to retrieve a large text or image field.

Transferring BLOBs is very memory-intensive, since each layer of software has a copy of at least part of the blob. Thus, sending a simple 40K picture can demand several times that amount of RAM before it gets passed over to the DBMS. Therefore an application must have sufficient memory resources to transfer large text and image data. The built in chunking mechanism can be used to reduce the amount of memory Omnis requires to transfer a LOB value. For example, the default settings for the session’s $lobthreshold and $chunksize properties ensure that data greater than 32K is sent in chunks no greater than 32K. The chunksize and threshold can be altered to suit the resources available. If a system has more memory, the threshold and chunksize can be increased to send fewer, larger chunks.

There are no limitations, aside from memory concerns, on sending or retrieving multiple LOBs in one SQL statement.

You should not forget to set the textsize parameter on the server. This parameter tells the server to truncate all outgoing values to this setting (see your Sybase documentation for more details). Therefore, if you set the textsize parameter to the default of 32,767 and select a 500K image, you get a 32,767 byte value in Omnis.

Do tStatement.$execdirect('set textsize 123456')
# this sets the textsize parameter, for this session, to about 123K 

This setting is for fetching values only. When fetching LOBs under Mac Classic using the standard commands, you should not set this parameter to its largest value. Increasing this also causes Open Client to allocate more memory to deal with the larger LOBs. Therefore, setting it too small will truncate your fetched data while setting it too large may cause Open Client to kill your connection. If you are retrieving a variety of LOBs, you should try to set it as closely as you can to the size of the largest LOB; you can set this for each SQL statement sent. You can also use the Sybase datalength() function to find out how long the value is that you want to retrieve, and use this to set the textsize parameter.

The Sybase DAM provides a faster and more memory efficient way to update a text or image column through the use of the statement method $writelob(). To use the $writelob() method you must already have the row in the database and the column value that is being updated must have non-NULL data in it.. You would usually create the row with a blank (' ') in the column, for instance, use the $writelob() method to update the value with the LOB data.

Do tStatement.$execdirect ("insert into mytable (x, mycol) values (2, ' ')")
Do tStatement.$writelob(LOB_DATA,'mytable','mycol','where x=2',kTrue)

This command places the value of the Omnis field LOB_DATA into the column mycol of the table mytable in the row where x has the value of 2. Thus, the method places a single LOB value into a location that you specify.

The method is defined as:

StatementObj.$writelob(vVariable, cTablename, cColumnname [,cWHERE-clause, bUselog = kTrue])

The vVariable parameter is the Omnis variable containing the data to be sent to the server, in the example this is LOB_DATA. This variable cannot be NULL or empty.

The cTablename and cColumnname parameters identify the table and column to update. These cannot be NULL or empty.

The cWHERE-clause parameter supplies an optional WHERE clause for a SQL SELECT statement, including the word ‘WHERE’. If your WHERE clause is ambiguous Omnis updates the first LOB value it finds, so the value updated may not be the one you intended. Make sure your clause specifies a unique row.

The bUseLog parameter denotes whether to log this action in the transaction log. If you do not log the action, you cannot roll it back. The default is kTrue to log the update. Setting this parameter to kFalse requires that the select into/bulkcopy option be set to true with the system procedure sp_dboption for the database on the DBMS. If you do not wish to log updates, you must consult your documentation and system administrator as this may have significant ramifications on being able to backup and recover your database.

The $writelob() method sets the flag false and sets error information in the same way as a standard statement method.

Sybase recommend that data should be updated using the $writelob() method if the data size exceeds 100K.

Remote Procedure Calls

The Sybase DAM supports the use of the session method $rpcdefine() to define a remote procedure and the statement method $rpc() to call a procedure. The DAM does not support the statement methods $rpcprocedures() and $rpcparameters().

An application must generate the parameter list passed to $rpcdefine() to describe the parameters in the Sybase procedure. When a $rpc() call invokes the procedure the parameters passed are mapped from their Omnis type definition to the equivalent Sybase type in the same way as standard Omnis bind variables. A $rpc() call will fail if a parameter definition includes an Omnis character or binary field larger than 255 since the parameter will map to a text or image field which are not valid for use as parameters in Sybase stored procedures.

If an rpc definition defines parameters of type kParameterInputOutput, these are treated as output parameters since Sybase does not support updateable input parameters. The DAM cannot update output parameters directly. If the procedure uses output parameters these must still be specified in the call to $rpc() and are returned as a parameter result set which is available when the statement property $rpcparamspending is kTrue. They must be processed by the application in the same way as a normal result set. Any result sets generated by the stored procedure must be processed before the parameter results become available. The return status should not be included in the call to $rpc() as this will be set in the statement $rpcreturnvalue property which is set after the stored procedure results are processed.

The following creates a Sybase stored procedure which takes 2 input parameters and 1 output parameter. This procedure returns two result sets.

Do tStatement.$execdirect(\
    'create procedure Test_SYBRPC @parm1 varchar(30), \
    @parm2 varchar(30), @parm3 varchar(60) \
    OUTPUT AS SELECT @parm3 = @parm1+@parm2 \
    SELECT * from sys execute sp_who RETURN 12345')

A list is used to define this procedure in the Sybase session.

Do #L1.$define(#1,#2,#3,#4) Returns #F
Do #L1.$add(kInteger,kLongint,0,kParameterReturnValue)
Do #L1.$add(kCharacter,kSimplechar,30,kParameterInput)
Do #L1.$add(kCharacter,kSimplechar,30,kParameterInput)
Do #L1.$add(kCharacter,kSimplechar,30,kParameterOutput)
Do tSession.$rpcdefine('Test_SYBRPC',#L1) Returns #F

The procedure can then be called.

Calculate Parm1 as 'Hello ' ## Character 30
Calculate Parm2 as ' There' ## Character 30
Calculate Parm3 as ## Character 60
Do tStatement.$rpc('Test_SYBRPC',Parm1,Parm2,Parm3) Returns #F

Since the stored procedure generates two result sets these must be processed first.

This will fetch the results from the sysusers and sp_who queries.

If tStatement.$resultspending=kTrue
  Do #L1.$define()
  Do tStatement.$fetch(#L1,kFetchAll) Returns #1
End If 
If tStatement.$resultspending=kTrue
  Do #L1.$define()
  Do tStatement.$fetch(#L1,kFetchAll) Returns #1
End If 

The stored procedure return status is placed in the statement’s $rpcreturnvalue property and the parameter result set is then available.

Calculate #1 as tStatement.$rpcreturnvalue ## will set #1 to 12345
If tStatement.$rpcparamspending=kTrue
  Do #L1.$define()
  Do tStatement.$fetch(#L1) Returns #1
End If 

Calculate Parm3 as #L1.1.1 ## will set Parm3 to 'Hello There'

Multiple Cursors

If a statement is issued without using a cursor, i.e. $usecursor is set to kFalse, any results generated will block the connection and no other operation on any other statement can be performed until the blocking result set is completely fetched or cancelled. To avoid blocking the connection with pending results, use a statement which has the $usecursor set to kTrue. Note that a statement using a Sybase cursor must have a unique statement name and only allows SQL SELECT and EXECUTE procedure commands to be issued.

Meta-Data Queries

As of Studio 5.1.1, the $indexes() meta data method returns additional information via the DamInfoRow column. The DamInfoRow will be defined with the following columns (as returned by the sp_statistics stored procedure):

Logon Problems using the SYBASEDAM

In the event of connection problems, there are a number of Technotes available on the Omnis website (https://www.omnis.net/developers/resources/technotes/) which discuss Sybase connection issues in greater detail.

Possible causes:

• The $SYBASE/interfaces file is missing or the contents are invalid- follow the installation tasks outlined above.

• The logon hostname does not match the name contained in the $SYBASE/interfaces file- check the contents of the interfaces file, paying attention to upper and lower case characters.

• The supplied username and/or password were incorrect- check at the server.

• The xcomp:ini:sybasedam.ini file was not found or one or more of the environment variables are set to incorrect values. Review this file.

• The DAM does not load. The dynamic linker may be unable to locate the required Sybase client libraries. Check environment variables (DYLD_LIBRARY_PATH for macOS, LD_LIBRARY_PATH for Linux)

Sybase Troubleshooting

• Sybase is a case-sensitive RDBMS. Check the case of the table or column names if you can see a table but cannot select anything out of it

• Sybase defaults to NOT NULL columns; you must initialize columns to a specific value while inserting data, or insertion will fail

• Any number with no digits after the decimal point, that is > +/- 231 will generate an error and not be inserted. This is because Sybase tries to parse numbers without decimal points as integers

• Sybase does not support binding a NULL Boolean field in Omnis to a Sybase bit field

• Sybase does some character mapping where required, but you may need to do character conversion explicitly using the Omnis character mapping tables.

• Sybase interprets empty strings as single spaces.

• Fetching pictures from Sybase stored there by other applications, even in standard formats, is likely to cause problems, since Omnis stores all pictures in a special format. This occurs even in platform-specific graphics formats such as PICT or BMP.

• The $tables() session method can only report information about tables in the current database and does not return system tables.

• The $columns() session method can only report information about tables owned by the current user in the current database.

• The $indexes() session method can only report information about indexes on tables in the current database.

• Sybase does not allow DDL statements to be issued within a user defined transaction, i.e. do not use statements such as CREATE, DROP and ALTER when the session’s transaction mode is kSessionTranManual. Do not use the $indexes() method using kSessionTranManual since this method creates a table.

• Sybase automatically strips spaces from character data returned to Omnis.

• “Data buffers could not be allocated” error following a logon attempt:
  This error normally occurs if the Sybase environment variables; SYBASE, SYBASE_OCS and/or LANG/LC_ALL are not correct.
  Check the sybinit.err file (in the Omnis folder) for more details about the error.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at: https://www.omnis.net/developers/resources/technotes/

Sybase Data Type Mapping

Omnis to Sybase

Sybase to Omnis

DB2

This section contains the additional information you need to access a DB2 Universal Server database, including server-specific programming, data type mapping to and from DB2, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

Properties and Methods

Session Properties

Session Methods

Statement Properties

Connecting to your Database

To connect to your database, you need to create a session object with a subtype of DB2SESS. In order to log on to the database using the SessObj.$logon() method, the hostname must contain the catalog database name entered using the DB2 Command Line Processor or using the Client Configuration Assistant if installed. The user name and password should contain the values required by the database. For example:

Do SessObj.$logon('MyDatabase','UserID','Password','MySession') Returns #F

In the event of connection failure, the DAM will timeout as dictated by any timeout policy in use by the server. Logon failures are usually reported immediately.

Transactions

Generally, using manual transaction mode results in increased performance because the session object does not force a commit after each statement.

If you do not have a results set pending, the DB2 session object will commit each statement if the transaction mode is automatic. If the transaction mode is server, the server commits the statement automatically.

Dates

The session property $defaultdate allows default values to be added to date values mapped to the server where the Omnis date value does not contain complete information, e.g. when a Short time is mapped to a server DATETIME. The date stored in this property is in a generic format, i.e. it is compatible with any regional date format that the server may be using.

Boolean Type

DB2 does not include a specific type for storing single bit data. The Omnis Boolean type is therefore converted to a CHAR(3) value and stored as ‘YES’ or ’NO’ in the server table. The string representation can then be mapped back to an Omnis Boolean type when the data is retrieved.

BLOB Type

The session property $blobsize can be used to specify the size argument for columns of type BLOB generated when the $createnames and $coltext methods are used.

Values range from 1 to 10000000. The default value for $blobsize is 10000000 which is also the maximum size of an Omnis binary variable.

This property is equivalent to the old-style <SETBLOBSIZE> keyword.

Meta-Data Queries

The meta-data statement methods $columns(), $indexes() and $tables() allow you to receive information about the objects in your database. The $tables() method takes an optional owner name as a parameter. The $indexes() and $columns() methods optionally take the database and/or owner name with the table name parameter. See SQL Programming for more information on these methods.

When a database, owner or table name is specified, the result set is constrained by those schemas which meet the filter criterion, i.e. to return column information about the “addressbk” table owned by “robert” in database “acc_db” the following can be issued.

Do tStatement.$columns('acc_db.robert.addressbk') Returns #F

DB2 Troubleshooting

Reserved Words

This section covers the DB2 specific reserved words.

The following schema names are reserved: SYSCAT, SYSFUN, SYSIBM & SYSSTAT.

In addition, it is strongly recommended that schema names never begin with the SYS prefix, as SYS is by convention used to indicate an area reserved by the system.

There are no words that are specifically reserved words in DB2. Keywords can be used as ordinary identifiers, except in a context where they could also be interpreted as SQL keywords. In such cases, the word must be specified as a delimited identifier. For example, COUNT cannot be used as a column name in a SELECT statement unless it is delimited.

IBM SQL and ISO/ANSI SQL92 include reserved words, these reserved words are not enforced by DB2 Universal Database, however it is recommended that they not be used as ordinary identifiers, since this reduces portability. Please see the final chapter in this manual which lists the SQL reserved words.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at: https://www.omnis.net/developers/resources/technotes/

DB2 Data Type Mapping

The following table describes the data type mapping for Omnis to DB2 connections. This mapping is predefined and is based on the best fit for each of the Omnis data types.

Omnis to DB2 UDB

DB2 UDB to Omnis

MySQL

This section contains the additional information you need to access a MySQL database, including server-specific programming, data type mapping to and from MySQL, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

Properties and Methods

Session Properties

Session Methods

Statement Methods

Logging on to MySQL

The MySQL DAM interfaces directly with the MySQL client library, therefore the way the DAM logs on to the server is slightly different to the other Object DAMs.

Specifically, the $logon() hostname parameter is taken as the server hostname or IP address. The username and password parameters are supplied as normal.

As well as the parameters supplied to $logon(), there are some additional parameters which you can set using the following session properties:

• $port - The port number of the MySQL server

• $database - The database name

• $clientflags - Sets additional behavior for the logon

• $socket - Specified if you do not want to use a TCP/IP connection

When logging on using the SQL Browser, default values are used when the Port and Database fields are left blank.

Transactions

If you require transaction support with MySQL, your server needs to support BDB or InnoDB table types. Manual transactions may only be made on tables of these types.

When creating tables, you need to specify the table type required if you do not want the default type (MyISAM).

The following properties and methods apply to transactions. Use of these properties or methods is equivalent to executing the SQL statements shown:

If you are not using InnoDB or BDB table types, you can achieve table locking using the MySQL lock tables or unlock tables SQL commands. Refer to the MySQL language reference for further details.

Using LOAD DATA

The MySQL DAM supports use of the LOAD DATA INFILE SQL syntax via the sessionobject.$query() method. You need to set the kMySqlOptLocalInifile connect option to enable local files to be loaded. The default command syntax will load the contents of a tab-delimited text file into the specified table, for example:

Do cSess.$connectoption(kMySqlOptLocalInifile,1) Returns #F
Do cSess.$logon('192.168.00.100','myUser','myPass','session1') Returns #F
Do cSess.$newstatement() Returns cStat
Do cStat.$execdirect('create table loadtest(col1 int, col2 varchar(32), col3 dat, col4 numeric(9,2))') Returns #F

# Now load the data into the table.. 

Do cSess.$query("load data local infile 'C:/Users/myUser/Desktop/data.txt' into table loadtest") Returns #F

Example text file contents (tab-separated values):

1   One    2020-08-26   1.23
2   Two    2020-07-31   2.45
3   Three  2019-06-06   33.75
4   Four   2018-01-30   127.0
5   Five   1999-10-02   32.333

Please refer to the MySQL documentation for further details on the LOAD DATA statement.

MySQL Data Type Mapping

Omnis to MySQL

The default data type mappings from Omnis to MySQL are shown below.

Note that this is equivalent to the list returned by a call to $getdatatypemapping() on a newly created session object:

Assigning a new mapping table using $setdatatypemapping()

If you need to make a change to the default Omnis to MySQL data type mappings, you should probably base your new mappings on the default mappings obtainable by calling $getdatatypemapping() and add/remove lines to the returned list as required before calling $setdatatypemapping() to install the new mapping table.

There are a number of points to note regarding the format and processing of the list used by these methods and these are discussed below.

Omnis subtype precedence

Note that where multiple occurrences of Omnis types appear in the list, the Omnis subtype(s) should be specified in ascending numerical order, especially if you intend a mapping to apply to all Omnis subtypes <= the supplied value. This is because when searching for a match, the list is processed in order from the first entry to last- the search ends at the first matching entry. The full list of acceptable data subtypes can be found in the Omnis catalog (F9) and their text equivalents are shown in the OmnisSubtype column, as summarised below:

Note that where an Omnis type does not have a subtype (e.g. Binary), it is acceptable to leave the subtype column blank.

Parameter column

The ‘Parameter’ value in the data type mapping list specifies the maximum length or size of data to which the mapping will apply, e.g. a value of 255 specified for a character data type signifies that that mapping will apply to Omnis character data with a length of <= 255 characters. You should therefore ensure that where there are multiple occurrences of the same Omnis data type and subtype, these are entered in ascending order of parameter value. The maximum size of an Omnis character/binary field is 10,000,000 bytes.

MySqlType column

The MySqlType value specifies the string which will be returned by session.$createnames() when that row matches the supplied Omnis data type & subtype. This can be (but is not restricted to) any string which constitutes a valid MySQL column type, e.g.

SET(‘One’,‘Two’,‘Three’,‘Four’)

Where a ‘$’ character is used as part of the MySQL type, the appropriate length or scale attribute will be substituted when $createnames() is called.

For valid MySQL data type assignments, the DAM also uses the data type mapping table to map outgoing bind variables to their corresponding MySQL column types.

Example applications of $setdatatypemapping()

The intended use of $setdatatypemapping() is to allow schema columns to conditionally map to custom MySQL data types, not implemented by default, e.g. the SET, ENUM, GEOMETRY and YEAR types and also to allow extra type qualifiers to be added, such as UNSIGNED, PRIMARY KEY, AUTO_INCREMENT, etc.

To get $createnames() to return one of these types, you might for example isolate a specific character string length and add a data type mapping for your new type.

Then whenever $createnames() encounters a string of that specific length, the mapping to your new type will occur. If you want to implement the SET data type, you could insert a new mapping entry between the first and second default entries, adjusting the length parameters as shown below:

Alternatively, you could dedicate the ‘national’ character subtype for your custom data types, leaving the ‘simple’ character subtype for standard character mappings.

MySQL to Omnis

The following mappings are hard-coded and cannot be altered.

MySQL Troubleshooting

The following points may help in resolving programming issues encountered using MySQL session and statement objects. For additional updated trouble shooting issues, refer to the readme file which accompanies your Omnis download

• $sqlstripspaces has no effect for MySQL sessions. MySQL automatically strips trailing spaces from data inserted into CHAR and VARCHAR columns. Character data returned from the server will already be stripped of trailing spaces.

• MySQL 4.1 does not support chunking of fetched (output) LOB data (TEXT and BLOB types). Chunking of input LOB data is supported.

• $rowcount will be –1 following execution of a SELECT, SHOW or EXPLAIN statement. This is because MySQL cannot determine this value until the final row has been fetched.

• The MySQL DAM is compatible with MySQL Server version 4.1 and later. Prior to Omnis Studio version 4.1, connection is only possible to a commercial version of MySQL Server (i.e. MySQL “pro” or “classic”). Later versions of Studio do not have this restriction.

• Authentication plug-in “caching_sha2_password” cannot be loaded. This error occurs attempting to connect to MySQL server 8.0 and later using an imcompatible client library. In Studio 10.2 and later, refer to technote TNSQ0039 for details on applying an external MySQL client library.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at: https://www.omnis.net/developers/resources/technotes/

ODBC

This section contains the additional information you need to access a database using ODBC middleware, including server-specific programming, data type mapping, as well as troubleshooting. For general information about logging on and managing your database using the Omnis SQL Browser, refer to the earlier parts of this manual.

Properties and Methods

In addition to the “base” properties and methods documented in the SQL Programming chapter, the ODBC DAM provides the following additional features.

Session Properties

A number of additional session properties have been added to the ODBC DAM in Studio 4.3.1 to facilitate better understanding and control of cursors and transactions. Use of these properties assumes that the session is logged-on and has been placed in manual transaction mode (kSessionTranManual):

Session Methods

Connecting to your Database

Do SessObj.$logon('MyDataSource', , , 'MySession') Returns #F

When the session property $usefiledsn is set to kTrue, this specifies that the hostname parameter is to be treated as a file data source name by the driver manager.

When the session property $uselogonprompt is set to kODBCPromptComplete, this specifies that the driver will prompt for missing logon information. Note that not all drivers support prompting and this may result in the logon failing.

Making a DSN-less Connection

To make a connection without using an ODBC DSN, all of the information necessary to make a connection needs to be passed as an ODBC connection string and the session $uselogonprompt property needs to set to kODBCPromptDsnLess.

The connection string is passed to the $logon() method via the hostname parameter. The username and password parameters are left blank.

ODBC connection strings consist of keyword-value pairs separated by semi-colons and are database specific. Examples of such strings include:

For SQL Server:

Driver=SQL Server; Server=192.168.0.10; Database=accounts; Uid=fred; Pwd=secret

For the Omnis ODBC Driver:

Driver=Omnis ODBC Driver; DataFilePath=c:\TRAVEL.DF1; Username=myuser; Password=mypassword

For specific details on connection strings, please refer to the documentation supplied with the RDBMS or with the ODBC driver.

Connecting using the ODBC Databridge

For macOS and Linux in particular, or to make an ODBC connection in the absence of a platform-specific ODBC driver or driver manager, you can “bridge” an ODBC connection across to a Windows PC which hosts the required ODBC driver necessary to complete the connection.

This process is described more fully in the ODBC Databridge documentation.
However, to make connection using the ODBC Databridge, you should use a URL of the form:

Do SessObj.$logon('odbc://192.168.0.22:8063/dsnName','user1','pwd') Returns #F

The above example assumes that the ODBC Databrige is running on the specified IP address using the default port, and that there is a User or System DSN named ‘dsnName’ defined on that machine.

Transactions

Generally, using manual transaction mode results in increased performance because the session object does not force a commit after each statement.

If you do not have a result set pending, ODBC session objects will commit each statement if the transaction mode is automatic. If the transaction mode is server, the session may be committed depending on the behavior of the ODBC driver.

Dates

The session property $defaultdate allows default values to be added to date values mapped to the server where the Omnis date value does not contain complete information, e.g. a Short time mapped to a server date time.

Multiple cursors

To allow multiple select cursors when connecting to Microsoft SQLServer the statement issuing the SELECT must have the $usecursor property set to kTrue before the statement is executed. If a statement is issued when $usecursor is kFalse and this statement returns a result set, this will prevent all other statements in the same session from returning data. The blocking results must be completely processed or cleared before another result set can be generated. If a commit or rollback is performed on the session, all the session’s statement cursors will be closed and all pending results will be lost. Note that a SQLServer cursor only allows SQL SELECT and EXECUTE procedure commands to be issued.

SQL Server 2000 Data Types

The following new types were introduced with Microsoft SQLServer2000:

SQL_BIGINT

Values fetched into Omnis from BIGINT columns are converted into the Character 20 type. This is necessary since BIGINTs are stored in 64 bits, giving them a range of ±2⁶³ or

-9223372036854775808 to 9223372036854775807. The largest numeric value which can be stored using the Integer 32 bit type is ±2³¹ or –2147483648 to 2147483647.

Omnis Character variables can be input into BIGINT columns provided that the character length (precision) does not exceed 19.

Note: Hash variables such as #S1 cannot be bound as input variables for BIGINT columns since their length is preset to 10,000,000.

SQL_VARIANT

Values fetched from SQL_VARIANT columns are converted into the Binary type so they can be preserved in their raw format.

Since the data type, precision and scale are not known prior to fetching, it may be necessary to pre-process the table before retrieving the variant data. This is done using the SQL_VARIANT_PROPERTY() function to build a list of variant types contained in a specified column.

Do myStatement.$execdirect('select cast(SQL_VARIANT_PROPERTY(col3,'BaseType') as char(32)) from mytable') returns #F

When fetching the data into Omnis, the CAST() function can then be used inside the SELECT statement to ensure that the incoming data gets converted to the proper Omnis types.

Do myStatement.$execdirect('select CAST(col3 as smalldatetime) from mytable') returns #F

For this reason, tables containing SQL_VARIANTs are probably best used in tandem with an index column, which is used to associate a cast type with each row. Since there is no variant type in Omnis, there is no systematic way of reading a whole column of variants.

Note: text, ntext, image & timestamp types are not supported by SQL_VARIANT.

Custom Data Types

When custom data types are fetched from SQL Server their base type is abstracted from the custom type and returned to Omnis. For example, if a custom data type is created using;

Do myStatement.$execdirect('EXEC sp_addtype birthday, dat, 'NOT NULL') returns #F

Do myStatement.$execdirect('create table test(col1 int,col2 birthday)') returns #F

Then, a $columns() performed on the table, describes col2 as DATETIME

If a custom data type is specified when creating or altering a table, this is passed straight through the DAM.

TABLE

The TABLE data type can be used in two ways.

The first is as a local variable in a user SQL function. Local variables are defined as type table and can be used to temporarily store the result of a query. This usage is beyond the scope of Omnis however.

The second is as the return value from a user-defined function. For example, the following function defines a table as it’s return value. (The table must be defined in the RETURNS section.):

CREATE FUNCTION Function1 ( @Param integer )
RETURNS @myTable TABLE
  (
    col1 integer, col2 char(32)
  )
AS
BEGIN
  INSERT @myTable
    SELECT * FROM valuetest WHERE col1 > @Param
  RETURN
END

Omnis can then call the function to obtain the table results, e.g.:

Do mylist.$define(col1,col2)
Do myStatement.$execdirect('select * from Function1(50)') returns #F

Do myStatement.$fetch(myList,kFetchAll)
ODBC Administration

ODBC Administration

The $getdrivers(), $getdatasources(), $getinfo(), $setinfo(), $configdsn(), $getoption() and $setoption() session methods (documented above) allow the ODBC DAM to be used to add, modify and remove ODBC Data Source Names (DSNs) as well as to retrieve and modify information about ODBC drivers and general ODBC administration attributes.

The $uselogonprompt property has been modified to allow driver prompting to be forced if required.

All of these methods return a boolean value to indicate successful operation. Errors generated by these methods are returned as normal via the session object’s $nativeerrorcode and $nativeerrortext properties.

Configuration of File DSNs

Using $getoption() to retrieve the default directory for file DSNs allows the FileOps component to be used together with other 4GL techniques in configuring File datasources.

To create a File DSN, you should prompt for the new filename (the DSN Name) and use FileOps to create the new file. One or more KeyWord-value pairs should also be written to the file, e.g. DRIVER=drivername- the minimum requirement for a File DSN. To complete the setup of the new DSN, you should follow the procedure for modifying/testing the File DSN.

To modify or test a File DSN, use the following procedure:

• Set $usefiledsn to kTrue

• Set $savefile to kTrue

• Set $uselogonprompt to kODBCPromptAlways

• Execute $logon() with the name of the File DSN as the hostname.

Under Win32, this prompts the ODBC Administrator library to display the driver specific logon dialogue which prompts for information necessary to make the connection. If the DAM is successful in logging on (and $savefile is set to kTrue), the Administrator library writes the additional information back to the File DSN, hence modifying the datasource.

To remove a File DSN, you should use the FileOps component to manually delete the specified filename.

macOS and Linux Considerations

Under Unix, the DAM locates user DSN information (odbc.ini) using the value of the ODBCINI environment variable if is set. If ODBCINI is not set, the DAM attempts to use the ".odbc.ini" (hidden file) in your user’s home directory or failing that, it defaults to "/Library/ODBC/odbc.ini".

The DAM locates system DSN information using the value of the ODBCSYSINI environment variable if it is set. If ODBCSYSINI is not set, the DAM attempts to locate the system driver information using the value of ODBCINSTINI instead. If ODBCSYSINI is set, this location is also assumed for the location of the driver information file (odbcinst.ini). Note that if set, ODBCSYSINI should identify a folder only- not a file name.

The ODBC Driver manager used on your system must support the necessary API calls in order to perform certain administration functions (editing and modifying DSN information, for example). If the required API calls are missing, these functions will not be available.

ODBC Troubleshooting

The following points may help in resolving issues in programming applications that use ODBC session objects.

• ODBC does not support any extended ORACLE cursor operations such as positioned update and delete.

• You must specify literals in SQL statements with single quotes ('), not double quotes (").

• Some data sources may strip trailing spaces prior to sending it to the session object. SQL Server behaves in this way.

Further troubleshooting notes, “how-tos” and tips can be found on the Omnis website at: https://www.omnis.net/developers/resources/technotes/

ODBC Data Type Mapping

The following table describes the data type mapping for Omnis to ODBC connections.

The Omnis to ODBC mapping will attempt to pick the best match based on the types the driver supports in the order listed. For example, if the driver supports SQL_VARCHAR and SQL_CHAR data up to a maximum column size of 255, but SQL_LONGVARCHAR data up to 2 GB, an Omnis Character(1000) will map to whatever the associated server native type is for SQL_LONGVARCHAR, e.g. TEXT.

Omnis to ODBC

_{^[1] Refer to the $nationaltowchar property for use with the Unicode version of Omnis Studio
^[2] As of Studio 8.1.5, $usescale can be used to calculate p as 15 + s. E.g. In this mode a Number 14dp will map to NUMERIC(29,14) giving 15 scalar digits plus 14 mantissa digits.}

ODBC to Omnis

Amazon SimpleDB

The Amazon DAM (DAMAZON) allows you to access the SimpleDB from Amazon Web Services LLC. According to Amazon, “SimpleDB is a highly available, scalable, and flexible non-relational data store that offloads the work of database administration. Developers simply store and query data items via web services requests, and Amazon SimpleDB does the rest.” For further information about Amazon SimpleDB, please refer to the Amazon SimpleDB website:

• General information
  http://aws.amazon.com/simpledb

• Developers Guide
  http://docs.amazonwebservices.com/AmazonSimpleDB/latest/DeveloperGuide/

This section also discusses various topics which differentiate cloud-based connectivity from traditional RDBMSs and the impact this has on the various properties and methods.

Dependencies

The Amazon DAM has runtime dependencies on several other dynamic libraries which must be present on your system’s library search path before the DAM can be used. When a DAMAZON session object is created, the DAM attempts to locate and resolve the symbols it needs from each of the external libraries.

If one or more symbol references cannot be resolved, these are reported to the Omnis trace log as warnings, $logon() is disabled and you should not attempt to call session or statement methods, otherwise a crash may occur.

The additional files required by the Amazon DAM for each platform are as follows:

Windows

libcurl.dll (requires msvcr90.dll)

libeay32.dll (requires msvcrt.dll)

libxml2.dll (requires iconv.dll & zlib1.dll)

macOS

libcurl.dylib (where libcurl.dylib -> /usr/lib/libcurl.4.dylib, for example)

libcrypto.dylib (where libcrypto.dylib -> /usr/lib/libcrypto.0.9.7.dylib, for example)

libxml2.dylib (where libxml2.dylib -> /usr/lib/libxml2.2.dylib, for example)

Linux

libcurl.so (/usr/lib/libcurl.so)

libcrypto.so (/usr/lib/libcrypto.so)

libxml2.so (/usr/lib/libxml2.so)

If these libraries are not present on your system, the appropriate package(s) may need to be installed or alternatively, downloaded and compiled from source. The principal libraries shown are all available under open source licence agreements.

For developers interested in downloading and compiling client libraries from source, information about each of the projects can be obtained from:

libcurl: http://curl.haxx.se/

libxml2: http://xmlsoft.org/

libcrypto/libeay32 : http://www.openssl.org/ (Links accurate at time of publishing)

Binary releases of these libraries may also be available to download from these and other sources.

Logging on to SimpleDB

To connect to SimpleDB, the endpoint required is supplied via the $logon() hostname parameter. In the case of Amazon SimpleDB, the endpoint is “sdb.amazonaws.com” or “sdb.eu-west-1.amazonaws.com” in Europe.

Your access key id and secret are supplied via the username and password parameters, for example:

Do SessObj.$logon('sdb.amazonaws.com',' AGIBJ5LOYFITD3BR7',' H/z6t3ARzuJL26uIE07 GTS1AkK+p5') Returns #F

For other databases, the endpoint may be specified using http syntax, for example:

Do SessObj.$logon('http://www.remoteserver.com/?','user_1','password') Returns #F

If the hostname parameter is omitted, i.e. substituted with a comma, the DAM uses sdb.amazonaws.com by default.

Meta Data

SimpleDB does not provide information about tables, columns and indexes in the same way as traditional relational databases. Instead, domains can be likened to tables, items can be likened to rows and attributes can be likened to columns. This has an impact on the behavior of the following meta-data methods:

The information returned by $columns() for a domain is summarised as follows:

SimpleDB Attributes and Multi-Values

Unlike Relational databases, SimpleDB attributes support multiple values. For example:

In addition, SimpleDB effectively supports only a single data type: Character. All data inserted into and retrieved from SimpleDB will be character data optionally encoded as UTF-8 bytes. Once fetched into Omnis, data can be assigned to typed variables as required. Such data will be automatically converted to the appropriate data type where possible.

Each item fetched from SimpleDB can potentially have a different number of attributes and attribute names. This prevents the use of Omnis Schema classes with SimpleDB since these require rigid column names and types. When dragging a schema class onto a SimpleDB session in the Omnis SQL Browser, all that can sensibly be achieved is to create a domain with the supplied table name.

SimpleDB does not support SQL in the traditional sense. You cannot use $prepare() & $execute() or $execdirect() to execute INSERT, UPDATE or DELETE statements as these are not supported. Instead, these statement methods can be used only to execute SELECT statements conforming to the SimpleDB SELECT syntax.

Creating a Domain

To manually create a domain (analogous to a table), use the StatObj.$createdomain() method. For example:

Do StatObj.$createdomain('Project810') Returns #F

Inserting Data

To insert items and attributes into SimpleDB, use the StatObj.$putattrib() method.

Each call to $putattrib() inserts a new attribute-value pair into the specified domain item. (There is no need to create the item before inserting an attribute, the item is created implicitly). Since SimpleDB supports multiple attribute values, you can assign several different values to the same attribute if required. Duplicate values are ignored. For example:

Do StatObj.$putattrib('Project810','Materials','Tools','13mm Wrench') Returns #F
Do StatObj.$putattrib('Project810','Materials','Tools','Quick release clamps') Returns #F

If many attributes are to be inserted, it may be preferable to assign the domain name to the StatObj.$domain property and the item name to the StatObj.$item property. These parameters can subsequently be omitted in calls to $putattrib()- and any of the other statement methods discussed below. The above example becomes:

Do StatObj.$domain.$assign('Project810')
Do StatObj.$item.$assign('Materials')
Do StatObj.$putattrib(, ,'Tools','13mm Wrench') Returns #F
Do StatObj.$putattrib(, ,'Tools','Quick release clamps') Returns #F

Deleting Data

To delete items, attributes and values from SimpleDB, use the StatObj.$delete() method.

Deleting Values

To delete a specific attribute value, the domain, item, attribute name and value should be specified. For Example:

Do StatObj.$delete('Project810','Materials','Timber','50x50x2.4m pse') Returns #F

Deleting Attributes

To delete an attribute including all of its values, the domain, item and attribute name only should be specified. For example:

Do StatObj.$delete('Project810','Materials','Timber') Returns #F

Deleting Items

To delete an entire item including all its attributes and values, the domain and item name only should be specified. For example:

Do StatObj.$delete('Project810','Materials') Returns #F

Deleting a Domain

StatObj.$delete() cannot be used to delete a domain. To do this- use StatObj.$deletedomain(). This method should be used with caution as it will permanently delete all items, attributes and values contained in the domain before removing the domain itself. For example:

Do StatObj.$deletedomain('Project810') Returns #F

Replacing Data

Whereas $putattrib() is used to append new attributes and values, StatObj.$replaceattrib() is used to replace all values for a specified attribute with the supplied single value. For example:

Do StatObj.$replaceattrib('Suits','Gents Formal Suit','Colour','Navy only') Returns #F

Fetching Data

The Amazon DAM uses Amazon SELECT statements to fetch multiple items. These are issued using the statement object’s $prepare(), $execute() and $execdirect() methods in a similar way to traditional SQL SELECT statements. The general form of a SimpleDB SELECT statement is as follows:

select output_list from domain_name [where expression] [sort_instructions][limit limit]

The output_list can be:

• * (all attributes)

• itemName() (the item names only)

• count(*)

• An explicit list of attributes (attribute1,..., attributeN)

For further information on the SELECT statement syntax, please refer to Amazon SimpleDB Developer Guide.

Items in the result set are returned one row-at-a-time. StatObj.$resultspending indicates whether there is a further item each time a call to StatObj.$fetch() is made and StatObj.$itemcount is initially set to the number of items in the response. The destination list or row variable is automatically redefined each time $fetch() is called. For example:

Do StatObj.$execdirect('select * from Suits where stocklevel > 1') Returns #F
Repeat 
  Do StatObj.$fetch(lvRow)
  …
Until  StatObj.$resultspending = kFalse

Retrieving an Item

You can retrieve all attributes for a specific item using the StatObj.$getall() method. The result set (a single row) generated by this call is returned using $fetch(). For example:

Do StatObj.$getall('Suits','Gents Suits') Returns #F
Do StatObj.$fetch(lvRow)

Retrieving Item Names

You can retrieve the names of items contained within a domain by calling the StatObj.$getitems() method. The result is returned as a single item containing a single attribute. The item names will be returned either as a comma-separated list or as a single column list- as dictated by the $attribcsv property.
A SELECT where-clause may be optionally specified if required, in which case only the names of items which satisfy the expression will be returned. For example:

Do StatObj.$getitems( ,"where Colour like 'Red%'") Returns #F
Do StatObj.$fetch(lvItems) 

Retrieving an Attribute

You can retrieve the contents of a specific attribute using the StatObj.$getattrib() method. The result set (a single row containing a single column) generated by this call is also returned using $fetch(). For example:

Do StatObj.$getattrib('Project810','Materials','Tools') Returns #F
Do StatObj.$fetch(lvRow)

Handling Multiple Values

When fetching data, each row returned to Omnis represents one item from the specified domain. Item attributes containing multiple values are handled in one of two ways; either as single-column lists or as comma-separated values as dictated by the StatObj.$attribcsv property.

When $attribcsv is set to kTrue (the default), rows fetched from SimpleDB will be defined with Character columns. Attributes (columns) with multiple values will be returned as a string of comma-separated values.

When $attribcsv is set to kFalse, rows fetched from SimpleDB will contain single-column lists in each column. Each single-column list will contain one row for each attribute value.

Handling Multiple Attributes

You can put, delete and replace several attribute values at once using the StatObj.$putmany(), StatObj.$deletemany() and StatObj.$replacemany() methods. The attribute-value pairs to be processed are supplied via a list variable defined with two character columns. Column 1 contains the attribute names, column 2 contains the corresponding values. For example:

Do myList.$define(lvChar1, lvChar2)
Do myList.$add('Tools','Posidrive screwdriver')
Do myList.$add('Tools','Metal hammer')
Do myList.$add('Charges','1½ hours labour')
Do StatObj.$putmany( , , myList) Returns #F

You can retrieve the values of multiple attributes using the StatObj.$getmany() method. The attribute names to be retrieved are supplied via a single-column list, for example:

Do myList.$define(lvChar1)
Do myList.$add('Tools')
Do myList.$add('Materials)
Do myList.$add('Charges')
Do StatObj.$getmany( , , myList) Returns #F

Each subsequent call to $fetch() returns a row containing separate attribute- either as a comma-separated-value or as a single column list, as dictated by $attribcsv.

Handling Multiple Items

When executing queries, the StatObj.$itemcount property is set to the number of items in the response- implying that each call to $fetch() retrieves one item.

When the response contains only attribute values, $itemcount will be set to zero.

Handling Multiple Requests

The SimpleDB DAM uses the transaction management features of the DAM interface to allow multiple requests to be executed as a combined batch of requests. To enable multiple-execution, the SessObj.$transactionmode property should set to kSessionTranManual.

In this mode, actions such as $createdomain(), $putattrib(), $getattrib(), $replacemany() and $execdirect() are accepted unconditionally into a queue. Nothing is sent to or received from the database until a SessObj.$commit() is executed, at which point each request is submitted in turn.

Unlike single request execution, every multiple request generates a response. Although actions to put, create, replace and delete attributes will return empty responses, this enables any errors and execution information associated with each action to be returned. For example:

Do cSess.$begin()
Do cStat.$putattrib(,,'Materials','White Paint') Returns #F
Do cStat.$putattrib(,,'Materials','Cement 25Kg') Returns #F
Do cStat.$replacemany(,,lvAttribList) Returns #F
Do cStat.$execdirect('select * from Project810') Returns #F
Do cSess.$commit() Returns #F

Handling Multiple Responses

When in manual transaction mode, each call to $commit() generates one or more responses. The number of responses available is returned via the SessObj.$responses property.

When $commit() is executed, StatObj.$itemcount and StatObj.$columncount are set to reflect the number of items and attributes in the initial response.

Items/attributes from the response are then retrieved using one or more calls to $fetch(). When all items/attributes from the current response have been retrieved, the StatObj.$endofresponse property is set to kTrue at which point, $itemcount and $columncount are also set to reflect the next response.

When fetching an empty response, note that $endofresponse will effectively remain set to kTrue. If the corresponding action generated an error, then StatObj.$nativeerrorcode and StatObj.$nativeerrortext will be set accordingly. Otherwise, the empty response (and empty row) can be discarded.

When all responses have been retrieved, the $resultspending property is set to kFalse, otherwise $resultspending remains set the kTrue while there are still responses waiting.

It is safe to abandon and/or replace multiple requests before executing them by simply calling SessObj.$begin() or changing the transaction mode back to kSessionTranAutomatic. You can also discard pending responses in this way.

$rollback() is not supported by the SimpleDB DAM- this has no effect.

Machine Utilization

Amazon SimpleDB measures usage of remote resources (and hence the charge it imposes on the end-user) in terms of “box usage”. Each action sent to the database incurs a box usage- quoted as a decimal fraction of one hour. StatObj.$boxusage returns the box usage for each action which generates a response.

The session object also has a $boxusage property which accumulates a total box usage for the open connection. When retrieving multiple responses, the box usage for each response is received (and added) in turn.

$boxusage may not be supported by all Simple databases in which case, the value remains set to zero.

Read Consistency

Amazon SimpleDB supports two types of read consistency, defined as follows:

• Eventually Consistent Reads
  the eventual consistency option maximizes your read performance (in terms of low latency and high throughput). However, an eventually consistent read (using Select or GetAttributes) might not reflect the results of a recently completed write (using PutAttributes, BatchPutAttributes, DeleteAttributes). Consistency across all copies of data is usually reached within a second; repeating a read after a short time should return the updated data.

• Consistent Reads
  in addition to eventual consistency, Amazon SimpleDB also gives you the flexibility and control to request a consistent read if your application, or an element of your application, requires it. A consistent read (using Select or GetAttributes with ConsistentRead=true) returns a result that reflects all writes that received a successful response prior to the read.

The Amazon DAM implements this functionality using the $consistentread session property. When set to kFalse (the default setting), the eventual consistency option is used. When set to kTrue, all $getattrib() and SELECT statement results are fetched using consistent reads.

Conditional Puts and Deletes

The PutAttributes and DeleteAttributes API calls used by Amazon SimpleDB support conditional put and delete operations which enable you to insert, replace or delete values for one or more attributes of an item if the existing value of an attribute matches the value you specify. If the value does not match or is not present, the update is rejected. Conditional Puts/Deletes are useful for preventing lost updates when different sources write concurrently to the same item.

The Amazon DAM implements this functionality using the $whereclause statement property. This property affects all put, replace and delete attribute calls and accepts a SQL-style where clause of the form:

“where <name> [= <value>] [exists|does not exist]”

<name> and <value> can be literal values; in which case they must be double-quoted, or bind variables. Double quotes inside literal values should be escaped using \”. For example:

Do cStat.$whereclause.$assign('where "Color" = "Light Brown"')
Do cStat.$whereclause.$assign('where "Undo" does not exist')
Do cStat.$whereclause.$assign('where "Project \"X\"" = @[lvChar]')

Once bound, variable values should be assigned before each call to $putattrib(), $delete(), etc:

Do cStat.$whereclause.$assign('where "Name" = @[lvChar]')
Calculate lvChar as "Brookes"
Do cStat.$putattrib('StockDB','Supplier1','Frequency','Daily') Returns #F
Calculate lvChar as "Robinson"
Do cStat.$delete('StockDB','Supplier2','Frequency') Returns #F

Currently, the exists condition may only be specified if both <name> and <value> attributes are also specified. To use does not exist, only the <name> attribute should be specified.

Subsequent calls to put, replace or delete attributes return kFalse if the condition is not met.

$whereclause is not affected by $clear(). To remove the where condition for a statement object; assign $whereclause to an empty string.

Session Properties

Session Methods

Statement Properties

Statement Methods

Implementation Notes

Bind Variables

Queries issued using $execute() and $execdirect() may contain bind variables- for example in the where clause of the SELECT statement. The DAM inlines variable values into the SQL text each time $execute() is called, placing single quotes around each value. Values containing single quotes are escaped by adding a second single quote for each occurrence. For example:

Calculate lVar as "Katharine O'Hara"
Do StatObj.$execdirect("select * from Customers where Name = @[lVar]") Returns #F

becomes

select * from Customers where Name = ‘Katharine O’’Hara’

Multiple Statement Objects

The SimpleDB API does not facilitate statement isolation- only session isolation. This means that each session object may spawn only one statement object.

An attempt to spawn a second statement object will result in an error.

Remote Procedures

SimpleDB does not support remote procedures, views or triggers. These are features of traditional relational databases.

Binary Data

SimpleDB attributes support only character data of maximum length 1024 bytes and are not suitable for storing binary data directly. A better approach (the intended approach) is that attribute values be used to store URLs or unique identifiers for pictures, files and other media which exist externally to the database.

OmnisSQL DAM

The OmnisSQL DAM provides an object-oriented interface to the Omnis data manager. As such the OmnisSQL DAM is a wrapper around the single-threaded Omnis DML engine.

Note the OmnisSQL DAM is only provided for backwards compatibility with legacy apps only; Omnis Datafiles and the OmnisSQL DAM should not be used for new apps.

Server-specific Programming

Logging on to an Omnis Data File

To connect to a data file using the OmnisSQL DAM, create an object variable of subtype “OMSQLSESS”.

You connect to a data file using the $logon() method. The hostname parameter should be the full path to the data file.

OmnisSQL does not require a username or password, but you can specify a session name that will appear in the SQL Browser and in the Notation Inspector under $sessions.

OmnisSQL expects a DOS-style pathname under Windows and an absolute POSIX-style path under macOS and Linux. For example:

Do mySession.$logon('C:\mydata\mydatafile.df1','','','session1') Returns #F ## on Windows
Do mySession.$logon('/Users/MyUser/mydatafile.df1','','','session1') Returns #F ## on macOS / Linux

Using the Omnis Databridge (ODB)

To logon to a data file being hosted by the Omnis Databridge, the hostname parameter should consist of an ODB URL of the form odb://, for example:

Do mySession.$logon('odb://192.168.0.150:5913:osxlocking','','','session1') Returns #F

Although analogous to the Open data file command, note that there is no internal-name parameter when using $logon(). Instead, use the session name (parameter 4).

Omnis SQL Language Definition

Note: The following sections contain legacy information and have been reproduced from the deprecated Omnis_SQL_v2api.pdf document.

The following sections show the grammar of Omnis SQL using BNF (Backus-Naur Form) diagrams, using the conventions from the ANSI standard. Each statement includes a note specifying what parts, if any, of the statement depart from the ANSI 1989 standard for SQL.

SQL Statement

SQL_statement ::=
     create_table_statement
   | create_index_statement
   | delete_statement_searched
   | drop_index_statement
   | drop_table_statement
   | insert_statement
   | select_statement
   | update_statement_searched
   | update_statement_positioned
   | alter_table_statement

The SQL statement is the text that goes in the DAM’s $prepare() or $execdirect() methods or in a statetement block starting with Begin statement. The rest of the grammar depends on this main element.

ANSI SQL has the following statements that Omnis does not implement. Most statements involve cursors, and Omnis implements these as commands rather than as SQL statements.

• close_statement
  closes a cursor (see the Close cursor, Quit cursor, and Reset cursors commands).

• commit_statement
  commits a transaction (see the Commit current session command).

• declare_cursor
  declares a cursor (see the Declare cursor command).

• delete_statement_positioned
  deletes a row based on current cursor position.

• fetch_statement
  fetches a row using the current cursor (see the Fetch commands).

• open_statement
  opens a cursor (see the Open cursor command)

• create_schema_statement
  creates a schema containing tables and views; Omnis SQL does not support schemas.

• create_view_statement
  creates a view; Omnis SQL does not support views.

• grant_privilege
  grants an access privilege on an object to a user; Omnis SQL does not implement any SQL security.

CREATE TABLE

create_table_statement ::=
   CREATE TABLE table ( table_element_comma_list )
   CONNECTIONS ( table_comma_list )

The CONNECTIONS clause is an Omnis extension to the ANSI standard that lets you specify a list of file classes to which to connect a file class. Connections are parent-child relationships between file classes.

table_element ::=
   column_definition | UNIQUE ( column_comma_list )

You can define a file class using the SQL CREATE TABLE statement. The fields in the format come from the list of column definitions. You can also specify that the values for a group of columns are unique, taken together, with the UNIQUE constraint. You can have more than one UNIQUE constraint. All the columns in a UNIQUE constraint must be defined with the NOT NULL qualifier (see below).

The ANSI standard contains several other table constraints, namely PRIMARY KEY, FOREIGN KEY and CHECK that Omnis SQL does not implement.

column_definition ::=
   column_data [ [ NOT ] NULL ]

The NOT NULL constraint specifies that when you insert a row, the value for this column must not be NULL.

The ANSI standard specifies a default clause that lets you define a default value for the column. It also lets you specify that the column is UNIQUE, REFERENCES a primary key in another table, or satisfies a CHECK constraint. Omnis SQL does not implement any of these features.

column_data ::=
   column_name data_type
data_type ::=
   [ LONG ] VARBINARY
   | BIT
   | VARCHAR ( NUMBER )
   | CHAR ( NUMBER )
   | NATIONAL CHAR[ACTER] VARYING (NUMBER)
   | NCHAR VARYING ( NUMBER )
   | SEQUENCE_TYPE
   | DATE [ ( { 1900..1999 | 1980..2079| 2000..2099 } ) ]
   | TIME
   | TIMESTAMP
   | TINYINT
   | SMALLINT
   | INTEGER
   | NUMERIC ( number, integer)
   | DEC[IMAL] ( number, integer)
   | FLOAT_TYPE [ ( integer ) ]
   | REAL
   | LIST
   | PICTURE

ANSI data types include CHARACTER, NUMERIC, DECIMAL, INTEGER, INT, SMALLINT, FLOAT, REAL, and DOUBLE PRECISION. Omnis does not implement FLOAT and DOUBLE PRECISION directly, though FLOAT_TYPE is similar to FLOAT.

The other data types are Omnis specific. The integer value in the NUMERIC, DECIMAL, and FLOAT_TYPE types corresponds to the Omnis subtypes for numbers; 0-8, 10, 12, and 14 are the possible values.

ALTER TABLE

alter_table_statement ::= ALTER TABLE table ADD
   { column_data | ( column_data_comma_list ) }

The ALTER TABLE statement lets you add a column to an already existing table using the same syntax as in CREATE TABLE.

The ALTER TABLE statement does not exist in the 1989 ANSI standard.

DROP TABLE

drop table statement ::= DROP TABLE table_name

The DROP TABLE statement removes a file slot and any data for that slot from an Omnis datafile.

The DROP TABLE statement does not exist in the 1989 ANSI standard.

CREATE INDEX

create_index_statement ::=
   CREATE [CASE SENSITIVE] [UNIQUE] INDEX index
   ON table ( index_column_comma_list )

index_column ::=
   column_reference [ ASC ]

The CREATE INDEX statement lets you create an index on an Omnis database column. You can make the index UNIQUE, asserting that no two rows of the database have the same value for this combination of columns. You can also make the index CASE SENSITIVE, this will usually result in more efficient queries. The index column list contains columns from the table, and the table must already exist. You can also specify ASC on an individual column to sort it in ascending, as opposed to descending, order.

The CREATE INDEX statement does not exist in the 1989 ANSI standard.

DROP INDEX

drop_index_statement ::= DROP INDEX index

The DROP INDEX statement removes the named index, which must already exist.

The DROP INDEX statement does not exist in the 1989 ANSI standard.

SELECT

select_statement ::=
   SELECT [ ALL | DISTINCT ] { value_expression_comma_list | * }
   from_clause
   [ where_clause ]
   [ group_by_clause ]
   [order_by_clause ]
   [FOR UPDATE ]

The SELECT statement is the basic query statement in Omnis SQL. It largely matches the ANSI standard, one exception being the having clause, which in Omnis SQL is part of the group by clause instead of being a separate clause in the select statement. That is, in Omnis SQL you cannot have a HAVING clause separate from the GROUP BY clause. The FOR UPDATE clause initiates special locking for the records in the query. When you fetch a row from a cursor containing a SELECT statement with a FOR UPDATE clause, Omnis locks the row for update. One of three things can then happen:

• You update the record with an UPDATE ... WHERE CURRENT OF cursor_name (see below), which on completion unlocks the row

• You fetch another row, which releases the lock on the previous row and locks the current one

• You terminate the transaction, which releases all locks

The order_by clause is separated out in ANSI SQL so that there is only one ordering for a query. Since Omnis SQL does not have any set operators, such as UNION, there is no need to separate out the ordering clause.

The ANSI 1989 standard has no for_update clause. This comes from embedded SQL, the syntax there is FOR UPDATE OF column_name_list.

Value Expression

value_expression ::=
   term
   | value_expression { + | - } term

term ::=
   factor
   | term { * | / } factor

factor ::=
   [ { + | - } ] primary

primary ::=
   literal
   | column_reference
   | function_reference
   | ( value_expression )

A value expression is a key element of SQL that lets you calculate a value using an arithmetic expression language. You build an expression out of literal numbers and strings, references to columns, or parenthesized, nested expressions. You can combine expressions with any of the four arithmetic operators. The grammar above expresses the precedence relationships between the operators: unary + and - take precedence over * and /, all of which take precedence over binary + and -.

Column and Table References

column_reference ::=
  [ table . ] column_name
  | [ alias . ] column_name

The column name corresponds to a field in a file class.

table ::=
  [ library_name . ] table_name

The table name corresponds to a file class or to a table alias in the same SELECT statement, and the library name corresponds to a library. The table must belong to the library.

Omnis SQL does not support the ANSI standard syntax alias.*, meaning all the columns from the table to which the alias refers. Also, if you use something other than a library name, or a name that Omnis cannot recognize as a library name, you will get a syntax error.

Function Reference

function_reference ::=
  scalar_function
  | aggregate_function

A function reference is either a scalar function or an aggregate function. Scalar functions operate on each row of data in the select; aggregate functions operate on groups of rows.

The ANSI SQL standard has no scalar functions.

scalar_function ::=
  scalar_function_name ( value_expression_comma_list )

There are a number of scalar functions, summarized below.

aggregate function ::=
   COUNT(*)
   | aggregate function name ( DISTINCT column reference )
   | aggregate function name ( [ ALL ] value expression

aggregate_function_name ::=
   AVG | MAX | MIN | SUM | COUNT

There are some departures from the ANSI standard for DISTINCT aggregates: you can use only one such function in a given SQL statement, and you cannot use aggregate functions in expressions in a GROUP BY clause or WHERE clause.

FROM Clause

from_clause ::=
   FROM table_reference_comma_list

table_reference ::=
   table_name [ AS ] [ alias ]

The FROM clause lets you specify the table to input into the SQL statement. Multiple tables in the list indicate a join, and the WHERE clause specifies the join condition. Each table reference can have an optional alias that lets you refer to the table in other parts of the SQL statement by the alias. You can use this to abbreviate references to the table in the other clauses.

The ANSI standard does not have the optional AS keyword.

WHERE Clause

where_clause ::=
   WHERE search_condition

search_condition ::=
   boolean_term | search_condition OR boolean_term

boolean_term ::=
   boolean_factor | boolean_term AND boolean_factor

boolean_factor ::=
   [ NOT ] boolean_primary

boolean_primary ::=
   predicate | ( search_condition )

The WHERE clause lets you select a subset of the input rows using a logical predicate. The above grammar defines the precedence of the logical operators AND, OR, and NOT.

predicate ::=
     comparison_predicate
   | between_predicate
   | in_predicate
   | like_predicate
   | relation_predicate
   | null_predicate

The ANSI standard has, in addition to the above predicates, the quantified and exists predicates (nested selects), which Omnis does not support. The relation_predicate is an Omnis extension to the standard that lets you use Omnis connections; see below.

comparison_predicate ::=
   value_expression comparison_operator value_expression

comparison_operator ::=
   < | > | = | <> | != | >= | <= | *= | =*

The standard comparison predicate involves one of the relational operators (greater than, less than, and so on).

ANSI SQL also allows you to use a nested select statement in place of the right-hand

value_expression; Omnis SQL does not support that. Omnis adds the !=, *=, and =* operators (not equal, left outer join, and right outer join, respectively) to the ANSI standard operators.

An outer join is a join that includes all the rows in the tables regardless of the matching of the rows. The *= operator includes all rows from the table on the left that satisfy the rest of the WHERE clause. The =* operator includes all rows from the table on the right that satisfy the WHERE clause. Rows from the other table (right and left, respectively, contribute values if there is a match and NULLs if not. This syntax is similar to the SYBASE outer join syntax.

between_predicate ::=
   value_expression [ NOT ] BETWEEN value_expression AND value_expression

in_predicate ::=
   value_expression [ NOT ] IN ( literal_comma_list )

The ANSI standard lets you use a subquery (a nested select) as well as a literal list; Omnis does not.

like_predicate ::=
   column_reference [ NOT ] LIKE literal

The ANSI standard adds an ESCAPE clause to the like_predicate to let you specify an escape character so you can match a % or _; Omnis does not implement this.

null_predicate ::=
   column_reference IS [ NOT ] NULL

relation_predicate ::=
   { CHILD | PARENT } OF table

The relation_predicate lets you test the current row as being either a child or a parent of rows in the specified table.

GROUP BY Clause

group_by_clause ::=
   GROUP BY column_reference_comma_list [ HAVING search_condition ]

The group_by_clause lets you group the input rows into groups according to a set of columns. The HAVING clause lets you select the groups, as opposed to the WHERE clause, which selects the rows going into the groups.

ANSI SQL has no ordering dependency between GROUP BY and HAVING, and you can have a HAVING clause without an accompanying GROUP BY. Omnis does not allow this.

Omnis SQL does not support the use of functions in a GROUP BY clause.

ORDER BY Clause

order_by_clause ::=
   ORDER BY order_column_comma_list

order_column ::=
   column_reference [ ASC | DESC ]

The order_by_clause lets you sort the output rows of the SQL statement using columns from the input tables.

The ANSI standard lets you sort by value_expressions in the select list by specifying the number of the expression; Omnis does not.

INSERT

insert statement ::=
   INSERT INTO table [ ( column_reference_comma_list ) ] { VALUES ( insert_value_comma_list ) | select_statement }

The INSERT statement inserts rows into an Omnis table. The first list of columns names the columns you are creating; this exists to let you reorder the list to match your list of values or select statement.

There are two alternative ways to supply values to the INSERT statement. You can supply actual values through a VALUES clause that contains a list of values, or you can give a SELECT statement that creates a table of data matching the insert list. See the SELECT statement section above for details on SELECT.

insert_value ::=
   literal | NULL

An insert value is a literal value or the NULL value specified by the string ìNULLî.

UPDATE

update_statement_searched ::=
   UPDATE table SET assignment_comma_list [ where_clause ]

assignment ::=
   column_reference = { value_expression | NULL }

The searched update statement updates all rows that satisfy the predicate in the WHERE clause by assigning the indicated value or NULL to the column.

Omnis SQL will let you preface the column name in the assignment with the library and table names, which extends the ANSI standard. There is no need to specify the additional names, but you can do so for clarity if you wish. Specifying a table other than the table in the UPDATE table clause, generates an error.

update_statement_positioned ::=
   UPDATE table SET assignment_comma_list WHERE CURRENT OF cursor

The positioned update statement updates the current row, the row to which the current cursor points. See the description of the Declare cursor command in the Omnis Help. The WHERE CURRENT OF cursor clause works with the SELECT ... FOR UPDATE statement to update rows locked for update.

DELETE

delete_statement_searched ::=
   DELETE FROM table [ where_clause ]

The DELETE statement deletes rows from the Omnis database based on the predicate in the WHERE clause. Omnis deletes all rows that satisfy the predicate.

JDBC

Note that support for JDBC has been removed in Studio 10 and above, but the supporting files can be obtained by contacting Omnis Support.

This section contains the information you need to access a database using the JDBC object DAM and JDBC drivers (plus their associated middleware where applicable), including server-specific programming, data type mapping and troubleshooting. For general information about logging on and managing your database using the SQL Browser, refer to the earlier parts of this manual.

Minimum Requirements

To use the JDBCSESS object the client machine must have the Java Runtime Environment v1.4 or higher installed. In addition, the JDBCSESS object will only support JDBC 2.x certified drivers. The JDBC DAM utilises the Omnis Java Engine (OJE), so it is important that the requirements for the OJE are also met. In particular a JVM_PATH environment variable must be set to the path of the JVM library in order for the OJE to start the Java Virtual Machine.

Properties and Methods

In addition to the “base” properties and methods documented in the SQL Programming chapter, the ODBC DAM provides the following additional features.

Session Properties

Session Methods

Connecting to your Database

Do SessObj.$setdriver('sun.jdbc.odbc.JdbcOdbcDriver')

Failure to perform this step will cause the $logon() to fail. In order for the specified JDBC driver to be successfully loaded, it must exist in the system CLASSPATH environment variable.

To log on to the database using the SessObj.$logon() method, the hostname must contain the database URL required by the specified driver. The user name and password should contain the values required by the database.

Transactions

Generally, using manual transaction mode results in increased performance because the session object does not force a commit after each statement.

If you do not have a result set pending, JDBC session objects will commit each statement if the transaction mode is automatic. If the transaction mode is server, the session may be committed depending on the behavior of the JDBC driver.

Dates

The session property $defaultdate allows default values to be added to date values mapped to the server where the Omnis date value does not contain complete information, e.g. a Short time mapped to a server date time.

Multiple cursors

To allow multiple select cursors when connecting to Microsoft SQLServer, the statement issuing the SELECT must have the $usecursor property set to kTrue before the statement is executed. If a statement is issued when $usecursor is kFalse and this statement returns a result set, this will prevent all other statements in the same session from returning data. The blocking results must be completely processed or cleared before another result set can be generated. If a commit or rollback is performed on the session, all the session’s statement cursors will be closed and all pending results will be lost.

JDBC Data Type Mapping

The following table describes the data type mapping for Omnis to JDBC connections. The Omnis to JDBC mapping will attempt to pick the best match based on the types the driver supports in the order listed. For example, if the driver supports VARCHAR and CHAR data up to a maximum column size of 255, but LONGVARCHAR data up to 2 gig, an Omnis Character(1000) will map to whatever the associated server native type is for LONGVARCHAR, e.g. TEXT.

Omnis to JDBC

JDBC to Omnis