Use a WHILE statement is used to control flow. It allows code to be executed repeatedly as long as the WHILE condition is true. Optionally, you may use the WHILE statement with DO and END WHILE.

A WHILE statement can have a beginning label, in which case it is called a labeled WHILE statement.

The following example increments the variable vInteger by 1 until it reaches a value of 10, when the loop ends.

The following is an alphabetical list of element definitions used in the grammar syntax.

SqlStatementList is defined as:

A predicate is defined as:

An expression is defined as:

An expression list is defined as:

Zen supports the following global variables:

Global variables are prefaced with two at signs, @@. All global variables are variables per connection. Each database connection has its own @@IDENTITY, @@BIGIDENTITY, @@ROWCOUNT, and @@SPID values. The value of @@VERSION is information about the version of the engine that executes the statement where it is used.

Either of these variables returns its most recently inserted column value. The value is a signed integer value. The initial value is NULL.

The variable can refer to only a single column. If the target table includes more than one IDENTITY column, the value of this variable refers to the IDENTITY column serving as the table primary key. If no such column exists, then the value of this variable refers to the first IDENTITY column in the table.

If the most recent insert was to a table without an IDENTITY column, then the value of @@IDENTITY is set to NULL.

For BIGIDENTITY values, use @@BIGIDENTITY. For SMALLIDENTITY values, use @@IDENTITY.

Returns NULL if no records have been inserted in the current connection, otherwise returns the IDENTITY column value of the most recently inserted row.

Returns the most recently inserted row if it has an IDENTITY column value of 12. Otherwise, returns no rows.

Inserts the IDENTITY value of the last row inserted into column C2 of the new row.

Updates column C1 with the IDENTITY value of the last row inserted, if the value of C1 is 10 greater than the IDENTITY column value of the last row inserted.

Updates column C1 with the value NULL if the value of C1 equals the IDENTITY column value of the last row inserted.

The example below creates a stored procedure and calls it. The procedure sets variable V1 equal to the sum of the input value and the IDENTITY column value of the last row updated. The procedure then deletes rows from the table anywhere column C1 equals V1. The procedure then prints a message stating how many rows were deleted.

This variable returns the number of rows that were affected by the most recent operation in the current connection. The value is an unsigned integer. The initial value is zero.

The @@ROWCOUNT variable is valid only when used after an INSERT, UPDATE, or DELETE statement.

Returns zero if no records were affected by the previous operation in the current connection, otherwise returns the number of rows affected by the previous operation.

Results:

In line four, the @@ROWCOUNT variable has the value of 1 because the previous INSERT operation affected one row.

Also see the example for @@IDENTITY.

This variable returns an eight-byte integer value that identifies the connection to a Zen server engine, reporting engine, or workgroup engine. The integer is a combination of a time value and an incremental counter. This variable can be used to identify uniquely each Zen connection.

@@SESSIONID requires a connection to the database engine to return a value. If the connection to the database engine is lost, the variable cannot return an identifier.

The example returns an integer identifier such as 26552653137523.

This variable, the server process identifier, returns the identifier integer value of the system thread for the Zen connection.

If the connection to the database engine is lost, the SPID variable cannot return an identifier. Instead, the statement returns an error with a SqlState of 08S01.

The example returns an integer identifier, such as 402.

SQL statements that use this variable return a value based on the Zen server engine, reporting engine, or workgroup engine to which the session is connected.

The example returns text information resembling the following:

This topic describes other characteristics of the SQL grammar. It is divided into the following sections:

When Zen must generate a temporary table in order to process a given query, it creates the file in a location determined in one of the following ways:

Zen deletes all temporary files used to process a query after the query is finished. If the query is a SELECT statement, then the temporary files exist as long as the result set is active, meaning until the result set is freed by the calling application.

Zen uses three types of temporary files: in-memory, on-disk, and Btrieve (MicroKernel Engine).

In-memory temporary files are used for the following circumstances:

On-disk temporary files are used for the following circumstances:

Btrieve temporary files are used for the following circumstances:

Zen does not create a Btrieve temporary file for each base table in a static cursor SELECT query. Instead, each base table is opened by using the MicroKernel to reserve pages in the file as a static representation of the file. Any change made through a static cursor cannot be seen by that cursor.

Zen interprets a NULL as an unknown value. Thus, if you try to compare two NULL values, they will compare as not equal.

An expression that evaluates to WHERE NULL=NULL returns FALSE.

Consider the following scenario: you insert the literal value '1' into a BINARY(4) column named c1, in table t1. Next, you enter the statement, SELECT * FROM t1 WHERE c1='1'.

The engine can retrieve data using the same binary format as was used to input the data. That is, the SELECT example above works properly and returns the value, 0x01000000, even though there is no literal match.

This behavior is the same as for Microsoft SQL Server.

The maximum column size for indexable VARCHAR columns is 254 bytes if the column does not allow Null values and 253 bytes if the column is nullable.

The maximum column size for CHAR columns is 255 bytes if the column does not allow Null values and 254 bytes if the column is nullable.

The maximum column size for indexable NVARCHAR columns is NVARCHAR(126). This limit applies to both nullable and not-null columns. The NVARCHAR size is specified in UCS-2 character units.

The maximum column size for NCHAR columns is NCHAR(127). This limit applies to both nullable and not-null columns. The NCHAR size is specified in UCS-2 character units.

The maximum Btrieve key size is 255. When a column is nullable and indexed a segmented key is created with 1 byte for the null indicator and a maximum 254 bytes from the column indexed. VARCHAR columns differ from CHAR columns in that either length byte (Btrieve lstring) or a zero terminating byte (Btrieve zstring) are reserved, increasing the effective storage by 1 byte. NVARCHAR (Btrieve wzstring) columns differ from NCHAR columns in that a zero terminating character is reserved, increasing the effective storage by 2 bytes.

Many locales use a comma to separate whole numbers from fractional numbers within a floating point numeric field. For example, they would use 1,5 instead of 1.5 to represent the number one-and-one-half.

Zen supports both the period and the comma as decimal separators. Zen accepts input values using the period or the comma, based on the regional settings for the operating system. By default, the database engine displays values using the period.

For output and display only, the session-level command SET DECIMALSEPARATORCOMMA can be used to specify output (for example, SELECT results) that uses the comma as the decimal separator. This command has no effect on data entry or storage.

Support for the comma as decimal separator is based on the locale setting in the operating system. Both the client operating system and the server operating system have a locale setting. The expected behavior varies according to both settings.

Decimal separator information can be retrieved or changed only for a machine running a Windows operating system. The decimal setting for Linux and macOS cannot be configured, and it is set to a period. If you have a Linux and macOS server engine and you want to use a comma as decimal separator, you must ensure that all your client computers are set to a locale that uses the decimal separator.

To change the regional settings on a Windows operating system, access the settings from the Control Panel. Stop and restart Zen services after your change to enable the database engine to use the setting.

Client locale uses a comma as decimal separator:

The above two SELECT statements, if executed from the client, return:

The above SELECT statement, if executed from the client after setting the decimal separator, returns:

Client locale uses period as decimal separator, and these statements are issued from a new connection (meaning default behavior for SET DECIMALSEPARATORCOMMA):

The above two SELECT statements, if executed from the client, return:

Client locale uses comma as decimal separator:

Client locale uses period as decimal separator:

The first select statement above, if executed from the client, returns:

The above SELECT statement, if executed after setting the decimal separator for display, returns: