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, “The New PostgreSQL C++ API—libpqxx.” 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 display 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( "
" );
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.9 shows a typical error message.
Figure 12.9. 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.
The Prepare/Execute model has been part of the ODBC architecture for quite a while, but it's a recent addition to the PostgreSQL server. In older versions of PostgreSQL, the ODBC driver simulated the Prepare/Execute process. If you're using a recent version of the ODBC driver (version 1.45 or later) and a recent PostgreSQL server (7.3 or later), and you have enabled the UseServerSidePrepare option, the ODBC driver implements the Prepare/Execute model by executing a PREPARE command followed by an EXECUTE command.
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 regardless of the number of rows you actually fetch.
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.10 shows you an example of what you would see when you run the odbc/qt-sql sample.
That's it! The rest of the qt-sql application is explained in Chapter 10.