PostgreSQL Source Code  git master
testlibpq3.c
Go to the documentation of this file.
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  * CREATE TABLE test1 (i int4, t text, b bytea);
14  * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
15  * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
16  *
17  * The expected output is:
18  *
19  * tuple 0: got
20  * i = (4 bytes) 1
21  * t = (11 bytes) 'joe's place'
22  * b = (5 bytes) \000\001\002\003\004
23  *
24  * tuple 0: got
25  * i = (4 bytes) 2
26  * t = (8 bytes) 'ho there'
27  * b = (5 bytes) \004\003\002\001\000
28  */
29 
30 #ifdef WIN32
31 #include <windows.h>
32 #endif
33 
34 #include <stdio.h>
35 #include <stdlib.h>
36 #include <stdint.h>
37 #include <string.h>
38 #include <sys/types.h>
39 #include "libpq-fe.h"
40 
41 /* for ntohl/htonl */
42 #include <netinet/in.h>
43 #include <arpa/inet.h>
44 
45 
46 static void
48 {
49  PQfinish(conn);
50  exit(1);
51 }
52 
53 /*
54  * This function prints a query result that is a binary-format fetch from
55  * a table defined as in the comment above. We split it out because the
56  * main() function uses it twice.
57  */
58 static void
60 {
61  int i,
62  j;
63  int i_fnum,
64  t_fnum,
65  b_fnum;
66 
67  /* Use PQfnumber to avoid assumptions about field order in result */
68  i_fnum = PQfnumber(res, "i");
69  t_fnum = PQfnumber(res, "t");
70  b_fnum = PQfnumber(res, "b");
71 
72  for (i = 0; i < PQntuples(res); i++)
73  {
74  char *iptr;
75  char *tptr;
76  char *bptr;
77  int blen;
78  int ival;
79 
80  /* Get the field values (we ignore possibility they are null!) */
81  iptr = PQgetvalue(res, i, i_fnum);
82  tptr = PQgetvalue(res, i, t_fnum);
83  bptr = PQgetvalue(res, i, b_fnum);
84 
85  /*
86  * The binary representation of INT4 is in network byte order, which
87  * we'd better coerce to the local byte order.
88  */
89  ival = ntohl(*((uint32_t *) iptr));
90 
91  /*
92  * The binary representation of TEXT is, well, text, and since libpq
93  * was nice enough to append a zero byte to it, it'll work just fine
94  * as a C string.
95  *
96  * The binary representation of BYTEA is a bunch of bytes, which could
97  * include embedded nulls so we have to pay attention to field length.
98  */
99  blen = PQgetlength(res, i, b_fnum);
100 
101  printf("tuple %d: got\n", i);
102  printf(" i = (%d bytes) %d\n",
103  PQgetlength(res, i, i_fnum), ival);
104  printf(" t = (%d bytes) '%s'\n",
105  PQgetlength(res, i, t_fnum), tptr);
106  printf(" b = (%d bytes) ", blen);
107  for (j = 0; j < blen; j++)
108  printf("\\%03o", bptr[j]);
109  printf("\n\n");
110  }
111 }
112 
113 int
114 main(int argc, char **argv)
115 {
116  const char *conninfo;
117  PGconn *conn;
118  PGresult *res;
119  const char *paramValues[1];
120  int paramLengths[1];
121  int paramFormats[1];
122  uint32_t binaryIntVal;
123 
124  /*
125  * If the user supplies a parameter on the command line, use it as the
126  * conninfo string; otherwise default to setting dbname=postgres and using
127  * environment variables or defaults for all other connection parameters.
128  */
129  if (argc > 1)
130  conninfo = argv[1];
131  else
132  conninfo = "dbname = postgres";
133 
134  /* Make a connection to the database */
135  conn = PQconnectdb(conninfo);
136 
137  /* Check to see that the backend connection was successfully made */
138  if (PQstatus(conn) != CONNECTION_OK)
139  {
140  fprintf(stderr, "Connection to database failed: %s",
141  PQerrorMessage(conn));
142  exit_nicely(conn);
143  }
144 
145  /* Set always-secure search path, so malicous 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 }
int PQgetlength(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3129
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6116
static void exit_nicely(PGconn *conn)
Definition: testlibpq3.c:47
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3118
int main(int argc, char **argv)
Definition: testlibpq3.c:114
void PQfinish(PGconn *conn)
Definition: fe-connect.c:3638
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2724
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2647
PGconn * conn
Definition: streamutil.c:55
static void show_binary_results(PGresult *res)
Definition: testlibpq3.c:59
int PQfnumber(const PGresult *res, const char *field_name)
Definition: fe-exec.c:2832
void PQclear(PGresult *res)
Definition: fe-exec.c:671
PGresult * PQexecParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char *const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat)
Definition: fe-exec.c:1911
int i
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1897
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6063
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:592