Easy Qt Database Forms #1

2012-07 Update

While I make use of QSqlTableModel::setRecord to set new values when the user performs a submit operation, this may not work with some databases in all cases. What works with SQLite and MySQL doesn't appear to work correctly with MS SQL using the ODBC interface. If your data table contains a primary key field, setRecord seems to attempt to change it and fails, even though you didn't change the key in the record. This can be fixed by simply using the QAbstractItemModel::setData base method instead, which allows you to select specific fields to modify instead of the whole record.

Easy Qt Database Forms #1

My switch from Borland's VCL framework to Trolltech's Qt is going very well. Qt version 4.3.0 speeded up graphics by about a factor of two and no screen update flicker is present, thanks to automatic double buffering in the Qt paint system. Applications automatically support Unicode, Internationalization, Vista themes and cross Win32/Linux/Mac development. Overall, the combination of Microsoft VS2005 and Qt is proving to be a great Win32 platform development tool.

One area where I had concerns was with database support. Qt provides very good coverage of all the main SQL database providers and also uses SQLite for an easy to use local file and memory based database. The model/view framework is used to provide an automatic spreadsheet like table view for looking at and editing of data. The QItemDelegate class can be sub-classed to provide custom lookups and editing of data where specialized fields are needed. One area that isn't well covered is using a database model with custom widgets on a form (see first screen shot below).

The Borland VCL offers data-aware components that are easy to hook up to a database and use. In most applications they work quite well, but if you need to interface custom controls that aren't data-aware, then you must hook database navigation events. I found out this could cause problems when I recently converted a BCB version 6 program to BDS2006 and the controls didn't work correctly.

Qt offers the QDataWidgetMapper class and it almost provides the same functionality as data-aware components when you are using simple numerical, boolean or text based fields. In my case I sometimes use drop-down lists to select a custom index value for a field and this class doesn't work well for this application.

Well, there is always a way to get something done, and the following discussion will show you how to use standard Qt widgets to create a data-aware form:

This shows a typical data-aware form using the Borland VCL. The fonts and components have been enlarged to allow easy touchscreen operation. The user selects the record to work on, makes changes and then presses the check mark button to commit the changes.

It should be noted that this form will adjust automatically to almost any screen size. This was done with a custom form adjustment method that goes through the form and adjusts fonts based on screen resolution.

Now for the Qt version of this form. The form constructor sets it all up:

  1. A QSqlTableModel is connected to the database, allowing easy access to all data.
  2. The primeInsert signal is used to initialize default record values when records are inserted.
  3. A QTableView is connected to the model and only the description field is shown.
  4. The currentRowChanged signal is connected up so we see navigation events.

The currentRowChanged signal sends navigation events to this slot method. I use a boolean disableControls to keep the widgets from posting changes when I set them in the program and an integer panelSizesCurrentRow to communicate the current database row between class methods. If the new row is valid I disable the widgets and set their values from the current record. Specialized lookups and other operations are possible at this point to convert custom field types to display types.

When the user submits changes or deletes a record, the current row becomes invalid. In that case I clear the row pointer and set the widgets to cleared values. Another option would be to disable the widgets.

The primeInsert signal is used to set default values into the record before it gets inserted into the database. Auto-increment primary keys don't need to be set as the Qt drivers take care of this automatically.

The add, delete, submit and revert operations are all very simple. Note that even though records are inserted into row zero, that will change after the submit when the database sort order is updated.

Each widget change signal checks to see if there is a valid record and then inserts the value into the record. Again, custom type conversions are possible between the widget values and field types.

The finished Qt form in plastique style. Not only does it look nice, but it also automatically adjusts to any screen size.

© James S. Gibbons 1987-2015