libpq++ C++ libpq++ is the C++ API to PostgreSQL. libpq++ is a set of classes that allow client programs to connect to the PostgreSQL backend server. These connections come in two forms: a database class and a large object class. The database class is intended for manipulating a database. You can send all sorts of SQL queries and commands to the PostgreSQL backend server and retrieve the responses of the server. The large object class is intended for manipulating a large object in a database. Although a large object instance can send normal queries to the PostgreSQL backend server it is only intended for simple queries that do not return any data. A large object should be seen as a file stream. In the future it should behave much like the C++ file streams cin, cout and cerr. This chapter is based on the documentation for the libpq C library (see ). There are several examples of libpq++ applications in src/interfaces/libpq++/examples in the source distribution. The following environment variables can be used to set up default values for an environment and to avoid hard-coding database names into an application program: Refer to for a complete list of available connection options. The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb or PQsetdbLogin if no value is directly specified by the calling code. These are useful to avoid hard-coding database names into simple application programs. libpq++ uses only environment variables or libpq's PQconnectdb conninfo style strings. PGHOST sets the default server name. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored (default /tmp). PGPORT sets the default TCP port number or Unix-domain socket file extension for communicating with the PostgreSQL backend. PGDATABASE sets the default PostgreSQL database name. PGUSER sets the user name used to connect to the database and for authentication. PGPASSWORD sets the password used if the backend demands password authentication. This is not recommended because the password can be read by others using the ps command with special options on some platforms. PGREALM sets the Kerberos realm to use with PostgreSQL, if it is different from the local realm. If PGREALM is set, PostgreSQL applications will attempt authentication with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. This environment variable is only used if Kerberos authentication is selected by the backend. PGOPTIONS sets additional runtime options for the PostgreSQL backend. PGTTY sets the file or tty on which debugging messages from the backend server are displayed. The following environment variables can be used to specify user-level default behavior for every PostgreSQL session: PGDATESTYLE sets the default style of date/time representation. PGTZ sets the default time zone. The following environment variables can be used to specify default internal behavior for every PostgreSQL session: PGGEQO sets the default mode for the genetic optimizer. Refer to the SET SQL command for information on correct values for these environment variables. The connection class makes the actual connection to the database and is inherited by all of the access classes. The database class provides C++ objects that have a connection to a backend server. To create such an object one first needs the appropriate environment for the backend to access. The following constructors deal with making a connection to a backend server from a C++ program. PgConnection makes a new connection to a backend database server. PgConnection::PgConnection(const char *conninfo) The conninfo string is the same as for the underlying libpq PQconnectdb function. Although typically called from one of the access classes, a connection to a backend server is possible by creating a PgConnection object. ConnectionBad returns whether or not the connection to the backend server succeeded or failed. bool PgConnection::ConnectionBad() const Returns true if the connection failed. Status returns the status of the connection to the backend server. ConnStatusType PgConnection::Status() Returns either CONNECTION_OK or CONNECTION_BAD depending on the state of the connection. PgDatabase makes a new connection to a backend database server. PgDatabase(const char *conninfo) After a PgDatabase has been created it should be checked to make sure the connection to the database succeeded before sending queries to the object. This can easily be done by retrieving the current status of the PgDatabase object with the Status or ConnectionBad methods. DBName returns the name of the current database. const char *PgConnection::DBName() Notifies returns the next notification from a list of unhandled notification messages received from the backend. PGnotify* PgConnection::Notifies() See PQnotifies in libpq for details. Exec sends a command to the backend server. It's probably more desirable to use one of the next two functions. ExecStatusType PgConnection::Exec(const char* query) Returns the result status of the command. The following status results can be expected: PGRES_EMPTY_QUERY PGRES_COMMAND_OK, if the command was not a query PGRES_TUPLES_OK, if the query successfully returned tuples PGRES_COPY_OUT PGRES_COPY_IN PGRES_BAD_RESPONSE, if an unexpected response was received PGRES_NONFATAL_ERROR PGRES_FATAL_ERROR ExecCommandOk sends a non-query command (one that does not return rows) to the backend server. int PgConnection::ExecCommandOk(const char *query) Returns true (1) if the command succeeds. ExecTuplesOk Sends a query command (one that returns rows) to the backend server. int PgConnection::ExecTuplesOk(const char *query) Returns true (1) if the query succeeds. ErrorMessage returns the last error message text. const char *PgConnection::ErrorMessage() Tuples returns the number of tuples (rows) in the query result. int PgDatabase::Tuples() const Fields returns the number of fields (rows) in each tuple of the query result. int PgDatabase::Fields() FieldName returns the field (column) name associated with the given field index. Field indices start at 0. const char *PgDatabase::FieldName(int field_num) const FieldNum returns the field (column) index associated with the given field name. int PgDatabase::FieldNum(const char* field_name) const -1 is returned if the given name does not match any field. FieldType returns the field type associated with the given field index. The integer returned is an internal coding of the type. Field indices start at 0. Oid PgDatabase::FieldType(int field_num) const FieldType returns the field type associated with the given field name. The integer returned is an internal coding of the type. Field indices start at 0. Oid PgDatabase::FieldType(const char* field_name) const FieldSize returns the size in bytes of the field associated with the given field index. Field indices start at 0. int PgDatabase::FieldSize(int field_num) const Returns the space allocated for this field in a database tuple given the field number. In other words the size of the server's binary representation of the data type. -1 is returned if the field is variable size. FieldSize returns the size in bytes of the field associated with the given field index. Field indices start at 0. int PgDatabase::FieldSize(const char *field_name) const Returns the space allocated for this field in a database tuple given the field name. In other words the size of the server's binary representation of the data type. -1 is returned if the field is variable size. GetValue returns a single field (column) value of one tuple of a PGresult. Tuple and field indices start at 0. const char *PgDatabase::GetValue(int tup_num, int field_num) const For most queries, the value returned by GetValue is a null-terminated string representation of the attribute value. But if BinaryTuples is true, the value returned by GetValue is the binary representation of the type in the internal format of the backend server (but not including the size word, if the field is variable-length). It is then the programmer's responsibility to cast and convert the data to the correct C type. The pointer returned by GetValue points to storage that is part of the PGresult structure. One should not modify it, and one must explicitly copy the value into other storage if it is to be used past the lifetime of the PGresult structure itself. BinaryTuples is not yet implemented. GetValue returns a single field (column) value of one tuple of a PGresult. Tuple and field indices start at 0. const char *PgDatabase::GetValue(int tup_num, const char *field_name) const For most queries, the value returned by GetValue is a null-terminated string representation of the attribute value. But if BinaryTuples is true, the value returned by GetValue is the binary representation of the type in the internal format of the backend server (but not including the size word, if the field is variable-length). It is then the programmer's responsibility to cast and convert the data to the correct C type. The pointer returned by GetValue points to storage that is part of the PGresult structure. One should not modify it, and one must explicitly copy the value into other storage if it is to be used past the lifetime of the PGresult structure itself. BinaryTuples is not yet implemented. GetLength returns the length of a field (column) in bytes. Tuple and field indices start at 0. int PgDatabase::GetLength(int tup_num, int field_num) const This is the actual data length for the particular data value, that is the size of the object pointed to by GetValue. Note that for character-represented values, this size has little to do with the binary size reported by PQfsize. GetLength returns the length of a field (column) in bytes. Tuple and field indices start at 0. int PgDatabase::GetLength(int tup_num, const char* field_name) const This is the actual data length for the particular data value, that is the size of the object pointed to by GetValue. Note that for character-represented values, this size has little to do with the binary size reported by PQfsize. GetIsNull returns whether a field has the null value. bool GetIsNull(int tup_num, int field_num) const Note that GetValue will return the empty string for null fields, not the NULL pointer. GetIsNull returns whether a field has the null value. bool GetIsNull(int tup_num, const char *field_name) const Note that GetValue will return the empty string for null fields, not the NULL pointer. DisplayTuples prints out all the tuples and, optionally, the attribute names to the specified output stream. void PgDatabase::DisplayTuples(FILE *out = 0, bool fillAlign = true, const char* fieldSep = "|", bool printHeader = true, bool quiet = false) const This function is obsolescent. PrintTuples prints out all the tuples and, optionally, the attribute names to the specified output stream. void PgDatabase::PrintTuples(FILE *out = 0, bool printAttName = true, bool terseOutput = false, bool fillAlign = false) const This function is obsolescent. CmdTuples returns the number of rows affected after an INSERT, UPDATE, or DELETE. If the command was anything else, it returns -1. int PgDatabase::CmdTuples() const OidStatus const char *PgDatabase::OidStatus() const PostgreSQL supports asynchronous notification via the LISTEN and NOTIFY commands. A backend registers its interest in a particular notification condition with the LISTEN command. All backends that are listening on a particular condition will be notified asynchronously when a NOTIFY of that name is executed by another backend. No additional information is passed from the notifier to the listener. Thus, typically, any actual data that needs to be communicated is transferred through a relation. libpq++ applications are notified whenever a connected backend has received an asynchronous notification. However, the communication from the backend to the frontend is not asynchronous. The libpq++ application must poll the backend to see if there is any pending notification information. After the execution of a command, a frontend may call PgDatabase::Notifies to see if any notification data is currently available from the backend. PgDatabase::Notifies returns the notification from a list of unhandled notifications from the backend. The function returns NULL if there are no pending notifications from the backend. PgDatabase::Notifies behaves like the popping of a stack. Once a notification is returned from PgDatabase::Notifies, it is considered handled and will be removed from the list of notifications. PgDatabase::Notifies retrieves pending notifications from the server. PGnotify* PgDatabase::Notifies() The second sample program gives an example of the use of asynchronous notification. The COPY command in PostgreSQL has options to read from or write to the network connection used by libpq++. Therefore, functions are necessary to access this network connection directly so applications may take full advantage of this capability. PgDatabase::GetLine reads a newline-terminated line of characters (transmitted by the backend server) into a buffer string of size length. int PgDatabase::GetLine(char* string, int length) Like the Unix system routine fgets(), this routine copies up to length-1 characters into string. It is like gets(), however, in that it converts the terminating newline into a zero byte. PgDatabase::GetLine returns EOF at end of file, 0 if the entire line has been read, and 1 if the buffer is full but the terminating newline has not yet been read. Notice that the application must check to see if a new line consists of a backslash followed by a period (\.), which indicates that the backend server has finished sending the results of the COPY. Therefore, if the application ever expects to receive lines that are more than length-1 characters long, the application must be sure to check the return value of PgDatabase::GetLine very carefully. PgDatabase::PutLine Sends a null-terminated string to the backend server. void PgDatabase::PutLine(char* string) The application must explicitly send the characters \. to indicate to the backend that it has finished sending its data. PgDatabase::EndCopy synchronizes with the backend. int PgDatabase::EndCopy() This function waits until the backend has finished processing the COPY. It should either be issued when the last string has been sent to the backend using PgDatabase::PutLine or when the last string has been received from the backend using PgDatabase::GetLine. It must be issued or the backend may get out of sync with the frontend. Upon return from this function, the backend is ready to receive the next command. The return value is 0 on successful completion, nonzero otherwise. As an example: PgDatabase data; data.Exec("CREATE TABLE foo (a int4, b char(16), d double precision)"); data.Exec("COPY foo FROM STDIN"); data.PutLine("3\tHello World\t4.5\n"); data.PutLine("4\tGoodbye World\t7.11\n"); ... data.PutLine("\\.\n"); data.EndCopy();