dcsimg

Building a Universal SQL Client

The Qt (sometimes pronounced "cute") C++ toolkit has been available since 1995, but it entered the mainstream of the computer programming world just a few years ago. Known for its first-class support of Unix operating systems, Qt, from Norwegian company Trolltech, surged in popularity along with the growth of Linux. Developers have turned to the toolkit for its cross-platform features alone: Qt helps you build Macintosh and Windows programs directly from your Linux source code. Qt also received a boost from the ever-popular KDE, which relies heavily on the toolkit to simplify GUI programming. Even the Linux kernel now requires Trolltech's libraries for its xconfig build module. As a result, Qt has cemented a spot among the best-selling C++ toolkits on the market.

The Qt (sometimes pronounced “cute”) C++ toolkit has been available since 1995, but it entered the mainstream of the computer programming world just a few years ago. Known for its first-class support of Unix operating systems, Qt, from Norwegian company Trolltech, surged in popularity along with the growth of Linux. Developers have turned to the toolkit for its cross-platform features alone: Qt helps you build Macintosh and Windows programs directly from your Linux source code. Qt also received a boost from the ever-popular KDE, which relies heavily on the toolkit to simplify GUI programming. Even the Linux kernel now requires Trolltech’s libraries for its xconfig build module. As a result, Qt has cemented a spot among the best-selling C++ toolkits on the market.

Qt is no silver bullet, however. Like Java, it requires you to call appropriate wrapper methods every time you work with files, networking, or the display. You basically have to write your code in Qt from the ground up if you want it to be portable, being careful not to call any OS-specific functions. KDE, for example, often calls the POSIX function unlink() to delete files, so it cannot be ported directly to non-Unix systems. Thus, the “write once, compile anywhere” mantra echoed in Trolltech’s whitepapers isn’t always true.

But unlike Java, Qt doesn’t require a virtual machine. Qt programs compile directly into machine code and run about as fast as any other C++ program. And, if you’re willing to open source your Qt/Linux programs, you can get Trolltech’s libraries, along with complete source code and documentation, for free. (The company provides the Linux/Unix/BSD libraries under the GPL license.) If, on the other hand, you want to keep your code proprietary, want technical support from Trolltech, or distribute Windows or Macintosh versions of your programs, you’ll have to purchase a license.

Depending on how many developer licenses you buy and which platforms you need to target, licensing costs can run into quadruple digits per developer. Still, you’re getting a mature and well-rounded toolkit for your money. Qt provides classes for just about every kind of function your program could need: custom GUI widgets, an events model, utility classes (strings, lists, etc.), OpenGL support, an XML module, ActiveX and Motif integration, and more. It also provides some nice touches such as garbage collection of object handles, a visual user-interface construction kit called “Qt Designer,” and a scaled-down version of Qt for small-footprint portable devices and PDAs.

While these fancy features have raised the eyebrows of developers and garnered press attention, one feature in particular — SQL support — has been left out of the limelight. And that’s unfortunate — SQL classes are the unsung workhorses of Qt, helping client software connect to powerful backend databases. Though SQL support isn’t quite as sexy as Qt’s new ActiveX integration, for instance, and SQL has long since lost its buzzword quality, the ability to link gigabytes of data to virtually any client platform is one of Qt’s charms.

In this article, let’s take a tour of the major SQL classes in Qt: QSqlDatabase, QSqlQuery, QSqlCursor, and others. In our tour, let’s look at data-aware tables, custom SQL forms, and some example code. Although space prohibits a complete tour — for example, the article doesn’t show you how to build your own database driver for Qt — you’ll still be able to write sophisticated database clients for just about any operating system after reading the next two thousand words.

Basic SQL in Qt

