PgDatabase
This is the basic database access class. It is derived from PgConnection and adds facilities to handle the results of data manipulation commands. These additional methods should be used only after a query has been sent to the backend and results are being received.
Besides the methods inherited from PgConnection, PgDatabase provides the methods listed in the table that follows. These methods are described later in this section.
Table 2-2. PgDatabase Interface
| Method | Description |
|---|---|
| PgDatabase | Makes a new connection to a backend database server. |
| Tuples | Obtain the number of rows in the result. |
| Fields | Obtain the number of columns in each result tuple. |
| FieldName | Obtain the name of the given column. |
| FieldNum | Obtain the column index associated with the given column name. |
| FieldType | Obtain the oid of the type of the given column. |
| FieldSize | Obtain the size (in bytes) of the given column. |
| GetValue | Retrieves a single column value. |
| GetIsNull | Test if the value of the given column is null. |
| GetLength | Retrieves the length (in bytes) of a column value. |
| CmdTuples | Obtain the number of rows affected by an INSERT, UPDATE, or DELETE. |
| GetLine | Reads a newline-terminated line of characters (transmitted by the backend). |
| PutLine | Sends a null-terminated string to the backend. |
| EndCopy | Synchronizes with the backend. |
Database Connection Methods
PgDatabase
PgDatabase makes a new connection to a backend database server.
PgDatabase::PgDatabase(count char *comminfo) |
Its argument is the same and its operation similar to PgConnection(), but it creates a PgDatabase object instead.
After you create an instance of a PgDatabase, check it to ensure that the connection to the database succeeded before sending queries to the object. You can do this by retrieving the current status of the PgDatabase object with the inherited Status or ConnectionBad methods. (See the Section called PgConnection Database Connection Methods.)
Retrieving SELECT Result Information
The PgDatabase object encapsulates a PGresult structure (see Chapter 1). You can use the following methods to retrieve data after a query has been sent to the backend and results are being received.
 | Only one PGresult exists per PgDatabase object. (That is, only one result per connection can be handled at one time.) |
