16.3. Embedded SQL

Since the SQL / 89 standard does not specify (even in applications) the rules for embedding SQL in the C language, we present only the general syntax rules for embedding used for any language. This will help assess the “standardization” degree of a particular implementation.

  <embedded SQL statement> :: =
   <SQL prefix>
 {<declare cursor>
 |  <embedded exception declaration>
 |  <SQL statement>}
  [<SQL terminator>]
 <SQL prefix> :: = EXEC SQL
 <SQL terminator> :: = END EXEC |  ;
 <embedded SQL declare section> :: =
   <embedded SQL begin declare>
  (<host variable definition> ...]
   <embedded SQL end declare>
 <embedded SQL begin declare> :: =
   <SQL prefix> BEGIN DECLARE SECTION [<SQL terminator>]
 <embedded SQL end declare> :: =
   <SQL prefix> END DECLARE SECTION [<SQL terminator>]
 <embedded variable name> :: =: <host identifier>
 <embedded exception declaration> :: =
    WHENEVER <condition> <exception action>
 <condition> :: = SQLERROR |  NOT FOUND
 <exception action> :: = CONTINUE |  <go to>
 <go to> :: = {GOTO |  GO TO} <target>
 <target> :: =: <host identifier> |  <unsigned integer> 

Embedded SQL statements, including cursor declarations, as well as exception declaration sections and main program variables, must be surrounded with EXEC SQL and END EXEC brackets. The cursor declaration must occur textually before any operator referencing this cursor. All main program variables used in embedded SQL statements must be declared in the main program variable declaration section textually preceding this statement. The syntax for declaring a variable corresponds to the syntax of the main programming language, but the variable name is preceded by a colon.

The exception handling mechanism in SQL / 89 is extremely simple (it can be said, primitive). You can set the reaction to the occurrence of two types of conditions: SQLERROR is a condition for the appearance of a negative value in the SQLCODE variable after the built-in operator; NOT FOUND - the condition for the appearance of values ​​in SQLCODE +100 (this code means the exhaustion of the cursor). The reaction may consist in performing an unconditional transition to the main program label (GO TO action), or be absent (CONTINUE action). The operator of determining the response to an exceptional situation, which is textually closer from the beginning of the program to the given SQL statement, is triggered.

Note that in many implementations two types of response codes are supported when executing SQL statements (embedded or taken from a module): through the SQLCODE variable with response codes represented by integers and through the SQLSTATE variable with response codes encoded in decimal numbers presented in text form. There is a tendency to switch to using only the SQLSTATE mechanism, but in standard implementations the SQLCODE mechanism should be supported.