/*
* testlibpq.c
*
* Test the C version of libpq,the Postgresql frontend library.
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc,char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields;
int i,j;
/*
* If the user supplies a parameter on the command line,use it as the
* conninfo string; otherwise default to setting dbname=postgres and using
* environment variables or defaults for all other connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr,"Connection to database Failed: %s",PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Our test case here involves using a cursor,for which we must be inside
* a transaction block. We could do the whole thing with a single
* PQexec() of "select * from pg_database",but that's too trivial to make
* a good example.
*/
/* Start a transaction block */
res = PQexec(conn,"BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr,"BEGIN command Failed: %s",PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* Should PQclear PGresult whenever it is no longer needed to avoid memory
* leaks
*/
PQclear(res);
/*
* Fetch rows from pg_database,the system catalog of databases
*/
res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr,"DECLARE CURSOR Failed: %s",PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn,"FETCH ALL in myportal");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr,"FETCH ALL Failed: %s",PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/* first,print out the attribute names */
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s",PQfname(res,i));
printf("\n\n");
/* next,print out the rows */
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-15s",PQgetvalue(res,i,j));
printf("\n");
}
PQclear(res);
/* close the portal ... we don't bother to check for errors ... */
res = PQexec(conn,"CLOSE myportal");
PQclear(res);
/* end the transaction */
res = PQexec(conn,"END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
/*
* testlibpq2.c
* Test of the asynchronous notification interface
*
* Start this program,then from psql in another window do
* NOTIFY TBL2;
* Repeat four times to get this program to exit.
*
* Or,if you want to get fancy,try this:
* populate a database with the following commands
* (provided in src/test/examples/testlibpq2.sql):
*
* CREATE TABLE TBL1 (i int4);
*
* CREATE TABLE TBL2 (i int4);
*
* CREATE RULE r1 AS ON INSERT TO TBL1 DO
* (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
*
* and do this four times:
*
* INSERT INTO TBL1 VALUES (10);
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include <libpq-fe.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc,char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
PGnotify *notify;
int nnotifies;
/*
* If the user supplies a parameter on the command line,PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Issue LISTEN command to enable notifications from the rule's NOTIFY.
*/
res = PQexec(conn,"LISTEN TBL2");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr,"LISTEN command Failed: %s",PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* should PQclear PGresult whenever it is no longer needed to avoid memory
* leaks
*/
PQclear(res);
/* Quit after four notifies are received. */
nnotifies = 0;
while (nnotifies < 4)
{
/*
* Sleep until something happens on the connection. We use select(2)
* to wait for input,but you could also use poll() or similar
* facilities.
*/
int sock;
fd_set input_mask;
sock = PQsocket(conn);
if (sock < 0)
break; /* shouldn't happen */
FD_ZERO(&input_mask);
FD_SET(sock,&input_mask);
if (select(sock + 1,&input_mask,NULL,NULL) < 0)
{
fprintf(stderr,"select() Failed: %s\n",strerror(errno));
exit_nicely(conn);
}
/* Now check for input */
PQconsumeInput(conn);
while ((notify = PQnotifies(conn)) != NULL)
{
fprintf(stderr,"ASYNC NOTIFY of '%s' received from backend PID %d\n",notify->relname,notify->be_pid);
PQfreemem(notify);
nnotifies++;
}
}
fprintf(stderr,"Done.\n");
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
/*
* testlibpq3.c
* Test out-of-line parameters and binary I/O.
*
* Before running this,populate a database with the following commands
* (provided in src/test/examples/testlibpq3.sql):
*
* CREATE TABLE test1 (i int4,t text,b bytea);
*
* INSERT INTO test1 values (1,'joe''s place','\\000\\001\\002\\003\\004');
* INSERT INTO test1 values (2,'ho there','\\004\\003\\002\\001\\000');
*
* The expected output is:
*
* tuple 0: got
* i = (4 bytes) 1
* t = (11 bytes) 'joe's place'
* b = (5 bytes) \000\001\002\003\004
*
* tuple 0: got
* i = (4 bytes) 2
* t = (8 bytes) 'ho there'
* b = (5 bytes) \004\003\002\001\000
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <libpq-fe.h>
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
/*
* This function prints a query result that is a binary-format fetch from
* a table defined as in the comment above. We split it out because the
* main() function uses it twice.
*/
static void
show_binary_results(PGresult *res)
{
int i,j;
int i_fnum,t_fnum,b_fnum;
/* Use PQfnumber to avoid assumptions about field order in result */
i_fnum = PQfnumber(res,"i");
t_fnum = PQfnumber(res,"t");
b_fnum = PQfnumber(res,"b");
for (i = 0; i < PQntuples(res); i++)
{
char *iptr;
char *tptr;
char *bptr;
int blen;
int ival;
/* Get the field values (we ignore possibility they are null!) */
iptr = PQgetvalue(res,i_fnum);
tptr = PQgetvalue(res,t_fnum);
bptr = PQgetvalue(res,b_fnum);
/*
* The binary representation of INT4 is in network byte order,which
* we'd better coerce to the local byte order.
*/
ival = ntohl(*((uint32_t *) iptr));
/*
* The binary representation of TEXT is,well,text,and since libpq
* was nice enough to append a zero byte to it,it'll work just fine
* as a C string.
*
* The binary representation of BYTEA is a bunch of bytes,which could
* include embedded nulls so we have to pay attention to field length.
*/
blen = PQgetlength(res,b_fnum);
printf("tuple %d: got\n",i);
printf(" i = (%d bytes) %d\n",PQgetlength(res,i_fnum),ival);
printf(" t = (%d bytes) '%s'\n",t_fnum),tptr);
printf(" b = (%d bytes) ",blen);
for (j = 0; j < blen; j++)
printf("\\%03o",bptr[j]);
printf("\n\n");
}
}
int
main(int argc,char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
/*
* If the user supplies a parameter on the command line,PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* The point of this program is to illustrate use of PQexecParams() with
* out-of-line parameters,as well as binary transmission of data.
*
* This first example transmits the parameters as text,but receives the
* results in binary format. By using out-of-line parameters we can
* avoid a lot of tedious mucking about with quoting and escaping,even
* though the data is text. Notice how we don't have to do anything
* special with the quote mark in the parameter value.
*/
/* Here is our out-of-line parameter value */
paramValues[0] = "joe's place";
res = PQexecParams(conn,"SELECT * FROM test1 WHERE t = $1",1,/* one param */
NULL,/* let the backend deduce param type */
paramValues,/* don't need param lengths since text */
NULL,/* default to all text params */
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr,"SELECT Failed: %s",PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
show_binary_results(res);
PQclear(res);
/*
* In this second example we transmit an integer parameter in binary
* form,and again retrieve the results in binary form.
*
* Although we tell PQexecParams we are letting the backend deduce
* parameter type,we really force the decision by casting the parameter
* symbol in the query text. This is a good safety measure when sending
* binary parameters.
*/
/* Convert integer value "2" to network byte order */
binaryIntVal = htonl((uint32_t) 2);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
res = PQexecParams(conn,"SELECT * FROM test1 WHERE i = $1::int4",paramLengths,paramFormats,1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr,PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
show_binary_results(res);
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
