Opening Firebird embedded database with QIBASE plugin

Mr.Floppy

Hello Ladies and Gentlemen,

I need to process some data from a FIREBIRD 3.0 embedded database file in Windows 10 x64 with Qt Creator 5.7.
I have compiled the QIBASE plugin by basically following this hint https://forum.qt.io/post/201575

- qmake "INCLUDEPATH+=C:/firebird/include" "LIBS+=-LC:/firebird/lib" "LIBS+=-lfbclient_ms" ibase.pro
- mingw32-make
- mingw32-make install

Now I have created a new widgets project and want to test the plugin, but all I get is one of two QSqlError messages.

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    const QString hostName = "localhost";
    const QString dbFileName = "D:/FirebirdDatabase/DATABASE.FDB";
    const QString userName = "SYSDBA";
    const QString password = "masterkey";

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE","firebirdDB");

    if (!db.isValid()) ui->textEdit->append("DB invalid!\n\n");

    //db.setHostName(hostName);
    db.setDatabaseName(dbFileName);
    db.setUserName(userName);
    db.setPassword(password);

    db.open();
    ui->textEdit->append(db.lastError().text());
}

First error, if the local Firebird-Server is NOT running:

Unable to complete network request to host "xnet://Global\FIREBIRD". - Failed to establish a connection. Error opening database

Alternative error, if the local Firebird-Server IS running

Your user name and password are not defined. Ask your database administrator to set up a Firebird login. Error opening database

The fact, that the Firebird-Server is responding with an error message suggests, that the Plugin is working okay, but the username and password exist, so the error message doesn’t make sense.
Nevertheless, since I want to access a local file, I think the server does not have to be running… so I am doing something wrong when configuring the connection the way I am used to from working with SQLITE most of the time.

I know that the database file itself is okay and the user credentials are correct, because with the Firebird ISQL Tool can read the file just fine, even when the server is NOT running.

Question:
Does anybody see an obvious error in my connection configuration?
Or is the plugin not working as expected after all?

Thanks for any hints!

Kind regards,
Floppy

jsulm

@Mr.Floppy I never used Firebird, but maybe this can help: http://www.firebirdsql.org/manual/ufb-cs-embedded.html
My guess is that you need to do what is explained in the link above.

Mr.Floppy

@jsulm Thanks for the reply!

I never used Firebird either, and after this one, i never intend to do so again. The documentation is horrible and there is a constant lack of date or version notifications, so most of the stuff one can find is pre 2010.

The link you posted is also deprecated for Firebird 3, because there is no fbembed.dll any more. They have merged it into one dll... but since this is the only Firebird embedded manual page on the internet, reading it again put me on the right track.
Although the error message doesn't imply it, firebird was indeed not finding enough of its libraries.

You have to copy a whole bunch of Firebird dlls into your application folder AND you have to provide the path to the dll that should be loaded in QSqlDatabase::connectOptions via "ClientLibrary=/pathto/firebird.dll"

So for now I just copied the entire firebird application folder into my application folder, because putting just the dlls they ask for in the documentation was not enough... I will sort that one out later.

My new connection configuration looks like this:

    const QString hostName = "localhost";
    const QString dbFileName = "D:\\firebird_test\\DATABASE.FDB";
    const QString userName = "SYSDBA";
    const QString password = "masterkey";
    const QString serverType = "1";
    const QString clientLibrary = QString("D:/firebird_test/fbclient.dll");

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE","connectionName");
  
    db.setHostName(hostName);
    db.setDatabaseName(dbFileName);
    db.setUserName(userName);
    db.setPassword(password);
    db.setConnectOptions(QString("Database=%1;" \
                                 "User=%2;" \
                                 "Password=%3;" \
                                 "ServerType=%4;" \
                                 "ClientLibrary=%5;")
                         .arg(dbFileName)
                         .arg(userName)
                         .arg(password)
                         .arg(serverType)
                         .arg(clientLibrary));

    db.open();

It connects at last.
Now I have to see what I can do with said connection and then clean up the mess... I will let you know the results ;)

Mr.Floppy

I think this is solved.

Via trial and error I have sorted out this dependency tree.

Your_Application/
    sqldrivers/
        qsqlibase.dll
    plugins/
        engine12.dll
    fbclient.dll
    ib_util.dll
    icudt52.dll
    icuin52.dll
    icuuc52.dll
    icudt52l.dat
    firebird.msg
    your_application.exe

Some closing hints:

My application does not need firebird.msg, but the documentation says it might come in handy in some usecases.
DO NOT forget icudt52l.dat. It will result in:
"Could not find acceptable ICU library"
If you DO prepare your query, wrong or missing user credentials will result in:
"Attempt to execute an unprepared dynamic SQL statement."

QSqlQuery query = QSqlQuery(db);
QString queryString = "SELECT id,value FROM table";
query.prepare(queryString);
query.exec();

If you do NOT prepare your query, wrong or missing user credentials will result in a way better message:
"no permission for SELECT access to TABLE table"

QSqlQuery query = QSqlQuery(db);
QString queryString = "SELECT id,value FROM table";
query.exec(queryString);

So in conclusion:
NEVER try to find a solution based on a Firebird error message.
They are as missleading as they could be.

I hope I am not running into any more problems and maybe this will be helpful for future readers, struggeling with Firebird 3 and Qt.

Kind regards,
Floppy

Marcky

Did not work with Firebird 3.0.3 embedded without a running server or firebird.exe -a. It just started to work as a real embedded database and not a tcp based connection when using an empty hostName. Instead of:

const QString hostName = "localhost";

just

const QString hostName = "";

That helped finally!