Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Embedded SQL in C/C++ Programs


Mimer SQL supports embedded SQL for C/C++ following the 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 (;).

Example:
 exec sql DELETE FROM HOTEL;

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.

Examples:
 exec sql SELECT HOTELCODE, ROOMNO
            FROM BOOK_GUEST
            WHERE RESERVATION = :CUSTNO;
 
 exec sql COMMENT ON TABLE ROOM_PRICES IS
            'Prices apply from date given'<LF>
            ' in column FROM_DATE';

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 HOTEL;

Special Characters

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

Keywords are separated by a white-space character.

Host Variables

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:

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

where character-declaration is:

and numeric-declaration is:

The following points should be noted:

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
DECIMAL
NUMERIC
float
double
long double
FLOAT
REAL
DOUBLE PRECISION
float
double
long double
CHARACTER
VARCHAR
DATETIME
INTERVAL
BINARY
BINARY VARYING
char
VARCHAR 1
1
The VARCHAR host variable type is recognized by the ESQL/C preprocessor and converted to the char data type in C.

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 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, declared as char or VARCHAR, the contents of the variable must be terminated by a null byte. When a host variable declared as char is read, its value is blank padded to the same length as the host variable. When a host variable declared as VARCHAR is read, no blank padding is performed.

When retrieving a value shorter than the character array host variable (declared as char), the host variable will be padded with blanks (and terminated with a null byte). When retrieving a value to a VARCHAR host variable, the variable will not be blank padded, just terminated by a null byte.

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.

When any type conversion is done when retrieving a value to a character host variable, the data will be right justified. When type conversion is done when retrieving a value to a VARCHAR 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 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).



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX