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");
...

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;
}

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");

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);
}

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));be cause 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")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. ;)