ESQL in C/C++ Programs

---

Mimer SQL supports ESQL for C/C++ following the ISO/ANSI standard.

SQL Statement Format

The following sections discuss the SQL statement format.

Statement Delimiters

SQL statements are identified by the leading delimiter EXEC SQL and terminated by a semicolon ;, for example:

 EXEC SQL DELETE FROM countries;

Line Continuation

Line continuation rules for SQL statements are the same as those for ordinary C statements.

For a string constant, a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), can be used to join two or more sub-strings. Each substring must be separately enclosed in delimiters. For example:

 EXEC SQL COMMENT ON TABLE currencies IS 'Holds currency'<CR>
                                         ' details';

Comments

Comments, from // to end-of-line or enclosed between the markers /* and */, may be written anywhere within SQL statements where a white-space is permitted, except between the keywords EXEC and SQL and within string constants. The comment may replace the white-space, for example:

 EXEC SQL DELETE/* all rows */FROM countries;

Special Characters

The delimiters in SQL are single quotation marks ' for string constants and double quotation marks " " for delimited identifiers. This is contrary to the C string delimiter usage.

A white-space character separates keywords.

Host Variables in C/C++

The following sections discuss declarations, SQL data type correspondence and value assignments.

Declarations

Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:

• host variables may be of AUTO, EXTERN or STATIC class
• array variables are not permitted with the exception of character arrays
• character arrays are interpreted as null terminated strings
• the VARCHAR host variable data type is recognized by the ESQL/C preprocessor and should be used when variable-length character data is to be returned from SQL as a null terminated string without any blank padding (the VARCHAR host variable should be declared with a length one greater than the length of the column, because of the null termination).
• the NCHAR host variable data type is recognized by the ESQL/C preprocessor and should be used when Unicode data is to be returned from SQL as a null terminated string with blank padding. The NCHAR host variable should be declared with a length one greater than the length of the column, because of the null termination.
• the NCHAR VARYING host variable data type is recognized by the ESQL/C preprocessor and should be used when variable-length Unicode data is to be returned from SQL as a null terminated string without any blank padding. The NCHAR VARYING host variable should be declared with a length one greater than the length of the column, because of the null termination.
• where binary data is stored in a character array, the size of the array must match the length of the binary data exactly because binary data is not terminated and therefore all array elements are significant
• variable names are case significant
• indicator variables should be declared as short or int
• SQLSTATE should be declared as char[6] or VARCHAR[6]
• Only data types CHAR, VARCHAR, NCHAR, NCHAR VARYING, BLOB, CLOB and NCLOB can be indexed.

When reading any character array host variable, declared as CHAR, VARCHAR, NCHAR or NCHAR VARYING, the contents of the variable must be null terminated. When a host variable declared as CHAR or NCHAR is read, its value is blank padded to the same length as the host variable. When a host variable declared as VARCHAR or NCHAR VARYING is read, no blank padding is performed.

When any type conversion is done when retrieving a numeric value to a fixed length character host variable, i.e. CHAR or NCHAR, the data will be right justified. When type conversion is done when retrieving a value to a variable length character type host variable, i.e. VARCHAR or NCHAR VARYING, the data will be left justified.

A syntax diagram showing the variable declarations recognized by the ESQL/C preprocessor is given below:

where character-declaration is:

and numeric-declaration is:

and lob-declaration is:

The following points should be noted:

• In accordance with the syntax rules of C, keywords are case-sensitive and are given in the required case in the syntax diagram. This deviates from the general practice in Mimer SQL documentation of using upper-case to denote keywords
• Index must be a number which is 1 or greater

SQL Data Type Correspondence

Valid host data types are listed below for each of the data types used in SQL statements.

SQL data type	C variable declaration
SMALLINT INTEGER BIGINT	short int long long long
DECIMAL NUMERIC	float double long double
FLOAT REAL DOUBLE PRECISION	float double long double
CHARACTER VARCHAR DATETIME INTERVAL BINARY BINARY VARYING	char varchar 1
NCHAR NCHAR VARYING	nchar 2 wchar_t nchar varying3 varwchar_t4
BLOB	sql type is blob 5
CLOB	sql type is clob 6
NCLOB	sql type is nclob 7

1The varchar host variable type is recognized by the ESQL/C preprocessor and converted to the char data type in C. 2The nchar host variable type is recognized by the ESQL/C preprocessor and converted to the wchar_t data type in C. 3The nchar varying host variable type is recognized by the ESQL/C preprocessor and converted to the wchar_t data type in C. 4The varwchar_t host variable type is recognized by the ESQL/C preprocessor and converted to the wchar_t data type in C. 5The blob host variable type is recognized by the ESQL/C preprocessor and converted to: 5struct { long hvn_reserved; unsigned long hvn_length; char hvn_data[L]; } hvn 5 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the lob-declaration. 6The clob host variable type is recognized by the ESQL/C preprocessor and converted to: 6struct { long hvn_reserved; unsigned long hvn_length; char hvn_data[L]; } hvn 6 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the lob-declaration. 7The nclob host variable type is recognized by the ESQL/C preprocessor and converted to: 7struct { long hvn_reserved; unsigned long hvn_length; wchar_t hvn_data[L]; } hvn 7 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the nclob-declaration

Note: Your C compiler may not support all of these possible declarations.

Value Assignments

The general rules for conversion of values between compatible but different data types, see the Mimer SQL Reference Manual, SQL Syntax Elements, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.

When reading any character array host variable, the contents of the variable must be null terminated. When a fixed length character host variable (char, nchar) is read, its value is blank padded to the same length as the host variable. When a variable length character host variable (varchar, nchar varying) is read, no blank padding is performed.

When retrieving a value shorter than a fixed length character array host variable, the host variable will be padded with blanks (and null terminated). When retrieving a value to a variable length character host variable, the variable will not be blank padded, just null terminated.

When retrieving binary data into a character array there is no padding or termination of the binary string, so all the character array elements have significance. The character array must, therefore, be declared with exactly the same length as the binary data.

If a numeric type conversion is done when retrieving a value to a fixed length character host variable, the data will be right justified. When type conversion is done when retrieving a value to a variable length character host variable, the data will be left justified.

Example:

 char cstr[9];
 VARCHAR vstr[9];
 

retrieving the value 'abc ' will give the following result:

 cstr = 'abc     ' /* blankpadded to eight characters */
 vstr = 'abc '     /* the same length as the value */
 

retrieving the value 123, the values will be as:

 cstr = '     123' /* right justified */
 vstr = '123'      /* left justified */
 

See the Mimer SQL Reference Manual, Special Characters, for a further discussion of different character string assignments.

Preprocessor Output Format

Output from the ESQL/C preprocessor retains SQL statements from the original source code as comments. Comments on the same line as SQL statements are retained as `comments within comments', marked by the delimiters /+ and +/.

The preprocessed code is structured to reflect the structuring of the original source code.

The use of the #line directive will ensure that any information from the C compiler will correctly reference line numbers in the original source code. It will also help a debugger correctly coordinate display of source lines in the original source file and the generated C file. Refer to information on running ESQL for the platform you are using for details on how to get #line directives.

Scope Rules

Host variables follow the same scope rules as ordinary variables in C. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for C is the same as a file (including included files).

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se