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 SQLSTATE
• through the diagnostics area, accessed by the SQL statement GET DIAGNOSTICS
• 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, 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[129],
         passw[19];
 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 COUNT(*)
             INTO :VAR
             FROM table
             WHERE condition;
 
 if VAR < LIMIT then ...

Indicator Variables

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, SET 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.

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 CHAR, VARCHAR or CLOB columns, or in NCHAR, NVARCHAR or NCLOB columns. Data in CHAR, VARCHAR and CLOB columns 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 NCHAR, NVARCHAR or NCLOB must 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 NCHAR, NVARCHAR, or NCLOB column 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 CHAR or NCHAR column 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 wchar_t rather than char).

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 CHAR to NCHAR, or from VARCHAR to NVARCHAR. This is done with the ALTER TABLE statement (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 (Unix, Mac OS X, others) the application must call the runtime library routine setlocale to 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 setlocale a 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_LOCALE that will be used when calling the Mimer client. The value of the environment variable is used as the second argument to setlocale.

To use the default locale set MIMER_LOCALE to 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 Unix platforms the prevalent representation is UTF-8 that allows any Unicode character to be stored in a character variable.

The SQLSTATE Variable

The SQLSTATE variable provides the application, in a standardized way, with return code information about the most recently executed SQL statement.

SQLSTATE must be declared between the BEGIN DECLARE SECTION and 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 SQLSTATE can contain digits and capital letters.

SQLSTATE consists 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 SQLSTATE according to the following:

SQLSTATE Class	Result category
00	Success
01	Success with warning
02	No data
Other	Error

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 DIAGNOSTICS statement. The syntax for GET DIAGNOSTICS (including a description of the diagnostics area) is in the Mimer SQL Reference Manual.

The GET DIAGNOSTICS statement does not change the contents of the diagnostics area, except it does set SQLSTATE.

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 info@mimer.se