Example of embedded SQL

Embedded SQL is an environment that is a combination of C/C++ program code and pseudo-code. The pseudo-code that can be interspersed with traditional C/C++ code is a subset of SQL statements. A preprocessor converts the embedded SQL statements into function calls that are part of the actual code that is compiled to create the application.

Following is a very simple example of an embedded SQL program. It illustrates updating an UltraLite database record by changing the surname of employee 195.

#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 = 'Johnson'
      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 this example is too simplistic to be useful, it illustrates 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 part of standard SQL. The INCLUDE SQLCA statement is one example.
In addition to SQL statements, embedded SQL also provides library functions to perform some specific tasks. The functions db_init and db_fini are two examples of library function calls.

Initialization

The above sample code illustrates initialization statements that must be included before working with the data in an UltraLite database:

Define the SQL Communications Area (SQLCA), using the following command:
EXEC SQL INCLUDE SQLCA;
This definition must be the first embedded SQL statement, so a natural place for it is the end of the include list.

If you have multiple .sqc files in your application, each file must have this line.
The first 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.
You must use the SQL CONNECT statement to connect to the UltraLite database.

Preparing to exit

The above sample code demonstrates the sequence of calls required when preparing to exit:

Commit or rollback any outstanding changes.
Disconnect from the database.
End your SQL work with a call to a library function named db_fini.

When you exit, any uncommitted database changes are automatically rolled back.