1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
/*
* testlibpq3.c
* Test the C version of LIBPQ, the POSTGRES frontend library.
* tests the binary cursor interface
*
*
*
populate a database by doing the following:
CREATE TABLE test1 (i int4, d float4, p polygon);
INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon);
INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon);
the expected output is:
tuple 0: got
i = (4 bytes) 1,
d = (4 bytes) 3.567000,
p = (4 bytes) 2 points boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000)
tuple 1: got
i = (4 bytes) 2,
d = (4 bytes) 89.050003,
p = (4 bytes) 2 points boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000)
*
*/
#include <stdio.h>
#include "libpq-fe.h"
#include "utils/geo-decls.h" /* for the POLYGON type */
void exit_nicely(PGconn* conn)
{
PQfinish(conn);
exit(1);
}
main()
{
char *pghost, *pgport, *pgoptions, *pgtty;
char* dbName;
int nFields;
int i,j;
int i_fnum, d_fnum, p_fnum;
PGconn* conn;
PGresult* res;
/* begin, by setting the parameters for a backend connection
if the parameters are null, then the system will try to use
reasonable defaults by looking up environment variables
or, failing that, using hardwired constants */
pghost = NULL; /* host name of the backend server */
pgport = NULL; /* port of the backend server */
pgoptions = NULL; /* special options to start up the backend server */
pgtty = NULL; /* debugging tty for the backend server */
dbName = getenv("USER"); /* change this to the name of your test database*/
/* make a connection to the database */
conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/* check to see that the backend connection was successfully made */
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr,"Connection to database '%s' failed.\n", dbName);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
/* start a transaction block */
res = PQexec(conn,"BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr,"BEGIN command failed\n");
PQclear(res);
exit_nicely(conn);
}
/* should PQclear PGresult whenever it is no longer needed to avoid
memory leaks */
PQclear(res);
/* fetch instances from the pg_database, the system catalog of databases*/
res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from test1");
if (res == NULL ||
PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr,"DECLARE CURSOR command failed\n");
if (res)
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn,"FETCH ALL in mycursor");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr,"FETCH ALL command didn't return tuples properly\n");
if (res)
PQclear(res);
exit_nicely(conn);
}
i_fnum = PQfnumber(res,"i");
d_fnum = PQfnumber(res,"d");
p_fnum = PQfnumber(res,"p");
for (i=0;i<3;i++) {
printf("type[%d] = %d, size[%d] = %d\n",
i, PQftype(res,i),
i, PQfsize(res,i));
}
for (i=0; i < PQntuples(res); i++) {
int *ival;
float *dval;
int plen;
POLYGON* pval;
/* we hard-wire this to the 3 fields we know about */
ival = (int*)PQgetvalue(res,i,i_fnum);
dval = (float*)PQgetvalue(res,i,d_fnum);
plen = PQgetlength(res,i,p_fnum);
/* plen doesn't include the length field so need to increment by VARHDSZ*/
pval = (POLYGON*) malloc(plen + VARHDRSZ);
pval->size = plen;
memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen);
printf("tuple %d: got\n", i);
printf(" i = (%d bytes) %d,\n",
PQgetlength(res,i,i_fnum), *ival);
printf(" d = (%d bytes) %f,\n",
PQgetlength(res,i,d_fnum), *dval);
printf(" p = (%d bytes) %d points \tboundbox = (hi=%f/%f, lo = %f,%f)\n",
PQgetlength(res,i,d_fnum),
pval->npts,
pval->boundbox.xh,
pval->boundbox.yh,
pval->boundbox.xl,
pval->boundbox.yl);
}
PQclear(res);
/* close the portal */
res = PQexec(conn, "CLOSE mycursor");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
}
|
