src/test/examples/testlibpq3.c

   1 /*
   2  * $PostgreSQL$
   3  *
   4  *
   5  * testlibpq3.c
   6  *              Test out-of-line parameters and binary I/O.
   7  *
   8  * Before running this, populate a database with the following commands
   9  * (provided in src/test/examples/testlibpq3.sql):
  10  *
  11  * CREATE TABLE test1 (i int4, t text, b bytea);
  12  *
  13  * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
  14  * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
  15  *
  16  * The expected output is:
  17  *
  18  * tuple 0: got
  19  *      i = (4 bytes) 1
  20  *      t = (11 bytes) 'joe's place'
  21  *      b = (5 bytes) \000\001\002\003\004
  22  *
  23  * tuple 0: got
  24  *      i = (4 bytes) 2
  25  *      t = (8 bytes) 'ho there'
  26  *      b = (5 bytes) \004\003\002\001\000
  27  */
  28 #include <stdio.h>
  29 #include <stdlib.h>
  30 #include <string.h>
  31 #include <sys/types.h>
  32 #include "libpq-fe.h"
  33
  34 /* for ntohl/htonl */
  35 #include <netinet/in.h>
  36 #include <arpa/inet.h>
  37
  38
  39 static void
  40 exit_nicely(PGconn *conn)
  41 {
  42         PQfinish(conn);
  43         exit(1);
  44 }
  45
  46 /*
  47  * This function prints a query result that is a binary-format fetch from
  48  * a table defined as in the comment above.  We split it out because the
  49  * main() function uses it twice.
  50  */
  51 static void
  52 show_binary_results(PGresult *res)
  53 {
  54         int                     i,
  55                                 j;
  56         int                     i_fnum,
  57                                 t_fnum,
  58                                 b_fnum;
  59
  60         /* Use PQfnumber to avoid assumptions about field order in result */
  61         i_fnum = PQfnumber(res, "i");
  62         t_fnum = PQfnumber(res, "t");
  63         b_fnum = PQfnumber(res, "b");
  64
  65         for (i = 0; i < PQntuples(res); i++)
  66         {
  67                 char       *iptr;
  68                 char       *tptr;
  69                 char       *bptr;
  70                 int                     blen;
  71                 int                     ival;
  72
  73                 /* Get the field values (we ignore possibility they are null!) */
  74                 iptr = PQgetvalue(res, i, i_fnum);
  75                 tptr = PQgetvalue(res, i, t_fnum);
  76                 bptr = PQgetvalue(res, i, b_fnum);
  77
  78                 /*
  79                  * The binary representation of INT4 is in network byte order, which
  80                  * we'd better coerce to the local byte order.
  81                  */
  82                 ival = ntohl(*((uint32_t *) iptr));
  83
  84                 /*
  85                  * The binary representation of TEXT is, well, text, and since libpq
  86                  * was nice enough to append a zero byte to it, it'll work just fine
  87                  * as a C string.
  88                  *
  89                  * The binary representation of BYTEA is a bunch of bytes, which could
  90                  * include embedded nulls so we have to pay attention to field length.
  91                  */
  92                 blen = PQgetlength(res, i, b_fnum);
  93
  94                 printf("tuple %d: got\n", i);
  95                 printf(" i = (%d bytes) %d\n",
  96                            PQgetlength(res, i, i_fnum), ival);
  97                 printf(" t = (%d bytes) '%s'\n",
  98                            PQgetlength(res, i, t_fnum), tptr);
  99                 printf(" b = (%d bytes) ", blen);
 100                 for (j = 0; j < blen; j++)
 101                         printf("\\%03o", bptr[j]);
 102                 printf("\n\n");
 103         }
 104 }
 105
 106 int
 107 main(int argc, char **argv)
 108 {
 109         const char *conninfo;
 110         PGconn     *conn;
 111         PGresult   *res;
 112         const char *paramValues[1];
 113         int                     paramLengths[1];
 114         int                     paramFormats[1];
 115         uint32_t        binaryIntVal;
 116
 117         /*
 118          * If the user supplies a parameter on the command line, use it as the
 119          * conninfo string; otherwise default to setting dbname=postgres and using
 120          * environment variables or defaults for all other connection parameters.
 121          */
 122         if (argc > 1)
 123                 conninfo = argv[1];
 124         else
 125                 conninfo = "dbname = postgres";
 126
 127         /* Make a connection to the database */
 128         conn = PQconnectdb(conninfo);
 129
 130         /* Check to see that the backend connection was successfully made */
 131         if (PQstatus(conn) != CONNECTION_OK)
 132         {
 133                 fprintf(stderr, "Connection to database failed: %s",
 134                                 PQerrorMessage(conn));
 135                 exit_nicely(conn);
 136         }
 137
 138         /*
 139          * The point of this program is to illustrate use of PQexecParams() with
 140          * out-of-line parameters, as well as binary transmission of data.
 141          *
 142          * This first example transmits the parameters as text, but receives the
 143          * results in binary format.  By using out-of-line parameters we can avoid
 144          * a lot of tedious mucking about with quoting and escaping, even though
 145          * the data is text.  Notice how we don't have to do anything special with
 146          * the quote mark in the parameter value.
 147          */
 148
 149         /* Here is our out-of-line parameter value */
 150         paramValues[0] = "joe's place";
 151
 152         res = PQexecParams(conn,
 153                                            "SELECT * FROM test1 WHERE t = $1",
 154                                            1,           /* one param */
 155                                            NULL,        /* let the backend deduce param type */
 156                                            paramValues,
 157                                            NULL,        /* don't need param lengths since text */
 158                                            NULL,        /* default to all text params */
 159                                            1);          /* ask for binary results */
 160
 161         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 162         {
 163                 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
 164                 PQclear(res);
 165                 exit_nicely(conn);
 166         }
 167
 168         show_binary_results(res);
 169
 170         PQclear(res);
 171
 172         /*
 173          * In this second example we transmit an integer parameter in binary form,
 174          * and again retrieve the results in binary form.
 175          *
 176          * Although we tell PQexecParams we are letting the backend deduce
 177          * parameter type, we really force the decision by casting the parameter
 178          * symbol in the query text.  This is a good safety measure when sending
 179          * binary parameters.
 180          */
 181
 182         /* Convert integer value "2" to network byte order */
 183         binaryIntVal = htonl((uint32_t) 2);
 184
 185         /* Set up parameter arrays for PQexecParams */
 186         paramValues[0] = (char *) &binaryIntVal;
 187         paramLengths[0] = sizeof(binaryIntVal);
 188         paramFormats[0] = 1;            /* binary */
 189
 190         res = PQexecParams(conn,
 191                                            "SELECT * FROM test1 WHERE i = $1::int4",
 192                                            1,           /* one param */
 193                                            NULL,        /* let the backend deduce param type */
 194                                            paramValues,
 195                                            paramLengths,
 196                                            paramFormats,
 197                                            1);          /* ask for binary results */
 198
 199         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 200         {
 201                 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
 202                 PQclear(res);
 203                 exit_nicely(conn);
 204         }
 205
 206         show_binary_results(res);
 207
 208         PQclear(res);
 209
 210         /* close the connection to the database and cleanup */
 211         PQfinish(conn);
 212
 213         return 0;
 214 }