Establishes a connection to Zim Integrated Server or an SQL database.

Syntax

connect to [ “ZIMSERV” | “JDBCSAM” | alias name ] using (database name, SQL server name,

userid, password, server name, domain name)

Comments

All parameters are strings or expressions that produce a string.

In the case of a multiple connection to more than one server possessing the same SAM name, an alias name should be defined in the configuration file “zimalias.zim”.

Example

For user “JOE” with password “PASSWORD” to connect to a Zim server, enter

connect to using (“Inventory”, “”, “JOE”, “PASSWORD”, “Joes_Server”, “LinuxAirport”)

To connect to MySQL (or any other SQL database server), you must use the JDBC SAM:

connect to using (“Sales”, “MySQL”, “TheUser”, “ThePassword”,”5455″)

If connecting to two servers of the same SAM name at the same time, one of them must be defined as an alias

See Also

DISCONNECT

Instructs SQL commands to be generated with the FOR UPDATE OF option.

Syntax

SET FOR UPDATE ON|OFF

Comments

When connected to any SQL servers, the SQL syntax generated to perform operations on the remote server can be controlled to add the FOR UPDATE OF option on the primary key fields to SELECT commands.

The option ON turns this generation on and remains in effect until another command, this time with the option OFF, is issued. Therefore, all SELECT statements generated by Zim after the switch is turned on will have the FOR UPDATE OF clause until the switch is turned off.

This is useful to guarantee that records will be locked for update within the next SQL commands.

This option setting only applies to SELECT statements generated by Zim in order to perform actions on connected SQL servers.

Example 1

SET FOR UPDATE ON
SET SQLTRACE ON
FIND MyEntA
SELECT FieldA FROM MyEntA FOR UPDATE OF FieldA
FIND MyEntB
SELECT FieldB FROM MyEntB FOR UPDATE OF FieldB
SET FOR UPDATE OFF
FIND MyEntA
SELECT FieldA FROM MyEntA

The FIND statements between the ON and OFF settings will be sent to the connected SQL server with the FOR UPDATE OF clauses.

Example 2

The following Zim program shows a small procedure that takes a control number and then adds a record using this control number as a primary key. The numbers at left are for subsequent reference only.

1  Procedure Prog2() Local (vl_number, vl_1)
2  on deadlock
3          if $intransaction=$true
4                      out $concat(“record blocked – “, $trim(vl_1))
5                      halt
6                      goto trans1
7          else
8                      goto previous
9          endif
10 endon
11
12 trans1:
13 transaction
14 let vl_1=”Compute tblControl”
15 find tblControl -> s1
16 let vl_number=(LastNumber+1)
17 out vl_1
18 out vl_number
19 halt
20 let vl_1=”Add tblCust”
21 add tblcust let custcode = vl_number  
22                     custname = vl_number
23 out vl_1
24 halt
25 let vl_1=”Change tblControl”
26 ch tblControl let LastNumber = vl_number
27 out vl_1
28 halt
29 endtransaction
30
31 EndProcedure

Scenario 1

By default, SET FOR UPDATE is OFF. This is the behaviour found in previous versions of Zim.

If two users are running the same program, then:

. User 1 starts Prog2 and stops at line 19: Vl_number is 1 and no locks were applied by Oracle;

. User 2 starts Prog2 and stops at line 19: Vl_number is 1 and no locks were applied by Oracle;

. User 1 continues execution: a record will be added in TblCust and execution stops at line 24;

. User 2 continues execution: it waits because Oracle serializes the ADD statement in TblCust;

. User 1 continues execution: TblControl is updated, Oracle locks TblControl and execution is halted at line 28;

. User 2 is still waiting…

. User 1 continues execution: Oracle releases the lock in TblControl, the transaction is committed and the program ends;

. User 2 now proceeds: Oracle will try to add a record but a duplicated record error is raised.

Scenario 2

SET FOR UPDATE is set to ON either before calling Prog2 or in line 11 of Prog2:

. User 1 starts Prog2 and stops at line 19: Vl_number is 2 (assuming that a record was added in Scenario 1) and Oracle locks TblControl;

. User 2 starts Prog2, but enters in a waiting state at line 15, since Oracle serializes the SELECT statement (the record is locked);

. User 1 continues execution: the record will be added in TblCust, Oracle locks TblCust and the execution stops at line 24;

. User 2 still waiting…

