Get Started with QT and Embedded FireBird

This article is the description of the first steps in setting QT, Visual Studio and FireBird. It may be useful for those, who just start working with database FireBird Embedded by means of QT provider. The article will be also helpful for the developers who just start working with QT.

When I wrote this article the question discussed was poorly documented and so I hope it can be really useful.

Contents

Introduction
Setting environment
1. Install QT SDK:
2. Build QT for the work with VS 2005(2008)
3. Install Visual Studio Addon for QT
4. Install FireBird
5. Build Plug-in for IBase
Connecting to the existent database
Creating FireBird database programmatically
Database queries
Simple Select from the database
How to call the stored procedure?
Conclusion
Useful links

Introduction

In this article we will consider questions:

Installation of QT SDK, its configuration and integration with Visual Studio.
Building IBase plug-in.
Creating the database programmatically and setting connection with it.
Also we will discuss the problem with the username and password for the database.
We will consider simple queries to the database and also calling of the stored procedures.

Setting environment

Described example is based on the QT 4.5.2 LGPL.

So to set the environment use the following steps.

1. Install QT SDK:

You should have required disk space (2-3 GB if you need to build all libraries and examples, 1.5 GB otherwise).
Install SDK
It’s recommended not to change the default path (or you can use the same path but for example on the disk “D:”).
Create Environmental Variable “QTDIR” and set the path “C:Qt2009.03qt” (if the disk was not changed on step 2.

2. Build QT for the work with VS 2005(2008)

1) Start “VS 2005 Command Prompt” (in the Start menu)

2) In the appeared console go to the folder QTDIR = “C:Qt2009.03qt”

3) Start “configure.exe” with such parameters:

ShellScript

configure.exe  –plugin –sql -ibase

You can see details on these parameters by means of the command:

ShellScript

configure.exe  -help

4) When the console asks: Which edition of Qt do you want to use? we choose Open Source Edition.

After that we press “y” to accept the license offer.

5) Now we should wait a bit while the files of VCProj and main Solution are being created. Finally the solution file projects.sln is created in the folder “C:Qt2009.03qt”.

3. Install Visual Studio Addon for QT

1) Close all Visual Studio applications.

2) Start the installation

3) Start Visual Studio and open QT options by means of the menu “QT->QT Options”. Click Add and create some name, for example “QT 4.5.2”. The specific name is not important but it is stored together with the project, and so the other developer can not to build the project in a proper way because of the error (“No such QT version is found on this machine” or something like this).

Specify the path to the QT folder that is for our example $(QTDIR)(“C:Qt2009.03qt”). And finally choose the new created record “QT 4.5.2” as the QT Default Version.

4) There is no need to build all projects. We should build only:

QtCore
QtGUI
QtSQl
QMain

Note: Win32 static library and QT Library have different settings for “Treat wchar_t as Built-In” property. If you want to build the Win32 static library into the QT application, then you should either build QT without this option or to change the property “Treat wchar_t as Built-In Type” to “No (/Zc:wchar_t-)”.

To switch off this option in QT you should do the following before the step 2:

Open file qmake.conf. It can be found here QTDIR/mkspecs/win32-msvc2005/qmake.conf. I worked with the Visual Studio 2005 and so used subfolder win32-msvc2005. If you work with the other version then open this file in the corresponding folder.
In this file we should change the flag QMAKE_CFLAGS by deleting -Zc:wchar_t-.

4. Install FireBird

Firebird can be downloaded here – FireBird.

5. Build Plug-in for IBase

Go to the folder $(QTDIR)srcpluginssqldriversibase and build the project in Debug and Release.

Before building change the project properties:

In the C/C++/General->Additional Include Directories add the path to the folder include (for example C:Program FilesFirebirdFirebird_2_1include)
In the Linker/General -> Additional Library Directories add the path to the folder where the libraries for FireBird are placed. (for example C:Program FilesFirebirdFirebird_2_1lib)
To build the IBase project we should correct the name of the linked library (Linker/Input->Additional Dependencies) from gds32_ms.lib to the fbclient_ms.lib. This library is included into the FireBird package.

Embedded Server can be downloaded here: FireBird Embedded 2.1.3 Release

Rename the file fbembed.dll to the fbclient.dll.

Connecting to the existent database

Before setting the connection with database we should first load the QIBASE plug-in. If you decide to use the plug-in and load it manually, then the following code is for you (with assumption that plug-in is in the same folder with EXE file).

if(!pluginLoader_.isLoaded())
    {
        pluginLoader_.setFileName(QApplication::instance()->applicationDirPath() 
+ QDir::separator() + qtIBasePluginName_);
        
        if (!pluginLoader_.load())
        {            
            //// Loading SQL Driver failed.;
            isInitialized_ = false;
            return false;
        }
    }
    QObject* object = pluginLoader_.instance();
    if (object == NULL)
    {            
        //Loading SQL Driver Instance failed.;
        pluginLoader_.unload();
        return false;
    }
    QSqlDriverPlugin* plugin = qobject_cast<QSqlDriverPlugin*>(object);
    if (plugin == NULL)
    {            
        //QSqlDriverPlugin == NULL;
        pluginLoader_.unload();
        return false;
    }
    driver_ = plugin->create("QIBASE");
    if (driver_ == NULL)
    {            
        //Loading QIBASE Driver Instance failed.;
        pluginLoader_.unload();
        return false;
    }
    isInitialized_ = true;
    return isInitialized_;
