PostgreSQL
PrevChapter 45. libpqNext

Sample Programs

Sample Program 1

         /*
          * testlibpq.c
          *   Test the C version of LIBPQ, the Postgres frontend library.
          *
          *
          */
         #include <stdio.h>
         #include "libpq-fe.h"

         void
         exit_nicely(PGconn* conn)
         {
           PQfinish(conn);
           exit(1);
         }

         main()
         {
           char *pghost, *pgport, *pgoptions, *pgtty;
           char* dbName;
           int nFields;
           int i,j;

         /*  FILE *debug; */

           PGconn* conn;
           PGresult* res;

           /* begin, by setting the parameters for a backend connection
              if the parameters are null, then the system will try to use
              reasonable defaults by looking up environment variables
              or, failing that, using hardwired constants */
           pghost = NULL;  /* host name of the backend server */
           pgport = NULL;  /* port of the backend server */
           pgoptions = NULL; /* special options to start up the backend server */
           pgtty = NULL;     /* debugging tty for the backend server */
           dbName = "template1";

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD) {
             fprintf(stderr,"Connection to database '%s' failed.0, dbName);
             fprintf(stderr,"%s",PQerrorMessage(conn));
             exit_nicely(conn);
           }

         /*  debug = fopen("/tmp/trace.out","w");  */
         /*   PQtrace(conn, debug);  */

           /* start a transaction block */

           res = PQexec(conn,"BEGIN");
           if (PQresultStatus(res) != PGRES_COMMAND_OK) {
             fprintf(stderr,"BEGIN command failed0);
             PQclear(res);
             exit_nicely(conn);
           }
           /* should PQclear PGresult whenever it is no longer needed to avoid
              memory leaks */
           PQclear(res);

           /* fetch instances from the pg_database, the system catalog of databases*/
           res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database");
           if (PQresultStatus(res) != PGRES_COMMAND_OK) {
             fprintf(stderr,"DECLARE CURSOR command failed0);
             PQclear(res);
             exit_nicely(conn);
           }
           PQclear(res);

           res = PQexec(conn,"FETCH ALL in myportal");
           if (PQresultStatus(res) != PGRES_TUPLES_OK) {
             fprintf(stderr,"FETCH ALL command didn't return tuples properly0);
             PQclear(res);
             exit_nicely(conn);
           }

           /* first, print out the attribute names */
           nFields = PQnfields(res);
           for (i=0; i < nFields; i++) {
             printf("%-15s",PQfname(res,i));
           }
           printf("0);

           /* next, print out the instances */
           for (i=0; i < PQntuples(res); i++) {
             for (j=0  ; j < nFields; j++) {
               printf("%-15s", PQgetvalue(res,i,j));
             }
             printf("0);
           }

           PQclear(res);

           /* close the portal */
           res = PQexec(conn, "CLOSE myportal");
           PQclear(res);

           /* end the transaction */
           res = PQexec(conn, "END");
           PQclear(res);

           /* close the connection to the database and cleanup */
           PQfinish(conn);

         /*   fclose(debug); */
         }

Sample Program 2

         /*
          * testlibpq2.c
          *   Test of the asynchronous notification interface
          *
            populate a database with the following:

         CREATE TABLE TBL1 (i int4);

         CREATE TABLE TBL2 (i int4);

         CREATE RULE r1 AS ON INSERT TO TBL1 DO [INSERT INTO TBL2 values (new.i); NOTIFY TBL2];

          * Then start up this program
          * After the program has begun, do

         INSERT INTO TBL1 values (10);

          *
          *
          */
         #include <stdio.h>
         #include "libpq-fe.h"

         void exit_nicely(PGconn* conn)
         {
           PQfinish(conn);
           exit(1);
         }

         main()
         {
           char *pghost, *pgport, *pgoptions, *pgtty;
           char* dbName;
           int nFields;
           int i,j;

           PGconn* conn;
           PGresult* res;
           PGnotify* notify;

           /* begin, by setting the parameters for a backend connection
              if the parameters are null, then the system will try to use
              reasonable defaults by looking up environment variables
              or, failing that, using hardwired constants */
           pghost = NULL;  /* host name of the backend server */
           pgport = NULL;  /* port of the backend server */
           pgoptions = NULL; /* special options to start up the backend server */
           pgtty = NULL;     /* debugging tty for the backend server */
           dbName = getenv("USER"); /* change this to the name of your test database*/

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD) {
             fprintf(stderr,"Connection to database '%s' failed.0, dbName);
             fprintf(stderr,"%s",PQerrorMessage(conn));
             exit_nicely(conn);
           }

           res = PQexec(conn, "LISTEN TBL2");
           if (PQresultStatus(res) != PGRES_COMMAND_OK) {
             fprintf(stderr,"LISTEN command failed0);
             PQclear(res);
             exit_nicely(conn);
           }
           /* should PQclear PGresult whenever it is no longer needed to avoid
              memory leaks */
           PQclear(res);

           while (1) {
               /* async notification only come back as a result of a query*/
               /* we can send empty queries */
               res = PQexec(conn, " ");
         /*      printf("res->status = %s0, pgresStatus[PQresultStatus(res)]); */
               /* check for asynchronous returns */
               notify = PQnotifies(conn);
               if (notify) {
                fprintf(stderr,
                     "ASYNC NOTIFY of '%s' from backend pid '%d' received0,
                     notify->relname, notify->be_pid);
                free(notify);
                break;
               }
               PQclear(res);
           }

           /* close the connection to the database and cleanup */
           PQfinish(conn);

         }

Sample Program 3

         /*
          * testlibpq3.c
          *   Test the C version of LIBPQ, the Postgres frontend library.
          *   tests the binary cursor interface
          *
          *
          *
          populate a database by doing the following:

         CREATE TABLE test1 (i int4, d float4, p polygon);

         INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon);

         INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon);

          the expected output is:

         tuple 0: got
          i = (4 bytes) 1,
          d = (4 bytes) 3.567000,
          p = (4 bytes) 2 points         boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000)
         tuple 1: got
          i = (4 bytes) 2,
          d = (4 bytes) 89.050003,
          p = (4 bytes) 2 points         boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000)

          *
          */
         #include <stdio.h>
         #include "libpq-fe.h"
         #include "utils/geo-decls.h" /* for the POLYGON type */

         void exit_nicely(PGconn* conn)
         {
           PQfinish(conn);
           exit(1);
         }

         main()
         {
           char *pghost, *pgport, *pgoptions, *pgtty;
           char* dbName;
           int nFields;
           int i,j;
           int i_fnum, d_fnum, p_fnum;

           PGconn* conn;
           PGresult* res;

           /* begin, by setting the parameters for a backend connection
              if the parameters are null, then the system will try to use
              reasonable defaults by looking up environment variables
              or, failing that, using hardwired constants */
           pghost = NULL;  /* host name of the backend server */
           pgport = NULL;  /* port of the backend server */
           pgoptions = NULL; /* special options to start up the backend server */
           pgtty = NULL;     /* debugging tty for the backend server */

           dbName = getenv("USER");  /* change this to the name of your test database*/

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD) {
             fprintf(stderr,"Connection to database '%s' failed.0, dbName);
             fprintf(stderr,"%s",PQerrorMessage(conn));
             exit_nicely(conn);
           }

           /* start a transaction block */
           res = PQexec(conn,"BEGIN");
           if (PQresultStatus(res) != PGRES_COMMAND_OK) {
             fprintf(stderr,"BEGIN command failed0);
             PQclear(res);
             exit_nicely(conn);
           }
           /* should PQclear PGresult whenever it is no longer needed to avoid
              memory leaks */
           PQclear(res);

           /* fetch instances from the pg_database, the system catalog of databases*/
           res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from test1");
           if (PQresultStatus(res) != PGRES_COMMAND_OK) {
             fprintf(stderr,"DECLARE CURSOR command failed0);
             PQclear(res);
             exit_nicely(conn);
           }
           PQclear(res);

           res = PQexec(conn,"FETCH ALL in mycursor");
           if (PQresultStatus(res) != PGRES_TUPLES_OK) {
             fprintf(stderr,"FETCH ALL command didn't return tuples properly0);
             PQclear(res);
             exit_nicely(conn);
           }

           i_fnum = PQfnumber(res,"i");
           d_fnum = PQfnumber(res,"d");
           p_fnum = PQfnumber(res,"p");

           for (i=0;i<3;i++) {
               printf("type[%d] = %d, size[%d] = %d0,
                   i, PQftype(res,i),
                   i, PQfsize(res,i));
           }
           for (i=0; i < PQntuples(res); i++) {
             int *ival;
             float *dval;
             int plen;
             POLYGON* pval;
             /*/
             ival =  (int*)PQgetvalue(res,i,i_fnum);
             dval =  (float*)PQgetvalue(res,i,d_fnum);
             plen = PQgetlength(res,i,p_fnum);

             /* plen doesn't include the length field so need to increment by VARHDSZ*/
             pval = (POLYGON*) malloc(plen + VARHDRSZ);
             pval->size = plen;
             memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen);
             printf("tuple %d: got0, i);
             printf(" i = (%d bytes) %d,0,
                 PQgetlength(res,i,i_fnum), *ival);
             printf(" d = (%d bytes) %f,0,
                 PQgetlength(res,i,d_fnum), *dval);
             printf(" p = (%d bytes) %d points boundbox = (hi=%f/%f, lo = %f,%f)0,
                 PQgetlength(res,i,d_fnum),
                 pval->npts,
                 pval->boundbox.xh,
                 pval->boundbox.yh,
                 pval->boundbox.xl,
                 pval->boundbox.yl);
           }

           PQclear(res);

           /* close the portal */
           res = PQexec(conn, "CLOSE mycursor");
           PQclear(res);

           /* end the transaction */
           res = PQexec(conn, "END");
           PQclear(res);

           /* close the connection to the database and cleanup */
           PQfinish(conn);

         }


PrevHomeNext
BUGSUppgtcl