Example
Code for Common Functions
Example 1:
/*
* testlibpq.c
*/
#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;
/*
* This value is used when the user provides the value of the conninfo character string in the command line.
* Otherwise, the environment variables or the default values
* are used for all other connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname=postgres port=42121 host='10.44.133.171' application_name=test connect_timeout=5 sslmode=allow user='test' password='test_1234'";
/* Connect to the database. */
conn = PQconnectdb(conninfo);
/* Check whether the backend connection has been successfully established. */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Since a cursor is used in the test case, a transaction block is required.
* Put all data in one "select * from pg_database"
* PQexec() is too simple and is not recommended.
*/
/* 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);
}
/*
* PQclear PGresult should be executed when it is no longer needed, to avoid memory leakage.
*/
PQclear(res);
/*
* Fetch data from the pg_database system catalog.
*/
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 name. */
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");
/* Print lines. */
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 do not need to check for errors. */
res = PQexec(conn, "CLOSE myportal");
PQclear(res);
/* End the transaction. */
res = PQexec(conn, "END");
PQclear(res);
/* Close the database connection and clean up the database. */
PQfinish(conn);
return 0;
}
Example 2:
/*
* testlibpq2.c
* Test out-of-line parameters and binary I/Os.
*
* Before running this example, run the following command to populate a database:
*
*
* CREATE TABLE test1 (i int4, t text);
*
* INSERT INTO test1 values (2, 'ho there');
*
* The expected output is as follows:
*
*
* tuple 0: got
* i = (4 bytes) 2
* t = (8 bytes) 'ho there'
*
*/
#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 is used to print out the query results. The results are in binary format
* and fetched from the table created in the comment above.
*/
static void
show_binary_results(PGresult *res)
{
int i;
int i_fnum,
t_fnum;
/* Use PQfnumber to avoid assumptions about field order in the result. */
i_fnum = PQfnumber(res, "i");
t_fnum = PQfnumber(res, "t");
for (i = 0; i < PQntuples(res); i++)
{
char *iptr;
char *tptr;
int ival;
/* Obtain the field value. (Ignore the possibility that they may be null). */
iptr = PQgetvalue(res, i, i_fnum);
tptr = PQgetvalue(res, i, t_fnum);
/*
* The binary representation of INT4 is the network byte order,
* which is better to be replaced with the local byte order.
*/
ival = ntohl(*((uint32_t *) iptr));
/*
* The binary representation of TEXT is text. Since libpq can append a zero byte to it,
* and think of it as a C string.
*
*/
printf("tuple %d: got\n", i);
printf(" i = (%d bytes) %d\n",
PQgetlength(res, i, i_fnum), ival);
printf(" t = (%d bytes) '%s'\n",
PQgetlength(res, i, t_fnum), tptr);
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 provides a parameter on the command line,
* The value of this parameter is a conninfo character string. Otherwise,
* Use environment variables or default values.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname=postgres port=42121 host='10.44.133.171' application_name=test connect_timeout=5 sslmode=allow user='test' password='test_1234'";
/* Connect to the database. */
conn = PQconnectdb(conninfo);
/* Check whether the connection to the server was successfully established. */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Convert the integer value "2" to the network byte order. */
binaryIntVal = htonl((uint32_t) 2);
/* Set the parameter array for PQexecParams. */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* Binary */
res = PQexecParams(conn,
"SELECT * FROM test1 WHERE i = $1::int4",
1, /* One parameter */
NULL, /* Enable the backend to deduce the parameter type. */
paramValues,
paramLengths,
paramFormats,
1); /* require 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);
/* Close the database connection and clean up the database. */
PQfinish(conn);
return 0;
}
Feedback