Tuples
Tuples obtains the number of rows in the result.
int PgDatabase::Tuples() |
Returns the number of rows in the query result. It is a wrapper for the libpq function PQntuples (see the Section called PQntuples in Chapter 1).
Fields
Fields returns the number of columns in each result tuple.
int PgDatabase::Fields() |
Returns the number of columns in each tuple of the query result. It is a wrapper for the libpq function PQnfields (see the Section called PQnfields in Chapter 1).
FieldName
FieldName returns the name of a given column.
const char * PgDatabase::FieldName(int field_num) |
Returns the name of the column associated with a given column index. Indices start at 0. It is a wrapper for the libpq function PQfname (see the Section called PQfname in Chapter 1).
FieldNum
FieldNum obtains the column index associated with the given column name.
int PgDatabase::FieldNum(const char * field_name) |
Returns the column index associated with a given column name. -1 is returned if the given name does not match any field. Indices start at 0. It is a wrapper for the libpq function PQfnumber (see the Section called PQfnumber in Chapter 1).
FieldType
FieldType obtains the Oid of the type of the given column.
Oid PgDatabase::FieldType(int field_num) Oid PgDatabase::FieldType(const char * field_name) |
Returns the Oid of the type of the given column, which can be specified by index or by name. Indices start at 0. It is a wrapper for the libpq function PQftype (see the Section called PQftype in Chapter 1).
FieldSize
FieldSize obtains the size (in bytes) of the given column.
Oid PgDatabase::FieldSize(int field_num) Oid PgDatabase::FieldSize(const char * field_name) |
Returns the Oid of the type of the given column, which can be specified by index or by name. Indices start at 0. This is the space allocated for this field in a database tuple (in other words, the size of the server's binary representation of the data type). -1 is returned if the field is variable in size.
FieldSize is a wrapper for the libpq function PQfsize (see the Section called PQfsize in Chapter 1).
Retrieving SELECT Result Values
GetValue
GetValue retrieves a single column value.
const char * PgDatabase::GetValue(int tup_num, int field_num) const char * PgDatabase::GetValue(int tup_num, const char * field_name) |
Returns a single column value of one row (tup_num) of a result. The value returned by GetValue is a null-terminated ASCII string representation of the attribute value. You can specify the column by index or by name. Row and column indices start at 0. It is a wrapper for the libpq function PQgetvalue (see the Section called PQgetvalue in Chapter 1).
Note that for NULL fields GetValue returns the empty string, not the NULL pointer. Use GetIsNull (described below) to check for NULL values.
GetIsNull
GetIsNull tests if the value of a given column is NULL.
bool char * PgDatabase::GetIsNull(int tup_num, int field_num) bool char * PgDatabase::GetIsNull(int tup_num, const char * field_name) |
Returns TRUE when the value of a given column in a given row (tup_num) is NULL. The column can be specified by index or by name. Row and column indices start at 0. It is a wrapper for the libpq function PQgetisnull (see the Section called PQgetisnull in Chapter 1).
GetLength
GetLength retrieves the length (in bytes) of a column value.
int PgDatabase::GetLength(int tup_num, int field_num) int PgDatabase::GetLength(int tup_num, const char * field_name) |
GetLength returns the length (in bytes) of a column value in the specified row (tup_num). The column can be specified by index or by name. Row and column indices start at 0. This is the actual length for the particular value (that is, the size of the object pointed to by GetValue). Note that for ASCII-represented values, this size has little to do with the binary size reported by FieldSize(). This is a wrapper for the libpq function PQgetlength() (see the Section called PQgetlength in Chapter 1).
Retrieving Non-SELECT Result Information
CmdTuples
CmdTuples obtains the number of rows affected by an INSERT UPDATE, or DELETE.
int PgDatabase::CmdTuples() |
Returns the number of rows affected by an INSERT, UPDATE, or DELETE command. For all other commands it returns -1. It is similar to the libpq function PQcmdTuple (see the Section called PQcmdTuples in Chapter 1), but it returns an integer, not a string.
Methods Associated with the COPY Command
The COPY command in Red Hat Database has options to read from or to write to the network connection used by libpq++. Special methods are provided so that applications can take advantage of this capability.
GetLine
GetLine reads a newline-terminated line of characters (transmitted by the backend).
int PgDatabase::GetLine(char* string, int length) |
GetLine reads a newline-terminated line of characters (transmitted by the backend) into a buffer string of size length.
Like the Linux 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 the null character.
GetLine returns EOF at end of file, 0 if the entire line has been read, or 1 if the buffer is full but the terminating newline has not yet been read.
The application must check to see if a line consists of a single period ("."), which indicates that the backend 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 examine the return value of GetLine.
PutLine
PutLine sends a null-terminated string to the backend.
void PgDatabase::PutLine(char* string) |
The application must explicitly send the two characters "\." on a line by themselves to the backend to indicate that it has completed sending data.
EndCopy
EndCopy synchronizes with the backend.
int PgDatabase::EndCopy() |
This function blocks until the backend has finished processing the copy. It should either be issued when the last string has been sent to the backend using PutLine or when the last string has been received from the backend using GetLine. It must be issued to avoid synchronization problems with the backend/frontend connection.
The return value is 0 on successful completion, nonzero on failure.
For example, a table can be created and populated as follows:
/* COPY Example */
/* Description: CREATE and populate it using COPY */
/* Create an instance */
PgDatabase data("dbname=dbname");
/* Create the target table */
data.Exec("create table foo (a int4, b char(16), d double precision)");
/* Start the COPY operation to populate the table */
data.Exec("copy foo from stdin");
/* Send the COPY data */
data.PutLine("3\tHello World\t4.5\n");
data.PutLine("4\tGoodbye World\t7.11\n");
...
/* Send completion string for the end of the data */
data.PutLine("\\.\n");
/* Synchronize with the backend */
data.EndCopy(); |
Examples of PgDatabase Use
This first example implements a small interactive loop where queries can be entered interactively and sent to the backend. Note that the header file libpq++.h must be included. The program must also be linked with the libpq++ library (by using the -lpq++ linker option).
You can find additional libpq++ examples in src/interfaces/libpq++/examples
Example 2-1. libpq++ Example Program 1
#include <iostream.h>
#include "libpq++.h"
int main()
{
// Open the connection to the database and make sure it's OK
PgDatabase data("dbname=template1");
if ( data.ConnectionBad() ) {
cout << "Connection was unsuccessful..." << endl
<< "Error message returned: " << data.ErrorMessage() << endl;
return 1;
}
else
cout << "Connection successful... Enter queries below:" << endl;
// Interactively obtain and execute queries
ExecStatusType status;
string buf;
int done = 0;
while (!done)
{
cout << "> ";
cout.flush();
getline(cin, buf);
if ( buf != "" )
if ( (status = data.Exec( buf.c_str() ))
== PGRES_TUPLES_OK )
data.DisplayTuples();
else
cout << "No tuples returned..." << endl
<< "status = " << status << endl
<< "Error returned: "
<< data.ErrorMessage() << endl;
else
done = 1;
}
return 0;
} // End main() |
Example 2-2. libpq++ Example Program 2
The second example queries the template1 database for a list of database names.
#include <iostream.h>
#include <iomanip.h>
#include "libpq++.h"
int main()
{
// Begin, by establishing a connection to the backend.
// When no parameters are given then the system will
// try to use reasonable defaults by
// looking up environment variables
// or, failing that, using hardwired constants
const char* dbName = "dbname=template1";
PgDatabase data(dbName);
// check to see that the backend connection
// was successfully made
if ( data.ConnectionBad() ) {
cerr << "Connection to database '"
<< dbName << "' failed." << endl
<< "Error returned: "
<< data.ErrorMessage() << endl;
exit(1);
}
// start a transaction block
if ( !data.ExecCommandOk("BEGIN") ) {
cerr << "BEGIN command failed" << endl;
exit(1);
}
// submit command to the backend
if ( !data.ExecCommandOk("DECLARE myportal CURSOR
FOR select * from pg_database") ) {
cerr << "DECLARE CURSOR command failed" << endl;
exit(1);
}
// fetch instances from the pg_database,
// the system catalog of databases
if ( !data.ExecTuplesOk("FETCH ALL in myportal") ) {
cerr << "FETCH ALL didn't return tuples properly" << endl;
exit(1);
}
// first, print out the attribute names
int nFields = data.Fields();
for (int i=0; i < nFields; i++)
cout << setiosflags(ios::right)
<< setw(15) << data.FieldName(i);
cout << endl << endl;
// next, print out the instances
for (int i=0; i < data.Tuples(); i++) {
for (int j=0; j < nFields; j++)
cout << setiosflags(ios::right)
<< setw(15) << data.GetValue(i,j);
cout << endl;
}
// Close the portal
data.Exec("CLOSE myportal");
// End the transaction
data.Exec("END");
return 0;
} |
Example 2-3. libpq++ Example Program 3
In the third example, the Asynchronous Notification methods inherited from PgConnection are used.
Assume that the following commands were previously issued:
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]; |
After the sample program begins executing, if someone issues a command:
INSERT INTO TBL1 VALUES (10); |
the program would display a "notification received" message.
#include <iostream.h>
#include "libpq++.h"
#include <stdlib.h>
main()
{
// Begin, by connecting to the backend using
// hardwired constants and a test database
// created by the user prior to the invocation
// of this test program.
char* dbName = "dbname=template1";
PgDatabase data(dbName);
// Check to see that the backend connection
// was successfully made
if ( data.ConnectionBad() ) {
cerr << "Connection to database '"
<< dbName << "' failed." << endl
<< data.ErrorMessage() << endl;
exit(1);
}
// Listen to a table
if ( !data.ExecCommandOk("LISTEN TBL2") ) {
cerr << "LISTEN command failed" << endl;
exit(1);
}
// Test asynchronous notification
while (1) {
// check for asynchronous returns
PGnotify* notify = data.Notifies();
if (notify) {
cerr << "ASYNC NOTIFY of '" << notify->relname
<< "' from backend pid '" << notify->be_pid
<< "' received" << endl;
free(notify);
break;
}
}
return 0;
} |