Client 4 - An Interactive Query Processor

Client 4 ”An Interactive Query Processor

I'll finish this chapter by developing a general-purpose, interactive query processor. In this example, I'll describe the SQLPrepare() / SQLExec() query execution method. Finally, I'll show you a way to process result sets more efficiently .

This example is based on the libpq++/qt-sql.cpp client from Chapter 10. Rather than showing you the entire application again, I'll just explain the differences ”refer to Chapter 10 for a complete explanation of the original application.

In this application, you can enter arbitrary SQL commands; the result set for SELECT statements appears in a table and the results for other commands displays in a status bar.

The first thing that you need to change in this client is the MyTable class. The new MyTable class includes an environment handle ( env ) and a connection handle ( db ).

 1 /* qt-sql.h */  2  3 class MyTable : public QTable  4 {  5 public:  6  7     MyTable( QWidget * parent );  8  9     SQLHDBC      db; 10     SQLHENV      env; 11 12     void buildTable( SQLHSTMT stmt ); 13     void displayErrors( SQLSMALLINT type, SQLHANDLE handle ); 14 15 }; 

Next , I'll borrow the resultField structure from the previous example. This structure contains metadata for a field and a pointer to a buffer ( value ) that holds the field data as you retrieve each row.

 // File qt-sql.cpp (partial listing - see downloads for complete text) 22 typedef struct 23 { 24   char          name[128+1]; 25   SQLSMALLINT   nameLength; 26   SQLSMALLINT   dataType; 27   SQLUINTEGER   fieldLength; 28   SQLSMALLINT   scale; 29   SQLSMALLINT   nullable; 30   SQLINTEGER    displaySize; 31   int           headerLength; 32   SQLINTEGER    resultLength; 33   char        * value; 34 } resultField; 

Now let's look at the MyTable constructor:

 // File qt-sql.cpp (partial listing - see downloads for complete text) 109 MyTable::MyTable( QWidget * parent ) 110   : QTable( parent ) 111 { 112   // 113   //  Create a database connection... 114   // 115   SQLRETURN  res; 116 117   res = SQLAllocHandle( SQL_HANDLE_ENV, 118           SQL_NULL_HANDLE, 119           &env ); 120   if( !SQL_OK( res )) 121   { 122     displayErrors( SQL_HANDLE_ENV, env ); 123     exit( -1 ); 124   } 125 126   SQLSetEnvAttr( env, 127        SQL_ATTR_ODBC_VERSION, 128        (SQLPOINTER)SQL_OV_ODBC2, 129        0 ); 130 131   res = SQLAllocHandle( SQL_HANDLE_DBC, 132          env, 133          &db ); 134 135   if( !SQL_OK( res )) 136   { 137     displayErrors( SQL_HANDLE_ENV, env ); 138     exit( -1 ); 139   } 140 141   res = SQLConnect( db, 142           (SQLCHAR *)qApp->argv()[1], SQL_NTS, 143           (SQLCHAR *)qApp->argv()[2], SQL_NTS, 144           (SQLCHAR *)qApp->argv()[3], SQL_NTS ); 145 146   if( !SQL_OK( res )) 147   { 148     displayErrors( SQL_HANDLE_DBC, db ); 149     exit( -1 ); 150   } 151 152 153   //  We don't have any table-oriented results to 154   //  show yet, so hide the table. 155   // 156   setNumRows( 0 ); 157   setNumCols( 0 ); 158 } 

