Multiple connections to MySQL database

[ShamusVW]

Hello
Can someone assist me a bit please.
I have set up a class to do the connecting to a database as follows:

void DBMySQL::setupConnectionToDB()
{
    db = QSqlDatabase::addDatabase("QMYSQL", m_ConnectionName);
    db.setDatabaseName(m_Database);
    db.setHostName(m_Host);
    db.setUserName(m_User);
    db.setPassword(m_Password);
    db.setConnectOptions();
    db.setConnectOptions("MYSQL_OPT_RECONNECT=1");
...

void DBMySQL::setupConnectionToDB()
{
    db = QSqlDatabase::addDatabase("QMYSQL", m_ConnectionName);
    db.setDatabaseName(m_Database);
    db.setHostName(m_Host);
    db.setUserName(m_User);
    db.setPassword(m_Password);
    db.setConnectOptions();
    db.setConnectOptions("MYSQL_OPT_RECONNECT=1");
...

To copy to clipboard, switch view to plain text mode 

I am wanting to set up multiple connections, and hence the m_ConnectionName.

I also have a function to query the database:

QStringList DBMySQL::getData(QString queryString)
{
    QStringList sl;
    QSqlQuery query;
    m_DBState = query.exec(queryString);
    if (m_DBState) {
        query.setForwardOnly(true);
        int fieldCount = query.record().count();
        m_Result = QString::number(fieldCount,10) + " batches retrieved";
        while (query.next()) {
            QString str("");
            for (int i = 1; i <= fieldCount ; ++i) {
                if (i == fieldCount)
                    str = str + query.value(i-1).toString();
                else
                    str = str + query.value(i-1).toString() + ",";
            }
            sl << str;
        }
    }
    else
        m_Result = "Could not retrieve data";
    m_ErrorMsg = db.lastError().text();
    emit dbState(m_DBState, m_Result, m_ErrorMsg);
    return sl;
}

QStringList DBMySQL::getData(QString queryString)
{
    QStringList sl;
    QSqlQuery query;
    m_DBState = query.exec(queryString);
    if (m_DBState) {
        query.setForwardOnly(true);
        int fieldCount = query.record().count();
        m_Result = QString::number(fieldCount,10) + " batches retrieved";
        while (query.next()) {
            QString str("");
            for (int i = 1; i <= fieldCount ; ++i) {
                if (i == fieldCount)
                    str = str + query.value(i-1).toString();
                else
                    str = str + query.value(i-1).toString() + ",";
            }
            sl << str;
        }
    }
    else
        m_Result = "Could not retrieve data";
    m_ErrorMsg = db.lastError().text();
    emit dbState(m_DBState, m_Result, m_ErrorMsg);
    return sl;
}

To copy to clipboard, switch view to plain text mode 

This returns the result of the query in a QStringList.

Now from my calling function I have done the following:

dbConnector1 = new DBMySQL("server1","database1","user1","pw1","default");
dbConnector2 = new DBMySQL("server1","database2","user2","pw2","other");

dbConnector1 = new DBMySQL("server1","database1","user1","pw1","default");
dbConnector2 = new DBMySQL("server1","database2","user2","pw2","other");

To copy to clipboard, switch view to plain text mode 

where dbConnector 1 & 2 are of type DBMySQL

I have a MainWindow with a combobox on, and I want to populate it:

void MainWindow::extractBatches(QString modelName)
{
    QString queryString = "SELECT DISTINCT batch_no FROM doicii_5_6k ORDER BY batch_no";
    QStringList batchList = dbConnector2->getData(queryString); //from "other" database
    ui->comboBoxBatch->insertItems(0,batchList);
    ui->comboBoxBatch->setCurrentIndex(-1);
}

void MainWindow::extractBatches(QString modelName)
{
    QString queryString = "SELECT DISTINCT batch_no FROM doicii_5_6k ORDER BY batch_no";
    QStringList batchList = dbConnector2->getData(queryString); //from "other" database
    ui->comboBoxBatch->insertItems(0,batchList);
    ui->comboBoxBatch->setCurrentIndex(-1);
}

To copy to clipboard, switch view to plain text mode 

However, I get the following error:

QSqlQuery::exec: database not open
QSqlDatabasePrivate::removeDatabase: connection 'default' is still in use, all queries will cease to work.
QSqlDatabasePrivate::removeDatabase: connection 'other' is still in use, all queries will cease to work.

Can someone explain what I am doing wrong?

My reason for wanting to access different databases is because there are already existing databases in use, I want info from more than one in my program, sort of like making a report based on all data from all databases. I will then take this data and put it into the "default" database.

Regards,
Shaun

[Lykurg]

Use

QSqlQuery query(QSqlDatabase::database(m_ConnectionName));

QSqlQuery query(QSqlDatabase::database(m_ConnectionName));

To copy to clipboard, switch view to plain text mode 

because you have to tell which database QSqlQuery should use, otherwise it uses the default, which you haven't defined, hence you get the error.

[ShamusVW]

Perfect.
Thank you!
Can I ask why it works like this though. Surely I have 2 different instances of DBMySQL class, they shouldn't even know about each other?

[Lykurg]

The problem is, that QSqlQuery needs a database to perform on. If you don't specify one, Qt uses it default database which is defines as

QSqlDatabase::database("defaultConnection")

QSqlDatabase::database("defaultConnection")

To copy to clipboard, switch view to plain text mode 

So since you use addDatabase() with custom connection names, defaultConnection is not set. That is the problem.

And QSqlDatabase is a singleton pattern therefore they know each other! (Not directly but...)

[ShamusVW]

Ok, thanks.
Didn't realise it was Singleton.