Embedded C (ECPG)

Rather than using function calls to perform SQL queries, ECPG allows SQL commands to be embedded in a C program. The ECPG preprocessor converts lines marked by EXEC SQL to native SQL calls. The resulting file is then compiled as a C program.

Figure  shows an ECPG version of our application.  

        
        /*
         *  ecpg sample program
         */
         
        #include <stdio.h>
         
        EXEC SQL INCLUDE sqlca;                                 /* ecpg header file */
         
        EXEC SQL WHENEVER SQLERROR sqlprint;
         
        int
        main()
        {
        EXEC SQL BEGIN DECLARE SECTION;
            char        state_code[3];                          /* holds state code entered by user */
            char       *state_name = NULL;                      /* holds value returned by query */
            char        query_string[256];                      /* holds constructed SQL query */
        EXEC SQL END DECLARE SECTION;
         
            EXEC SQL CONNECT TO test;                           /* connect to the database */
         
            printf("Enter a state code:  ");                    /* prompt user for a state code */
            scanf("%2s", state_code);
         
            sprintf(query_string,                               /* create an SQL query string */
                        "SELECT name \
                         FROM statename \
                         WHERE code = '%s'", state_code);
         
            EXEC SQL PREPARE s_statename FROM :query_string;
            EXEC SQL DECLARE c_statename CURSOR FOR s_statename;/* DECLARE a cursor */
         
            EXEC SQL OPEN c_statename;                          /* send the query */
         
            EXEC SQL WHENEVER NOT FOUND DO BREAK;
         
            while (1)                                           /* loop through all rows returned */
            {
                EXEC SQL FETCH IN c_statename INTO :state_name;
                printf("%s\n", state_name);                     /* print the value returned */
                state_name = NULL;
            }
         
            free(state_name);                                   /* free result */
         
            EXEC SQL CLOSE c_statename;                         /* CLOSE the cursor */
         
            EXEC SQL COMMIT;                                    
         
            EXEC SQL DISCONNECT;                                /* disconnect from the database */
         
            return 0;
        }
         

The interface implements the ANSI embedded SQL C standard, which is supported by many database systems.