The MyTable constructor should be familiar by now. You initialize an environment handle, inform ODBC that you are an ODBC version 2 ( SQL_OV_ODBC2 ) application, and then try to connect to the database identified on the command line. When this application is invoked, it expects three command-line arguments: a data source name, a username, and a password. The qApp->argv() function returns a pointer to the array of command-line arguments. If the connection attempt fails, you call the displayErrors() function to display any error messages. displayErrors() is shown here:

 // File qt-sql.cpp (partial listing - see downloads for complete text) 160 void MyTable::displayErrors( SQLSMALLINT type, SQLHANDLE handle ) 161 { 162   SQLHDBC   dbc  = SQL_NULL_HDBC; 163   SQLHENV   env  = SQL_NULL_HENV; 164   SQLHSTMT  stmt = SQL_NULL_HSTMT; 165 166   switch( type ) 167   { 168     case SQL_HANDLE_ENV:  env  = (SQLHENV)handle; break; 169     case SQL_HANDLE_DBC:  dbc  = (SQLHENV)handle; break; 170     case SQL_HANDLE_STMT: stmt = (SQLHSTMT)handle; break; 171   } 172 173   SQLRETURN   result; 174   SQLCHAR     sqlState[6]; 175   SQLINTEGER  nativeError; 176   SQLSMALLINT requiredLength; 177   SQLCHAR     messageText[SQL_MAX_MESSAGE_LENGTH+1]; 178 179   QDialog     * dlg  = new QDialog( this, 0, TRUE ); 180   QVBoxLayout * vbox = new QVBoxLayout( dlg ); 181   QPushButton * ok   = new QPushButton( "Ok", dlg ); 182 183   setCaption( "Error" ); 184   QMultiLineEdit * edit = new QMultiLineEdit( dlg ); 185 186   vbox->addWidget( edit ); 187   vbox->addWidget( ok ); 188 189   connect( ok, SIGNAL( clicked()), dlg, SLOT( accept())); 190 191   edit->setReadOnly( TRUE ); 192 193   do 194   { 195     result = SQLError( env, 196                        dbc, 197                        stmt, 198                        sqlState, 199                        &nativeError, 200                        messageText, 201                        sizeof( messageText ), 202                        &requiredLength ); 203 204     if( SQL_OK( result )) 205     { 206       edit->append((char *)messageText ); 207       edit->append( "\n" ); 208     } 209   } while( SQL_OK( result )); 210 211   dlg->adjustSize(); 212   dlg->exec(); 213 214 } 

The displayErrors() function is complicated by the fact that you may get multiple error messages from ODBC ”you can't use the usual QT MessageBox class to display multiple errors. Instead, we construct a dialog that contains an edit control (to contain the error messages) and an OK button. Figure 12.5 shows a typical error message.

Figure 12.5. Sample error message.

After the dialog object has been built, you call SQLError() to retrieve the error messages and append each message into the edit control. When you have retrieved the final error message, you display the dialog by calling the dlg->exec() function.

Now let's look at the code that used to execute a command:

 // File qt-sql.cpp (partial listing - see downloads for complete text) 216 void MyMain::execute( void ) 217 { 218   //  This function is called whenever the user 219   //  presses the 'Execute' button (or whenever 220   //  the user presses the Return key while the 221   //  edit control has the keyboard focus) 222   SQLHDBC   db = table->db; 223   SQLHSTMT  stmt; 224   SQLRETURN res; 225   QString   qcmd = edit->text(); 226   SQLCHAR * cmd; 227 228   // Convert the query command from Unicode 229   // into an 8-bit, SQLCHAR format 230 231   cmd = (SQLCHAR *)qcmd.latin1(); 232 233   SQLAllocHandle( SQL_HANDLE_STMT, db, &stmt ); 234 235   res = SQLPrepare( stmt, (SQLCHAR *)cmd, SQL_NTS ); 236 237   if( !SQL_OK( res )) 238   { 239     table->displayErrors( SQL_HANDLE_STMT, stmt ); 240   } 241   else 242   { 243 244     if( SQL_OK( SQLExecute( stmt ))) 245     { 246       SQLSMALLINT  columnCount; 247 248       SQLNumResultCols( stmt, &columnCount ); 249 250       if( columnCount == 0 ) 251       { 252          SQLINTEGER  rowCount; 253          SQLRowCount( stmt, &rowCount ); 254 255          if( rowCount == -1 ) 256            status->message( "Ok" ); 257          else 258          { 259            QString m( "Ok, %1 rows affected" ); 260 261            status->message( m.arg((int)rowCount )); 262          } 263       } 264       else 265       { 266          status->message( "Ok..." ); 267          table->buildTable( stmt ); 268       } 269     } 270     else 271       table->displayErrors( SQL_HANDLE_STMT, stmt ); 272 273   } 274 275   SQLFreeHandle( SQL_HANDLE_STMT, stmt ); 276 } 

