Introduction
Love them or hate them, there is one thing that you can pretty much guarantee if you are going to spend any time doing computer programming, and that is that sooner or later, you are going to have to either create or display data from a database. In this article, we look at how to program a MySQL database in KDE. For the completely uninitiated, the KDE environment is built upon the Qt C++ libraries provided by Trolltech, so in this article, classed that start with the letter Q are part of the Qt library, and classes that start with the letter K are part of the KDE library. The main classes we will use are the built-in database widgets of QDataTable, QDataBrowser, and QDataView, but first of all, we need to set up some information to use in our database. For this demonstration, we will be using MySQL which comes with openSuSe and probably given its popularity, every other version of Linux as well.
Just to clarify things, this article was originally written as Chapter five of Beginning KDevelop Programming on SuSe 10.0, and has been updated and tested for this release on openSuse 10.3.
Setting up the database
There seems to be two ways you can go about setting up MySQL. The easy way and the painful way, and there seems to be only a slight difference that decides which way your set up is going to go. This way was worked out over several days of testing. If you want to do it another way or you do run into trouble, you'll be needing this link: MySQL Documentation.
Order
Install MySQL
Start MySQL
Change root password (recommended)
(Optional) Uninstall MySQL
1. Install My SQL
MySQL is installed with openSUSE; it is just not started as a service, but there are still reasons for checking the install with Yast. Mostly this is because the Graphical User Interface tools for MySQL are not installed by default, so unless you set up the system knowing that you would need them, you probably won't have them.
To install or to just check if you have everything you need, open the Control Center (Yast) and select the Software Management option. It should open with the search option, type mysql, it doesn't matter which case, and hit return. The parts you are interested in here are the mysql-administrator and the mysql-query-browser; the query browser is for more advanced use than we will be using here, but it can't hurt to have it.
The Yast setup should be familiar if you have setup the system yourself; if not, click on the checkbox for the item you are interested in. A black tick will install it, a blue tick means it is already installed, a bin means remove it, and a lightening bolt thingy means update it.
It should also be noted that these programs are included on the main menu at System/Service Configuration, but if you want to set up the links on your desktop, you can find them in /usr/share/applications. On 10.3, they will appear in the New Applications section at the top of the Applications section of the main menu. You can copy these to your desktop if you think you are going to need them regularly.
From an openSUSE 10.3 perspective, the MySQL Administrator is not present on the disk; you need to go to the openSUSE web site and do a software search for "mysql-administrator". If you install this through the one click install, it will also download all the required files so that you can install the MySQL query browser through Yast as normal afterwards.
2. Start MySQL
By default, the MySQL server is disabled when you set up Suse, so you need to start it yourself. To do this, you need to access the Runlevel Services on whatever version of Linux you are using. On Suse, this is done through Control Center/YaST2 Modules/System/System Services (Runlevel). You will need Administrator privileges to access the services list.
Once you enable the MySQL server here, it will be started whenever you start your computer, so once you've done it, you can just forget about it and use MySQL anytime you want.
3. Change root password
At this point, it is recommended that you change the root password. The syntax is:
mysqladmin -u root password " newpwd"
4. Uninstall MySQL
If you have got to the state where you are just getting nowhere and are having trouble getting answers from the MySQL website, the quickest solution, as long as you aren't using a machine that has a valid production database on it, is just to delete MySQL and start again from scratch. You do this by opening Yast, searching for mysql, and setting the MySQL option to be uninstalled, which should show a bin at the checkbox.
The database files for MySQL on Suse are stored in var/lib/mysql. If you delete this folder, you will have completely removed the MySQL information, including all databases and users, so like I say, if the computer has ever been used as a MySQL production machine, do not touch this folder or you will lose everything.
Administration
To setup MySQL exactly how you want it, start the MySQLAdministrator.
Use root as the user and the password that you have set recently.
When it starts, it should look something like the above. The important bits here are the users and the catalog sections, where you can see the databases that are on your system.
Knoda
The default tool of choice for working with MySQL is going to be the KDE Knoda application for the simple reason that it allows us to set up the database without having to write all the SQL ourselves. You may need to install this through Yast.
The link to the Knoda application can be found under the main menu Office/Database.
When started, Knoda will ask which driver you want to use, we select mysql, and Connect, which gives us the connection dialog.
To setup and use our database, we are just going to use the root connection with the password entered when we set up MySQL earlier. Once Knoda connects, assuming everything goes as planned, you should have access to the MySQL default databases Bri. We can ignore these as for our projects for this chapter, we are going to setup a simple music database that we can then access through KDevelop and use within our own programs.
To create a completely new MySQL database with Knoda, go to the File/New/Database menu.
Select the New Database menu item and we get this dialog:
As you can see, to setup a new database, all we have to do is type in the name and select OK. At this point, absolutely nothing will appear to happen as Knoda will not automatically change to the newly created database, nor will it ask you if you want to. This is not a major problem though as changing to the new database is easy.
Select the Database combobox and select from any of the available databases. We want KDevelopMusic which, once selected, Knoda will load our new, empty database. First of all, we want to create three tables. Creating a new item in the table, be it a table, Query, etc., is as simple as right clicking and selecting New. For this example music database, we are going to create three tables, these being the Artist table, the Album table, and the Songs table.
We can see here the preliminary setup for the Artist table which contains two fields: Artist_ID and Artist. To add a field to a table, click on the New Field button and the fill out the options for the field in the option boxes on the top right. You can experiment with fields and tables here, but you should really have a good idea of what your tables are going to contain and how they are going to work together before you start.
One of the worst ways you can possibly design a database is by trying to cram everything into a single table. This is just asking for trouble because you will then be making the job of accessing the table far more difficult; for example, if we know the ID of an artist with this table, we can use a pseudo SQL statement along the lines of Select * from Artist where Artist_ID = knownvalue. If however, we also add the album names to this table, we are going to end up trying to separate out the individual album titles from the results of our Select statement.
The tables created here are for a simple music database; basically, we are going to be storing the names of the artists, the titles of the albums, and the songs that each album contains. The database will be created in such a way that we can get the artist name from the song, or the album name from the song, but we won't include stuff like running times of songs, release dates, or publishers. If you really want all those details, you can always add them or record the album in amaroK.
Once you have created the tables, you can start adding information to them. This is done in Knoda by right clickzng the table and selecting Start.
For now, we will add some initial testing information in Knoda so that we can make sure that everything works as expected from this end. We can also play around with the data here so that when we get to developing programs with KDevelop, we can concentrate on the programming side of things and have some SQL statements already if we need them.
For test purposes, I've added three Bands with four albums, containing sixty two songs between them. The test tables look like this, first the Artist table, followed by the Album table, and a snippet from the Songs table:
In order to view this as the result of a proper database query, we need to create a new query by right clicking on Queries and selecting New.
This is the Knoda graphical development for a SQL query, and the query itself is the standard query for this database. It basically gets all the songs according to album and artist, when you run it, like so:
I've called it the Standard Query because any other queries that are used will basically be an edit to this query. There are two ways that you can develop SQL queries with Knoda: the most obvious is through the graphical interface as shown above, and the other, for those who can write SQL off the top of their heads, you can type in the SQL.
To get to the SQL editor, select the View menu, and select to use QBE. This will turn off the graphical development and allow you to type the SQL straight in to the editor. The SQL for the Standard Query is:
SELECT "Artist0"."Artist" , "Album1"."Album_Title" , "Songs2"."Song_Title"
FROM "Artist" "Artist0" , "Album" "Album1" , "Songs" "Songs2"
WHERE ("Album1"."Artist_ID"="Artist0"."Artist_ID")
AND ("Songs2"."Album_ID"="Album1"."Album_ID")
Though seeing as the graphical interface is there and it opens by default, it seems kind of silly not to use it if it is going to make life easier.
When we first start a new query, the tables shown in the picture above are not there so we need to add them. We do this by right clicking in the empty space above the combo boxes. This will give you a menu with one option: "Add data source". When you select this, a dialog box will open showing you the tables that are available in the currently selected database.
You are free to add as many tables as you want, and in the case of this example, we add all of them before closing the dialog.
The first thing you'll want to do when you have added the tables to the query is set up the links between the tables. This is done by selecting the item in the table to link and dragging it to the appropriate item in another table.
For example, in the above image, Artist_ID in the Artist table should map to Artist_ID in the Album table. It is good practice to have any links between tables to be named the same in each table, the idea being that anyone looking at the tables can then easily see what links to what. Whilst on the subject of names, the alert ones will have noticed that Knoda has changed the table names adding a number beginning with zero to the table names as they were added. These are just aliases for the tables which are resolved in the From part of the SQL statement.
FROM "Artist" "Artist0" , "Album" "Album1" , "Songs" "Songs2"
which tells the database that "Artist0" is an alias for "Artist", etc.
When you release the mouse over the table that you wish to establish a link with, you will get a dialog, which shows you the link that Knoda thinks you are trying to make, and allows you to change it if you meant to link to another item in the table.
Once you have established the links, all you need to do is setup exactly what it is that you want to display. This is done through the drop down boxes at the bottom of the screen. If you are a complete beginner, it can take a few attempts to get this to display exactly the way that you want to, but as a basic rule of thumb, you should have tables that display the least information on the left and work right with the tables, displaying more information as you go. Once the tables have been added to the query, they will be selectable from the drop down box for the table. Once we have selected the table, we can select any field from that table that we want to display.
In the Standard Query that we have set up in this example, we only show the information that is relevant to the topic. All the items that end in ID are designed for us to use from within the database, and are therefore not really important to anyone who is looking at the database simply to see what tracks are on a specific album.
KDevelop database support
KDevelop has three GUI widgets that can be used for displaying database information; these are the Qt class widgets DataTable, DataBrowser, and DataView.
Using a DataTable
Unfortunately, database programming isn't as straightforward as it could be at the moment, so it is better to start with the idea that we are going to have to do most of the work by hand. The main source of the problems being that while the DataTable will attempt to set up a database connection if you go through the wizard to set it up, it doesn't save the information that you use to log into the database, which means that if you try to just use the wizards and run the code, you will be told that there was an error connecting to the database.
The way to get the QDataTable to work is to drop it onto the form and then just cancel the wizard. This will add a blank QDataTable to your form. We can work with this.
First of all, we are going to have to add a dialog so that we can get the user name and the password from the user. We could hard code these but it's just plain nasty, so in an effort not to encourage bad habits, we'll do it properly from the start.
Adding a Dialog
To add a new dialog to the project, click on the "New File" tab on the left side of KDevelop and select the appropriate dialog. For our simple user and password dialog, we'll select the standard dialog with buttons (bottom). When we select the dialog, we are given a setup dialog,
This just asks what the name of the file is, and gives a final opportunity to change our minds about which dialog we want. We are then presented with an automake dialog that asks which of the current projects we want to add this to. If you just want to add the dialog to the current project you are working on, then accept the defaults by clicking OK.
Although we now have a new UI file, we still do not have any class files to implement the new user interface, in this case our password dialog. There is however a need to be careful about class names here so don't go rushing in creating files called PasswordDialog.h and PasswordDialog.cpp or you could start making life really complicated and annoying. Remember what was said in earlier chapters about how the meta object compiler works and how it will create a header and .cpp file for a .ui file even though you will never see it, and it is this file that controls the class declarations for the form widgets. Any code implementation of a form needs to inherit from these classes, which admittedly is a bit tricky at the moment as they don't exist yet.
So first of all, we need to get the meta object compiler to generate our class and header file. This is done by running Automake and friends, followed by configuring from the build menu and then building the project. Once the project is built, you will see the newly created files in the projectname/debug/src directory. In this case, they are the passworddialog.h which looks like:
#ifndef PASSWORDDIALOG_H
#define PASSWORDDIALOG_H
#include <qvariant.h>
#include <qdialog.h>
class QVBoxLayout;
class QHBoxLayout;
class QGridLayout;
class QSpacerItem;
class QPushButton;
class passwordDialog : public QDialog
{
Q_OBJECT
public:
passwordDialog( QWidget* parent = 0, const char* name = 0, bool modal = FALSE,
WFlags fl = 0 );
~passwordDialog();
QPushButton* buttonHelp;
QPushButton* buttonOk;
QPushButton* buttonCancel;
protected:
QHBoxLayout* Layout1;
QSpacerItem* Horizontal_Spacing2;
protected slots:
virtual void languageChange();
};
and the passworddialog.cpp file:
#include <kdialog.h>
#include <klocale.h>
** Form implementation generated from reading ui file
**'/home/pseudonym67/Dev/KDE/BeginningKDEProgramming/chapterfivedatatable/src/
** PasswordDialog.ui'
**
** Created: Fri Mar 3 18:09:34 2006
** by: The User Interface Compiler ($Id: qt/main.cpp 3.3.4 edited Nov 24 2003 $)
**
** WARNING! All changes made in this file will be lost!
****************************************************************************/
#include "PasswordDialog.h"
#include <qvariant.h>
#include <qpushbutton.h>
#include <qlayout.h>
#include <qtooltip.h>
#include <qwhatsthis.h>
* Constructs a passwordDialog as a child of 'parent', with the
* name 'name' and widget flags set to 'f'.
*
* The dialog will by default be modeless, unless you set 'modal' to
* TRUE to construct a modal dialog.
*/
passwordDialog::passwordDialog( QWidget* parent, const char* name, bool modal, WFlags fl )
: QDialog( parent, name, modal, fl )
{
if ( !name )
setName( "passwordDialog" );
setSizeGripEnabled( TRUE );
QWidget* privateLayoutWidget = new QWidget( this, "Layout1" );
privateLayoutWidget->setGeometry( QRect( 20, 240, 476, 33 ) );
Layout1 = new QHBoxLayout( privateLayoutWidget, 0, 6, "Layout1");
buttonHelp = new QPushButton( privateLayoutWidget, "buttonHelp" );
buttonHelp->setAutoDefault( TRUE );
Layout1->addWidget( buttonHelp );
Horizontal_Spacing2 = new QSpacerItem( 20, 20, QSizePolicy::Expanding,
QSizePolicy::Minimum );
Layout1->addItem( Horizontal_Spacing2 );
buttonOk = new QPushButton( privateLayoutWidget, "buttonOk" );
buttonOk->setAutoDefault( TRUE );
buttonOk->setDefault( TRUE );
Layout1->addWidget( buttonOk );
buttonCancel = new QPushButton( privateLayoutWidget, "buttonCancel" );
buttonCancel->setAutoDefault( TRUE );
Layout1->addWidget( buttonCancel );
languageChange();
resize( QSize(511, 282).expandedTo(minimumSizeHint()) );
clearWState( WState_Polished );
connect( buttonOk, SIGNAL( clicked() ), this, SLOT( accept() ) );
connect( buttonCancel, SIGNAL( clicked() ), this, SLOT( reject() ) );
}
* Destroys the object and frees any allocated resources
*/
passwordDialog::~passwordDialog()
{
}
* Sets the strings of the subwidgets using the current
* language.
*/
void passwordDialog::languageChange()
{
setCaption( tr2i18n( "UserName And Password" ) );
buttonHelp->setText( tr2i18n( "&Help" ) );
buttonHelp->setAccel( QKeySequence( tr2i18n( "F1" ) ) );
buttonOk->setText( tr2i18n( "O&K" ) );
buttonOk->setAccel( QKeySequence( tr2i18n( "Alt+K" ) ) );
buttonCancel->setText( tr2i18n( "Ca&ncel" ) );
buttonCancel->setAccel( QKeySequence( tr2i18n( "Alt+N" ) ) );
}
#include "PasswordDialog.moc"
Now we can set about getting our dialog up an running. Right click on the the .ui file in Automake Manager.
In order to create the classes to implement the password dialog, we need to select either the "Subclassing Wizard" or the "Create or Select Implementation", the actual file creation is the same in either one.
The main difference is that with the Subclassing Wizard, as you can see, you can choose to specialise the implementation of some of the buttons. In this case, we are going to need to implement the accept function so that we can later save the strings added for the username and the password and use them to access the database. In order to avoid naming issues, I've named the class PasswordDialogImpl to make it clear that this is the implementation of the password dialog.
The final dialog that we get is the Automake Manager dialog. If we look at the .h file, we can see that:
#ifndef PASSWORDDIALOGIMPL_H
#define PASSWORDDIALOGIMPL_H
#include "PasswordDialog.h"
class PasswordDialogImpl : public passwordDialog
{
Q_OBJECT
public:
PasswordDialogImpl(QWidget* parent = 0, const char* name = 0, bool modal = FALSE,
WFlags fl = 0 );
~PasswordDialogImpl();
public slots:
protected:
protected slots:
virtual void accept();
};
not only does it inherit from the MOC created passwordDialog class, but provides an implementation of the accept() function. As we have just added new files to the project, run automake and friends from the build menu, followed by configure, and then run build to make sure it all builds correctly. Now we can get on with implementing the dialog.
Implementing the dialog
From the dialog point of view, the implementation is straightforward. We declare a couple of strings, one for the user name and one for the password.
private:
**/
QString strUserName;
**/
QString strPassword;
and add the get and set functionality, and then we add the following code to the overridden accept function:
setPassword( passwordLineEdit->text() );
setUserName( userLineEdit->text() );
QDialog::accept();
The technical bit appears when we realise that we want to have the dialog appear as the application starts but preferably before it is displayed. This way, when the application starts, it will show the database table data as we want it, without it obviously drawing it after the application has started. To do this, we need to override the polish function. The polish function performs the initialisation of the widget before it is shown, but the documentation comes with the warning that you should call the base class polish function before implementing your own code. This ensures that the default code for the widget is completed before you make your changes, as making the changes before the base classes have called polish can trigger further calls to polish, ending up with infinite recursion.
So our overriding of the polish function looks like this:
void ChapterFiveDataTableWidget::polish()
{
QWidget::polish();
PasswordDialogImpl *dlg = new PasswordDialogImpl();
if( dlg->exec() == QDialog::Accepted )
{
loadDatabase( dlg->userName(), dlg->password() );
}
}
Here we call the base class polish and then create a new dialog and execute it modally with the exec function. Note that calling show would display a modeless dialog that would have the main application widget displaying in the background, which is exactly what we don't want it to do.
Query the database
Now that we have the username and the password entered as the application starts, there are three more parameters that we need to be able to connect to the database. These are the host computer and the database name and the driver that we are going to use to connect to the database. These are the parameters that are entered into the Knoda connection dialog so they should be familiar by now. I've set up strings to hold the variables in the ChapterFiveDataTableWidget class and provided the appropriate access functions, although this class is not accessed from anywhere, so they could just be left as private variables that are intialised in the constructor as:
strName = "KDevelopMusic";
strDriver = "QMYSQL3";
strHost = "localhost";
These variables are all used in the loadDatabase function which is called at the end of our overridden polish function.
The driver variable starts things off and is used with the line:
dbConnection = QSqlDatabase::addDatabase( strDriver );
This call initialises our database connection variable that is declared in the ChapterFiveDataTableWidiget class declaration as:
QSqlDatabase *dbConnection;
A detailed listing of database drivers that can be used with Qt is available in the SQL Module Drivers section of the help.
Now we set up the connection parameters to the database:
dbConnection->setDatabaseName( name() );
dbConnection->setUserName( user );
dbConnection->setPassword( password );
dbConnection->setHostName( host() );
if( dbConnection->open() == true )
As you can see, we are simply using code to set exactly the same parameters we used for Knoda, so as long as we can get into Knoda, there should be no reason for the dbConnection->open call to return false.
We are now in a position where we are ready to actually query the database and display it and when using a datatable, as long as we have done our preparation properly, the actual querying of the database is disgustingly easy. First of all, a browse through the QSqlCursor class help file and its derived class QSqlSelectCursor shows us that we can pass a table name to QSqlCursor or a SQL statement to the QSqlSelectCursor constructor, along with our database collection, and this will give the results of our query in our newly constructed cursor object which we can then pass to the QDataTable object and let it do all the drawing and displaying for us. In code, this looks like this:
QSqlSelectCursor *cursor = new QSqlSelectCursor( "SELECT Artist FROM Artist",
dbConnection );
dataTable1->setSqlCursor( cursor, true, false );
dataTable1->refresh();
for( int i=0; i<dataTable1->numCols(); i++ )
{
dataTable1->adjustColumn( i );
}
The first call we use which is blanked out in the above code is the creation of a QSqlCursor object that takes the Artist table as a parameter and an auto-populate value of true along with the database connection pointer.
We then pass the cursor to QDataTable by calling the setSqlCursor function, passing in the cursor itself and the value true for the autopopulate option which tells the table to automatically display the data. The final parameter is for autodelete, and as we don't want the table to be edited here, we set it to false, which is the default. The following call to QDataTable refresh tells the table to redraw itself and it will use the current cursor to do this.
The final piece of code just gets the number of columns and then tells all the table columns to adjust their width to that of the largest entry in the column.
The resulting view of the table is:
which is fine and is exactly what we asked for as it shows all the data in the table. The only problem being that it is not really what we want as the Artist_ID is a key value and is of no interest to anyone who is simply trying to view the records in the database, so we'll switch to QSqlSelectCursor and see if we can get something a little more appropriate.
If we use the SQL statement:
"SELECT Artist FROM Artist"
in the QSqlSelect constructor, we get:
which is much more along the lines of what we want. But of course, now we have to play just a little more and see what the StandardQuery we did in Knoda would look like:
Using the DataBrowser
Setting up the DataBrowser project can be a little tricky, so here's the least painful way to do it. Create this using the Simple Designer based KDE application as with all the others, and remove the label and the button as well as all the code. Then break the layout on the form and resize it so that you can fit the QDataBrowser object onto it. Then do a complete build of the project, you will need to run automake and friends and then configure, just to make sure that everything is satisfactory. Then shut down KDevelop. The object of this is so that when you load KDevelop, this project is loaded on startup. Restart KDevelop and then add the QDataBrowser to the form. If the Database Connection wizard starts up, then everything is fine, otherwise you are just going to have to restart KDevelop and try again.
For this project, we will want to run through the wizard as although it does not retain the database connection information and we will have to code that by hand, it does give us a GUI for our query results and that saves us the task of having to do it.
First of all, we click on the Setup Database Connections button and fill out the connection dialog.
As you can see, the only change here is that we have provided a name for the connection, the rest of the input is as it has been throughout this chapter. Clicking on the Connect button gives us:
The Music connection has been established so we can click the Close button. We used the Artist table last time so this time we'll select the Album table and click Next. I'll admit at this point that this database is probably not ideal for demonstrating the browser as it would be more effective if the tables were to contain more items for display, but even with one displayed field, you should get the idea, and the programming principals will apply no matter how many fields are present in the database table.
The selection of fields is controlled by the buttons in the middle which are not very clear here, but from the top down, they are:
- Move field to list of displayed fields
- Remove field from list of displayed fields
- Move field down
- Move field up
Once we have the fields set up that we want, we click Next. The next screen in the wizard allows us to add a Where clause to the SQL.
and to set the sort order for the displayed fields. Then we set the display options:
The final option allows us to set if we want the wizard to generate the SQL for updates. This gives us a GUI that looks like:
Reusing a dialog
The next thing to do is add the polish function to our class that inherits from the "projectname" base class; we do this in the usual way by adding:
public slots:
virtual void polish();
to the header file and then adding the implementation to the CPP file. There is some code from the the wizard generated file that we can use. The implementation of the polish function in the ChapterFiveDataBrowserWidgetBase class is:
if ( dataBrowser3 ) {
if ( !dataBrowser3->sqlCursor() ) {
QSqlCursor* cursor = new QSqlCursor( "Album", TRUE, MusicConnection );
dataBrowser3->setSqlCursor( cursor, TRUE );
dataBrowser3->refresh();
dataBrowser3->first();
}
}
which is useful to us later, but it won't work as it is because the necessary parameter for the MusicConnection, which is the name given by the wizard to QSqlDatabase, has not been set yet.
First of all, we need to get the user name and password using the dialog that we used in the chapterfivedatatable project. We do this by right clicking on the project in the Automake Manager.
and selecting Add Existing Files.. which gives us this dialog:
Select the .ui file and the implementation header and definition file, and click Add Selected. As long as you don't start changing the names of things, the implementation should work without a hitch as the MOC will generate "projectname" widgetbase classes exactly as it did with the original project.
You will then be asked if you want to link or copy the files. In the normal scheme of things, you would have the classes that you reuse in a standard directory and link to them rather than copy them; this then means that if you do need to make any changes to the class files, then they are automatically propagated through any projects and you don't need to start searching your hard disk for different implementations of the same class.
In this case though, they have been copied to the project as in this case, the project is a stand alone project that should be distributed in such a way as it can be built with as few issues as possible.
QWidget::polish();
PasswordDialogImpl *dlg = new PasswordDialogImpl();
if( dlg->exec() == QDialog::Accepted )
{
and build the project making sure that you have all the required headers in place. Then add the setup code for QSqlDatabase:
MusicConnection = QSqlDatabase::addDatabase( driver() );
if( MusicConnection == 0 )
{
KMessageBox::error( this, "The database connection is invalid", "Data Browser Demo" );
return;
}
MusicConnection->setDatabaseName( name() );
MusicConnection->setHostName( host() );
MusicConnection->setUserName( dlg->userName() );
MusicConnection->setPassword( dlg->password() );
if( MusicConnection->open() == true )
{
Now the problem is that this code still doesn't work because of this:
As far as the debugger is concerned, at this moment in time, the GUI objects have not been completely constructed, which means that any tests for the data browser objects are pretty hit or miss; in fact, on my computer, the line:
if( !dataBrowser3->sqlCursor() )
fails every time because it does not give a false value for sqlCursor. So what we do is remove this line and continue to generate the new QSqlCursor that points to our data. At the very worst, here with the QSqlCursor inheriting from QObject, we should only have an extra QSqlCursor hanging around until the end of the function.
When we remove the test on the QSqlCursor, we get:
if( MusicConnection->open() == true )
{
if( dataBrowser3 )
{
QSqlCursor* cursor = new QSqlCursor( "Album", TRUE, MusicConnection );
dataBrowser3->setSqlCursor( cursor, TRUE );
dataBrowser3->refresh();
dataBrowser3->first();
}
}
and a program that gives us:
On a final note, you should remember that we accepted the default setting for auto-editing, which is on. This means that any changes you make to the titles while running this program will be made to the database.
Using the DataView
The QDataView is a cut down version of QDataBrowser without the inbuilt ability to move through the records. It is designed more for paying attention to a particular section of a database rather than browsing through all the records in it. For say, looking at personal records where the next record in the database doesn't necessarily have any connection with what you are doing with this record. For this reason, the Music database just wasn't adequate to display the idea of how the QDataView works, so I put together a quick fictional people database using Knoda using the same methods as described above.
The Knoda table looks like:
As you can see, it is a simple enough database containing nothing more than the names and addresses of fictional people, well, the addresses are fictional anyway. It does however, give us enough fields to make our dataview project look a bit more respectable.
To start the wizard for creating the chapterfivedataview project, create the Simple Designer based KDE application as before, and remove the widgets and button_clicked functionality; save and build the project and then add a DataView to the form.
The wizard is almost identical to the DataBrowser wizard, only slightly shorter.
With the only differences in the setup from previous projects being minor, such as the change of the database name.
though there are more fields to display when we get to the fields dialog.
When we get to the code, we notice that things are pretty much the same as have been in recent projects. The basic structure is to set up a QSqlForm and add all the display widgets to the form. As a user of the project, you don't directly use the QSqlForm yourself although if you wish to hand code a database form, looking at the wizard implementation is the way to do it, as it sets the names for the widgets to table columns of the database and then maps the record you pass to the form to the appropriate columns. This is also how the previous QDataBrowser project works although in that project, the QDataBrowser wraps the QSqlForm and controls things for us.
One thing you will notice if you look at the generated code is that the polish function is not overloaded automatically here. This is because the design of the QDataView isn't really suited to accessing the database immediately on start up and is why the chapterfivedataview project is developed the way it is.
We start with the idea of a separate application that contacts the database when it needs to and displays a single entry from the database. Technically, we could have overridden the polish function and connected to the database from there, but I decided to separate everything out. So now we have a separate database function that is activated by clicking the button, and at start up, what is an empty QComboBox. The idea for the project being that the user connects to the database and a list of available people is placed in the QComboBox. The user then selects the name of the person they wish to view the details of in the QComboBox and then the form is filled out.
The database connection starts when the button is clicked:
PasswordDialogImpl *dlg = new PasswordDialogImpl();
if( dlg->exec() == QDialog::Accepted )
{
FictionalPeopleConnection = QSqlDatabase::addDatabase( databaseDriver() );
FictionalPeopleConnection->setDatabaseName( databaseName() );
FictionalPeopleConnection->setHostName( host() );
FictionalPeopleConnection->setUserName( dlg->userName() );
FictionalPeopleConnection->setPassword( dlg->password() );
if( FictionalPeopleConnection->open() == true )
{
QSqlSelectCursor *cursor = new QSqlSelectCursor(
"SELECT FictionalPeople.First_Name, FictionalPeople.Middle_Names,
FictionalPeople.Surname FROM FictionalPeople", FictionalPeopleConnection );
cursor->select();
QString strName;
while( cursor->next() == true )
{
strName = cursor->value( "First_Name" ).toString() + " ";
if( cursor->value( "Middle_Names" ).toString().isEmpty() == false )
{
strName += cursor->value( "Middle_Names" ).toString() + " ";
}
strName += cursor->value( "Surname" ).toString();
namesComboBox->insertItem( strName );
}
}
}
As you can see, there is nothing that should surprise you in this function. The password and database access is exactly the same as it has been in the previous two projects; it is just initialised differently. The SQL statement:
SELECT FictionalPeople.First_Name, FictionalPeople.Middle_Names,
FictionalPeople.Surname FROM FictionalPeople
gets the first name, the middle names, and the last name from all the entries in the database. We then build up a name string by using the value function from the QSqlRecord class. We can do this because if you follow the class hierarchy in the help, you will see that the QSqlSelectCursor class inherits from the QSqlCursor class which in turn is derived from the QSqlRecord and the QSqlQuery classes. You could call the fact that it doesn't automatically fill in the form as either a bug or a feature depending on your mood, but when you think about it, if there is a long list of names, why would you specifically want to see the first one any more than the others? Even when they are sorted.
The way to do this is to receive the activated signal from the QComboBox when a new item is selected in the list and displayed in the viewable area.
Adding the Signal Handler here adds the function to our widget class and then we need to add a variable name for QString so that we can use it.
virtual void namesComboBox_activated( const QString& name );
and the function is,
void chapterfivedataviewWidget::namesComboBox_activated( const QString& name )
{
int nSurname = name.findRev( ' ' );
+ QString::number( nSurname ) + "\nin name " + name );
int nLength = name.length();
setSurname( name.mid( nSurname+1, nLength ) );
+ " \nFrom the name " + name );
int nFirstName = name.find( ' ' );
setFirstName( name.left( nFirstName ) );
+ "\nWith the space at " + QString::number( nFirstName ) );
if( nFirstName != nSurname )
{
setMiddleName( name.mid( nFirstName+1, nSurname - ( nFirstName+1 ) ) );
+ "\n Starting from " + QString::number( nFirstName+1 ) + "\nEnding at "
+ QString::number( nSurname ) + "\nFrom a length of "
+ QString::number( nLength ) );
}
QString strSqlString = "SELECT * FROM FictionalPeople
WHERE FictionalPeople.First_Name = '" + firstName() + "' ";
if( middleName() != 0 && middleName().isEmpty() == false )
strSqlString += "AND FictionalPeople.Middle_Names = '" + middleName() + "' ";
strSqlString += "AND FictionalPeople.Surname = '" + surname() + "'";
QSqlSelectCursor *cursor = new QSqlSelectCursor( strSqlString,
FictionalPeopleConnection );
cursor->select();
cursor->next();
dataView2->refresh( cursor );
strMiddleName.truncate( 0 );
}
A bit of a blob, so we'll break it down into its three sections. The first section is the processing of the string that is passed to us from the QComboBox. This string is the full name of the person we want, so we will have to break it up again. Without the KMessageBox calls that show the progress through the string, it looks like this:
int nSurname = name.findRev( ' ' );
int nLength = name.length();
setSurname( name.mid( nSurname+1, nLength ) );
int nFirstName = name.find( ' ' );
setFirstName( name.left( nFirstName ) );
if( nFirstName != nSurname )
{
setMiddleName( name.mid( nFirstName+1, nSurname - ( nFirstName+1 ) ) );
}
Here we use a few of the QString functions to break up the string, starting with the findRev function which starts at the back of the string and moves towards the start until it finds what you have specified in the call to the function. We then store the length of the string for future use, and also declare a few string variables in the class to keep the information. These take the, hopefully, obvious names of FirstName, MiddleName, and Surname. We then store the surname using the value plus one returned by findRev because we don't want the space. We then find the first space from the start of the string using the find function and then store the first name using the left function. Once we have the end and the beginning, anything else falls into the middle names category so we get it or them using the mid function.
QString strSqlString = "SELECT * FROM FictionalPeople
WHERE FictionalPeople.First_Name = '" + firstName() + "' ";
if( middleName() != 0 && middleName().isEmpty() == false )
strSqlString += "AND FictionalPeople.Middle_Names = '" + middleName() + "' ";
strSqlString += "AND FictionalPeople.Surname = '" + surname() + "'";
The next section of code builds the SQL statement using the name values that we have just extracted from the name QString. It should be noted that the variable values that we add are surrounded by ' marks. This is a small detail but essential as the query won't work without them.
The final section is:
QSqlSelectCursor *cursor = new QSqlSelectCursor( strSqlString,
FictionalPeopleConnection );
cursor->select();
cursor->next();
dataView2->refresh( cursor );
strMiddleName.truncate( 0 );
Here we run the query and make sure that we are at the start of the results and that they or it is ready to be read. Then we pass the cursor straight to the QDataview refresh function which takes a QSqlResult and automatically fills out the form.
One final tip for database development is never presume that the SQL statement has worked. If something has failed, it is more often than not a fault in the SQL statement. When you are sure that it works, check everything else.
Summary
This has been a brief look at database development with KDevelop focusing on the available widgets. It has tried to cover a lot of ground, but there is much left for the reader to experiment with. This chapter could go on for a few hundred more pages about transactions and saving data etc., but hopefully it will be enough to get people started.
