Preparing the Database for ODBC Access

Start up the Development Center (DC) and click Tools -> Remote Access -> Prepare Database.

This action runs a script that populates the metadata entities that are used by non-Zim applications to acquire EntitySet information.

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.

Even though per user entity sets are private and only seen by the user that created them, they can still be available to an ODBC Driver connection by means of the Dynamic Creation of a Data Source, which informs the work path that contains the per user entity sets. When the third party software connects to the corresponding Zim database, the per user entity sets are handled in the proper way as though they were local to this connection.

In order for the ODBC Driver connection to properly “see” the data in the per user entity sets, the third party software must be invoked within the Zim session that created the per user entity sets and their corresponding data.

Invalid results will be achieved if the ODBC Driver connection is established:

. before starting the Zim session that will create the data and the proper connection;

. before creating the data, but within the Zim session;

. after ending the Zim session that created the data and the connection.

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