Database Connection

• PGconn *PQconnectdb(const char *conninfo)
• returns: PGconn connection handle
• conninfo is a string built of name=value pairs where name is on of:
• dbname
• host
• hostaddr
• port
• user
• password
• tty
• requiressl
• PGconn *PQconnectStart(const char *conninfo) - non-blocking connect to backend
• PostgresPollingStatusType PGconnectPoll(PGconn *conn) - status of socket
• returns:
• PGRES_POLLING_ACTIVE - connection is active
• PGRES_POLLING_READING - select()/poll() PQsocket(conn) for reading
• PGRES_POLLING_WRITING - select()/poll() PQsocket(conn) for writing
• PGRES_POLLING_OK - connection is established
• PGRES_POLLING_FAILED - connection failed
•  ConnStatusType PQstatus(PGconn *conn) - status of a connection
• returns:
• CONNECTION_STARTED - connection starting up
• CONNECTION_MADE - connection is ready for use
• CONNECTION_BAD - connection failed
• CONNECTION_AWAITING_RESPONSE - connection started, waiting for response from postmaster
• CONNECTION_AUTH_OK - Authentication sent and received, waiting for backend startup
• CONNECTION_SETENV - Environment negotiation in progress
• void PQfinish(PGconn *) - close a connection

01   #include "libpq-fe.h"
02   #include <stdio.h>
03
04   int main(void)
05   {
06      PGconn *conn;
07      conn = PQconnectdb("user=postgres dbname=a");
08
09      switch(PQstatus(conn)) {
10              case CONNECTION_BAD:
11                      fprintf(stderr,"Could not establish connection\n");
12                      exit(1);
13                      break;
14              case CONNECTION_STARTED:
15                      fprintf(stderr,"Connecting...\n");
16                      break;
17              case CONNECTION_OK:
18                      fprintf(stderr,"Connected\n");
19                      break;
20              default:
21                      break;
22      }
23      PQfinish(conn);
24   }

01   #include "libpq-fe.h"
02   #include <stdio.h>
03
04   #include <sys/time.h>
05   #include <sys/types.h>
06   #include <unistd.h>
07
08
09   int main(void)
10   {
11      int i;
12      PGconn *conn;
13       struct timeval tv;
14      conn = PQconnectStart("user=postgres dbname=a");
15      tv.tv_sec = 0;
16      tv.tv_usec = 50;
17
18      i = 0;
19      while(i<100) {
20              switch(PQconnectPoll(conn)) {
21                      case PGRES_POLLING_ACTIVE:
22                              fprintf(stderr,"polling active");
23                              break;
24                      case PGRES_POLLING_WRITING:
25                              fprintf(stderr,"polling writing");
26                              select(1,NULL,(fd_set *)PQsocket(conn),NULL,&tv);
27                              break;
28                      case PGRES_POLLING_READING:
29                              fprintf(stderr,"polling reading");
30                              select(1,(fd_set *)PQsocket(conn),NULL, NULL,&tv);
31                              break;
32                      case PGRES_POLLING_OK:
33                              fprintf(stderr,"connection ready");
34                              break;
35                      case PGRES_POLLING_FAILED:
36                              fprintf(stderr,"failed"); exit(1);
37              }
38              i++;
39      }
40      PQfinish(conn);
41   }

Query Execution

• PGresult *PQexec(PGconn *conn,const char *query)
• returns PGresult pointer
• PQexec() will block
• ExecStatusType PQresultStatus(const PGresult *res)
• returns:
• PGRES_EMPTY_QUERY - query sent to the backend was empty
• PGRES_COMMAND_OK - query returning no data (delete, insert, etc) was successful
• PGRES_TUPLES_OK - query was successful, even if no tuples returned
• PGRES_NONFATAL_ERROR
• PGRES_FATAL_ERROR
• void PQclear(PGresult *res) - free up memory held by query results
• char *PQresultErrorMessage(PGresult *res) - return error message for res

Retrieving SELECT results

• int PQntuples(PGresult *res) - number of tuples returned
• int PQnfields(PGresult *res) - number of fields per tuple in the result set
• char *PQgetvalue(PGresult *res, int tup_num, int field_num) - get the result for the given field

01   #include "libpq-fe.h"
02   #include <string.h>
03
04   int main(void)
05   {
06      PGconn *conn;
07      PGresult *res;
08      int i,j;
09      conn = PQconnectdb("user=postgres dbname=a");
10      if(PQstatus(conn) == CONNECTION_BAD) {
11              fprintf(stderr,"Connection failed: %s\n",PQerrorMessage(conn));
12              exit(1);
13      }
14
15      res = PQexec(conn,"SELECT id,firstname,lastname from employees;");
16      if(PQresultStatus(res) != PGRES_TUPLES_OK) {
17              fprintf(stderr,"Query failed: %s\n",PQresultErrorMessage(res));
18              exit(1);
19      }
20
21      for(i=0;i<PQntuples(res);i++) {
22              for(j=0;j<PQnfields(res);j++) {
23                      printf("%s: %s ",PQfname(res,j),PQgetvalue(res,i,j));
24              }
25              printf("\n");
26      }
27      PQclear(res);
28      PQfinish(conn);
29      return(0);
30   }

• int PQsetnonblocking(conn,int arg) - When arg is TRUE, query processing is non-blocking
• int PQsendQuery(conn,const char *query) - send query to backend and don't block
• PQgetResult(PGconn *conn) - returns non-null until query is finished
• int PQflush(PGconn *conn) - flush data to backend
• int PQresultCancel(PGconn *conn) - cancel the query currently being processed by the backend

• Library mostly thread safe, except for PGconn structure - cannot manipulate across threads
• Must not deadlock using non-blocking in thread of control

Prev

Next