Financial Freedom

Written by:

Sergey Timoschuk,

Software Developer of Device Team, Apriorit Inc.

Content

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:  QTSDK 4.5.2 – http://get.qt.nokia.com/qtsdk/qt-sdk-win-opensource-2009.03.exe.

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:\Qt\2009.03\qt\” (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:\Qt\2009.03\qt\”

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

configure.exe –plugin –sql -ibase

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

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:\Qt\2009.03\qt\”.

3. Install Visual Studio Addon for QT

1) Close all Visual Studio applications.

2) Start the installation qt-vs-addin-1.0.2.exe – http://get.qt.nokia.com/vsaddin/qt-vs-addin-1.1.2.exe.

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:\Qt\2009.03\qt\”). 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 – http://downloads.sourceforge.net/project/firebird/firebird-win32/2.1.3-Release/Firebird-2.1.3.18185-0_Win32.zip?use_mirror=softlayer.

5. Build Plug-in for IBase

Go to the folder $(QTDIR)\src\plugins\sqldrivers\ibase 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 Files\Firebird\Firebird_2_1\include) In the Linker/General -> Additional Library Directories add the path to the folder where the libraries for FireBird are placed. (for example C:\Program Files\Firebird\Firebird_2_1\lib) 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 – http://downloads.sourceforge.net/project/firebird/firebird-win32/2.1.3-Release/Firebird-2.1.3.18185-0_Win32_embed.zip?use_mirror=softlayer

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(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:\FireBirdAndQT\debug\New.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:

 

database.setDatabaseName(filePath);

database.setUserName(userName);

database.setPassword(password);

 

 

QString connectionString = QString(connectionString_).arg(filePath);

database.setConnectOptions(connectionString);

 

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.\n\n”

“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 (QTDIR\examples\sql\Connection.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();

…

} Conclusion

I hope that this article will help you to:

Configure QT environment for the further work. Build the plug-in for the work with FireBird. Connect to the existent database or create the new one programmatically. Execute various requests to the firebird.

I’ve attached the test database and code to illustrate the described steps and examples.

To browse the database you can use these clients:

IBExpert – http://ibexpert.net/ibe/ FlameRobin – http://www.flamerobin.org/

To access the test database use login Serg and password 12345.

Download the source code.

Useful links Firebird database – http://www.firebirdsql.org/. FlameRobin (open-source administration tool) – http://www.flamerobin.org/. IBExpert (administration tool, free Personal Edition download) – http://ibexpert.net/ibe/. Other Firebird tools – http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_contrib_download. QTSDK 4.5.2 – http://get.qt.nokia.com/qtsdk/qt-sdk-win-opensource-2009.03.exe