Host Language Dependent Aspects

You can use embedded SQL (ESQL) statements in any of the following host languages:

•C/C++

•COBOL

•Fortran

Note:It is not a complete description of the rules for writing ESQL programs. The programmer should use the main body of this manual as a guide to writing programs, and refer to this appendix for language-specific details.

The following topics are discussed for each language:

•SQL statement format: delimiters, margins, line continuation, comments, special characters.

•Restrictions.

•Host variables - declarations, SQL data type correspondence, value assignment rules.

•Preprocessor output format.

•Scope rules.

This appendix describes features of ESQL that differ between the respective host languages.

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.

EXEC SQL INSERT INTO "tablename" VALUES ('text string');

 

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 hostvariable should be declared with a length one greater than the length of the column, because of the null termination

•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.

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

IEEE floating point

Mimer supports single precision and double precision IEEE floating point values stored in the C types float and double. Values are stored exactly in the database with the following exceptions:

•-0.0 is stored as 0.0

•NaN (Not a Number) or Inf (Infinity) values are not permitted in the database and will not be stored.

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).

ESQL in COBOL Programs

Mimer SQL supports ESQL for COBOL following the COBOL-85 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
END-EXEC.

SQL statements are treated exactly as ordinary COBOL statements with regard to the use of an ending period to mark the end of a COBOL sentence. Any valid COBOL punctuation may be placed after the END-EXEC terminator.

Examples:

EXEC SQL DELETE FROM countries END-EXEC.

 

IF SQLSTATE NOT = "02000" THEN

           EXEC SQL COMMIT END-EXEC

ELSE

           EXEC SQL ROLLBACK END-EXEC.

Margins

Statements (including delimiters) may be written anywhere between positions 8 and 72 inclusive.

Line Continuation

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

If a string constant within an SQL statement is divided over several lines, the first non-blank character on the continuation line must be a string delimiter. There is no terminating string delimiter at the end of the line preceding the continuation line.

Example

EXEC SQL SELECT CODE, CURRENCY

           FROM MIMER_STORE.CURRENCIES

           WHERE CODE LIKE :CURRENCY-CODE END-EXEC.

EXEC SQL COMMENT ON TABLE CURRENCIES IS

                    'Holds currency

-                   ' details' END-EXEC.

 

An alternative way to break a character string constant over several lines, is to use a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), to join two or more substrings.

Each substring must be separately enclosed in delimiters.

EXEC SQL COMMENT ON TABLE CURRENCIES IS

                    'Holds currency'<CR>

                    ' details' END-EXEC.

Comments

Comment lines, marked by an asterisk (*) in position 7, may be written within SQL statements. The whole line following a comment mark is treated as a comment.

Debugging lines and page eject lines (marked by D and / respectively in position 7) are treated as comments by the preprocessor.

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 default COBOL string delimiter usage.

EXEC SQL INSERT INTO "tablename" VALUES ('text string') END-EXEC.

 

Observe that the minus sign (-) is valid in variable names in COBOL. All arithmetic expressions using this operator should have at least one space separating the operands from the operator. For example:

:A - B   means: variable called A minus column B

:A-B     means: variable called A-B

Restrictions

The following restrictions apply specifically to COBOL:

•END-EXEC is a keyword reserved to SQL.

•COBOL figurative constants (such as ZERO and SPACE) may not be used as constants in SQL statements.

Host Variables in COBOL

The following sections discuss declarations, SQL data type correspondence, preprocessor output format 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:

•variable names must begin with a letter. Within this restriction, any valid COBOL variable name may be used

•host variable structures may not be used

•the specifications JUSTIFIED, BLANK WITH ZERO and OCCURS may not be used

•the data type must be consistent with SQL data types as specified below

•level number 01 or 77 should be used for all variable names that are used in SQL statements. Other levels may be used for program host variables, but they are not recognized by the preprocessor

•FILLER entries are ignored for variables used in SQL statements

•Indicator variables should be declared as PIC S9(4) COMP or PIC S9(9) COMP

A syntax diagram for COBOL variable declarations recognized by the ESQL/COBOL preprocessor is given below. Other declarations are ignored by the preprocessor:

 

Commas and semicolons may be used in accordance with standard COBOL practice.

The following abbreviations are accepted:

Note:The PIC S9(n)9(m) formulation is not accepted.

SQL Data Type Correspondence

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

Varying-length character string structures may be used in ESQL statements in COBOL programs. In assigning the value of such variables to columns, the current length of the string is used.

The variable name used in SQL statements is the name of the structure (level 01 declaration), not of the character string element (level 49).

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.

The first element in a varying-length character string structure is used to store the current length of the character string. When writing to the variable, the first element is updated with the current length of the variable. If the column value is longer than the variable, the value is truncated.

Preprocessor Output Format

