Client 4 - An Interactive Query Processor

Client 4 ”An Interactive Query Processor

At this point, you should be familiar with the five basic libpq++ classes: PgConnection , PgDatabase , PgTransaction , PgLargeObject , and PgCursor . The PgDatabase class probably forms the basis of most of your libpq++ applications, so I'd like to explore it a bit more for the final application in this chapter.

The PgDatabase class exposes all the member functions that you need to process a result set. The following member functions return the number of rows and columns (respectively) returned by a SELECT statement:

 int PgDatabase::Tuples(); int PgDatabase::Fields(); 

The PgDatabase::CmdTuples() member function returns the number of rows affected by an INSERT , UPDATE , or DELETE command. If the most recent command was not an INSERT , UPDATE , or DELETE , PgDatabase::CmdTuples() will return “1.

 int CmdTuples(); 

The PgDatabase::FieldName() member function returns the name of a field, given a field number.

PgDatabase::FieldNum() returns a field number given a field name (or “1 if the given field name is not a member of the result set).

 const char * PgDatabase::FieldName( int field_num ); int PgDatabase::FieldNum( const char* field_name ); 

PgDatabase::FieldType() returns the OID (object ID) of the data type for a given field (you can use the following query to see a list of data types and their OIDs: select oid, typename from pg_type; ). Notice that you can identify the field in which you are interested by providing either a field name or a field number. This is true for the remainder of the member functions in this section.

 Oid FieldType( int field_num ); Oid FieldType( const char* field_name ); 

PgDatabase::PgFieldSize() returns the size (in bytes) of the given field. The size returned by PgFieldSize() represents the amount of space required to store the field on the server; it returns “1 if the field is defined by a variable sized data type.

 int FieldSize( int field_num ); int FieldSize( const char* field_name ); 

The PgDatabase::GetValue() , PgDatabase::GetIsNull() , and PgDatabase::PgGetLength() member functions return information about a given field within a given row.

 const char* GetValue( int row_num, int field_num ); const char* GetValue( int row_num, const char* field_name ); bool        GetIsNull( int row_num, int field_num ); bool        GetIsNull( int row_num, const char* field_name ); int         GetLength( int row_num, int field_num ); int         GetLength( int row_num, const char* field_name ); 

Now, let's put the PgDatabase class to use in an interactive query program (see Listing 10.8). I'll use the Qt library to build the user interface. In this application, you can enter arbitrary SQL commands; the result set for SELECT statements appear in a table, and the results for other commands display in a status bar. Figure 10.5 shows a sample of what you are going to build.

Listing 10.8 qt-sql.h

 1 /* qt-sql.h */  2  3 class MyTable : public QTable  4 {  5 public:  6  7     MyTable( QWidget * parent, const char * connect );  8  9     PgDatabase * db; 10 11     void buildTable( void ); 12 13 }; 14 15 class MyMain : public QWidget 16 { 17   Q_OBJECT 18 public: 19   MyMain( const char * connect ); 20 21 public slots: 22   void execute( void ); 23   void quit( void ); 24 25 private: 26 27 // These are our user-interface components: 28   QMultiLineEdit * edit; 29   QStatusBar     * status; 30   MyTable        * table; 31 32 }; 

Figure 10.5. qt-query Results.

You should be familiar with the MyTable class by now. MyTable is a QTable that knows how to work with a PgDatabase object. The MyMain class defines the bulk of the user interface for your application. A MyMain object is a QWidget (container) that contains a table, status bar, and multiline editor.

Listing 10.9a qt-sql.cpp

 1 /* qt-sql.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 <qmultilineedit.h>  // QT MultiLineEdit widget 10 #include <qstatusbar.h>      // QT Statusbar widget 11 12 #include <libpq++.h>         // PostgreSQL libpq++ API 13 #include <iostream.h>        // Standard C++ io library 14 15 #include "qt-sql.moc" 16 17 int main( int argc, char * argv[] ) 18 { 19 20   QApplication a( argc, argv ); 21   MyMain       w( a.argv()[1] ? a.argv()[1] : "" ); 22 23   a.setMainWidget( &w ); 24 25   w.show(); 26   a.exec(); 27  return( 0 ); 28 } 

The main() function defines a prototypical Qt application. You start by creating a QApplication object and a MyMain object and then wire together the two objects by calling the QApplication::setMainWidget() function.

Listing 10.9b qt-sql.cpp

 29 MyMain::MyMain( const char * connectStr ) 30 { 31   // Establish a reasonable size for our main window 32   resize( 640, 450 ); 33 34   // Create two layout helpers - 35   //   the vbox layout object will stack things vertically 36   //   the buttons layout object will hold a row of buttons 37 38   QVBoxLayout *  vbox    = new QVBoxLayout( this ); 39   QHBoxLayout *  buttons = new QHBoxLayout(); 40 41   // 42   //  Create the user-interface components 43   // 44   edit   = new QMultiLineEdit( this ); 45   status = new QStatusBar( this ); 46   table  = new MyTable( this, connectStr ); 47 48   // remove the resize-grip from the statusbar, 49   // it looks kinda strange in the middle of a 50   // window. 51 52   status->setSizeGripEnabled( FALSE ); 53 54   // 55   //  give the keyboard focus to the editor control 56   // 57   edit->setFocus(); 58 59   connect( edit, SIGNAL( returnPressed()), this, SLOT( execute())); 60 61   vbox->addWidget( edit ); 62   vbox->addWidget( status ); 63   vbox->addWidget( table ); 64 65   // And finally create the row of buttons at 66   // the bottom of the main window (quit, execute) 67   // 68   vbox->addLayout( buttons ); 69 70   QPushButton * quit = new QPushButton( "Quit", this ); 71   connect( quit, SIGNAL( clicked()), this, SLOT( quit())); 72 73   QPushButton * exec = new QPushButton( "Execute", this ); 74   connect( exec, SIGNAL( clicked()), this, SLOT( execute())); 75 76   buttons->addWidget( quit ); 77   buttons->addWidget( exec ); 78 79 } 

The MyMain constructor is where you build most of the user interface. You use two layout managers (a QHBoxLayout and a QVBoxLayout ) to take care of widget positioning. The layout managers also reposition and resize the component widgets if you resize the main window ”that's a lot of code that you don't have to write. After the editor, status bar, and table have been created, you add each one to the vertical layout manager (the ordering is important ”you want the editor on top, the status bar in the middle, and the table control at the bottom, so you have to add them in that order).

You may want to remove the call to connect() (on line 59). That particular function call wires the Return key to the MyMain::execute() function. If you leave that function call in your code, your query will be sent to the server every time you press Return. Some of you will prefer to use multiple lines to structure your queries and you will probably be in the habit of using the Return key to move to the next line of the editor. If you remove the call to connect() , you will have to use the Execute button.

Listing 10.9c qt-sql.cpp

 81 MyTable::MyTable( QWidget * parent, const char * connect )  82   : QTable( parent )  83 {  84   //  85   //  Create a database connection...  86   //  87   db = new PgDatabase( connect );  88  89   if( db->ConnectionBad())  90   {  91     QMessageBox::critical( 0, "Connection failed",  92     db->ErrorMessage());  93     exit( -1 );  94   }  95  96   //  We don't have any table-oriented results to  97   //  show yet, so hide the table.  98   //  99   setNumRows( 0 ); 100   setNumCols( 0 ); 101 } 

The MyTable constructor creates a database connection and then hides the table control. You display the table whenever you have some results to show to the user.

Listing 10.9d qt-sql.cpp

 103 void MyMain::execute( void ) 104 { 105   //  This function is called whenever the user 106   //  presses the 'Execute' button (or whenever 107   //  the user presses the Return key while the 108   //  edit control has the keyboard focus) 109 110   PgDatabase    * db = table->db; 111   ExecStatusType  result; 112 113   //  Execute whatever the user has entered into 114   //  the edit control 115   // 116   result = db->Exec(( const char *)edit->text()); 117 118   // 119   //  Now process the results... 120   // 121   switch( result ) 122   { 123     case PGRES_EMPTY_QUERY: 124       status->message( "That was fun..." ); 125       break; 126 127     case PGRES_COMMAND_OK: 128       status->message( "Ok" ); 129       break; 130 131     case PGRES_TUPLES_OK: 132       status->message( "Ok..." ); 133       table->buildTable(); 134       break; 135 136     default: 137       status->message( db->ErrorMessage()); 138       break; 139   } 140 } 

The MyMain::execute() function is called whenever you want to execute the query string that the user has entered. The PgDatabase::Exec() function returns one of the values described in Table 10.1. If the user enters an empty command or a command that will not return any rows ( INSERT for example), you just add a message to the status bar. If you enter a command that can return rows (for example, SELECT ), you call buildTable() to fill the table control with the result set.

Listing 10.9e qt-sql.cpp

 142 void MyTable::buildTable( void ) 143 { 144   //  This function is called to fill in 145   //  the table control.  We want to fill 146   //  the table with the result set. 147 148   setNumRows( db->Tuples()); 149   setNumCols( db->Fields()); 150 151   // 152   //  First, populate the column headers... 153   // 154   for( int col = 0; col < db->Fields(); col++ ) 155   { 156       horizontalHeader()->setLabel( col, db->FieldName( col )); 157   } 158 159   // 160   //  Now, put the data into the table... 161   // 162   for( int row = 0; row < db->Tuples(); row++ ) 163   { 164     for( int col = 0; col < db->Fields(); col++ ) 165     { 166       setText( row, col, db->GetValue( row, col )); 167     } 168   } 169 } 

You've already seen buildTable() (see Listing 10.7). This function copies the result set into the table control. The PgDatabase::Fields() member function tells you how many fields are in the result set, and PgDatabase::Tuples() tells you how many rows to expect. PgDatabase::FieldName() returns the name of a given field (identified by its field number). And finally, PgDatabase::GetValue() returns a pointer to the value (in the form of a NULL - terminated string) for a given row and column.

Listing 10.9f shows the MyMain::quit() function. This function is called whenever you click the Quit button. You may remember that the qt-query client left a nasty message in the server log (and/or on the screen) each time you exited. The qt-query application was not closing the database connection properly.

The PgDatabase class closes its database connection whenever the destructor function executes. Normally, the destructor function is executed when an object goes out of scope. You allocated the PgDatabase object from the heap, which means that it will never go out of scope (until the program ends, which is too late). To ensure that the destructor for PgDatabase is executed, you intercept a mouse click on the Quit button and call the MyMain::quit() function (see Listing 10.9b, line 71). You can see (at line 177) that we are forcing the db destructor to execute by using the delete operator. When the destructor executes, it closes the database connection, so no more nasty error message.

Listing 10.9f qt-sql.cpp

 171 void MyMain::quit( void ) 172 { 173   PgDatabase * db = table->db; 174 175   if( db != NULL ) 176   { 177     delete db; 178     db = NULL; 179   } 180 181   qApp->quit(); 182 183 }