MyMain::execute() starts by making a copy of the query ( edit->text() ) and converts the string from Unicode (Qt's native character encoding) into ASCII (the format expected by ODBC).

Next, you initialize a statement handle.

In the previous example ( client3.c ), I used the SQLExecDirect() function to execute a SQL command. In this function, I am using a different execution model ”the Prepare/Execute model.

You should use the Prepare/Execute model if you are expecting to execute the same SQL command multiple times, possibly substituting different values for each execution. Some ODBC-compliant databases support "parameter markers" within a SQL command. You generally use parameter markers when you are using the Prepare/Execute model. Here is an example of a command that contains parameter markers:

 insert into customers values ( ?, ?, ? ); 

Each question mark in this command represents a parameter whose value is provided each time the command is executed. (The parameters are numbered ”the leftmost question mark is parameter number 1, the next mark is parameter number 2, and so on.)

The advantage to the Prepare/Execute model is that you send the command to the server only once, but you can execute the command as many times as needed. Most ODBC-compliant databases parse the command and create an execution plan when you call SQLPrepare() . When you want to execute the statement, you bind each parameter to a memory address, place the appropriate value at that address, and then call SQLExecute() to execute the command. When you use the Prepare/Execute model with a database that supports parameter markers, you can gain a huge performance boost.

It's not really appropriate to use the Prepare/Execute model to process ad hoc queries. Prepare/Execute is useful when you plan to execute the same SQL command multiple times. You can also use Prepare/Execute to simplify your code: Factor the code that generates a command into a function separate from the code that generates data.

PostgreSQL and the Prepare/Execute Model

PostgreSQL does not support parameter markers directly. The PostgreSQL ODBC driver performs parameter substitution and sends the translated command to the database each time you call SQLExecute() . You will not see a performance boost using Prepare/Execute when your application is connected to a PostgreSQL database, but you should be aware of the technique anyway. If you are building an ODBC application, you are probably concerned with portability (and performance) issues.

After you have successfully prepared and executed the command entered by the user, you are ready to process the results.

The first thing you need to know is whether the command could have returned any rows. (In other words, was this a SELECT command.) ODBC version 2.x does not provide a function that tells you what kind of SQL command you just executed, but you can use the SQLNumResultCols() to infer that information. If SQLNumResultCols() tells you that there are no columns in the result set, you can assume that you have not executed a SELECT command. In that case, you use SQLRowCount() to determine how many rows (if any) were affected by the command. For UPDATE , INSERT , and DELETE statements, SQLRowCount() returns a value (greater than or equal to zero) indicating how many rows were affected. For other types of statements (such as BEGIN WORK or CREATE TABLE ), SQLRowCount() returns “1 . Use the value returned by SQLRowCount() to determine how to update the status bar.

When you execute a SELECT command, you call the MyTable::buildtable() function to copy the result set into a table:

 // File qt-sql.cpp (partial listing - see downloads for complete text) 278 void MyTable::buildTable( SQLHSTMT stmt ) 279 { 280   //  This function is called to fill in 281   //  the table control.  We want to fill 282   //  the table with the result set. 283   SQLSMALLINT   i; 284   SQLSMALLINT   columnCount; 285   resultField * fields; 286 287   setNumRows( 0 ); 288   setNumCols( 0 ); 289 290   //  First, examine the metadata for the 291   //  result set so that we know how much 292   //  room we need for each column. 293 294   SQLNumResultCols( stmt, &columnCount ); 295 296   fields = new resultField[ columnCount+1 ]; 297 298   setNumCols( columnCount ); 299 300   for( i = 1; i <= columnCount; i++ ) 301   { 302     SQLDescribeCol( stmt, 303           i, 304           (SQLCHAR *)fields[i].name, 305           sizeof( fields[i].name ), 306           &fields[i].nameLength, 307           &fields[i].dataType, 308           &fields[i].fieldLength, 309           &fields[i].scale, 310           &fields[i].nullable ); 311 312     SQLColAttribute( stmt, 313            i, 314            SQL_DESC_DISPLAY_SIZE, 315            NULL, 316            0, 317            NULL, 318            &fields[i].displaySize ); 319 320     fields[i].value = (char *)malloc( fields[i].displaySize+1 ); 321 322     // Build the column headers as we go 323     horizontalHeader()->setLabel( i-1, fields[i].name ); 324 325   } 326 327   //  Bind the fields to our buffers 328   for( i = 1; i <= columnCount; i++ ) 329   { 330     SQLRETURN res; 331 332     res = SQLBindCol( stmt, 333               i, 334               SQL_C_CHAR, 335               fields[i].value, 336               fields[i].displaySize+1, 337               &fields[i].resultLength ); 338 339     if( !SQL_OK( res )) 340       displayErrors( SQL_HANDLE_STMT, stmt ); 341   } 342 343   // 344   //  Now, put the data into the table... 345   // 346   int       row = 0; 347   SQLRETURN res; 348 349   while( SQL_OK(( res =  SQLFetch( stmt )))) 350   { 351     if( res == SQL_SUCCESS_WITH_INFO ) 352       displayErrors( SQL_HANDLE_STMT, stmt ); 353 354     setNumRows( row+1 ); 355 356     for( int col = 1; col <= columnCount; col++ ) 357     { 358       setText( row, col-1, fields[col].value ); 359     } 360 361     row++; 362 363   } 364 } 

buildTable() starts by initializing the table to zero rows and zero columns. Next, you use SQLNumResultCols() to determine how many columns are in the result set. You allocate a resultField structure for each column.

Then, you build an array of resultField structures (the same way you did in the odbc/client3.c example ) using SQLDescribeCol() and SQLColAttribute() .You also populate the table's column headers as you process the metadata.

Rather than using SQLGetData() to retrieve field values, I'm going to bind each column to a memory buffer. Then, as you fetch each row from the server, ODBC automatically copies the data values into your bind buffers. Here is the function prototype for SQLBindCol() :

 SQLRETURN SQLBindCol( SQLHSTMT     stmtHandle,                       SQLUSMALLINT columnNumber,                       SQLSMALLINT  bindDataType,                       SQLPOINTER   bindBuffer,                       SQLINTEGER   bindBufferLength,                       SQLLEN     * resultLength ) 

When you call SQLBindCol() , you are binding a column ( columnNumber ) to a memory address ( bindBuffer and bindBufferLength ) and asking ODBC to convert the field data into a specific data type ( bindDataType ). You can also provide a pointer to a result length ”after you fetch a row, the result length will contain the length of the data value (or SQL_NULL_DATA if the field is NULL ). In general, you will get better performance results if you bind each column rather than using SQLGetData() . You have to call SQLGetData() for each column in each row, but you have to bind each column only once.

After you have bound all the columns in the result set, you can start fetching. For each row that you fetch, you increase the table size by one row (this isn't very efficient, but ODBC does not give you a way to determine the size of the result set without fetching each row).

Finally, use the QTable::setText() member function to insert each column into the table.

Figure 12.6 shows you an example of what you would see when you run the odbc/qt-sql sample.

Figure 12.6. Running the qt-sql application.

That's it! The rest of the qt-sql application is explained in Chapter 10.