. User 1 continues execution: TblControl is updated, Oracle locks TblControl and execution stops at line 28;

. User 2 still waiting…

. User 1 continues execution: Oracle releases the lock in TblControl, the transaction is committed and the program ends;

. User 2 resumes execution: Oracle retrieves a TblControl record, Vl_number becomes 3 and the transaction continues normally;

Branches to another location in an application program.

Syntax

GOTO labelname

Parameters

Comments

GOTO can be used only to branch forward or backward within the same procedure.

To declare a label, type the labelname, followed by a colon, at the appropriate point in the program. The labelname must be the first word on the line where the label is declared. Labels cannot be declared in or before an exception handler.

Exception Handlers and the GOTO Command

The GOTO command can be used within an exception handler to resume execution at an arbitrary point in the procedure body.

In the following example, when a deadlock occurs, the DEADLOCK handler restarts a transaction by using a GOTO command:

procedure MyProc5(Parm1,Parm2)

   on deadlock

     goto RetryTransaction

   endon

RetryTransaction:

   transaction

   … other commands …

   endtransaction

endprocedure

Note that the GOTO command uses a label name. All labels in a procedure must be declared following the last exception handler declaration. A GOTO cannot be used to branch into an exception handler, and an GOTO command executed in an exception handler must branch out of the exception handler to some point within the body of the procedure.

Any command in a procedure could potentially trigger an exception handler. After an exception condition has been handled, a developer typically wants the application program either

• to retry the command that caused the exception, or
• to skip the command that caused the exception and go on to the next command

One way to implement these requirements is to label each command line, and using the GOTO command, conditionally branch out of the exception handler to the appropriate command line. Alternatively, two implicit GOTO labels can be provided to address these requirements: GOTO PREVIOUS and GOTO NEXT.

The PREVIOUS and NEXT labels are defined dynamically to identify, respectively, the command line that caused the exception and the next command line. Note that the GOTO NEXT has the same effect as ENDON.

The GOTO NEXT and GOTO PREVIOUS commands can be used only within the body of an exception handler.

Example

The GOTO command is used to break out of a WHILE loop when an exit condition has been met.

while Quantity > 0

:

while ProdCode is not $null

:

if Event.EventName = “F3” % This key has been defined

   goto Exit       % as the “exit” key.

endif

:

endwhile

:

endwhile

Exit:

:

The GOTO command is used in an exception handler to restart a transaction that has been aborted as a result of a deadlock condition.

procedure MyProc (Param1, Param2)

 on deadlock

   goto retry_transaction

  endon

retry_transaction:

 transaction

 … other commands…

   endtransaction

endprocedure

The sequence of events is

• a deadlock condition occurs while the application is attempting to execute a transaction
• the ON DEADLOCK exception handler is triggered
• the exception handler executes the GOTO command, that causes the software to skip the retry_transaction label
• the application continues to process the commands that follow the label, effectively restarting the execution of the transaction

See Also

GOTO NEXT

GOTO PREVIOUS

ON

Presents a message box to the application user and waits for a response.

Syntax

$messagebox(message,type,style,defaultbutton[,heading])

Parameters

Message box types include:

Push button styles include:

Return Value

Character string.

Comments

Use $messagebox to display a message in a dialog box of the specified type, with the specified heading or a default heading in the caption. The application user responds by activating one of the buttons provided by style, one of which is a defaultbutton. $Messagebox returns a character string containing the label of the button pressed by the application user.

Example

$messagebox("Delete file?",2,5,2,"File Operations")

Presents the application user with a File Open common dialog box.

Syntax

$filebrowse(directorypath, pattern, flags ,[heading])

Parameters

Return Value

For EntitySets, relationships, and application directories, the value returned is the number associated with the disk file that contains the specified object.

For application documents, the value returned is the number associated with the disk file that contains the application directory in which the application document was created.

Comments

Use $filebrowse to display the File Open common dialog box and select a file.

The value returned by the function depends on the application user’s interaction with the dialog box:

• If the application user activates the Cancel push button, $Null is returned.
• If the application user makes a selection (multiple selections not enabled), the function returns the directory path concatenated with the file name.
• If the application user makes a selection (multiple selections enabled), the function returns the directory path and a list of one or more file names separated by semicolons.

Example

A properly constructed pattern appears as shown below:

'All files |*.*|ZIM files|*.zim;zim0*'

In the following code fragment, the variable vIconFileSel is assigned the results of the user’s interaction with the File Open dialog box:

let vIconFileSel= $filebrowse ($concat($DBPath, "\Icons"), "Icon files|*.ico|All files|*.*", cFileMustExist + cPathMustExist, "Icon File Selection")

Identifies particular records in an SQL database.

Syntax

Object [WHERE clause] [GROUP BY expr1] [HAVING expr2]

[ORDER BY expr3 [ASC|DESC]] [KEEP component]

Parameters

Comments

An SQL set specification (sometimes called an SQLsetspec) is a statement that uses EntitySets, relationships, forms, structured application documents and result sets to identify particular records in an SQL database. Each object in the set specification is called a component. (Result sets can represent more than one component.)
SQL set specifications are used only within the SQL commands DELETE FROM, INSERT, SELECT, and UPDATE.
Any number of objects can be declared. But, if you declare more than one object, then you must declare the relationships through which records in those objects are associated.
An SQL set specification can also include a condition that limits record selection (WHERE clause), a grouping statement (GROUP BY clause), a group condition that limits group selection (HAVING clause), a sorting statement (ORDERED BY clause), and a component projection list that limits the number of components in the resulting set (KEEP clause).
The WHERE clause can also contain another SELECT statement and OUTER JOIN references using “*=” for LEFT OUTER JOIN or “=*” for RIGHT OUTER JOIN (see examples).
Each object can be further qualified using any valid combination of the following subcommands:
-> (Dynamic rename) or USING
and in particular circumstances:
INTERSECT, MINUS, UNION

Example

select * from Employees where LastName=”Smith”

The set specification includes a WHERE condition for record selection.

SELECT * FROM Employees WHERE Salary > (SELECT Salary FROM Employees WHERE JobPosition = “THE BOSS”)

SELECT Ents.EntName,Fields.OwnerName FROM Ents,Fields WHERE Ents.EntName *= Fields.Ownername

This selects all EntitySets and having or not having corresponding Fields in the same way the

COMPLETE clause does in a FIND statement.

SELECT Ents.EntName,Fields.OwnerName FROM Ents,Fields WHERE Ents.EntName *= Fields.Ownername AND

Fields.Ownername is $NULL

This selects all EntitySets not having corresponding Fields in the same way the UNRELATED

clause does in a FIND statement.

See Also

DELETE FROM

INSERT

SELECT

Set Specification

UPDATE

SET (EntitySet/Data Relationship Attribute)

Modifies the value of an attribute of a database object.

Syntax

SET tablename tabletype [ “ZIMSERV” | “JDBCSAM” | alias name ]

SET tablename [ remname |  remowner ] value

SET fieldname [ remname ] value

Comments

These two forms of the SET command modify attributes of EntitySets, data relationships, and fields that affect client-server behavior.

These commands affect the internal value of these attributes. They do not update the external data dictionary. It is prudent to modify the data dictionary to conform to any new attribute values that are set. The utility ZIMFILES can be used to display internal values of any database object.

When modifying TABLETYPE, the value must be a valid SAM name or custom server name. This is equivalent to modifying EntType in EntitySets or RelType in Relationships and then performing “erase” and “create” commands on the EntitySet or data relationship.

Changing the REMNAME or REMOWNER to the value null string (”) removes any Remote Name or Remote Owner Name associated with the database object.

The SET REMOWNER command is equivalent to changing the “RemoteOwner” in “EntitySets” or “Relationships” and then recreating the EntitySet or relationship.

The SET REMNAME command is equivalent to changing the “RemoteName” in “EntitySets” or “Relationships” and then recreating the EntitySet or relationship.

Any compiled Zim programs that reference database objects that have been modified with these commands should be recompiled.

Example 1

To modify the EntitySet Customers so that it is under the control of an Oracle SQL server, enter

> set Customers tabletype 'jdbcsam'
> set MyEnt tabletype 'zimserv'

Example 2

To modify the Remote Name of the field ZIP in EntitySet customers, enter

> set Customers.ZIP remname '"ZIP or Postal Code"'

When modifying the attribute of a field, qualify it with its entity set or relationship name to avoid ambiguity. Note that double quotation marks are included in the REMNAME value. Most database engines require double quotation marks around the column name if it contains embedded blanks.

Example 3

To remove the Remote Name of the field ZIP in EntitySet customers that was added above, enter

> set Customers.ZIP remname ''

See Also

Remote Name Mapping

Selects data for a report and specifies overall report format.

