Application Development Guide

Embedding SQL Statements in a Host Language

You can write applications with SQL statements embedded within a host language. The SQL statements provide the database interface, while the host language provides the remaining support needed for the application to execute.

Table 2 shows an SQL statement embedded in a host language application. In the example, the application checks the SQLCODE field of the SQLCA structure to determine whether the update was successful.

Table 2. Embedding SQL Statements in a Host Language
Language Sample Source Code
C/C++ 
 EXEC SQL UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr';
 if ( SQLCODE < 0 )
    printf( "Update Error:  SQLCODE = %ld \n", SQLCODE );

Java (SQLJ) 
 try {
    #sql { UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr' };
 }
 catch (SQLException e) {
    println( "Update Error:  SQLCODE = " + e.getErrorCode() );
 }

COBOL 
 EXEC SQL UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr' END_EXEC.
 IF SQLCODE LESS THAN 0
    DISPLAY 'UPDATE ERROR:  SQLCODE = ', SQLCODE.

FORTRAN 
 EXEC SQL UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr'
 if ( sqlcode .lt. 0 ) THEN
    write(*,*) 'Update error:  sqlcode = ', sqlcode

SQL statements placed in an application are not specific to the host language. The database manager provides a way to convert the SQL syntax for processing by the host language.

For the C, C++, COBOL or FORTRAN languages, this conversion is handled by the DB2 precompiler. The DB2 precompiler is invoked using the PREP command. The precompiler converts embedded SQL statements directly into DB2 run-time services API calls.

For the Java language, the SQLJ translator converts SQLJ clauses into JDBC statements. The SQLJ translator is invoked with the SQLJ command.

When the precompiler processes a source file, it specifically looks for SQL statements and avoids the non-SQL host language. It can find SQL statements because they are surrounded by special delimiters. For the syntax information necessary to embed SQL statements in the language you are using, see the following:

Table 3 shows how to use delimiters and comments to create valid embedded SQL statements in the supported compiled host languages.

Table 3. Embedding SQL Statements in a Host Language
Language Sample Source Code
C/C++ 
 /* Only C or C++ comments allowed here */
 EXEC SQL
    -- SQL comments or
    /* C comments or */
    // C++ comments allowed here
    DECLARE C1 CURSOR FOR sname;
 /* Only C or C++ comments allowed here */

SQLJ 
 /* Only Java comments allowed here */
 #sql c1 = {
    -- SQL comments or
    /* Java comments or */
    // Java comments allowed here
    SELECT name FROM employee
 };
 /* Only Java comments allowed here */

COBOL 
 * See COBOL documentation for comment rules
 * Only COBOL comments are allowed here
  EXEC SQL
     -- SQL comments or
 *   full-line COBOL comments are allowed here
     DECLARE C1 CURSOR FOR sname END-EXEC.
 * Only COBOL comments are allowed here

FORTRAN 
C     Only FORTRAN comments are allowed here
      EXEC SQL
     + -- SQL comments, and
C     full-line FORTRAN comment are allowed here
     + DECLARE C1 CURSOR FOR sname
      I=7 ! End of line FORTRAN comments allowed here
C     Only FORTRAN comments are allowed here

[ Top of Page | Previous Page | Next Page ]