Exporting Zim Definitions to SQL

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.