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  * 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
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
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, "Connection to database failed: %s",
142  PQerrorMessage(conn));
143  exit_nicely(conn);
144  }
145 
146  /* Set always-secure search path, so malicious users can't take control. */
147  res = PQexec(conn, "SET search_path = testlibpq3");
148  if (PQresultStatus(res) != PGRES_COMMAND_OK)
149  {
150  fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
151  PQclear(res);
152  exit_nicely(conn);
153  }
154  PQclear(res);
155 
156  /*
157  * The point of this program is to illustrate use of PQexecParams() with
158  * out-of-line parameters, as well as binary transmission of data.
159  *
160  * This first example transmits the parameters as text, but receives the
161  * results in binary format. By using out-of-line parameters we can avoid
162  * a lot of tedious mucking about with quoting and escaping, even though
163  * the data is text. Notice how we don't have to do anything special with
164  * the quote mark in the parameter value.
165  */
166 
167  /* Here is our out-of-line parameter value */
168  paramValues[0] = "joe's place";
169 
170  res = PQexecParams(conn,
171  "SELECT * FROM test1 WHERE t = $1",
172  1, /* one param */
173  NULL, /* let the backend deduce param type */
174  paramValues,
175  NULL, /* don't need param lengths since text */
176  NULL, /* default to all text params */
177  1); /* ask for binary results */
178 
179  if (PQresultStatus(res) != PGRES_TUPLES_OK)
180  {
181  fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
182  PQclear(res);
183  exit_nicely(conn);
184  }
185 
186  show_binary_results(res);
187 
188  PQclear(res);
189 
190  /*
191  * In this second example we transmit an integer parameter in binary form,
192  * and again retrieve the results in binary form.
193  *
194  * Although we tell PQexecParams we are letting the backend deduce
195  * parameter type, we really force the decision by casting the parameter
196  * symbol in the query text. This is a good safety measure when sending
197  * binary parameters.
198  */
199 
200  /* Convert integer value "2" to network byte order */
201  binaryIntVal = htonl((uint32_t) 2);
202 
203  /* Set up parameter arrays for PQexecParams */
204  paramValues[0] = (char *) &binaryIntVal;
205  paramLengths[0] = sizeof(binaryIntVal);
206  paramFormats[0] = 1; /* binary */
207 
208  res = PQexecParams(conn,
209  "SELECT * FROM test1 WHERE i = $1::int4",
210  1, /* one param */
211  NULL, /* let the backend deduce param type */
212  paramValues,
213  paramLengths,
214  paramFormats,
215  1); /* ask for binary results */
216 
217  if (PQresultStatus(res) != PGRES_TUPLES_OK)
218  {
219  fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
220  PQclear(res);
221  exit_nicely(conn);
222  }
223 
224  show_binary_results(res);
225 
226  PQclear(res);
227 
228  /* close the connection to the database and cleanup */
229  PQfinish(conn);
230 
231  return 0;
232 }
int PQgetlength(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3175
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6596
static void exit_nicely(PGconn *conn)
Definition: testlibpq3.c:48
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3164
int main(int argc, char **argv)
Definition: testlibpq3.c:115
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4098
#define printf(...)
Definition: port.h:198
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2770
#define fprintf
Definition: port.h:196
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2693
PGconn * conn
Definition: streamutil.c:56
static void show_binary_results(PGresult *res)
Definition: testlibpq3.c:60
int PQfnumber(const PGresult *res, const char *field_name)
Definition: fe-exec.c:2878
void PQclear(PGresult *res)
Definition: fe-exec.c:695
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:1954
int i
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1940
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6543
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:678