Category: ZIM User Documentation

Zim supports Microsoft Open Database Connectivity Drivers (ODBC, defined by Microsoft as a standard way for applications to access data stored in database files) which allows third-party tools to access a Zim database via the Zim ODBC Driver.

This driver is used to access data from a Zim database that is either local to the machine on which the driver is installed or remotely located. In either case, Zim Server must be running in the machine where the database is located.

The way ZIM ODBC Driver works is this:

1) The third-party tool requires an external data connection using a Data Source (see Configuring a Data Source). This connection is performed by means of the Zim ODBC Driver;

2) Using the parameters provided by the Data Source, Zim ODBC Driver connects to Zim Server that is running in the specified machine;

3) Once the connection is established, the Zim Data Dictionary (Entity Sets and Relationships with fields) present in the Zim database is sent to the third-party tool for further operations.

Following to the connection, all operations go through these steps:

1) The third-party tool builds an SQL statement to perform the operation and sends this SQL statement to the Zim ODBC Driver;

2) The Zim ODBC Driver adapts the SQL statement to Zim needs and sends it to Zim Server;

3) Zim Server processes the statement and sends back the appropriate information;

4) This information is sent back to the third-party tool for processing.

The SQL statement provided by the third-party tool is described in Supported SQL Grammar. In particular, the SQL CREATE and SQL DROP commands are not supported for tables or indexes.

Note: In Zim, constant strings can be represented either surrounded in single quotes (‘) or double quotes (“). In SQL, constant strings are surrounded by single quotes (‘) whereas identifiers are surrounded by double quotes (“).

statement ::= SELECT select | INSERT insert | DELETE delete | UPDATE update | passthroughSQL

passthroughSQL ::= any statement supported by the back end

tablename ::= identifier

columnname ::= identifier

select ::= selectcols FROM tablelist where groupby having orderby

delete ::= FROM table where

insert ::= INTO table insertvals

update ::= table SET setlist where

setlist ::= set | setlist , set

set ::= column = NULL | column = expression

insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES ( valuelist )

columnlist ::= column , columnlist | column

column ::= columnname

valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL

selectcols ::= selectallcols * | selectallcols selectlist

selectallcols ::= | ALL | DISTINCT

selectlist ::= expression , selectlist | expression

where ::= | WHERE boolean

having ::= | HAVING boolean

boolean ::= and | and OR boolean

and ::= not | not AND and

not ::= comparison | NOT comparison

comparison ::= ( boolean ) colref IS NULL | colref IS NOT NULL |

expression LIKE pattern | expression NOT LIKE pattern |

expression IN ( valuelist ) | expression NOT IN ( valuelist ) |

expression op expression

op ::= > | >= | < | <= | = | <>

pattern ::= string | ? | USER

expression ::= expression + times | expression – times | times

times ::= times * neg | times / neg | neg

neg ::= term | + term | – term

term ::= ( expression ) | colref | simpleterm | aggterm

aggterm ::= COUNT ( * ) | AVG ( expression ) | MAX ( expression ) | MIN ( expression ) | SUM ( expression )

simpleterm ::= string | realnumber | ? | USER | date | time | timestamp

groupby ::= | GROUP BY groupbyterms

groupbyterms ::= colref | colref , groupbyterms

orderby ::= | ORDER BY orderbyterms

orderbyterms ::= orderbyterm | orderbyterm , orderbyterms

orderbyterm ::= colref asc | integer asc

asc ::= | ASC | DESC

colref ::= aliasname.columnname | columnname

aliasname ::= identifier

tablelist ::= tableref, tablelist | tableref

tableref ::= table | table aliasname

table ::= tablename

identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotation marks)

string ::= a string (enclosed in single quotation marks)

realnumber ::= a non-negative real number

integer ::= a non-negative integer

