Communicating with the Application Program
Information is transferred between the embedded SQL (ESQL) application program and the Mimer SQL database manager in four ways:
- through host variables used in SQL statements
- through the status variable
- through the diagnostics area, accessed by the SQL statement
- through an SQL descriptor area.
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. This is done by enclosing the variable declarations between the SQL statements
BEGIN DECLARE SECTIONand
END DECLARE SECTION.
The following example in C declares the character variables
passwfor use in SQL statements:int rc, pf, cnt; exec sql BEGIN DECLARE SECTION; char user, passw; exec sql END DECLARE SECTION;
Any variables declared outside the
SQL DECLARE SECTIONwill not be recognized by the preprocessor.
Variables are declared within the section using the normal host language syntax.
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 (
- to assign values to columns in the database (
- to manipulate information taken from the database or contained in other variables (in expressions)
- to get descriptor and diagnostics information (
- 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
INTEGERcolumn containing values that do not fit into the largest integer variable allowed on your machine (remember that Mimer SQL supports
INTEGERvalues 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.
ExampleEXEC SQL SELECT COUNT(*) 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
CALLstatement, the value of the main variable is undefined and the value of the indicator variable is set to
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 NULLin the database.
An indicator variable should always be used when a host variable is used for a routine parameter with mode
INOUTbecause 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
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.
External Character Set Support
The handling of the single byte character data types follows the current locale setting on the machine to determine what characters are stored/retrieved when an embedded SQL application passes single-byte character strings to the Mimer client.
When character data is stored in Mimer SQL it can be stored in
CLOBcolumns, or in
NCLOBcolumns. Data in
CLOBcolumns use the Latin-1 character representation (also called ISO 8859-1). This character set can only be used to store 256 different characters. For the exact characters that can be stored see Mimer SQL Reference Manual, Appendix B, Character Sets. To store any other characters the data type
NCLOBmust be used. These column types can store any character.
If a locale is used by the application that has characters that are not included in Latin-1, it means that the columns in the database data must use an
NCLOBcolumn to store the correct characters. With the locale support the Mimer SQL client understands the representation of the characters in the application and maps them accordingly to its internal representation.
When retrieving data from the database, the translation work the other way. I.e. when retrieving data from a
NCHARcolumn to a single-byte character variable, the current locale must be able to represent all the characters returned from the database. When this is not possible, a conversion error -10401 is returned. If characters stored in the database have no representation in the chosen locale, a wide character data type must be used by the application instead (e.g. the C type
This means that applications using older versions of Mimer may have to be updated to work with the new version. Typically the data type used in the database is altered from
NCHAR, or from
NVARCHAR. This is done with the
ALTER TABLEstatement (see Mimer SQL Reference Manual, ALTER TABLE). Other possible changes is to switch from a character representation (e.g.
char) to a Unicode representation (e.g.
wchar_t) for the application variables, or to switch to a locale that can handle all relevant characters.
On Windows the setting used for the external character set is set in the Regional and Language Options in the Control Panel under the tab Advanced. This setting is used automatically by the Mimer client.
On VMS the system continues to use the Latin-1 character representation regardless of locale settings.
On other platforms (Linux, Mac OS X, others) the application must call the runtime library routine
setlocaleto pick the locale to use. For example, the call
setlocale(LC_CTYPE, "")sets the default locale as decided by the environment setting. The actual conversions made by the Mimer client are through the library routines
mbstowcs(multibyte character set to wide char set) and
wcstombs. Please note that if an application does not call
setlocalea default 7-bit locale is used. This means that no 8-bit characters can be used without getting a conversion error. For applications where the source is not available it is possible to set an environment variable
MIMER_LOCALEthat will be used when calling the Mimer client. The value of the environment variable is used as the second argument to
To use the default locale set
current. On Windows the environment variable is set to the desired code page, i.e. only numeric values may be specified (for example: 1250: ANSI Central Europe, 1251: ANSI Cyrillic, 1252: Latin1, 1253: ANSI Greek, 1254: ANSI Turkish, and so on.)
The fact that the character type is considered a multi-byte character set allows any external character representation to be used. In particular various character sets such as Traditional Chinese Big5 and Japanese Shift-JIS may be used. The character set may, of course, be a single byte character set as such as the Greek Latin-7 character set (code page 1253 on Windows). On Linux platforms the prevalent representation is UTF-8 that allows any Unicode character to be stored in a character variable.
The SQLSTATE Variable
SQLSTATEvariable provides the application, in a standardized way, with return code information about the most recently executed SQL statement.
SQLSTATEmust be declared between the
BEGIN DECLARE SECTIONand the
END DECLARE SECTION(i.e. in the SQL declare section), as a 5 character long string (excluding any terminating null byte).
The return codes provided by
SQLSTATEcan contain digits and capital letters.
SQLSTATEconsists of two fields. The first two characters of SQLSTATE indicates a class, and the following three characters indicates a subclass. Class codes are unique, but subclass codes are not. The meaning of a subclass code depends on the associated class code.
To determine the category of the result of an SQL statement, the application can test the class of
SQLSTATEaccording to the following:
For a list of SQLSTATE values, see Return Codes.
The Diagnostics Area
The diagnostics area holds status information for the most recently executed SQL statement.
There is always one diagnostics area for an application, no matter how many connections the application holds.
Information from the diagnostics area is selected and retrieved by the
GET DIAGNOSTICSstatement. The syntax for
GET DIAGNOSTICS(including a description of the diagnostics area) is described in Mimer SQL Reference Manual, GET DIAGNOSTICS.
GET DIAGNOSTICSstatement does not change the contents of the diagnostics area, except it does set
The SQL Descriptor Area
An SQL descriptor area is used to hold data and descriptive information required for execution of dynamic SQL statements. SQL descriptor areas are allocated and maintained by ESQL statements, described in the Mimer SQL Reference Manual.
The SQL descriptor area is discussed in detail in SQL Descriptor Area.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40