Embedded SQL statements in C and C++ applications

Embedded SQL C and C++ applications consist of three main elements to setup and issue an SQL statement.

• A DECLARE SECTION for declaring host variables. The declaration of the SQLCA structure does not need to be in the DECLARE section.
• The main body of the application, which consists of the setup and execution of SQL statements
• Placements of logic that either commit or rollback the changes made by the SQL statements

Correct C and C++ Element Syntax

Statement initializer

EXEC SQL

Statement string

Any valid SQL statement

Statement terminator

Semicolon (;)

For example, to issue an SQL statement statically within a C application, you need to include a EXEC SQL statement within your application code:

   EXEC SQL SELECT col INTO :hostvar FROM table;

The following example demonstrates how to issue an SQL statement dynamically using the host variable stmt1:

	strcpy(stmt1, "CREATE TABLE table1(col1 INTEGER)"); 
	EXEC SQL EXECUTE IMMEDIATE :stmt1; 

The following guidelines and rules apply to the execution of embedded SQL statements in C and C++ applications:

• You can begin the SQL statement string on the same line as the EXEC SQL statement initializer.
• Do not split the EXEC SQL between lines.
• You must use the SQL statement terminator. If you do not use it, the precompiler will continue to the next terminator in the application. This can cause indeterminate errors.
• C and C++ comments can be placed before the statement initializer or after the statement terminator.
• Multiple SQL statements and C or C++ statements may be placed on the same line. For example:

     EXEC SQL OPEN c1; if (SQLCODE >= 0) EXEC SQL FETCH c1 INTO :hv; 

• Carriage returns, line feeds, and TABs can be included within quoted strings. The SQL precompiler will leave these as is.
• Do not use the #include statement to include files containing SQL statements. SQL statements are precompiled before the module is compiled. The precompiler will ignore the #include statement. Instead, use the SQL INCLUDE statement to import the include files.
• SQL comments are allowed on any line that is part of an embedded SQL statement, with the exception of dynamically issued statements.
  • The format for an SQL comment is a double dash (--), followed by a string of zero or more characters, and terminated by a line end.
  • Do not place SQL comments after the SQL statement terminator. These SQL comments cause compilation errors because compilers interpret them as C or C++ syntax.
  • You can use SQL comments in a static statement string wherever blanks are allowed.
  • The use of C and C++ comment delimiters /* */ are allowed in both static and dynamic embedded SQL statements.
  • The use of //-style C++ comments are not permitted within static SQL statements
• SQL string literals and delimited identifiers can be continued over line breaks in C and C++ applications. To do this, use a back slash (\) at the end of the line where the break is desired. For example, to select data from the NAME column in the staff table where the NAME column equals 'Sanders' you could do something similar to the following sample code:

     EXEC SQL SELECT "NA\
     ME" INTO :n FROM staff WHERE name='Sa\
     nders'; 

  Any new line characters (such as carriage return and line feed) are not included in the string that is passed to the database manager as an SQL statement.

• Substitution of white space characters, such as end-of-line and TAB characters, occurs as follows:
  • When they occur outside quotation marks (but inside SQL statements), end-of-lines and TABs are substituted by a single space.
  • When they occur inside quotation marks, the end-of-line characters disappear, provided the string is continued properly for a C program. TABs are not modified.

  Note that the actual characters used for end-of-line and TAB vary from platform to platform. For example, UNIX and Linux® based systems use a line feed.