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