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
exit_nicely(PGconn * conn)47 exit_nicely(PGconn *conn)
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
show_binary_results(PGresult * res)59 show_binary_results(PGresult *res)
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
main(int argc,char ** argv)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 }
232