UltraLite Embedded SQL User's Guide
  Tutorial: Build an Application Using Embedded SQL
    Lesson 2: Write an embedded SQL source file

Explanation of the sample program

Although the sample program is simple, it contains elements that must be present in every embedded SQL source file used for database access.

The following list describes the key elements in the sample program. Use these steps as a guide when creating your own embedded SQL UltraLite application.

1. Include the appropriate header files.

   The sample program uses standard I/O, therefore the stdio.h header file has been included.

2. Define the SQL communications area, sqlca.

   Use the following command:

   EXEC SQL INCLUDE SQLCA;

   This definition must be your first embedded SQL statement, so place it at the end of your include list.

   Prefix SQL statements  All SQL statements must be prefixed with the keywords EXEC SQL and must end with a semicolon.

3. Define host variables by creating a declaration section.

   Host variables are used to send values to the database server or receive values from the database server. Create a declaration section as follows:

   EXEC SQL BEGIN DECLARE SECTION;
      long pid=1;
      long cost;
      char pname[31];   
   EXEC SQL END DECLARE SECTION;   

   For information about host variables, see Using host variables.

4. Call the embedded SQL library function db_init to initialize the UltraLite runtime library.

   Call this function as follows:

   db_init(&sqlca);

5. Connect to the database using the CONNECT statement.

   To connect to the UltraLite sample database, you must supply the login user ID and password. Connect as user DBA with password SQL as follows:

   EXEC SQL CONNECT "DBA" IDENTIFIED BY "SQL";

6. Insert data into database tables.

   When an application is first started, its database tables are empty. When you synchronize the remote database with the consolidated database, the tables are filled with values so that you may execute select, update or delete commands.

   Rather than using synchronization, this sample code directly inserts data into the tables. Directly inserting data is a useful technique during the early stages of UltraLite development.

   If you use synchronization and your application fails to execute a query, it can be due to a problem in the synchronization process or due to a mistake in your program. To locate the source of failure may be difficult. If you directly fill tables with data in your source code rather than perform synchronization, then, if your application fails, you will know automatically that the failure is due to a mistake in your program.

   After you have tested that there are no mistakes in your program, remove the insert statements and replace them with a call to the ULSynchronize function to synchronize the remote database with the consolidated database.

   For information on adding synchronization to an UltraLite application, see Adding synchronization to your application.

7. Execute your SQL query.

   The sample program executes a select query that returns one row of results. The results are stored in the previously defined host variables cost and pname.

8. Perform error handling.

   The sample program executes a select request that returns an error code, sqlcode. This code is negative if an error occurs; SQL_NOTFOUND is returned if there are no query results. The sample program handles these errors by returning -1.

9. Disconnect from the database.

   You should rollback or commit any outstanding changes before disconnecting.

   To disconnect, use the DISCONNECT statement as follows:

   EXEC SQL DISCONNECT;

10. End your SQL work with a call to the library function db_fini:

    db_fini(&sqlca);