Category: ZIM-X Database Connectivity

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.

The export facility is used to translate Zim definitions for entity sets and/or data relationships and create them on the designated SQL server. The definitions of the resulting tables (with associated indexes) correspond to each exported Zim EntitySet or data relationship definition. The Zim definitions to be exported are selected from the EntitySets and Relationships and their associated Fields that are in your database. The selected objects to be exported are grouped according to their Remote Owner Name field in EntitySets and Relationships and a complete export cycle is accomplished for each different Remote Owner Name.

In a Zim Client-Server application, all EntitySets and data relationships should have at least one unique index defined on a field or virtual field. Limitations of SQL require that if the unique index is on a virtual field, then the field expression must be

$concat(field1,field2,…)

where “field1”, “field2” are the names of real fields only. They cannot be expressions or other virtual field names. An error occurs during the export process if an attempt is made to export an indexed virtual field whose expression does not abide by this rule. See Exporting Zim Table Definitions for more information on this subject.

To export Zim table definitions to the server, choose the Tools/Export/Objects/To SQL option from the main menu of DC. The SQL Export Definiton window appears. Choose the server to which you wish to export the definitions by selecting it from the “Export selected items to” list box. The names of the EntitySets and data relationships in your Zim database are listed. (If more than 30 table definitions exist, you can scroll forwards and backwards through the table names.) To the left of each table name is a check box. Clear the check box if you do not wish to export the definition to the server. Selecting the “Deselect All” button clears all check boxes and selecting the “Select All” button selects all boxes. Initially, all entries have their check boxes selected.

Also appearing on this window is the Replace Existing Table check box. If this box is selected, Zim attempts to DROP each table on the server before it creates it. Hence, selecting this box unconditionally replaces each existing table definition. By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

The “CREATE TABLE” syntax for a specific SQL server can optionally include additional syntax which relates to the physical storage characteristics of the created table. This syntax varies widely from server to server. If the specification of some or all of the optional physical table characteristic parameters is desired, enter the appropriate syntax in the field “Physical Table Characteristics Syntax:“. The syntax specified is appended to the “CREATE TABLE” statement for each name entered in the form. For example, entering “TABLESPACE ts_account” ensures that the table is created in the “ts_account” tablespace.

Another way to change characteristics is to check the “Modify SQL Definitions” check box. This options brings the resulting SQL definitions through the default text  editor for editing. Edit the definitions you want to apply and save the text. The export then proceeds.

To export those EntitySets and/or data relationships selected, click the “Export” button. The table definitions are translated to the appropriate “CREATE TABLE” syntax for the selected SAM and the output is written to a file. Next, the server parameter dialog box appears. Values for these items are required so that the utility program “SQLEXEC” (which the DC invokes) can access the appropriate database in the SQL server in which to create the tables. SQLEXEC in turn invokes the “Server Interface Module” of the selected SAM. These items are identical to parameters 2 through 7 of the “CONNECT” command. Refer to the Connecting to SQL Servers topic for information on how to supply the correct values for these items. Although these values are used to connect to the SQL database, Zim itself does not need to be connected to the database.

With the appropriate values supplied for these items, the export process proceeds to execute a general purpose SQL statement executor program “SQLEXEC”. SQLEXEC traces the SQL statements that it is executing as well as any error conditions that are detected. It does not stop executing on an error condition. In general, the only error condition that is acceptable is an error from the “DROP TABLE” statement when the table does not already exist. All other error conditions should be investigated. A common error is attempting to create an SQL table with a name beginning with the underscore (“_”) character. This is legal in Zim, but generally not legal in SQL.

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.

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.

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.

The driver provides a login dialog under one of two conditions. The login appears if

• the application using the driver passes it a blank user id or password

• the user id or password variables are not defined in the registry

The User ID and Password passed to the driver from the application override those defined in the registry.

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.

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″*)–