Category: Zim:X Interacting With SQL Databases

A Zim Client-Server application can be developed as a stand-alone standard Zim application on any supported platform. Once developed, the application can be moved easily to another platform and configured to access remote data sources. Because of this capability, Zim enables you to separate your application from the ultimate data source, enabling development to be done independently of the target database management system or platform. Zim lets you perform application development and testing off-line on a stand-alone PC, a portable, or a workstation and then implement the production application on a Zim database, an SQL database, or a combination of both. This reduces the demands on your environment, including your SQL database. This portability lets you perform your development and testing on one hardware platform and implement as a client application on a completely different platform.

Zim applications can process data stored in more than one location. Database servers often become performance bottlenecks because data is located in one area and the server cannot handle the processing load for that data. Zim avoids this bottleneck by letting you distribute or partition data elements across both the database server and the Zim databases. This gives you the power to balance the load on your network. By off-loading data from your server, Zim boosts performance by lowering server demand.

Client-Server database application development with Zim provides application portability to database servers. An application written for a particular server can be migrated to another server without changes to the application. Zim portability lets you adopt new technologies as they emerge.

Zim lets clients on different platforms run the same application against the same database server concurrently. Clients can be moved to other platforms.  The client platforms for an application can be mixed and matched, as required.

Zim provides a universal interface to database servers. Zim still enables you to access the unique features and strengths inherent in any database server by taking advantage of proprietary extensions of the SQL dialect. Zim syntax supports any SQL database in an optimal way.

Zim enables you to test your applications interpretively, without compiling first.

Zim can manipulate and retrieve data from third-party data sources as well as from a Zim database. Client applications for SQL database servers are designed and developed as complete Zim application systems. The objective of the Zim product is 100 percent source code portability of applications including seamless access to databases being managed by SQL relational database management systems (SQL servers) supplied by independent vendors. In fact, a Zim Client-Server database application can consist of an arbitrary mixture of tables managed by an SQL server and entity sets and relationships managed by Zim.

As an architectural model, client-server architecture consists of two components: a server process and a client process. A server process provides services to client processes, whereas a client process requests services from a server process.

A database management system is a server process that provides database access and update services to application programs (client processes). Any application program that issues database requests to a database management system is a client-server database application. The database server can be on the same machine as the application program, it can be a database server on a local area network; or it can be on a host machine with access through a gateway. In all cases, the application program is acting as a client and the database management system is acting as a server.

In order to use the client-server capabilities of Zim, two software components are required:

Zim software

The JDBC SAM (Server Access Module) which makes a “bridge” between the Zim application and the specific SQL database server in use. Any SQL database servers available in the market allow the JDBC connection, both in 32 or 64 bits.

DB2 provides support for three levels of multiple user concurrency control. These isolation levels select the extent to which database operations performed by one user are isolated from concurrent operations performed by other users.

“Repeatable Read” isolation level means that locks are held on all rows read or updated by a user for the duration of the current transaction (or logical unit of work). At this isolation level, if a row is read at some time and at a later time during the same transaction is re-read, the row still contains the same data values. That is, within a transaction, a read of a row is “repeatable” with identical results.

“Cursor Stability” isolation level means that within a transaction, locks are held only on the most recently read row or rows. Were a row that had been previously read within a transaction to be re-read, there is no guarantee that it returns the same column values as previously as another user could have updated the row in the meantime.

“Uncommited Read” isolation level means that within a transaction, rows that have been updated by one user can be read by another user and the reading user sees the updated values. The updating user may later decide to cancel the transaction, in which case the reading user would have retrieved (and possibly made decisions based on) data that is no longer stored in the database.

In general, the cursor stability isolation level is appropriate. However, in cases where updates are being performed on tables that are being related to themselves (reflexive relationships), the repeatable read isolation level should be selected, as it is highly probable that previously read rows will be re-read.

Execution Error Conditions

The execution environment of a Zim client-server application involves products from more than one vendor. This can introduce the potential for additional error situations. For example, if the parameters to the connect command are not specified correctly for the particular installation of the SQL server being used, then the connect command fails and returns an error code. If the initialization or execution environment requirements of a specific SAM have not been properly established, errors can be generated. The following topics address the types of error conditions that can occur and how they are handled.

The Deadlock Error Condition

