Using Host Variables
Host variables are used in SQL statements to pass values between the database and the application program.
Declaring Host Variables
All variables used in SQL statements must be declared for the preprocessor, by enclosing the variable declarations between the SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION.
Any variables declared outside the SQL DECLARE SECTION will not be recognized by the preprocessor.
Variables are declared within the section using the normal host language syntax.
For instance, the following example in C declares only the character variables user and passw for use in SQL statements:int rc, pf, cnt; exec sql BEGIN DECLARE SECTION; char user, passw; exec sql END DECLARE SECTION;
Variables which are not used in SQL statements may also be declared in the SQL DECLARE SECTION. This will however extend the symbol table established by the preprocessor more than is necessary.
The use of array variables is currently not supported in embedded Mimer SQL (except for character string variables).
Using Variables in Statements
Host variables may be used:
- to receive information from the database (SELECT INTO, FETCH, CALL and SET statements)
- to assign values to columns in the database (CALL, INSERT and UPDATE statements)
- to manipulate information taken from the database or contained in other variables (in expressions)
- to get descriptor and diagnostics information (GET DESCRIPTOR, SET DESCRIPTOR and GET DIAGNOSTICS)
- in dynamic SQL statements.
In all these contexts, the data type of the host variable or database column must be compatible with the data type of the corresponding database value or host variable. General considerations of data type compatibility may be found in the Mimer SQL Reference Manual. Host language specific aspects are described in Host Language Dependent Aspects of this manual.
If you have an INTEGER column containing values that do not fit into the largest integer variable allowed on your machine (remember that Mimer SQL supports INTEGER values with a precision of up to 45 digits), you can, for example, use a character string or float host variable for that column. In this case, Mimer SQL automatically performs the necessary conversions.
Host variables are preceded by a colon when used in SQL statements, see the Mimer SQL Reference Manual, Host Identifiers.
Note: The colon is not part of the host variable, and should not be used when the variable is referenced in host language statements.
Example:EXEC SQL SELECT column INTO :VAR FROM table WHERE condition; if VAR < LIMIT then ...
In ESQL, indicator variables associated with main variables are used to handle NULL values in database tables.
Indicator variables should be an exact numeric data type with scale zero and are declared in the same way as main variables in the SQL DECLARE SECTION.
See Declarations for a description of how main and indicator variables should be declared in the specific host languages.
Indicator variables are used in SQL statements by either specifying the name of the indicator variable, preceded by a colon, after the main variable name or by using the keyword INDICATOR, for example::main_variable :indicator_variable
or:main_variable INDICATOR :indicator_variable
Transfer from Tables to Host Variables
When a NULL value is retrieved into a host variable by a FETCH, SELECT INTO, EXECUTE or CALL statement, the value of the main variable is undefined and the value of the indicator variable is set to -1.
An error occurs if the main variable is not associated with an indicator variable in the SQL statement. It is therefore recommended as a precaution that indicator variables are used for all columns which are not defined as NOT NULL in the database.
An indicator variable should always be used when a host variable is used for a routine parameter with mode OUT or INOUT because a NULL value can always be returned via a routine parameter.
When a non-null value is assigned to a main variable associated with an indicator variable, the indicator variable is set to zero or a positive value. A positive value indicates that the value assigned to a main character variable was truncated, and gives the length of the original value before truncation.
Transfer from Host Variables to Tables
When the host variable associated with an indicator variable is used to assign a value to a column, the value assigned is NULL if the value of the indicator variable is set to -1.
In such a case, the value of the main variable is irrelevant. If the indicator variable has a value of zero or a positive value, or if the main variable is not associated with an indicator variable, the value of the main variable itself is assigned to the column.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40