src/test/examples/testlibpq3.c

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