Embedded SQL application template in C

You are provided with a sample embedded SQL application to test your embedded SQL development environment and to help you learn about the basic structure of embedded SQL applications.

Embedded SQL applications require the following structure:

Including the required header files
Host variable declarations for values to be included in SQL statements
A database connection
The execution of SQL statements
The handling of SQL errors and warnings related to SQL statement execution
Dropping the database connection

The following source code demonstrates the basic structure required for embedded SQL applications written in C.

Sample program: template.sqc

       #include <stdio.h>                                                  1
       #include <stdlib.h>
       #include <string.h>
       #include <sqlenv.h>
       #include <sqlutil.h>                                               

      EXEC SQL BEGIN DECLARE SECTION;                                      2
        short id;
        char name[10];
        short dept;
        double salary;
        char hostVarStmtDyn[50];
      EXEC SQL END DECLARE SECTION;                                        

      int main()                                                           
       { 
         int rc = 0;                                                       3
         EXEC SQL INCLUDE SQLCA;                                           4

         /* connect to the database */
         printf("\n Connecting to database...");
         EXEC SQL CONNECT TO "sample";                                     5
         if (SQLCODE  <0)                                                  6
         {
            printf("\nConnect Error:  SQLCODE = %d. \n", SQLCODE);
            goto connect_reset;
         }
         else
         {
            printf("\n Connected to database.\n");
         }
         
         /* execute an SQL statement (a query) using static SQL; copy the single row 
            of result values into host variables*/
         EXEC SQL SELECT id, name, dept, salary                            7
                 INTO :id, :name, :dept, :salary
                 FROM staff WHERE id = 310;
         if (SQLCODE  <0)                                                  6 
         {
            printf("Select Error:  SQLCODE = %d. \n", SQLCODE);
         }
         else
         {
            /* print the host variable values to standard output */
            printf("\n Executing a static SQL query statement, searching for 
               \n the id value equal to 310\n");
            printf("\nID        Name        DEPT       Salary\n");
            printf(" %d           %s          %d       %f\n", 
                id, name, dept, salary); 
         }
         
         strcpy(hostVarStmtDyn, "UPDATE staff 
                                SET salary = salary + 1000 
                                WHERE dept = ?");
         /* execute an SQL statement (an operation) using a host variable
            and DYNAMIC SQL*/
         EXEC SQL PREPARE StmtDyn FROM :hostVarStmtDyn;
         if (SQLCODE  <0)                                                  6
         {
            printf("Prepare Error:  SQLCODE = %d. \n", SQLCODE);
         }
         else
         {
            EXEC SQL EXECUTE StmtDyn USING :dept;                          8
         }
         if (SQLCODE  <0)                                                  6
         {
            printf("Execute Error:  SQLCODE = %d. \n", SQLCODE);
         }
     
         /* Read the updated row using STATIC SQL and CURSOR */
         EXEC SQL DECLARE posCur1 CURSOR FOR 
            SELECT id, name, dept, salary 
            FROM staff WHERE id = 310;
         if (SQLCODE  <0)                                                 6
         {
            printf("Declare Error:  SQLCODE = %d. \n", SQLCODE);
         }
         EXEC SQL OPEN posCur1;
         EXEC SQL FETCH posCur1 INTO :id, :name, :dept, :salary ;         9
         if (SQLCODE  <0)                                                 6
         {
            printf("Fetch Error:  SQLCODE = %d. \n", SQLCODE);
         }
         else
         {
            printf(" Executing an dynamic SQL statement, updating the 
                     \n salary value for the id equal to 310\n");
            printf("\n ID   Name        DEPT       Salary\n");
            printf(" %d           %s          %d       %f\n", 
                id, name, dept, salary);         }
         
         EXEC SQL CLOSE posCur1;
         
         /* Commit the transaction */
         printf("\n  Commit the transaction.\n");
         EXEC SQL COMMIT;                                                 10
         if (SQLCODE  <0)                                                 6
         {
            printf("Error:  SQLCODE = %d. \n", SQLCODE); 
         }
         
         /* Disconnect from the database */
         connect_reset :
            EXEC SQL CONNECT RESET;                                       11
            if (SQLCODE  <0)                                              6
            {
               printf("Connection Error:  SQLCODE = %d. \n", SQLCODE); 
            }
         return 0;
        } /* end main */

Notes to Sample program: template.sqc:

Note	Description
1	Include files: This directive includes a file into your source application.
2	Declaration section: Declaration of host variables that will be used to hold values referenced in the SQL statements of the C application.
3	Local variable declaration: This block declares the local variables to be used in the application. These are not host variables.
4	Including the SQLCA structure: The SQLCA structure is updated after the execution of each SQL statement. This template application uses certain SQLCA fields for error handling.
5	Connection to a database: The initial step in working with the database is to establish a connection to the database. Here, a connection is made by executing the `CONNECT` SQL statement.
6	Error handling: Checks to see if an error occurred.
7	Executing a query: The execution of this SQL statement assigns data returned from a table to host variables. The C code used after the SQL statement execution prints the values in the host variables to standard output.
8	Executing an operation: The execution of this SQL statement updates a set of rows in a table identified by their department number. Preparation (`EXEC SQL PREPARE StmtDyn FROM :hostVarStmtDyn;`) is a step in which host variable values, such as the one referenced in this statement, are bound to the SQL statement to be executed.
9	Executing an operation: In this line and the previous line, this application uses cursors in static SQL to select information in a table and print the data. After the cursor is declared and opened, the data is fetched, and finally the cursor is closed.
10	Commit the transaction: The COMMIT statement finalizes the database changes that were made within a unit of work.
11	And finally, the database connection must be dropped.