Because Zim Client-Server applications are implicitly multi-user, the deadlock error condition can occur in an otherwise properly executing application. Each Server Access Module (SAM) detects the deadlock error condition from the SQL server it supports and translates the error into a Zim deadlock “$ ErrCode” value 2010. Accordingly the “ON DEADLOCK” exception handler can be used to handle deadlock conditions in a manner appropriate to the requirements of the application.

For more information, see Multiple Locking.

Errors from SQL Servers

Some SQL servers generate return codes distinguishing between error and warning conditions. If a server supports this distinction, then the “$ErrCode” reflected to the Zim Client-Server application also distinguishes between errors and warnings. Hence, the Zim “ON ERROR” and “ON WARNING” exception handlers can be used.

If the server does not support such a differentiation, then a Zim error condition is generated into “$ErrCode”.

In either case, $SQLErrCode contains the actual SQL server error code and $SQLErrMsg contains the SQL server error message.

SAM Errors

SAMs have their own error codes. These error codes are in the -100000 range and are very rare. Some, however, are reported by Zim; the most likely ones are as follows:

The benefit of $serverfunction is the ability to code server functions that Zim does not support. Often there are similar functions in Zim and the server, but the mapping is not exact. As a result, the function is not present in the .sql file, forcing Zim to retrieve the entire table and evaluate the where clause on the client side. Sometimes functions are missing from the .sql file.

Users can code the equivalent server function instead of waiting for updates to the .sql file.

If $ServerFunction() is executed against a Zim table or with ExecuteMode set to ZimMode, the value returned by $ServerFunction is the value of the first argument.

Zim Client-Server uses the SQLCPI variable to find the SAM module and the utilities used to import and export objects. Under Windows, the SQLCPI variable is set in the Windows registry and is set to the directory where the SAM software is installed.

When running Zim on Windows, ensure that the registry variable SQLCPI  is set properly.

Verify that the SQLCPI registry variable has been set properly from within Zim by outputting the $SQLPath system variable, as shown below:

> output $SQLPath
c:Zim

In addition to installing Zim and modifying your UNIX operating environment to accommodate the Zim software, you must also set the environment variable “SQLCPI” to represent the directory for SAM files.

To define SQLCPI, enter the following command if you are running the Bourne or Korn shells:

SQLCPI=/usr/zim; export SQLCPI

where zim is the directory where you installed the Zim SAM.

If you are running the C-shell, enter the following command:

setenv SQLCPI /usr/zim

Before you run Zim, ensure that the location of the Zim software is included in the UNIX PATH environment variable. If you are running the SQLDif utility, ensure that the location of the SQLCPI import and export utilities are included in the UNIX PATH environment variable as well.

Note:This topic assumes that your current PATH includes the directories /bin and /usr/bin.

To add /usr/zim to your PATH in the Bourne or Korn shells, enter

PATH=/bin:/usr/bin:/usr/zim

In the C-shell, enter

set path = ( /bin /usr/bin /usr/zim)

You can also add these commands to your login script so that they are automatically executed each time you log in to your system.

If the server function requires parameters (called input host variables), these can be supplied by placing parameter markers in the syntax (indicated with a ‘?’) and then specifying the parameters as the second and subsequent arguments to $ServerFunction.

Here is an example:

change all orders where orderno = 1003 \
   let RequiredByDate = $ServerFunc('trunc( ?, ? )', $addmonths( $date, 1 ), 'MM' )

Note the parameter markers in the syntax for the Oracle function “trunc”. It truncates a date giving another date based on the format string, the second argument. In the following example, the format ‘MM’ returns the first day of the month. This change command sets RequiredByDate to the first day of the next month.

The generated syntax is

UPDATE orders set RequiredByDate = TRUNC(?, ?) where OrderNo = 1003

The second parameter marker was unnecessary. It can be inserted as part of the function as follows:

Change all orders where orderno = 1003 \
   let RequiredByDate = $ServerFunc("trunc( ?, 'MM' )", class="zimcode"$addmonths( $date, 1 ) )

Note that strings in SQL are delineated by single quotation marks. This requires the use of double quotation marks around argument 1, or escaping the quotation marks with a ‘\’.

There are no types to coerce parameters to for the server-side function. Users have to ensure those function arguments are of the correct type. Use

