Client 3 - Processing Queries

Client 3 ”Processing Queries

For the rest of the examples in this chapter, I will use the Qt library to build the user interface. Qt is a toolkit that you can use to build complete, attractive GUI applications for Unix (Linux) and Windows systems. Using Qt complicates the code, but I hope you find that the results are worth it. To use any of these examples, you must be running in a GUI environment (either the X Window System or Microsoft Windows). The resulting applications are graphical in nature and cannot be executed without a windowing environment.

The PgConnection class doesn't really provide much functionality. Using a PgConnection object, you can make a connection attempt, determine whether the connection attempt succeeded or failed, and execute a simple command. You can't use a PgConnection object to retrieve result set information. To do that, you need a different kind of object ”a PgDatabase . The PgDatabase class inherits directly from PgConnection . Anything that you can do with a PgConnection object, you can also do with a PgDatabase object (but the reverse is not true). The PgDatabase class exposes member functions that you can use to process result set information. In the discussion that follows , I'll be talking about member functions exposed by PgConnection and PgDatabase . Keep in mind that because PgDatabase inherits from PgConnection , you can call PgConnection member functions using a PgDatabase object.

PgDatabase (and the PgConnection base class) provide three different member functions that you can use to execute a query.

First, int PgConnection::ExecTuplesOk(const char *query) sends a query to the backend and waits for a result set to be accumulated . ExecTuplesOk() should be used when you need to execute a query that returns rows (as opposed to a command, such as INSERT , that returns a simple result: the OID of the new row). ExecTuplesOk() returns a non-zero value if the given string was a query and the query was successful.

Next , int PgConnection::ExecCommandOk(const char *query) is identical to ExecTuplesOk() , except that it should be used for commands (rather than queries that can return rows). You would use ExecCommandOk() to execute commands such as INSERT , DELETE , or CREATE TABLE ”those commands that return a simple result, rather than an arbitrary number of rows. ExecCommandOk() returns a non-zero value if the given string was a command and the command executed successfully.

ExecStatusType PgConnection:: Exec (const char* query) is a general-purpose function that can execute either a command or a query. The Exec() member fun ction returns a value that is equal to one of the enumeration members shown in Table 10.1.

Table 10.1. PgConnection::Exec() Return Values

Return Value	Meaning
PGRES_EMPTY_QUERY	The given string did not contain a command
PGRES_COMMAND_OK	The given string contained a command and the command succeeded
PGRES_TUPLES_OK	The given string contained a query and the query succeeded
PGRES_COPY_OUT	A copy out operation has started
PGRES_COPY_IN	A copy in operation has started
PGRES_BAD_RESPONSE	A bad response was received from the server
PGRES_NONFATAL_ERROR	A non-fatal error has occurred
PGRES_FATAL_ERROR	A fatal error has occurred

The first five values in Table 10.1 indicate success.

If any of these query execution member functions indicates that an error ( PGRES_BAD_RESPONSE , PGRES_NONFATAL_ERROR , or PGRES_FATAL_ERROR ) has occurred, you can call the const char *PgConnection::ErrorMessage() function to retrieve the text of the error message.

Now, let's see how some of the query execution functions in a real application. The next client application that we'll look at, qt-query (see Listing 10.3), executes a single query and, if successful, displays the results in tabular form. When you run this program, you supply a query and an optional connection string on the command line ”for example:

 ./qt-query "SELECT * FROM tapes" "dbname=movies" 

qt-query attempts to connect to the specified database or the default database if you don't include the second command-line argument. If the connection attempt fails, you'll see an error message similar to the one shown in Figure 10.1.

Figure 10.1. qt-query error message.

If the connection attempt is successful, qt-query will send the query string (from the first command-line argument) to the server and display the results. If the query fails, you will see a message similar to the one shown in Figure 10.1 (although the message will be different). If the query is successful, you will see the results in a window similar to that shown in Figure 10.2.

Figure 10.2. qt-query results.

(Note: As in previous chapters, I'll start adding line numbers as the code listings become longer.)

Listing 10.3 qt-query.h

 1 /* qt-query.h */  2  3 class MyTable : public QTable  4 {  5 public:  6  7   MyTable( QWidget * parent, const char* connect, const char* query );  8  9   PgDatabase * db; 10 11 12 }; 13 14 class MyMain : public QWidget 15 { 16 17 public: 18   MyMain( const char * connect, const char * query ); 19 20 private: 21 22   MyTable   * table; 23 24 }; 

I've declared two classes: MyTable and MyMain .

The MyTable class inherits from Qt's QTable widget. QTable is a class that displays data in a tabular format ”you can see an example in Figure 10.2.

MyMain inherits from Qwidget ”a basic widget control that you will use to contain the other controls (a Quit button and the QTable widget) that you create.

Next, let's look at the implementation of these two classes. In Listings 10.4a and 10.4b, I've included the source code for three functions. The first two, main() and the MyMain::MyMain() constructor, are dealing primarily with Qt. The last function, MyTable::MyTable() , is where you start using the libpq++ classes to connect to a database, execute a query, and display the results.

If you aren't interested in the details of building a Qt application, you can skip ahead to Listing 10.4b.

Listing 10.4a qt-query.cpp

 1 /* qt-query.cpp */  2  3 #include <qapplication.h>    // QT Basic application classes  4 #include <qwidget.h>         // QT Basic widget class  5 #include <qtable.h>          // QT Table widget  6 #include <qmessagebox.h>     // QT MessageBox widget  7 #include <qlayout.h>         // QT Layout manager  8 #include <qpushbutton.h>     // QT Pushbutton widget  9 10 #include <libpq++.h>         // PostgreSQL libpq++ API 11 #include <iostream.h>        // Standard C++ io library 12 13 #include "qt-query.h" 14 15 int main( int argc, char * argv[] ) 16 { 17   QApplication app( argc, argv ); 18   MyMain       win( app.argv()[2], app.argv()[1] ); 19 20   app.setMainWidget( &win ); 21 22   win.show(); 23   app.exec(); 24   return( 0 ); 25 } 26 MyMain::MyMain( const char * connect, const char * query ) 27 { 28   QVBoxLayout *  vbox    = new QVBoxLayout( this ); 29 30   table = new MyTable( this, connect ? connect : "", query ); 31 32   QPushButton * quit = new QPushButton( "Quit", this ); 33 34   connect( quit, SIGNAL( clicked()), qApp, SLOT( quit())); 35 36   vbox->addWidget( table ); 37   vbox->addWidget( quit ); 38 39 } 

The first few lines of qt-query.cpp are used to #include various Qt header files. Each Qt class that you use is declared in a separate header file.

The main() function is purely concerned with setting up a Qt application. You start by defining a QApplication object ”every Qt application must have a QApplication. Next, you define a MyMain object (I'll explain this class in a moment). When you run this program, you have to provide at least one command-line argument. The first argument is a query string. The second argument, if present, should be a connection string (something like dbname=movies password=cows ). The second argument is a query string. The QApplication object examines the command line before you get a chance to parse it apart ( QApplication may remove Qt-specific arguments from the command line). To gain access to the post- processed command line, I use the app->argv() function to pass the first two arguments to the MyMain constructor. Line 20 tells the Qt library that you want to use the MyMain widget as the main application window. Lines 22 and 23 are used to start the Qt application.

Next, let's examine the MyMain constructor. Because you are writing a GUI application, there is a little bit of scaffolding that you have to include in your code to handle screen layout. You will use a layout manager to handle screen layout. The QVBoxLayout manager class gives you an easy way to arrange components within a vertical box (in other words, the widgets that you add to the layout manager are stacked vertically).

You can see the layout that you are trying to generate in Figure 10.3. The thick black line surrounding the QTable and QPushButton shows the QVBoxLayout (it will actually be invisible when you run the client; I'm just showing it here so you have some idea of its function). The QPushButton widget appears at the bottom of the QVBoxLayout and the QTable consumes the remaining real estate.

Figure 10.3. qt-query widget layout.

Line 30, defines a new MyTable object, sending it the connection string and query text. Most of the interesting stuff happens in the MyTable constructor, and I'll describe that function next.

Line 32 creates a pushbutton (with the label Quit), and line 34 arranges for the button to do something useful when you press it (in this case, you connect the clicked() signal with the applications quit() slot).

Signals and Slots ”The Qt Event Handling Architecture

In this context, signal and slot refer to the way that you wire together an action and a behavior in a Qt application. A widget fires a signal whenever an event occurs that affects that widget; for example, a QPushButton widget fires the clicked() signal when the user clicks on the button. A slot is a member function that can be connected to a signal. You are wiring the click() signal from your QPushButton widget to the quit() slot of the qApp object. When the user clicks on the QPushButton , it fires a click() signal, which is intercepted by the qApp 's quit() function. The quit() function causes the application to exit. That's pretty much all you need to know about signals and slots.

Lines 36 and 37 add the table widget and the pushbutton to your layout manager. Because you add the table widget first, it is at the top of the window and the pushbutton appears at the bottom of the window.

Now let's see the interesting code ”the MyTable constructor (shown in Listing 10.4b) is where you get back to interacting with libpq++.

Listing 10.4b qt-query.cpp

 41 MyTable::MyTable( QWidget * parent, 42      const char * connect, 43      const char * query ) 44   : QTable( parent ) 45 { 46   db = new PgDatabase( connect ? connect : "" ); 47 48   if( db->ConnectionBad()) 49   { 50     QMessageBox::critical( 0, "Connection failed", 51       db->ErrorMessage()); 52     exit( 1 ); 53   } 54 55   if( db->ExecTuplesOk( query ? query : "" )) 56   { 57     setNumRows( db->Tuples()); 58     setNumCols( db->Fields()); 59 60     for( int col = 0; col < db->Fields(); col++ ) 61     { 62       horizontalHeader()->setLabel( col, db->FieldName( col )); 63     } 64 65     for( int row = 0; row < db->Tuples(); row++ ) 66     { 67       for( int col = 0; col < db->Fields(); col++ ) 68       { 69         setText( row, col, db->GetValue( row, col )); 70       } 71     } 72   } 73   else 74   { 75     QMessageBox::critical( 0, "Query failed", db->ErrorMessage()); 76     exit( 1 ); 77   } 78 } 

Line 46 creates a new PgDatabase object ”the connection string that comes from the command-line argument one passes through. Recall that there are two constructors for a PgDatabase object ”the one that you are using expects a connection string and actually attempts to make a connection. If the connection attempt fails, a message displays and exits the entire application. Note that you use the PgDatabase::ErrorMessage() function to retrieve the error text and then display the reason for failure.

Line 55 executes the query text. If the query succeeds, you start filling our table widget with the result set. (If the query fails, line 75 displays the reason for failure and exit.)

The QTable widget makes it easy to build a nicely formatted table. Start by defining the number of rows and columns that you want in your table. How many rows do you want? The PgDatabase::Tuples() member function tells you how many rows in the result set. The PgDatabase::Fields() member function tells you how many columns that you need. After the table is properly sized , you want to build the column headers. PgDatabase::FieldName() returns the name of each field in the result set, and you just pass along that information to the horizontalHeader() component of our QTable .

Finally, you fill the table with the result set. The PgDatabase::GetValue() member function returns one field (within a given row) in the form of a NULL - terminated string. The QTable::setText() member function fills a given cell with a string. It can't get much easier than that.

When you run this program and click the Quit button, you may notice an ugly error message ( Unexpected EOF on client connection ) ”if you don't see the error, it will appear in your PostgreSQL server log. The database server generates this error message if you forget to close the database connection before your program ends. When you use the PgDatabase (or PgConnection ) class, the database connection is established by the class constructor and torn down by the class destructor. In this client application ( qt-query ), the destructor won't execute when you click the Quit button ”the Qt library calls exit() and doesn't give your C++ objects a chance to clean up themselves . I'll show you how to take care of this problem a little later in this chapter (see Listing 10.9e for more information).

Working with Transactions

The libpq++ library provides a class that makes it easy to work with transactions:

PgTransaction . A transaction is a group of one or more SQL commands that have a handy property: Either all the commands complete, or none of the commands complete. This is important in many applications in which you don't want to leave the database in an unknown state because some modification (or modifications) did not run to completion. When you wrap the modifications within a single transaction, PostgreSQL guarantees that the modifications are treated atomically ; that is, all the modifications persist, or none of them persists.

The PgTransaction class inherits directly from PgDatabase (which means that all the public member functions exposed by PgDatabase are available through a PgTransaction object as well). You can use the PgDatabase or PgConnection classes to manage transactions, but you have to execute the BEGIN WORK , COMMIT , and ROLLBACK commands yourself. The PgTransaction class provides an interesting alternative: It uses the lifetime of a C++ object to mark the beginning and ending points of a transaction.

Recall that when you create a PgDatabase object, the constructor expects a connection string and uses that string to establish a database connection. The constructor for a PgTransaction works the same way. Here is a code snippet that shows how to instantiate a PgTransaction object:

 int main( int argc, char * argv[] ) {     PgTransaction  tran( argv[1] ? argv[1] : "" );     if( tran.ConnectionBad())     {         cout << "Connection failed" << endl              << tran.ErrorMessage() << endl;     }     else     {         cout << "Connection ok" << endl;     } } 

When the constructor for tran executes, it attempts to establish a database connection and then executes a BEGIN WORK command ”this starts a new transaction. You can now use the PgTransaction object in the same way that you would use a PgDatabase object (remember that PgTransaction inherits from PgDatabase ).

When the PgTransaction is destroyed (in this case, it goes out of scope at the end of main() ), the PgTransaction destructor closes out the transaction. In PostgreSQL releases prior to 7.2, the PgTransaction destructor executes an END (or COMMIT ) command. Starting in version 7.2, PgTransaction destructor will ABORT (or ROLLBACK ) the transaction if you have not committed it.

So you can see that all the operations that you perform using a PgTransaction object are executed within a transaction block. The constructor starts a transaction and the destructor ends the transaction.

PgTransaction defines two protected member functions: BeginTransaction() and EndTransaction() . Because these member functions are protected (rather than private), you can manage the transaction yourself from a derived class. You might, for example, extend the PgTransaction class to execute a ROLLBACK command if a fatal error occurs.

One important note here: You probably won't use the PgTransaction class in complex applications. Each time you instantiate a PgTransaction object, you establish a new database connection. Each time a PgTransaction object is destroyed, the database connection is torn down. Those are expensive operations. Most likely, you will want to use a stripped-down PgDatabase object and execute BEGIN , COMMIT , and ROLLBACK commands yourself.

Another alternative is to create your own class to solve the connection/ teardown performance problem. Listing 10.5 is a short example that shows how you might construct such a class.

Listing 10.5 persist-tran.cpp

 1 /* persist-tran.cpp */  2 #include <libpq++.h>  3 #include <iostream.h>  4  5 class Transaction  6 {  7 public:  8  9   Transaction( PgDatabase & db ); 10   ~Transaction(); 11 12   PgDatabase & db; 13 }; 14 15 Transaction::Transaction( PgDatabase & myDb ) 16   : db( myDb ) 17 { 18   if( db.Status() == CONNECTION_OK ) 19   { 20     (void)db.Exec( "BEGIN" ); 21   } 22 } 23 24 Transaction::~Transaction() 25 { 26   if( db.Status() == CONNECTION_OK ) 27   { 28     (void)db.Exec( "COMMIT" ); 29   } 30 } 31 32 void do_transaction( PgDatabase & db ) 33 { 34   Transaction  tran( db ); 35 36   tran.db.Exec( "update customers set balance = balance * 1.10::numeric" ); 37 38 } 39 40 int main( int argc, char * argv[] ) 41 { 42   PgDatabase db( argv[1] ? argv[1]: "" ); 43 44   if( db.Status() != CONNECTION_OK ) 45   { 46     cout << "Connection failed" << endl << db.ErrorMessage() << endl; 47   } 48   else 49   { 50     do_transaction( db ); 51   } 52 } 

The Transaction class encapsulates a transaction, much as a PgTransaction would. The difference between the two is that a Transaction object works with an existing database connection, rather than creating a new one.

The main() function starts by creating a PgDatabase object. If the PgDatabase object is connected to a database, you pass that object to the do_transaction() function.

do_transaction() starts by creating a Transaction object ”the constructor for a Transaction requires a PgDatabase reference. At this point, the Transaction object has access to a database connection. Take a look at the constructor function for Transaction : When a Transaction object is created, it immediately begins a new transaction. Likewise, the destructor function will COMMIT the transaction (on the server) when the transaction goes out of scope.

Working with Cursors

Now that you know how to work with a transaction using PgTransaction , let's look at a class that extends PgTransaction to provide an easy-to-use cursor interface: PgCursor .

A cursor is a mechanism that allows an application to process the rows in a result set in smaller chunks , rather than having to deal with the entire result set at once. SQL is a set-oriented language, but programmers using procedural languages (such as C++) find it easier to deal with one row at a time.

The PgCursor class encapsulates cursor operations. PgCursor inherits from PgTransaction (which inherits from PgDatabase , which inherits from PgConnection ), so you can do anything with a PgCursor object that you can do with the base classes.

The constructor for a PgCursor object requires two arguments: a connection string and a cursor name. When you instantiate a PgCursor object, the constructor will establish a database connection and remember the cursor name (the cursor isn't active at this point; you still have to provide the query for the cursor).

After the database connection is successfully established (remember that you still need to check for connection success yourself), you can use the PgCursor:: Declare() member function to create the cursor. Declare() expects two arguments: the query text and an indicator that specifies whether the cursor should be a binary cursor. (Remember from Chapter 8 that a binary cursor returns data in PostgreSQL-internal form and a non-binary cursor returns data in the form of NULL -terminated strings.)

 int main( int argc, char * argv[] ) {     PgCursor  cursor( "dbname=accounting", "next_record");     if( cursor.ConnectionBad())     {         cout << "Connection failed" << endl              << cursor.ErrorMessage() << endl;     }     else     {         cout << "Connection ok" << endl;     }     if( !cursor.Declare( "select * from returns", 0 ))     {         cout << "DECLARE failed:" << endl              << cursor.ErrorMessage() << endl;     } } 

The call to cursor.Declare() sends the following command to the server:

 DECLARE next_record CURSOR FOR select * from returns 

If the DECLARE command fails, the Declare() function will return .

Now that you have established a cursor, there are three member functions that you can use to control the cursor:

 int Fetch( const char* dir = "FORWARD" ) int Fetch( unsigned num, const char* dir = "FORWARD" ); int Close(); 

Each of the Fetch() functions sends a FETCH command to the server. The first Fetch() function sends a FETCH ALL command to the server. You can use the second Fetch() function to fetch a specific number of rows. By default, each FETCH command is a FORWARD fetch, and you can specify other options using the dir parameter.

Now let's look at a sample that shows how to use the Fetch() functions with a Qt Table widget. When you run this program, you provide two command-line arguments: a connection string and a select statement. For example, if you invoke the application as

 $ ./qt-cursor "select * from pg_tables" "dbname=movies" 

you will see a screen similar to that shown in Figure 10.4.

Figure 10.4. Sample qt-cursor display.

You can press any of the Fetch buttons at the bottom of the window to experiment with the various cursor operations.

Like the previous example, this application uses the Qt toolkit to construct the user interface. Also like the previous example, there is some extra setup work that you have to do in order to build a Qt application. Let's start by looking at the class declarations (see Listing 10.6).

Listing 10.6 qt-cursor.h

 1 /* qt-cursor.h */  2  3 class MyTable : public QTable  4 {  5   Q_OBJECT  6  7 public:  8  9   MyTable( QWidget * parent, const char * connect, const char * query ); 10 11   PgCursor * cursor; 12 13 public slots: 14  void fetch( int id ); 15 16 private: 17  void buildTable( void ); 18 19 }; 20 21 class MyMain : public QWidget 22 { 23 24 public: 25   MyMain( const char * connect, const char * query ); 26 27 private: 28  MyTable   * table; 29 30 }; 

Listing 10.6 shows the qt-cursor.h file. This file declares the two classes that you will need to build: MyTable and MyMain . If you look ahead to Listing 10.7 ( qt-cursor.cpp ), you may notice that I don't #include "qt-cursor.h" ; instead, I #include "qt-cursor.moc" . Why? The MyTable class (at lines 13 and 14)declares a new slot. You might remember (from the initial discussion of Qt earlier in this chapter) that a slot is a member function that can be connected to a signal (a signal is an even, such as a mouse click). If you tried to #include the qt-cursor.h file as written, your C++ compiler would complain about the word "slots" at line 13. Instead, any header file that defines a new Qt slot must be processed by the moc preprocessor. When you run a header file through moc , the preprocessor will produce an equivalent .moc file that can be compiled by a C++ compiler. The makefile included with the sample code for this book takes care of running the moc preprocessor for you.

Listing 10.7 qt-cursor.cpp

 1 /* qt-cursor.cpp */   2   3 #include <qapplication.h>    // QT Basic application classes   4 #include <qwidget.h>         // QT Basic widget class   5 #include <qtable.h>          // QT Table widget   6 #include <qmessagebox.h>     // QT MessageBox widget   7 #include <qlayout.h>         // QT Layout manager   8 #include <qpushbutton.h>     // QT Pushbutton widget   9 #include <qhbuttongroup.h>   // QT Button group widget  10  11 #include <libpq++.h>         // PostgreSQL libpq++ API  12 #include <iostream.h>        // Standard C++ io library  13  14 #include "qt-cursor.moc"  15  16 void main( int argc, char * argv[] )  17 {  18   QApplication app( argc, argv );  19   MyMain       win( app.argv()[2], app.argv()[1] );  20  21   app.setMainWidget( &win );  22  23   win.show();  24   app.exec();  25 }  26  27 MyMain::MyMain( const char * connect, const char * query )  28 {  29   QVBoxLayout   * vbox  = new QVBoxLayout( this );  30   QHButtonGroup * group = new QHButtonGroup( this );  31  32   table = new MyTable( this, connect ? connect : "", query );  33  34   new QPushButton( "Quit", group );       // id = 0  35   new QPushButton( "Fetch All", group );  // id = 1  36   new QPushButton( "Fetch Prev", group ); // id = 2  37   new QPushButton( "Fetch Next", group ); // id = 3  38   new QPushButton( "Prev 5", group );     // id = 4  39   new QPushButton( "Next 5", group );     // id = 5  40  41   vbox->addWidget( table );  42   vbox->addWidget( group );  43  44   connect( group, SIGNAL( clicked( int )),  45   table, SLOT( fetch( int )));  46 }  47  48 void MyTable::fetch( int id )  49 {  50   int result;  51  52   switch( id )  53   {  54     case 0:  55       QApplication::exit( 0 );  56       break;  57  58     case 1:  // Fetch All  59       result = cursor->Fetch();  60       break;  61  62     case 2:  // Fetch Previous  63       result = cursor->Fetch( 1, "backward" );  64       break;  65  66     case 3:  // Fetch Next  67       result = cursor->Fetch( 1, "forward" );  68       break;  69  70     case 4:  // Fetch Previous 5  71       result = cursor->Fetch( 5, "backward" );  72       break;  73  74     case 5:  // Fetch Next 5  75       result = cursor->Fetch( 5, "forward" );  76       break;  77   }  78  79   if( result == 0 )  80   {  81     QMessageBox::critical( 0, "fetch failed",  82     cursor->ErrorMessage());  83   }  84   else  85   {  86     buildTable();  87   }  88 }  89  90 void MyTable::buildTable( void )  91 {  92   setNumRows( cursor->Tuples());  93   setNumCols( cursor->Fields());  94  95   for( int col = 0; col < cursor->Fields(); col++ )  96   {  97       horizontalHeader()->setLabel( col, cursor->FieldName( col ));  98   }  99 100   for( int row = 0; row < cursor->Tuples(); row++ ) 101   { 102     for( int col = 0; col < cursor->Fields(); col++ ) 103     { 104       setText( row, col, cursor->GetValue( row, col )); 105     } 106   } 107 } 108 109 MyTable::MyTable( QWidget * parent, 110   const char * connect, 111   const char * query ) 112   : QTable( parent ) 113 { 114   cursor = new PgCursor( connect, "my_cursor" ); 115 116   if( cursor->ConnectionBad()) 117   { 118     QMessageBox::critical( 0, "Connection failed", 119     cursor->ErrorMessage()); 120     exit( -1 ); 121   } 122 123   if( !cursor->Declare( query )) 124   { 125     QMessageBox::critical( 0, "Query failed", 126     cursor->ErrorMessage()); 127     exit( -1 ); 128   } 129 } 

Let's start by looking at the MyMain::MyMain() constructor This function creates the bulk of your user interface. The main window has a table positioned at the top and a row of buttons at the bottom. The QVBoxLayout object stacks the table over the buttons, and the QHButtonGroup arranges the buttons in a horizontal row.

Line 32 creates a new MyTable object, which I'll discuss in a moment.

Next, you create the buttons. Because you are looking at the PgCursor::Fetch() methods , I've created a button for each of the major operations.

The remainder of the MyMain::MyMain() constructor is devoted to wiring the buttons and the table into the Qt API. Rather than managing each button individually, you create a QHButtonGroup object that manages the entire group. When you create each button, you specify that the parent of the button is a QHButtonGroup . Each button is assigned an id , starting at 0 (the buttons are automatically assigned an id based on the order of creation). The call to connect() arranges for the Qt library to call MyTable::fetch( int ) whenever you press one of the buttons within the button group. Qt passes the id of the selected button as the one and only parameter.

Next, let's look at the MyTable constructor. You start building a MyTable object by creating a new PgCursor object. As usual, pass a connection string to the PgCursor constructor and provide a cursor name. At this point, the PgCursor object has connected to the database, but it hasn't actually executed any commands yet. It remembers the name of the cursor, but it won't actually create the cursor until you call the Declare() member function.

When you call the Declare() function, you are executing a command on the server. In this example, you send the following command:

 DECLARE my_cursor CURSOR FOR SELECT * FROM pg_tables; 

If anything goes wrong with the DECLARE ... CURSOR command, the Declare() function will return .

After the MyTable constructor completes, the Qt library displays the (empty) table to the user and waits for a button press.

When you press one of the Fetch buttons, Qt will call the MyTable::fetch() function, giving you the button id as a parameter. Inside of MyTable::fetch() , you examine the button id and decide which of the PgCursor::Fetch() functions to call. Table 10.2 shows you the correspondence between button labels and calls to Fetch() :

Table 10.2. PGCursor::Fetch() Function Examples

Button Label	Calls
Fetch All	Fetch()
Fetch Prev	Fetch( 1, "backward" )
Fetch Next	Fetch( 1, "forward" )
Fetch Prev 5	Fetch( 5, "backward" )
Fetch Next 5	Fetch( 5, "forward" )

Let's look at what happens the first time you press one of the Fetch buttons, say Fetch Next 5. Before calling PgCursor::Fetch() , the PgCursor object has just processed a DECLARE ... CURSOR command. The result set for this object reflects the status of the DECLARE ... CURSOR command. If you were to call cursor->Tuples() or cursor->Fields() at this point, you would find that the DECLARE ... CURSOR statement returns rows and columns. When you call the PgCursor::Fetch() function, the result set for the DECLARE ... CURSOR command is replaced by the result set for a FETCH command. At this point, a call to cursor->Tuples() would return 5 (or fewer if there are fewer than five rows left in the cursor). After the result set has been assembled , you call buildTable() to actually populate the table control.

The buildTable() function (that follows) makes use of the PgDatabase:: FieldName() , PgDatabase::GetValue() , PgDatabase::Tuples() , and PgDatabase::Fields() functions to create the table column headers and the table cells .

 void MyTable::buildTable( void ) {   setNumRows( cursor->Tuples());   setNumCols( cursor->Fields());   for( int col = 0; col < cursor->Fields(); col++ )   {       horizontalHeader()->setLabel( col, cursor->FieldName( col ));   }   for( int row = 0; row < cursor->Tuples(); row++ )   {     for( int col = 0; col < cursor->Fields(); col++ )     {       setText( row, col, cursor->GetValue( row, col ));     }   } } 

Working with Large-Objects

Most of the tables that you create are defined in terms of simple data types. You already know that PostgreSQL provides numeric, textual, date-time, geometric, and logical data types. But what data type should you use to store photographs? Or .MP3 audio files?

One answer is a large-object (you might also see the term BLOB , or binary-large-object ). A large-object is just an entry in the pg_largeobject system table. PostgreSQL provides a few predefined functions that make it reasonably easy to work large-objects.

A second alternative is the BYTEA data type. A column of type BYTEA can store an arbitrarily sized string of octets (also known as bytes). The BYTEA data type is similar to the VARCHAR data type but there are some important differences. First, a VARCHAR value cannot hold a character whose value is 0 ” I'm not talking about the character '0' whose value is actually 48 (see http://www.asciitable.com); I mean the character often called NULL . A BYTEA value can hold any 8-bit character. Second, a VARCHAR value is defined in terms of some specific character set (usually US ASCII). This means that the collation sequence that is used when you compare two VARCHAR values may be based on something other than just the numeric value of each byte. When you compare two BYTEA values, the relationship between the two values is determined by comparing the numeric value of each character.

Whether you choose to use the large-object interface or the BYTEA data type depends mostly on how large your data is and what you need to do with it. A BYTEA column can hold up to 1GB ”a large-object can hold values larger than 1GB. PostgreSQL provides a few functions that make it easy to load binary data from an external file into a large-object. Loading external data into a BYTEA column isn't quite so easy. When you insert data into a BYTEA column, you must translate the data into a quoted (also called escaped) form (see Chapter 2, "Working with Data in PostgreSQL"). When you SELECT data from a BYTEA column, it comes back in quoted form and that's not always easy to work with (you have to parse through the result and unquote it yourself). When you retrieve data from a large-object, you get the same binary data that you put into it, but you have to get at the data using some special functions, described in this section.

For more information on the BYTEA data type, refer to Chapter 2. In this section, I'll describe how to work with large-objects using libpq++.

Let's say that you want to add a picture to the tapes table ”for each tape, you want to store a photograph of the box that was shipped with the tape. Currently, the tapes table looks like this:

 CREATE TABLE tapes (     tape_id     character(8),     title       character varying(80) ); 

Because you aren't actually storing a photograph in this table (remember that large-objects are stored in the pg_largeobject table), you add a large-object identifier instead. A large-object identifier has a data type of OID. Here's what the new tapes table looks like after adding the row reference:

 CREATE TABLE tapes (     tape_id    character(8),     title      character varying(80),     photo_id   oid ); 

It's important to remember that the photo_id column doesn't actually hold a photograph ”it holds the address of a row in the pg_largeobjects table.

To store a photo in PostgreSQL, you might use the lo_import() function. lo_import() takes a filename as an argument and returns an oid as a result ”for example:

 INSERT INTO tapes VALUES (        'AA-55892',        'Casablanca',        lo_import('/tmp/casablanca.jpg' ) ); 

The call to lo_import() opens the /tmp/Casablanca.jpg file, imports the contents of that file into the pg_largeobjects table, and returns the oid of the new large-object ”we insert the oid into the photo_id column.

After you have a photo in your database, what can you do with it? It doesn't make a lot of sense to SELECT the photo from a text-based client ”you would just see a lot of binary garbage.

You could use the lo_export() function to copy a photo back out to the filesystem. For example:

 SELECT lo_export( photo_id, '/tmp/casa2.jpg' ) WHERE tape_id = 'AA-5892'; 

If you are using the libpq++ class library, you can use the PgLargeObject class. PgLargeObject inherits from the PgConnection class ”anything that you can do with a PgConnection object you can do with a PgLargeObject object.

PgLargeObject offers a few member functions specifically designed for working with large-objects. The PgLargeObject::Import() function imports a file and returns the oid . Of course, there is an Export() function as well.

The other interesting members of the PgLargeObject class are the Open () , Read() , and LSeek() functions. After you Open() a large-object, you can use the LSeek() and Read() functions to read the binary data into your application.

Here is a snippet of code that shows how you might use a PgLargeObject to read a photo (or audio file or whatever) into your application:

 oid   photo_id = 27642; PgLargeObject  photo( photo_id, "dbname=movies" ); int size = photo.LSeek( 0, SEEK_END ); void * photo_bits = malloc( size ); photo.LSeek( 0, SEEK_SET ); photo.Read( photo_bits, size ); photo.Close(); 

When you create a PgLargeObject object, you specify a large-object identifier as the first constructor argument and an optional connect string as the second argument. The PgLargeObject constructor connects to the database and opens the specified large-object.

The first call to LSeek() tells you how many bytes you need to allocate to hold the entire picture. The second call to LSeek() positions back to the beginning of the large-object. The call to Read() fills your buffer ( photo_bits ) with the actual contents of the large-object.

After you have read the large-object into your application, you can take whatever action is appropriate to the object. For example, if the large-object contains an audio file, you might want to play it for the user; if the large-object contains a photograph, you may want to display it. You can't do either of those things using a text-mode user interface (such as psql ), but if you are creating your own client application, you can process large-objects however you need.

PgLargeObject exports other member functions to create and delete large-objects from memory (the Import() function creates a large-object based on the contents of a file).

Like the PgTransaction class, you aren't likely to use PgLargeObject directly within a sophisticated application. Each time you create a PgLargeObject , you are spawning a new backend database process (this is expensive). If you need to work with large-objects in a C++ application, you'll probably want to implement your own large-object class that doesn't spawn and then close a new database process for each large-object. I would recommend reading the source code for the PgLargeObject class as a starting point for building your own large-object manager class.