QT 4.7.0 - DB connection pool - multithreaded program

[Kozy]

Greetings,

i am having alot of problems making a connection pool. I have made a QHash with a Qstring and a QSqlDatabase pointer.

QHash<QString, QSqlDatabase* > openConnections;

QHash<QString, QSqlDatabase* > openConnections;

To copy to clipboard, switch view to plain text mode 

I try to open a connection with this:

for(int i=0; i<number;i++){
        QSqlDatabase *db;
        db = new QSqlDatabase;
        QString DatabaseConnectionName;
        QString dbNameX=dbName;
        DatabaseConnectionName=dbNameX.append(QString::number(i));
        db->addDatabase("QODBC",DatabaseConnectionName);
        db->setDatabaseName(dbName);
        db->setPassword(password);
        db->setUserName(userName);
         if(!db->open()){
            QSqlError errDB;
            errDB = db->lastError();
            std::cout << qPrintable(errDB.text()) << std::endl;
        }
        openConnections.insert(dbName, db);
 
     }

for(int i=0; i<number;i++){
        QSqlDatabase *db;
        db = new QSqlDatabase;
        QString DatabaseConnectionName;
        QString dbNameX=dbName;
        DatabaseConnectionName=dbNameX.append(QString::number(i));
        db->addDatabase("QODBC",DatabaseConnectionName);
        db->setDatabaseName(dbName);
        db->setPassword(password);
        db->setUserName(userName);
         if(!db->open()){
            QSqlError errDB;
            errDB = db->lastError();
            std::cout << qPrintable(errDB.text()) << std::endl;
        }
        openConnections.insert(dbName, db);

     }

To copy to clipboard, switch view to plain text mode 

I get a driver not loaded error. If i do this for a single connection everthing works:

QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QODBC","TESTDB");
    db.setDatabaseName("TESTDB");
    db.setUserName("USER");
    db.setPassword("PASS");
    if( !db.open())
    {
        QSqlError errDBRev;
        errDBRev = db.lastError();
        std::cout << qPrintable(errDBRev.text()) << std::endl;
    }else{
        std::cout << "Connect worked" << std::endl;
    }

QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QODBC","TESTDB");
    db.setDatabaseName("TESTDB");
    db.setUserName("USER");
    db.setPassword("PASS");
    if( !db.open())
    {
        QSqlError errDBRev;
        errDBRev = db.lastError();
        std::cout << qPrintable(errDBRev.text()) << std::endl;
    }else{
        std::cout << "Connect worked" << std::endl;
    }

To copy to clipboard, switch view to plain text mode 

Can someone point me in the right direction.

I need a connection pool since my program makes alot of querrys on multiple databases ( monitoring sistem ). Mostly it holds about 500 active threads that need to be connected to 2 DBs and execute specific SQLs every X seconds/minutes/hours and i know opening and closing connections is a costly procedure.

With regards,

Marko

[TorAn]

I am curious - what type of hardware are you running your 500 threads on?

[wysota]

Each thread has to create its own connection and the creation process has to take place inside the thread. So you can't "reuse" a connection after its thread dies. I do somehing like this:

QList<QSqlRecord> threadedSql(const QSqlDatabase &db, const QString &query, const QVariantMap &arguments) {
    static QAtomicInt no = 0;
    QString connectionName = "connection"+QString::number(no++);
    QList<QSqlRecord> result;
    {
        QSqlDatabase localDb = QSqlDatabase::cloneDatabase(db, connectionName);
        if(!localDb.open()) break;
        QSqlQuery q(localDb);
        q.prepare(query);
        foreach(QString key, arguments.keys()) q.bindValue(key, arguments.value(key));
        if(!q.exec()) break;
        while(q.next()) { result << q.record(); }
        localDb.close();
    }
     QSqlDatabase::removeDatabase(connectionName);
     return result;
}
// ...
 
QVariantMap args;
args.insert(":id", 7);
QFuture<QList<QSqlRecord> > future = QtConcurrent::run(threadedSql, QSqlDatabase::database(), "SELECT * FROM TABLE WHERE id=:id", args);
//...
QList<QSqlRecord> result = future.result();

QList<QSqlRecord> threadedSql(const QSqlDatabase &db, const QString &query, const QVariantMap &arguments) {
    static QAtomicInt no = 0;
    QString connectionName = "connection"+QString::number(no++);
    QList<QSqlRecord> result;
    {
        QSqlDatabase localDb = QSqlDatabase::cloneDatabase(db, connectionName);
        if(!localDb.open()) break;
        QSqlQuery q(localDb);
        q.prepare(query);
        foreach(QString key, arguments.keys()) q.bindValue(key, arguments.value(key));
        if(!q.exec()) break;
        while(q.next()) { result << q.record(); }
        localDb.close();
    }
     QSqlDatabase::removeDatabase(connectionName);
     return result;
}
// ...

QVariantMap args;
args.insert(":id", 7);
QFuture<QList<QSqlRecord> > future = QtConcurrent::run(threadedSql, QSqlDatabase::database(), "SELECT * FROM TABLE WHERE id=:id", args);
//...
QList<QSqlRecord> result = future.result();

To copy to clipboard, switch view to plain text mode 

What you can do to avoid creating connections is to hold an active pool of threads (you can hold them on wait conditions or individual mutexes).

[Kozy]

Greetings,

thank you for your reply.

to clear it up my threads are active, they run forever. They do however sleep for a specified amount of time.

So If I understand you correctly, i cannot open the connections in the main thread and store their pointers in a list/hash and then pass them to the thread that request a connection over functions...? I think this should be possible in java.

I guess if this is not possible I will have to combine my threads into groups of threads per server, so i will not need as many connections as i do now and maybe think of keeping the connections alive. Currently i close them and clean all the resources when i don't need them anymore for the next few minutes. In my design every query has a time of sleep in the XML structure and source / destination, query, DB info...

About the question of what kind of hardware the app is running on, it is a 4 CPU virtual machine with a small storage that holds an instance of win server 2003 that also has Apache, Tomcat and DB2 installed. On this server I also run a PHP web page where we can see status of DBs and we can also monitor app. servers...

The app is monitoring over 70 DB2s "health" with different SQLs that let us know what is going on out in the field.

With regards,

Marko

[wysota]

to clear it up my threads are active, they run forever. They do however sleep for a specified amount of time.

This leads me to a conclusion you don't need so many threads. If they sleep for a period of time, they can be used to do some other work. In the end you'll probably do with 10-20 threads max.

So If I understand you correctly, i cannot open the connections in the main thread and store their pointers in a list/hash and then pass them to the thread that request a connection over functions...?

Passing any pointers doesn't make sense as you can retrieve a handle to a connection using the connection name and calling QSqlDatabase::database().

I think this should be possible in java.

It's possible in C++ as well but this particular behaviour is caused by the fact how Qt implements SQL connections. They are associated with the thread that creates them and you can't do anything about it.