Output from the ESQL/COBOL preprocessor retains SQL statements from the original source code as comments. Comments within SQL statements are retained exactly as written. The output follows the ANSI standard for record format, and should be compiled with a COBOL compiler set to accept ANSI standard.

Debugging lines and page eject lines (using D and / respectively in position 7) remain unchanged after preprocessing.

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

Scope Rules

Host variables follows the same scope rules as ordinary variables in COBOL. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for COBOL is the same as a routine.

ESQL in Fortran Programs

Mimer SQL supports ESQL for ANSI Fortran-90 fixed format.

Source statements must be provided as fixed format, 80 byte records.

SQL Statement Format

The following sections discuss the SQL statement format.

Statement Delimiters

The leading delimiter EXEC SQL identifies SQL statements. The end of an SQL statement is marked by the end of the line when the following line does not begin with a continuation character. The Fortran-90 statement delimiter ; can also be used.

Example

EXEC SQL DELETE FROM countries

Margins

Statements (including delimiters) may be written anywhere between positions 7 and 72 inclusive.

Line Continuation

Line continuation rules for SQL statements are the same as those for ordinary Fortran statements. The continuation character is any character except space and 0 (zero) in position 6. The Fortran limitation of a maximum of 19 continuation lines per statement does not apply within SQL 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 substrings. Each substring must be separately enclosed in delimiters.

Examples:

    EXEC SQL SELECT CODE, CURRENCY

+                FROM MIMER_STORE.CURRENCIES

+                WHERE CODE LIKE :CODE

 

    EXEC SQL COMMENT ON TABLE CURRENCIES IS

+                        'Holds currency'<CR>

+                        ' details'

Statement Numbers

Any labeled SQL statement in the source code will generate a CONTINUE statement during preprocessing. Declarative SQL statements used before the first executable SQL statement should not be labeled.

Comments

Comment lines, marked by * or C in position 1, may be written within SQL statements. The whole line following a comment mark is treated as a comment, and the following line must either be another comment or follow the continuation rules given above.

Note:Lines that are completely blank are not treated as comments by the
ESQL/FORTRAN preprocessor. The absence of a continuation character indicates the end of the previous statement, and a completely blank line may be used to structure comments in the output from the preprocessor. See Preprocessor Output Format for details.

Fortran-90 style comments may also be used, marked by the ! character (the text between the ! and the end-of-line is treated as a comment).

Debugging lines (marked with a D in position 1) are treated as comments by the preprocessor.

Host Variables

The following sections discuss declarations, SQL data type correspondence, value assignments, preprocessor output format and scope.

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:

•any valid Fortran variable name may be used.

•variables must be scalar variables (i.e. they may not be elements of vectors or arrays).

•implicit declaration by means of the IMPLICIT statement or default typing may not be used.

•Fortran COMPLEX variables may not be used.

•character variables must be declared with a fixed constant length. Expressions and variable length declarations (such as CHARACTER*(*)) may not be used.

•indicator variables should be declared as INTEGER*2 or INTEGER*4.

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

 

The data type declaration must be separated from the variable name by at least one space (which is not required in Fortran declarations outside the SQL DECLARE SECTION).

Thus the declaration:

INTEGER*2A

 

is not recognized. The required formulation is:

INTEGER*2 A

 

Lists of variables following a single default data type declaration are accepted. Any declarations in a list that are not valid in SQL contexts are ignored by the preprocessor. Thus, the following statement declares variables A and D as INTEGER*4 and B as INTEGER*2 for use in SQL statements, while the array C is ignored:

INTEGER*4    A, B*2, C(10), D

SQL Data Type Correspondence

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

The following additional points should be noted:

•Fortran does not support DECIMAL data types. A string of digits including a decimal point is interpreted as a REAL constant in Fortran. Exponential notation should always be used to specify floating point values in SQL statements.

•DOUBLE PRECISION constants may be written with a D as the exponent marker in Fortran (e.g. 1.23D+02). The only permissible exponent marker within SQL statements is E (e.g. 1.23E+02).

Value Assignments

The general rules for conversion of values between compatible but different data types, see of 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.

Preprocessor Output Format

Output from the ESQL/FORTRAN preprocessor retains SQL statements from the original source code as comments. The output follows the ANSI standard for record format, and should be compiled with a Fortran compiler set to accept ANSI standard. Comments within SQL statements are retained exactly as written.

Completely blank lines between SQL statements and following comments cause the preprocessor to write the comments after the generated SQL call. Otherwise comments immediately following SQL statements are output before the generated call. Debugging lines (using D in position 1) remain unchanged after preprocessing.

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

Scope Rules

Host variables follows the same scope rules as ordinary variables in Fortran.

SQL descriptor names, cursor names and statement names must be unique within the compilation unit.

A compilation unit for Fortran is the same as a routine.