Syntax

REPORT [num] FROM [setspec] [GRAPHIC [TEMPLATE name]] [orientation] [PAUSE n]

[format options] [-> clause]

Parameters

Comments

The REPORT FROM command produces a set whose members are used as the source of data values for the report. The various clauses in the REPORT FROM command also specify the overall format of the report and determine if the report is line-oriented or column-oriented.

The format option PAGELAYOUT is only valid for graphical reports and specifies the layout of the page to be used for displaying or printing the report. is an expression that evaluates to a string containing eight parameters separated by semicolons like this example:

REPORT FROM Customers GRAPHIC TEMPLATE tCust PAGELAYOUT “2140;1950;1;1;0;0;0;0”
Other report commands…
ENDREPORT

The values in PAGELAYOUT mean (in tenths of millimeters): Height of the page; Width of the page; Number of Rows (for labels); Number of Columns (for labels); Top Margin; Left margin; Vertical distance between labels; Horizontal Distance between labels.

See Also

BREAK (Reports)

ENDREPORT

How to Use The Report Generator

Report Item Format Options

Returns the current settings of certain device or registry properties.

Remarks

The function $getproperty always returns a 260-character string aligned to the left. If needed to handle numeric values, a $trim function needs to be used to remove all trailing blanks thus avoiding conversion errors.

Syntax #1

Returns the current settings of certain desktop properties.

$getproperty("desktop", property)

Parameters

Return Value

Character string or $null if property is not a recognized string.

Syntax #2

Returns the current settings of certain printer properties in the Windows environment.

$getproperty("printer", property)

Parameters

Return Value

Character string or $null if property is not a recognized string.

Comments

The string contains the current setting of property.

Syntax #3

Returns the current settings of the Zim variables stored in the Windows Registry.

$getproperty (registry_section, registry_variable)

Parameters

Return Value

Character string or $null if the section or the variable is not present in the Registry.

Syntax #4

$GetProperty ("session", <config_setting>)

Parameters

Return Value

The return value is the current value of the specified config_setting.

Syntax #5

$GetProperty ("OleVerbList", FileOrClassName)

Parameters

Return Value

The return value is the list of supported verbnames of a file or OLE class. Semicolons separate the verbs in the returned list.

Examples

>out $getproperty ("OleVerbList", "ztizim.bmp")
Edit;Open

In the above command, the OleVerbList associated with a .bmp file is “Edit;Open” which means that there are two available verbs: Edit and Open.

let vFontSize = $getproperty ("printer", "fontsize")

In the above command, vFontSize can be a character or number variable. (The software converts the character string returned by $getproperty to a number, if required.)

let vODBCVersion = $getproperty ("REG:[ODBC]", "VERSION")

Gets the version of the installed Zim ODBC Driver.

See Also

$printersetup
$setproperty

Creates an object in an application directory.

Syntax #1

Creates an object in an application directory.

CREATE object name [IN dirname][$fn <filenumber>] [DELETE | KEEP]

CREATE object name [IN dirname]

Parameters

Syntax #2

Creates an index.

CREATE INDEX ownername.fieldname [UNIQUE]

Syntax #3

Creates a field.

CREATE FIELD ownername fieldname

Parameters

Comments

The application directory named in a CREATE command must have been accessed in update mode (see ACCESS) and all information needed to define the object must exist in the Object Dictionary. For CREATE to proceed, the explicit dirname in the CREATE command (or the implicit directory zim) must match the owner-directory specified for object in the Object Dictionary; or, there must be no owner-directory specified for object in the Object Dictionary. In the latter case, object can be created in any or in every application directory available in the database (except foreign directories).
A field or form field cannot have the same name as an existing variable, EntitySet, relationship, role, directory, application document, window, constant, result set, form, menu, or display in the same application directory. Several fields or several form fields can have identical names, provided that each identically named field belongs to a different ownername.
There is an internal limit of 65000 CREATEs per Zim directory in Zim.  If this limit is exceeded, a 3029 error is generated. For more information, see Zim Error Codes.  To resolve this error, export all objects and import these objects into a newly initialized Zim database.  In a newly initialized database, the CREATE counter is reset to 0.

Example

create entityset Employees in Personnel

Creates an EntitySet called Employees in the application directory Personnel.

create index Employees.FirstName

Creates an index for an un-indexed field FirstName in the Employees EntitySet.

See Also

ERASE

PERMISSION

RENAME