Receive our weekly advanced programming tips for FREE
17.April
How use embedded c under PostgreSQL
0
| 0 votes
Autor: bookkeeper
Chapter: PostgreSQL

Motivation

The sourcecode

Get c code

Complie c code


Motivation:

Postgresql provide the embedded SQL under c. The advantages are, First, it takes care of the tedious passing of information to and from variables in your C program. Second, the SQL code in the program is checked at build time for syntactical correctness. Third, embedded SQL in C is specified in the SQL standard and supported by many other SQL database systems. The PostgreSQL implementation is designed to match this standard as much as possible, and it is usually possible to port embedded SQL programs written for other SQL databases to PostgreSQL with relative ease.


Lets get started!

Every statement begin with "EXEC SQL..."

#include 

EXEC SQL INCLUDE sqlca; /* error handling */

int     main (int argc, char **argv)
{
    EXEC SQL BEGIN DECLARE SECTION;
        char    v_relname[512];
        int     v_relpages;
        EXEC SQL END DECLARE SECTION;
   
    /* connect to database */
        EXEC SQL CONNECT TO tcp:postgresql://epi:5432/test_db
                AS pg_conn USER postgres;

        if(sqlca.sqlcode != 0) /* connect error */
                return 1;

        /* declare a cursor */
        EXEC SQL DECLARE EIN_CURSOR CURSOR FOR
                SELECT relname, relpages FROM pg_class LIMIT 4;

        /* open cursor */
        EXEC SQL OPEN EIN_CURSOR;

        /* read datasets */
        while(true)
        {
                EXEC SQL FETCH IN EIN_CURSOR INTO :v_relname, :v_relpages;
                printf("%s: %d\n", v_relname, v_relpages);

                if(sqlca.sqlcode != 0)
                    break;
        }
    /* close cursor */
        EXEC SQL CLOSE EIN_CURSOR;
    /* close connection */       
    EXEC SQL DISCONNECT;

        return 0;
}

To get the c code, you need to run:

ecpg pgc.pgc

the output will be:

/* Processed by ecpg (4.4.1) */
/* These include files are added by the preprocessor */
#include #include #include #include /* End of automatic include section */ #line 1 "pgc.pgc" #include #line 1 "/usr/include/postgresql/sqlca.h" #ifndef POSTGRES_SQLCA_H #define POSTGRES_SQLCA_H #ifndef PGDLLIMPORT #if defined(WIN32) || defined(__CYGWIN__) #define PGDLLIMPORT __declspec (dllimport) #else #define PGDLLIMPORT #endif /* __CYGWIN__ */ #endif /* PGDLLIMPORT */ #define SQLERRMC_LEN 150 #ifdef __cplusplus extern "C" { #endif struct sqlca_t { char sqlcaid[8]; long sqlabc; long sqlcode; struct { int sqlerrml; char sqlerrmc[SQLERRMC_LEN]; } sqlerrm; char sqlerrp[8]; long sqlerrd[6]; /* Element 0: empty */ /* 1: OID of processed tuple if applicable */ /* 2: number of rows processed */ /* after an INSERT, UPDATE or */ /* DELETE statement */ /* 3: empty */ /* 4: empty */ /* 5: empty */ char sqlwarn[8]; /* Element 0: set to 'W' if at least one other is 'W' */ /* 1: if 'W' at least one character string */ /* value was truncated when it was */ /* stored into a host variable. */ /* * 2: if 'W' a (hopefully) non-fatal notice occurred */ /* 3: empty */ /* 4: empty */ /* 5: empty */ /* 6: empty */ /* 7: empty */ char sqlstate[5]; }; struct sqlca_t *ECPGget_sqlca(void); #ifndef POSTGRES_ECPG_INTERNAL #define sqlca (*ECPGget_sqlca()) #endif #ifdef __cplusplus } #endif #endif #line 3 "pgc.pgc" /* error handling */ int main (int argc, char **argv) { /* exec sql begin declare section */ #line 8 "pgc.pgc" char v_relname [ 512 ] ; #line 9 "pgc.pgc" int v_relpages ; /* exec sql end declare section */ #line 10 "pgc.pgc" /* connect to database */ { ECPGconnect(__LINE__, 0, "tcp:postgresql://epi:5432/test_db" , "postgres" , NULL , "pg_conn", 0); } #line 14 "pgc.pgc" if(sqlca.sqlcode != 0) /* connect error */ return 1; /* declare a cursor */ /* declare EIN_CURSOR cursor for select relname , relpages from pg_class limit 4 */ #line 21 "pgc.pgc" /* open cursor */ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare EIN_CURSOR cursor for select relname , relpages from pg_class limit 4 ", ECPGt_EOIT, ECPGt_EORT);} #line 24 "pgc.pgc" /* read datasets */ while(true) { { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch in EIN_CURSOR", ECPGt_EOIT, ECPGt_char,(v_relname),(long)512,(long)1,(512)*sizeof(char), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_int,&(v_relpages),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);} #line 29 "pgc.pgc" printf("%s: %d\n", v_relname, v_relpages); if(sqlca.sqlcode != 0) break; } /* close cursor */ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close EIN_CURSOR", ECPGt_EOIT, ECPGt_EORT);} #line 36 "pgc.pgc" /* close connection */ { ECPGdisconnect(__LINE__, "CURRENT");} #line 38 "pgc.pgc" return 0; }

Nothing special ;)

To compile the code you need to run:

gcc -Wall -I`pg_config --includedir` -L`pg_config --libdir` -lecpg -o pgc pgc.c

Have Fun!