UltraLite User's Guide
  The Embedded SQL Interface

Introduction

Embedded SQL lets you insert standard SQL statements into either C or C++ code. A SQL preprocessor translates these SQL statements into C and C++ compatible source code. You compile each preprocessed file as you would an ordinary C or C++ source file.

The preprocessor generates source code that makes calls to library functions. These functions are defined in a library or imports library. You include one of these libraries when you link your program.

The following is a very simple embedded SQL program. It updates the surname of employee 195 and commits the change.

#include <stdio.h>
EXEC SQL INCLUDE SQLCA;
main( )
{
   db_init( &sqlca );
   EXEC SQL WHENEVER SQLERROR GOTO error;
   EXEC SQL CONNECT "DBA" IDENTIFIED BY "SQL";
   EXEC SQL UPDATE employee
      SET emp_lname = 'Plankton'
      WHERE emp_id = 195;
   EXEC SQL COMMIT;
   EXEC SQL DISCONNECT;
   db_fini( &sqlca );
   return( 0 );
   error:
      printf( "update unsuccessful: sqlcode = %ld\n",
         sqlca.sqlcode );
      return( -1 );
}

Although too simple to be useful, this example demonstrates the following aspects common to all embedded SQL applications:

• Each SQL statement is prefixed with the keywords EXEC SQL.

• Each SQL statement ends with a semicolon.

• Some embedded SQL statements are not found in standard SQL. The INCLUDE SQLCA statement is one example.

• Embedded SQL provides library functions to perform some specific tasks. The functions db_init and db_fini are examples.

Before working with data 

The above example demonstrates the necessary initialization statements. You must include these before working with the data in any database.

1. You must define the SQL communications area, sqlca, using the following command.

   EXEC SQL INCLUDE SQLCA;

   This definition must be your first embedded SQL statement, so a natural place for it is the end of your include list.

   If you have multiple .sqc files in your application, each file must have this line.

2. Your first executable database action must be a call to an embedded SQL library function named db_init. This function initializes the UltraLite runtime library. Only embedded SQL definition statements can be executed before this call.

   For more information, see db_init function.

3. You must use the CONNECT statement to connect to your database.

Preparing to exit 

This example also demonstrates the sequence of calls you must make when preparing to exit.

1. Commit or rollback any outstanding changes.

2. Disconnect from the database.

3. End your SQL work with a call to a library function named db_fini.

If you leave changes to the database uncommitted when you exit, any uncommitted operations are automatically rolled back.

Error handling 

There is virtually no interaction between the SQL and C code in this example. The C code only controls flow. The WHENEVER statement is used for error checking. The error action, GOTO in this example, is executed after any SQL statement causes an error.

Structure of embedded SQL programs