Category: ZIM-X Database Connectivity

An ODBC Data Source to access a Zim database can be configured in two different ways:

. Using the Zim ODBC Driver Setup to create a data source;

. Using Zim language to dynamically create and maintain a data source tailored for specific needs like calling a third party software from within Zim.

In both cases, the Zim ODBC Driver product must have been previously installed in the machine where the setup or the dynamic configuration is going to happen.

Zim ODBC Driver Setup

The setup of the desired Data Source will happen via the Zim ODBC Driver setup dialog invoked this way (this procedure was based on Windows XP environments; other Windows environments might be slightly different):

. Press the Start button on windows;

. Select Control Panel;

. Select Administrative Tools;

. Select Data Sources (ODBC);

. On the ODBC Data Source Administrator window, either click on the User DSN or System DSN tab. The User DSN indicates that the new data source will be accessible by the current user of this Windows environment, whereas the System DSN indicates that the new data source can be used by all users of this machine provided that they have the appropriate permission. Unless internal policies used in your company state otherwise, click on the System DSN tab;

. Next, click on the “Add…” button the start the new setup (if you already have an existing Data Source that needs to be changed, click on the button “Configure…“);

. The Zim ODBC Driver Setup appears with the following aspect:

where the fields mean:

Dynamic Creation of a Data Source

Zim language can be used to dynamically create and maintain a data source belonging to the System DSN group of data sources.

The following Zim statements create a new Data Source called “MyDataSource”:

let v = $setproperty(“REG:[ODBC]”, “MyDataSource”, $getproperty(“REG:[ODBC]”, “VERSION”))
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\DBQ”, “Example”)
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\Driver”, $getproperty(“REG:[ODBC]”, “DRIVER”))
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\DSN_NAME”, “MyDataSource”)
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\Host”, “localhost”)
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\Server”, “6002”)
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\UID”, “ZIM”)
let v = $setproperty(“REG:[ODBC]”, “MyDataSource\WorkPath”, $workpath)

After these Zim commands have been executed, any third party software that connects to a Zim database in order to retrieve or update data, can make use of the ODBC Data Source “MyDataSource” via a Zim SYSTEM command:

SYSTEM “C:\MySoftware\MyExecutable.exe MyDataSource” CLOSE

The properties “VERSION” and “DRIVER” cannot be set because the are established at installation time by the Zim ODBC Driver Installer.

A third party software can invoke a Data Source created this way and use per user entity sets. Refer to the section Using Per User Entity Sets for further details.

The same way data source properties can be set, they can also be retrieved using the Zim command $SetProperty as shown in these examples:

out $getproperty(“REG:[ODBC]”, “MyDataSource\DBQ”)                              % Prints the name of the database
out $getproperty(“REG:[ODBC]”, “MyDataSource\Host”)                              % Prints the address of the host

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 = ?

ZimWeb includes page template support. This allows you to combine parameters from Zim with the page (web page, etc.) delivered to the client.

Template file format

A page template is just a regular page (HTML etc.), with parameter placeholders to indicate where each parameter to be supplied by the Zim application is to be placed. This template can be designed easily with your normal web page design tool.

The parameters placeholders are formatted like HTML comments (e.g. so for a parameter named company you would use a parameter placeholder ).

Note: the parameter and the parameter placeholder must be in the same case.

The page template processor will substitute the value of the parameter for the parameter placeholder. If the value of a parameter in a placeholder is not supplied, its value will be assumed to be blank. You can use the same parameter placeholder more than once in a given template.

Sample template

customer.htm

Parameter XML format

The Zim application supplies the values of parameters in an XML format.

The root element can have any name. The parameter names are the names of the elements which are children of the root element. The character content of each of the children of the root element is the value of the parameter.

An easy way to demonstrate this is by the following example:

Sample XML data

Note: the use of the CDATA section in the above to allow the company parameter to include any characters. Alternatively the "&" character could have been replaced by its corresponding entity &.

The various XML technologies incorporated into ZimWeb may require references from one source to another (e.g. one XSLT stylesheet referencing another, or a reference to an external image in XSL-FO).

The following explains precisely how references to other sources (URIs) are interpreted in different circumstances.

URIs in XML generated by the Zim database agent

If there is a relative URI in the XML generated by the Zim database agent – e.g. a reference to a DTD (document type definition) – then this is interpreted as relative to a file zii.xml (which does not have to exist) in the base path of the ZimWeb application.

(e.g. If you have installed ZimWeb in the manner described in the installation instructions then specifying that a DTD is at path dtd/mydtd.dtd will refer to the file [TOMCAT_ROOT]/ZII/dtd/mydtd.dtd.)

URIs in XSLT stylesheets

If there is a relative URI in an XSLT stylesheet – (e.g. a reference to another stylesheet or document) – then this is interpreted as relative to that stylesheet. (e.g. If you have a stylesheet[TOMCAT_ROOT]/ZII/styles/cool.xsl that imports or includes a stylesheet library/valueformat.xsl then that will refer to the file [TOMCAT_ROOT]/ZII/styles/library/valueformat.xsl.

URIs in XSL-FO

If there is a relative URI in XSL-FO – (e.g. a reference to an image in an external file) – then this is interpreted as relative to a file zii.xml (which does not have to exist) in the base path of the ZII application.

e.g. If you have installed ZimWeb in the manner described in the installation instructions then specifying a reference to an image at path images/fop.jpg will refer to the file [TOMCAT_ROOT]/ZII/images/fop.jpg.

Zim application programs can be compiled in ZIMMODE, SERVERMODE, or SQLMODE depending the type of the data dictionary objects used (fields EntType, RelType or DocType). See details in the Zim command SET COMPILEMODE.

Zim applications can operate in ZIMMODE, SERVERMODE, or SQLMODE by means of the use of the command  SET EXECUTEMODE which controls the way an application runs.

The Zim “COMPILE” command is capable of compiling application programs in one of the same three modes as described for program execution:

The default mode of operation is SERVERMODE. For all database servers except Zim Integrated Server, SERVERMODE and SQLMODE behave identically. In SERVERMODE, a Zim client accessing tables on Zim Integrated Server sends Zim commands to the server instead of SQL commands which greatly improves performance. In SQLMODE, the ZIM client determines the optimal SQL syntax to send to the server by inspecting the values for “EntType” or “RelType” in the Object Dictionary for the tables specified in the command.

Therefore, for each EntitySet or data relationship, either standard Zim compiled code or, ZIS/SQL database specific compiled code is generated depending upon the “EntType” and/or “RelType” values. In addition, when compiling under SERVERMODE or SQLMODE, if at least one command causes an SQL reference to be generated, then the compiler flags the entire compiled program file as compiled for an SQL database. If there are no ZIS/SQL references generated, then the compiled file is flagged as compiled for Zim. You do not have to be connected to the SQL server to compile in standard Zim mode.

The mode in which the Zim compiler operates may be set by the command:

SET COMPILEMODE [ SERVERMODE | SQLMODE | ZIMMODE ]

where SQLMODE is the default setting. For example, if the command:

set compilemode zimmode

were executed, then the Zim “COMPILE” command would revert to generating standard Zim compiled code and would also flag the compiled program file as being compiled for Zim. All members of the Zim product families can execute such compiled programs.

The “SET COMPILEMODE” command is ignored by the Zim runtime products.

When developing client-server database applications, it is important to consider the location of the data. Any data under the control of the database server contributes to both the load on the network as well as load on the server machine. Because of this, it is important to analyze the data associated with a given application and determine the best location (or locations).

Static data, or data that does not change, can be duplicated and distributed over the client workstations to reside under Zim control. This type of data is typically part of the application. A table of help screen text messages is an example of static data that can (or should) be located on the client workstation.

Data that changes on a monthly, weekly, or even daily basis can be located on both the client and server workstations. An application can down load this data (or part of it) to a local copy at an appropriate time. The single server copy would be updated as required and client workstations would pick up any updates automatically.

Finally, there is dynamic shared data that resides on the server and is shared by the client workstations. This type of data resides under the SQL database’s control and be accessed and manipulated from within Zim.

In Client-Server applications, you do not have to do anything special to access or manipulate data stored in the SQL database. The data definitions of objects under Zim’s control and the data definitions of objects not under Zim’s control are exactly the same. To an application, Zim manipulates both in exactly the same way.

The development of a Zim Client-Server application can be performed in isolation from the SQL database; it can in fact take place on an entirely different computing platform. Zim does not have to be connected to the server through the development phase. This “off-line” development capability eliminates the heavy load that can be placed on SQL servers by development activities.

In order to access SQL database tables, Zim must have a definition of the table. The table must be defined in two places: in Zim and in the SQL database. EntitySets and relationships are flagged as being under the control of Zim or under the control of the SQL database. The definitions of those EntitySets and relationships under control of the SQL database are either exported to the SQL database or imported from the SQL database. You must ensure that the definitions in Zim and the SQL database are compatible. Zim’s SQL definition interface provides the means to easily import and export definitions.

Figure 1

In Figure 1, the table “Customers” is defined both in Zim and in the SQL database. If “Customers” is defined as a Zim EntitySet or if Zim is running in ZIM mode, then all references to customers are directed to the Zim EntitySet. If, however, “Customers” is defined to be an SQL table and Zim is running in SQL mode (the default), then all references to customers result in the generation of the appropriate SQL code which is sent to the SQL database.

Zim requires that each EntitySet and data relationship to be managed by the SQL database have at least one unique index defined for it. The unique index is a primary key for the SQL table to which the EntitySet or data relationship corresponds. A primary key is necessary to directly access any record (row) of any SQL table. It does this by generating select statements which specify values for the primary key column(s) of the table. For more information, see Primary Keys.

Zim supports two types of transactions: implicit and explicit. An implicit transaction is associated with each individual Zim command that accesses the database in some manner (either read/only or read/write). When the command ends, all locks that were acquired are released and any database updates are committed to the database. If the command fails (e.g. a deadlock condition), then Zim automatically removes any partial updates and releases all locks.

Explicit transactions are provided in order to support database updates associated with multiple database access commands that must either be all committed or all removed as a single “logical unit of work” (or “logical unit of recovery”). An explicit transaction is initiated by the command “transaction” and ended either by the command “endtransaction” (that commits any database updates) or by the command “quittransaction” (that cancels any database updates). Both commands release all held locks. Should any database access command inside an explicit transaction fail (e.g. by a deadlock condition) then all partial updates to that point are removed and all held locks are released.

Zim supports this “transaction” functionality against an SQL server database.