date ::= a date in ODBC escape clause format (for example, {d’1996-02-05′} or

   –(*vendor(Microsoft),product(ODBC) d’1996-02-05’*)–

time ::= a time in ODBC escape clause format (for example, {t’10:19:48′} or

   –(*vendor(Microsoft),product(ODBC) t ’10:19:48’*)–

timestamp ::= a timestamp in ODBC escape clause format (for example,

  {ts’1996-02-05 10:19:48.529′} or

  –(*vendor(Microsoft),product(ODBC) ts ‘1996-02-05 10:19:48.529″*)–

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

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.

Adding More Power and Flexibility to Zim

Zim is a powerful and flexible environment for developing and using all types of database applications.

Zim‘s Entity-Relationship (ER) data model and fully-integrated Object Dictionary enable progressive program development, whether the information processing system is simple or complex. Zim Server provides a database server capability that can be used with ZIMQTC or other client processes.

The following topics describe ZimWeb, including its operations and how it can be used to create Web applications using Zim.

The Technology inside ZimWeb

ZimWeb has some distinct advantages over previous solutions, including:

• It is an all-Java servlet – it runs on any platform with a Java Servlet Engine, such as Apache’s Tomcat
• It makes web development for Zim applications easier with support for features such as client sessions, cookies, etc.
• It supports XML and multiple client types (web browsers, cellphones and tablets, etc.) by integrating with the Apache’s Xalan-Java version 2 XSLT styling engine and Apache’s Formatting Object Processor.

To help you understand why these technologies are so useful for developing the Internet and other applications we recommend that you read the bibliography.

ZimWeb and Zim Server

ZimWeb makes use of ZIMSERVER to access Zim databases and connects a Zim Server to the internet. Thus, a good working knowledge of the Zim Server product is required for the development of Web applications using Zim.

This user guide describes the features and functions of ZimWeb. There is also a complete description of the implementation of the example application. Most Web applications using ZimWeb can be modeled after this example. Since the example does not use all features of ZimWeb, a number of additional topics provide reference information on software installation, configuration, etc.

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 &.

ZimWeb Connection Architecture

Web applications can be implemented in Zim, using a Java Servlet program called ZIIServlet, provided as part of ZimWeb. A Java Servlet must be executed on a Java Servlet Container, such as Apache’s Jakarta Tomcat.

ZIIServlet works together with Zim Server to process requests from the client, whose interaction with the end user occurs through a web browser. In particular, request parameters (e.g. HTML form field values) are passed as parameters to a Zim program that is executed on Zim Server. That program is responsible for processing the request and constructing the response. Although the response can be sent directly back to the client, ZimWeb includes a variety of options for processing it.

ZimWeb communicates with Zim Server using TCP/IP. Zim Server and ZimWeb, can run on the same or different systems, provided that there is a network connection between the them.

The following diagram shows the basic components of a Web application using ZimWeb:

ZimWeb Components

ZimWeb is composed of four components: the ZIIServlet, the Java Servlet Container, the Zim Database Agent and a web browser.

Java Servlet Container

Also known as a Java Servlet Engine. An environment in which Java Servlets can be executed. Tests were done with Apache’s Tomcat, which is the official reference platform for Java Servlet Containers. A Java Servlet Container can be run independently, or it can be integrated with a Web Server. The ZimWeb Reference Platform includes Tomcat, but a variety of alternatives are available.

ZIIServlet

ZIIServlet is a Java Servlet. It can be loaded automatically by the Java Servlet Container when the first request is received, or alternatively it can be loaded in advance so that it is ready beforehand.

Zim Database Agent

The Zim Database Agent is part of Zim Server. It is a process that runs on a specific machine and accesses a specific database. ZimWeb can communicate with any number of databases on any number of database machines. However, a Zim Database Agent is always connected to a specific database.

Web Browser

Typical web browsers include Microsoft Internet Explorer on Windows, Safari on MacOSX and iOS, Chrome, Firefox and Opera on serveral platforms, from desktops to tablets and smartphones. The web browser is not supplied with ZimWeb. End users will connect to ZimWeb using any available web browser in their machines

XML Technologies

The following books will be particularly useful in explaining the XML technologies that are crucial to using the ZimWeb effectively:

• XML By Example (2nd Edition) and
  Applied XML Solutions by
  Benoit Marchal – these will give you a good overview of the XML landscape.
• XSLT Programmer’s Reference 2nd Edition by
  Michael Kay – this is the reference book for XSLT, though it is a bit “heavy” to get you started –
  XML By Example (2nd Edition) is a much gentler introduction. But he knows his stuff – he wrote the
  Saxon XSLT processor.
• XSLT by
  Doug Tidwell – he’s a “technology evangelist” from IBM.
• XSLT Cookbook by
  Sal Mangano
• XSL-FO by
  Dave Pawson.

Java

If you want to learn about Java then take a look at these books:

General Java programming

• Learning Java, 2nd Edition by Patrick Niemeyer and Jonathan Knudsen – a great introduction to the Java language.
• Java in a Nutshell, 4th Edition by David Flanagan, and Java Enterprise in a Nutshell, 2nd Edition by William Crawford, Jim Farley and David Flanagan – together, these provide a useful reference for the Java language.

Server-side Java programming

• Java Servlet Programming, 2nd Edition by Jason Hunter with William Crawford – this is a very clear exposition of server-side Java programming.
• Professional Apache Tomcat by lots of authors. This recently-published book is really helpful in trying to get the best out of the Tomcat servlet engine.

ML Processing with Java

• Java & XML, 2nd Edition by Brett McLaughlin – this is an excellent explanation of this topic.
• Java and XSLT by Eric M. Burke – another excellent O’Reilly book.