…

Now when the plug-in for working with FireBird is loaded, we can start with the setting the connection to our database.

connectionName_ = "Connection_1";
            
    QSqlDatabase database; 
    //Adding database (DRIVER);
    database = QSqlDatabase::addDatabase(driver_, connectionName_);
    //Check Valid database.;
    if (!database.isValid())
    {
        QString lastError = database.lastError().text();
        //Database is not valid
        return false;
    }
    //Set database configurations.;
// filePath = ":D:FireBirdAndQTdebugNew.FDB";
// userName = "Serg";
// password = 12345;
// connectionString_ = "server type=Embedded; auto_commit=True; 
// auto_commit_level=4096; connection lifetime=1; DataBase="%1"";
    database.setDatabaseName(filePath);
    database.setUserName(userName);
    database.setPassword(password);
    QString connectionString = QString(connectionString_).arg(filePath);
    database.setConnectOptions(connectionString);
    bool result = false;
    
    //"Openning database. Driver PTR == %d", (int)database.driver();
    result = database.open();
    if(!result)
    {
        QString lastError = database.lastError().text();        
        lastError_ = (uint)database.lastError().number();        
    }

I want you to pay special attention to the property QSqlDatabase of the object:

{code} database.setDatabaseName(filePath); database.setUserName(userName); database.setPassword(password); QString connectionString = QString(connectionString_).arg(filePath); database.setConnectOptions(connectionString); {/code}

Login, password and full path to the database could be passed in the connection string, but there I faced with the following problem. When passing all mentioned settings in the connection string and not by the functions set…(), I discovered that they were not assigned to the database object.

Creating FireBird database programmatically

To create the database in the program you should perform the following:

bool FireBirdDatabase::Create(const QString& filePath, const QString& 
userName, const QString& password)
{
    if (!isInitialized_)
    {
        Initialize();
    }
    if (QFile::exists(filePath))
    {
        return false;
    }
    databasePath_ = filePath;
    QString queryString;
    queryString += "CREATE DATABASE";
    queryString += " '" + filePath + "'";
    queryString += " USER '" + userName + "'";
    queryString += " PASSWORD '" + password + "'";
    queryString += " DEFAULT CHARACTER SET UNICODE_FSS";
    ISC_STATUS_ARRAY status;
    isc_db_handle   databaseHandle = NULL;
    isc_tr_handle   transactionHandle = NULL;
    unsigned short g_nFbDialect = SQL_DIALECT_V6;
    if (isc_dsql_execute_immediate(status, &databaseHandle, &transactionHandle, 
0, queryString.toStdString().c_str (), g_nFbDialect, NULL))
    {
        long SQLCODE=isc_sqlcode(status);
        return false;    
    }
    isc_commit_transaction( status, &transactionHandle );
    if (databaseHandle != NULL)
    {
        ISC_STATUS_ARRAY status;
        isc_detach_database(status, &databaseHandle);
    }
    return true;
}

Why did I choose isc_dsql_execute_immediate() method of the database creation? The answer is simple – I just didn’t manage to do it in another way :). Some providers allow to create database as follows:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
 db.setDatabaseName(":memory:");
    if (!db.open())
{
QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Unable to establish a database connection.n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.nn"
                     "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
}

BUT in particular for this QT version and IBASE driver this method doesn’t work.

To get more detailed information you can turn to the examples from QT (QTDIRexamplessqlConnection.h).

Note: Be careful – FireBird works only with the ASCII coding. Therefore if your path (filePath) contains UNICODE symbols then the function isc_dsql_execute_immediate returns an error.

Database queries

Simple `Select` from the database.

void DatabaseModel::SelectJobs(QStringList& jobs )
{
    QSqlQuery query = QSqlQuery(fireBirdDatabase_.CreateQuery());
    QString preparedString = "SELECT JOB_NAME FROM TBL_JOBS";
    query.prepare(preparedString);
    if (!query.exec())
    {
        QString err = query.lastError().text();         
       
        throw std::runtime_error("Error executing Query.");
    }    
    while (query.next())
    { 
        QSqlRecord record = query.record();
        jobs.append(record.value(0).toString());
        
    }
}

where fireBirdDatabase_.CreateQuery() looks as follows:

{
  return  QSqlQuery(QSqlDatabase::database(connectionName_));
  }

How to call the stored procedure?

Let’s consider an example – the procedure to add the new record to the database.

Our procedure obtain parameters: the string with the position name, the salary value as the integer number, and also the description that is stored in the binary form (BLOB).

Our procedure returns the ID of the new record.

{
      …
    QByteArray description("Test description");
    int salary = 1200;
    jobName = "tester";
    QSqlQuery query(fireBirdDatabase_.CreateQuery());
             bool result = query.prepare("EXECUTE PROCEDURE SP_INSERT_JOB (?, ?, ?)");
            query.addBindValue(jobName);
            query.addBindValue(salary);
    query.addBindValue(description);    
    if (!query.exec())
    {
        QString err = query.lastError().text();            
        throw std::runtime_error("Error executing Query.");
    }    
    query.next();
    int jobID = query.value(0).toUInt();
      …
    }