http://developer.mimer.com

Variables in BSQL

---

Host variables are used in ESQL statements to pass values between the database and an application program, see the Mimer SQL Programmer's Manual.

Host variables are also supported in BSQL to facilitate interactive design and testing of SQL statements intended for use in ESQL application programs.

In BSQL, host variables serve as parameter markers, and the user is prompted for parameter values when the statement is executed.

You can use host variables used to:

• assign values to columns in the database (UPDATE and INSERT statements)
• to manipulate information taken from the database or contained in other variables (in expressions)
• to provide values for comparison predicates.

In all these contexts, the data type and length of the host variable must be compatible with that of any database values within the same syntax unit.

Writing Host Variables in SQL

Host variables are written in SQL as:

 :host-identifier
 

or

 :host-identifier :indicator-identifier
 

or

 :host-identifier INDICATOR :indicator-identifier
 

In the first construction, the host identifier is the name of the main host variable.

In the second and third constructions, the main variable host-identifier is associated with an indicator variable indicator-identifier, used to signal the assignment of a NULL value to the main variable.

See the Mimer SQL Programmer's Manual for a description of the use of indicator variables.

Scope of Host Variables

The scope of host variables in BSQL is restricted to the individual usage instance in each statement.

Variables may not be used to pass values between separate statements, and the same variable name used more than once in a statement represents separate, independent variables.

Using Host Variables

When host variables are used in BSQL, BSQL prompts for the variable values, for example:

 SQL>SELECT * FROM HOTEL WHERE CITY = :CITY;
 CITY: STOCKHOLM
 

This statement is then executed as:

 SQL>SELECT * FROM HOTEL WHERE CITY = 'STOCKHOLM';
 

Note: The entered variable is not enclosed between single quotation marks, in contrast to the corresponding string value.
Variables enclosed in single quotation marks will be interpreted as literal strings.

Including Indicator Variables

If an indicator variable is included, you will be prompted for whether to use a NULL value.

If you answer the prompt with No, you will then be prompted for a value.

If you answer Yes, the NULL value will be used.

 For example:
 SQL>UPDATE BOOK_GUEST SET ARRIVE    = :ARRIVE:NULL,
 SQL>                      DEPART    = :DEPART:NULL
 SQL>  WHERE RESERVATION = 1348;
 Null:N
 ARRIVE: 2001-04-23
 Null:Y
 

Note: The prompts appear in the order in which the variables are used in the statement. In the example above, the ARRIVE value will be updated to 2001-04-23 and the DEPART value will be set to NULL.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se