add Orders from DSZomOrders90000 \
   let RequiredByDate = $ServerFunction( 'LAST_DAY( ? )', $ToDate( DSZomOrders9000.OrderDate )

to set RequiredByDate to the last day of the month in which the order was placed. Omitting $ToDate causes a data type mismatch error since DSZomOrders9000.OrderDate is a field in a document and therefore of type CHAR.

If the command is going to be executed entirely on the server, any input host variables to $ServerFunc must be constant values that can be evaluated on the client side. The example above sets RequiredByDate to the first day of the next month. The expression $AddMonths( $Date, 1) is constant and can be evaluated on the client side before passing it to the server. If the change command were changed to set RequiredByDate to the first day of the next month of the OrderDate, modify the syntax for the host variable to $AddMonths( OrderDate, 1) to

change all orders where orderno = 1003 \
   let RequiredByDate = $ServerFunc("trunc( ?, 'MM' )", $AddMonths( OrderDate, 1 ) )

This expression is not constant since OrderDate varies with every row in the table. This expression cannot be evaluated on the client side since the command is executed entirely on the server.

The SQL syntax generated is

UPDATE Orders set RequiredByDate = trunc( ?, 'MM' ) )

When executed, the command returns an SQLErrCode of -932, inconsistent data types. In the prior example, the Zim client calculated the value of the $AddMonths expression and passed it to Oracle. In this case, the only constant expression Zim can find is for the constant 1, which it passes to the engine, resulting in the error. Re-code this using Oracle functions as shown below:

> change all orders let RequiredByDate = $ServerFunc( "trunc( Add_Months(OrderDate, 1 ), 'MM' )" )

If the command is coded as

> change all orders let RequiredByDate = $ServerFunc( "trunc( ?, 'MM' )",$ToDate( $AddMonths( OrderDate, 1) ))

it works. This is because $ToDate is not supported on the Server so Zim retrieves every row before updating each row, which enables Zim to properly evaluate the input host variable. In general, do not rely on the syntax Zim generates. Do not use field values in the input host variables; pass them directly as part of the syntax in argument 1 of $ServerFunction.

The SQL syntax for this last example (not recommended) is

SELECT RequiredByDate, OrderDate, OrderNo from Orders
UPDATE Orders set RequiredByDate = TRUNC( ?, 'MM' ) where OrderNo = ?

The Import/Export functions of the Zim Development Center application provides support for the import/export and translation of database object definitions between the Zim and SQL server environments. In particular, Zim EntitySet and data relationship definitions can be exported to the SQL server to create corresponding tables and indexes. Additionally, SQL table definitions can be extracted from the server’s catalog tables, translated, and delivered into the Zim environment as members of the “Fields” EntitySet which is part of the Zim Data Dictionary.

While you are using the import or export facility of the development center, do not be explicitly connected to the SQL Server. Both facilities connect and disconnect from the SQL Server as needed.

This option is used to import table definitions from the SQL server and to translate them into equivalent Zim definitions. To import SQL definitions into Zim, select the Tools/Import/Objects/From SQL option from the development center main menu.

The SQL Definition Import window appears. On this window, choose the ODBC Server as the server from which to import. Next click the “Fetch Def’n” button.  The Server Parameters window appears. The connection parameters are identical to parameters 2 through 7 of the “CONNECT” command. The SAM uses these to access the correct database. Refer to Connecting to SQL Servers for information on how to supply the correct values for these items. (Note that while these utilities connect to the SQL database, they do not connect Zim to the database.) The “Table Owner Name” field can be used to denote the owner of the tables which Import retrieves definitions. Note that the column definitions for all tables owned by “Table Owner Name” are retrieved. If the table owner name field is left blank, Import retrieves all table definitions which are available to the user.

If the check box to the left of the table name is selected when the “Import” button is selected, that table’s column (field) definitions are imported into Zim. By default all tables are flagged for import. Use the “Deselect All” button to clear all check boxes. The “Select All” button selects all check boxes. If more than 30 table definitions are retrieved, you can scroll forwards and backwards through the table name. SQL table definitions that you select to import are added by ZOM. All tables are added as EntitySets with the enttype set to the name of the selected SAM. Imported SQL table definitions do not have any index information. The “index” field for all imported SQL fields always has the value ‘NO’. You have to modify the imported fields to indicate any indices that you wish Zim to maintain.