Before heading off on the tour, a few ground rules. This article assumes that you’re already familiar with SQL and have at least a little bit of experience with relational databases and queries. (If not, check out http://www.sqlcourse.com for an interactive tutorial.) It also assumes you have some flavor of SQL database up and running.

If you do not have a database server, it’s pretty easy to install one on most Linux distributions: just install the SQL server packages, then make sure that the server daemon is running. If you don’t already have a database running, you should choose either the PostgreSQL or MySQL packages since those are the two open-source SQL drivers that the free version of Qt supports. (An ODBC driver is also available in Qt if you need it, and the commercial version includes drivers for Oracle and Sybase.)

Finally, you need to know how to connect to the server, create some tables, and initialize the tables with data. If you’re not sure how to do all this in Linux, consider using one of the many graphical front-ends. Webmin, for example, can handle these tasks, while also helping you start and stop the database daemon, set permissions for users, and execute arbitrary SQL commands.

Now, let’s begin the tour.

Our first stop is a quick overview of the SQL classes in Qt. In true object-oriented fashion, each of these classes represents a particular element of a SQL database, and, like any C++ class, you can extend them and customize them via simple inheritance. The design and naming scheme of the classes is fairly sane: QSqlQuery sends queries to the database; QSqlField corresponds to columns in a table; and so on. Perhaps the only oddly-named class is QSqlIndex, which is really just a convenience class for specifying the columns on which to sort the results of a query. See Table One for a comprehensive list of all classes.




Table One: The SQL classes in
Qt


NAMEDESCRIPTION
QsqlDatabaseOpens and closes connections to a
database on a SQL server
QsqlQueryHigh-level interface for executing SQL
commands and navigating through the results (e.g. by calling first(),
next(), seek(int), etc.)
QsqlCursorChild of QSqlQuery; adds
methods for adding, removing, and changing data in the result
set
QsqlRecordRepresents a single row in a table of
results; provides functions for retrieving and changing any of the
columns
QsqlRecordInfoReturned by QSqlDatabase;
used to retrieve list of columns in a table
QsqlFieldRepresents a single cell in a table of
results; mainly used for getting and setting the contents of the
cell
QsqlFieldInfoStores information about a column
in a table: name, type, precision, etc.
QsqlIndexRepresents one or more columns in a
table; used for restricting results to given columns or for specifying a
sort index
QsqlFormProvides a link between a set of
user-interface widgets and the corresponding columns of a database
table
QsqlPropertyMapAllows custom widgets to be used
in
mQSqlForm by binding a database column to a
widget property
QsqlEditorFactoryCreates the default widget for
a given SQL field type; can be overridden by sub-classing
QsqlErrorRetrieves the last known error if a
call to a Qt SQL method fails
QSqlHas no methods; only provides enumerated
constants such as
QSql::BeforeFirst
QsqlDriverAbstract base class for writing SQL
drivers in Qt but not needed for client programs; use
QSqlDatabase instead
QsqlDriverPluginAbstract base class for writing
SQL drivers in Qt but not needed for client programs
QsqlResultAbstract base class for writing SQL
drivers in Qt but not needed for client programs; use
QSqlQueryor
QSqlCursor instead

In general, you almost never need to use all sixteen of the Qt SQL classes. In fact, if you just want to connect to your database and extract some data, you only need two: QSqlDatabase and QSqlCursor. Listing One proves the point: it shows the simplest possible SQL program that you can write in Qt. However, the techniques and code shown in Listing One apply to even very complex programs, so let’s look at the code line by line.




Listing One: The smallest possible SQL client in Qt

01 #include <qapplication.h>
02 #include <qsqldatabase.h>
03 #include <qsqlcursor.h>
04
05 int main( int argc, char *argv[] )
06 {
07 QApplication app(argc, argv);
08
09 QSqlDatabase *db = QSqlDatabase::addDatabase(“QMYSQL3″);
10 db->setDatabaseName(“hello_database”);
11 db->setUserName(“mysql_username”);
12 db->setPassword(“mysql_password”);
13 db->setHostName(“192.168.1.20″);
14
15 QSqlCursor cursor(“hello_table”);
16 cursor.select();
17 cursor.next();
18
19 qDebug( cursor.value(0).toString() );
20
21 return 0;
22 }

The first six lines should be self-explanatory. The include statements simply provide definitions of the Qt classes, and the main function is like any other C++ program. Line 7 is where the code really begins; it declares a QApplication object so that Qt can run some initialization routines and clean up properly when the program exits. (This object is required by the SQL classes, so you need to include it even if your program never actually creates a main window.)

In line 9, the code sets up a connection to the database by calling the static addDatabase() function in QSqlDatabase. The first parameter is a string that specifies the name of the database driver. This code was run against a MySQL server, so it specifies QMYSQL3, but you can also choose from four others: QODBC3 (Open Database Connectivity), QOCI8 (Oracle 8 and 9), QTDS7 (Sybase Adaptive Server and Microsoft SQL Server), and QPSQL7 (PostgreSQL 6 and 7). You can connect to any of these databases at the same time as long as you specify a unique string as the second parameter in addDatabase(). This “nickname” allows you to reference each connection by name later on in the program. Incidentally, you don’t have to worry about closing the connection or deleting the object — Qt will do that for you automatically when the program exits.

Getting a pointer to a QSqlDatabase object is only the first step. As shown in lines 10-13, the object needs to know the name of the database, the username and password, and the host name or IP address of the server. An added bonus here is that you can put the SQL server anywhere you want: either on a local network or on the other side of the globe. Qt can connect to servers anywhere on the Internet as long as the remote firewall is set up properly.

On line 15, the code finally starts retrieving data from the SQL server. It begins by creating a QSqlCursor object, and passing it the name of a table via the constructor. (This class, by the way, is the all-important conduit through which you normally send queries and retrieve results, so be sure to study Trolltech’s documentation of its methods carefully.) The next line selects the entire table by calling QSqlCursor::select() with no parameters.

Line 17 is a bit confusing unless you have experience with SQL cursors. The tricky part is that the select() call does not, as you might expect, give you the first row of the results automatically. Instead, select() puts you at a virtual row known as the “before-first” row. You have to call next() to advance to the true first row. Likewise, there is an “after-last” row, and if you iterate through every row in a result set and go beyond the last one, no error occurs. This behavior is a bit strange for C++ programmers who think of moving beyond the last index as a serious error, but it’s perfectly normal and, in fact, required by the SQL-92 standard for cursors.

So, after calling next(), the cursor is looking at the first row of the results. The cursor can now pull out the data, as shown on line 19, by calling QSqlCursor::value() and specifying either a column index (zero-based) or a column name. The data comes back as a QVariant, a catch-all object that holds any data type. Since this data is now in your client, you can display it, write it to disk, or do whatever you wish. In Listing One, the code simply writes the table’s first cell to the console by calling qDebug.

Now that we’ve reached the end of Listing One, you may have noticed that the code never performs any error checking. That code’s been omitted for simplicity. However, a true Qt/SQL program would perform extensive error checking and provide for recovery. As a pure-C++ toolkit, you might expect Qt to use exception handling for this task, but instead, the SQL classes use an old-fashioned C-style “get last error code” technique. Though tedious and a little awkward, it works well enough and is easy to understand. For an example of this error handling, as well as a look at the more advanced features of SQL in Qt, let’s move on to Listing Two, the next stop on our whirlwind tour.




Listing Two: A demonstration of the simple but powerful QDataTable class

01 #include <qapplication.h>
02 #include <qsqldatabase.h>
03 #include <qsqlcursor.h>
04
05 int main( int argc, char *argv[] )
06 {
07 QApplication app(argc, argv);
08
09 QSqlDatabase *db = QSqlDatabase::addDatabase(“QMYSQL3″);
10 db->setDatabaseName(“hello_database”);
11 db->setUserName(“mysql_username”);
12 db->setPassword(“mysql_password”);
13 db->setHostName(“192.168.1.20″);
14
15 if ( !db->open() ) {
16 qWarning( “Failed to open database: ” +
17 db->lastError().driverText() );
18 qWarning( db->lastError().databaseText() );
19 return 1;
20 }
21
22 QSqlCursor cursor(“hello_table”);
23 QDataTable *datatable = new QDataTable(&cursor, TRUE);
24 app.setMainWidget(datatable);
25 datatable->refresh();
26 datatable->show
27 return app.exec();
28 }

More Powerful SQL

Listing Two begins by setting up a database connection just as before, but this time, it also calls QSqlDatabase::open() to explicitly open the connection. If this function returns false, the code knows that there was a problem and calls lastError() to get a handle to a QSqlError object. It can then retrieve any errors reported by the local SQL driver and the remote SQL database by calling driverText() and databaseText(), respectively. If, for example, the user provides the wrong password, the two functions will return something like this:


Failed to open database: QMYSQL3: Unable to connect

Access denied for user: ‘mysql_username@192.168.1.21‘ (Using password: YES)

You can also retrieve an error code from the last function by calling QSqlError::number(), but it’s not useful. The MySQL driver, for instance, always returns -1 on connection errors.

Lines 23 to 26 of Listing Two make some real magic. In these five lines alone, the code sets up a complete database application with help from the QDataTable class. It tells the class to select a table from the database, display it in a scrollable window, and allow the user to add, edit, and delete records. QDataTable is smart enough to retrieve only the rows of the table that are visible and to load new records automatically as the user scrolls around.

Figure One shows the effects of this simple but powerful class. Although it’s not obvious from the screen shot, you can double-click any cell to edit it, or right-click it to insert and delete records. QDataTable handles all of this automatically, but you can easily customize the behavior by changing the properties of the class and responding to its signals. For instance, you can prevent the user from making changes to the database by calling QDataTable: :setReadOnly(false).








compile_01
Figure One: A shot of Listing Two, showing QDataTable

As powerful as QDataTable is, it can be too limiting if you need to extract data manually or display it in a non-table format. Luckily, the QSqlCursor class provides fine-grained control over SQL data, and is nearly as versatile as raw SQL commands. Unlike command strings, however, QSqlCursor allows the C++ compiler to catch syntax errors. For example, a SQL command such as SELECT * FROM customers WHERE LastName = ‘Doe’ ORDER BY FirstName provides plenty of opportunities for misspellings. You can reduce the chances of a run-time disaster by translating this command into the following Qt code:


QSqlCursor cursor(“customers”);
cursor.select(“LastName = ‘Doe’”,
cursor.index(“FirstName”));

Both versions yield the exact same results: a list, ordered by first name, of all customers named “Doe.”

A Real-world Example

So far, the tour has shown us only the canonical “Hello, world!” style of database applications. Our next stop is an example of a more realistic database, something that a technical support center might use to keep track of customers and calls. This application, commonly known as a “customer relationship manager,” or CRM, actually uses two databases: one for a list of customers, and another for a list of cases (that is, contact between a customer and a technical support representative). Let’s look at how Qt can integrate these two databases and give them a friendly user interface.

As with all Qt/SQL programs, there are three approaches to developing the CRM application:

* Write the entire code from scratch

* Construct the user interface visually in Qt Designer, then write the rest of the application from scratch

* Construct the user interface and database forms visually in Qt Designer, and write just enough code to tie it all together

The latter approach is made possible by the aforementioned QDataTable, and also QDataBrowser, a visual component that queries a database and automatically creates a user interface for viewing and editing the records within it. You simply drag a QDataBrowser object onto your form, as shown in Figure Two, and the Designer creates the buttons, edit boxes, and even the C++ code to perform the actions that respond to button clicks.








compile_02
Figure Two: Qt’s Designer can construct database applications visually, but it can also reduce your flexibility

Although this approach saves time at first, it can slow your progress if you need something more than the pre-fabricated dialog box that QDataBrowser generates. The Qt Designer does allow customization to a certain degree — for instance, it can help you add code to filter and sort the contents of a QDataTable — but for maximum flexibility, the second approach works best. It lets you design the user interface visually while still giving you total control over the database logic.

The documentation for Qt Designer provides a fairly good tutorial on creating SQL applications visually, so let’s focus on the more hands-on approach. First, the user interface portion of the CRM application is pretty straightforward; a simple dialog box containing two tabs is created in Designer. One tag is for the customers and the other is for the cases. Each tab has First, Previous, Next, and Last buttons for browsing, as well as Add, Update, and Delete buttons for editing. If you look at the table definition for the case database, you’ll see that each record holds the social security number of a customer, so the case tab also displays some information about the customer (see Figure Three).

After creating the user interface, it’s time for the grunt work. Use Designer to create event functions (known as “slots” in Qt-speak) that are invoked when the user clicks any of the buttons. The slots, in turn, invoke functions in a custom class called Database, the foundation of derived classes CustomerDatabase and CaseDatabase. These classes provide a convenient wrapper around Qt’s database functions and simplify the design of the code. You can create similar custom classes to speed development of your applications by following the examples in database.cpp.








compile_03
Figure Three: Customer relationship management is one kind of SQL application that Qt makes easy

You might notice that, when deleting, the Database class creates a separate cursor to do the deletion. Trying to use the same cursor for deleting as well as navigation can crash your program. This can happen if, for instance, you select a row using the delete cursor, then select the same table with the navigation cursor. There are two ways around this:

* Allocate the delete cursor dynamically (using new), then be sure to remove it (using delete) before using the navigation cursor

* Remove the dynamically-allocated navigation cursor (using delete), then re-allocate it (using new) when you’re done deleting

The latter method is slower, but more desirable, because the cursor updates itself with the changes you just made to the database. The only catch is that you must save the last known position of the cursor so that you can return to the same record after the deletion. But as long as you use something like the Database class in the sample code, you don’t have to worry about any of this.

If you want to see the two other approaches, example code can be found at http://www.linux-mag.com/downloads/ 2003-09/compile. Instructions on compiling the programs and setting up the databases are included in the README.

Surprisingly, the application developed entirely from within Qt Designer, “crm-designer,” actually took more work than the application written mostly from scratch, “crm,” using Designer only for the user interface. Often, trying to navigate the Designer and getting it to do what’s needed requires required more effort than just pounding out the C++ code manually. Keep this experience in mind if you intend to develop Qt/SQL applications for a production environment.

The End of the Tour

This column only touches briefly touched on the idea of combining the power of SQL with the elegance of Qt. Like any other new technology, there’s a learning curve, but it’s easy to pick up, especially if you have experience with just about any other C++ toolkit and have even a little knowledge of SQL. Once you’ve got a taste of Qt, you’ll be creating high-octane and effective SQL applications in no time.



Trevor Harmon is a computer engineering graduate student at the University of California, Irvine. You can reach Trevor via e-mail at trevor@vocaro.com.

Comments are closed.