Open MYSQL Database with connectionName

SGaist

Hi,

You don't need to re-create the database object each time, just create once e.g. in the constructor and open/close the connection as needed.

helenebro

Thank you for your help.
In reality, I open two differents database from two classes. The first class create the second class.
First class, MyApp:

MyApp::MyApp() {
    MyClass *myClass = new MyClass();
    getDataApp();
}
void MyApp::getDataApp() {
    if(openDataBaseApp()) {
       ...
       dbApp.close();
   }
}
bool MyApp::openDataBaseApp() {
  if(!dbApp.isValid()) {
    dbApp = QSqlDatabase::addDatabase("QMYSQL3");
    dbApp.setHostName("localhost");
    dbApp.setUserName("myUserName");
    dbApp.setPassword("myPassword");
    dbApp.setDatabaseName("databaseMyApp");
  }
  if(!dbApp.open())
  {
    qDebug() << dbApp.lastError().text() << "\n";
    return false;
  }
  return true;
}

Second Class , MyClass:

MyClass::MyClass() {
    getDataMyClass();
}
void MyClass::getDataMyClass() {
    if(openDataBaseMyClass()) {
       ...
      dbMyClass.close()
   }
}
bool MyClass::openDataBaseMyClass() {
  if(!dbMyClass.isValid()) {
    dbMyClass = QSqlDatabase::addDatabase("QMYSQL3");
    dbMyClass.setHostName("localhost");
    dbMyClass.setUserName("myUserName");
    dbMyClass.setPassword("myPassword");
    dbMyClass.setDatabaseName("databaseMyClass");
  }
  if(!dbMyClass.open())
  {
    qDebug() << MyClass.lastError().text() << "\n";
    return false;
  }
  return true;
}

If I put dbMyClass and dbMyApp on respective constructor, I have a problem when I re-call getDataBase, it seems driver not loaded.

MyApp::MyApp() {
    MyClass *myClass = new MyClass();
    dbApp = QSqlDatabase::addDatabase("QMYSQL3");
    dbApp.setHostName("localhost");
    dbApp.setUserName("myUserName");
    dbApp.setPassword("myPassword");
    dbApp.setDatabaseName("databaseMyApp");
    getDataApp();
}
MyClass::MyClass() {
    dbMyClass = QSqlDatabase::addDatabase("QMYSQL3");
    dbMyClass.setHostName("localhost");
    dbMyClass.setUserName("myUserName");
    dbMyClass.setPassword("myPassword");
    dbMyClass.setDatabaseName("databaseMyClass");
    getDataMyClass();
}

This is the log :

dbDataCube is create QSqlDatabase(driver=""QMYSQL3"", database=""databaseMyClass"", host=""localhost"", port=-1, user=""myUserName"", open=false) 
openDataBase dbMyClass
dbMyClass is open 
dbMyClass close
QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
dbApp is create QSqlDatabase(driver=""QMYSQL3"", database=""databaseMyApp"", host=""localhost"", port=-1, user=""myUserName"", open=false) 
dbApp is open 
openDataBase dbMyClass
"Driver not loaded Driver not loaded" false 
dbMyClass not open

SGaist

Ok, that's because you're not naming your connections when setting up your QSqlDatabase objects. You should give each a different name.

By the way, which OS are you running ?

helenebro

have to admit I'm a little bit confused.

When I has a connection name, my database doesn't open.
If I setup my QSqlDatabase on constructor (whitout connection name), it works the first timer (with the warning) but if I call again my function to get my database, I have the errror : Driver not loaded

If I understand, I should first create two QSqlDatabase with two different name, then get the QSqlDatabase but in this case I can't open database (access denied).

#include "myapp.h"

MyApp::MyApp(QQmlContext *ctx, QObject *parent) : QObject( parent)
{
        QSqlDatabase::addDatabase("QMYSQL3", "myapp");
        QSqlDatabase::addDatabase("QMYSQL3", "myclass");

        MyClass *myClass = new MyClass();
        ctx->setContextProperty("myClass", myClass);

        dbMyApp = QSqlDatabase::database("myapp");
        dbMyApp.setHostName("localhost");
        dbMyApp.setUserName("userName");
        dbMyApp.setPassword("password");
        dbMyApp.setDatabaseName("dbMyApp");
        getDataMyApp();
}

void MyApp::getDataMyApp()
{
    if(openDataBaseMyApp()) {
         QSqlQuery query ;
         int nb = 0;
         if(query.exec("SELECT * from MaTable"))
         {
             while(query.next()) {
             nb++;
         }
     }
     dbMyApp.close();
     qDebug()<<"there are "<<nb<<"input";
    }
}

bool MyApp::openDataBaseMyApp()
{
    if(!dbMyApp.open())
    {
        qDebug() << dbMyApp.lastError().text() << "\n";
        return false;
    }
    return true;
}


#include "myclass.h"

MyClass::MyClass(QObject *parent) : QObject(parent)
{
    dbMyClass = QSqlDatabase::database("myclass");
    dbMyClass.setHostName("localhost");
    dbMyClass.setUserName("userName");
    dbMyClass.setPassword("password");
    dbMyClass.setDatabaseName("dbMyClass");

    getDataMyClass();
}

void MyClass::getDataMyClass()
{
    if(openDataBaseMyClass()) {
        QSqlQuery query ;
        int nb = 0;
        if(query.exec("SELECT * from MaTable"))
        {
            while(query.next()) {
                nb++;
            }
        }
        dbMyClass.close();
        qDebug()<<"there are "<<nb<<"input";
    }
}

bool MyClass::openDataBaseMyClass()
{
        if(!dbMyClass.open())
        {
            qDebug() << dbMyClass.lastError().text() << "\n";
            return false;
        }
    return true;
}

But I have ther error ;

QSqlDatabasePrivate::database: unable to open database: "Access denied for user 'otherName'@'localhost' (using password: NO) QMYSQL: Impossible d'établir une connexion" 
QSqlQuery::exec: database not open
il y a  0 product dans la base 
QSqlDatabasePrivate::database: unable to open database: "Access denied for user 'otherName'@'localhost' (using password: NO) QMYSQL: Impossible d'établir une connexion" 
QSqlQuery::exec: database not open
il y a  0 titre dans la base

SGaist

That's not a Qt problem there. You otherName user doesn't have access to the database you want to get.

helenebro

Yes but "OtherName" isn't the name that I have set with dbMyApp.setUserName("userName");

SGaist

How did you setup the databases ?

Out of curiosity, why two different databases for your application ?

helenebro

What do you mean by saying "setup" ?

MyApp

MyApp::MyApp(QQmlContext *ctx, QObject *parent) : QObject( parent)
{
    QSqlDatabase::addDatabase("QMYSQL3", "myapp");
    QSqlDatabase::addDatabase("QMYSQL3", "myclass");
    MyClass *myClass = new MyClass();
    ctx->setContextProperty("myClass", myClass);
    dbMyApp = QSqlDatabase::database("myapp");
    dbMyApp.setHostName("localhost");
    dbMyApp.setUserName("root");
    dbMyApp.setPassword("mySQL:r00t");
    dbMyApp.setDatabaseName("cube_application");
    getDataMyApp();
}

MyClass

MyClass::MyClass(QObject *parent) : QObject(parent)
{
    dbMyClass = QSqlDatabase::database("myclass");
    dbMyClass.setHostName("localhost");
    dbMyClass.setUserName("root");
    dbMyClass.setPassword("mySQL:r00t");
    dbMyClass.setDatabaseName("catalogue_4mod");
    getDataMyClass();
}

I have two databases because data have no relationship. My application contains several "mini application".

SGaist

I meant server side setup.

But again why multiple databases ? You can have your unrelated tables in only one database. They don't need to have any relation between them.

helenebro

My database is only on localhost and create by command line.
Indeed the two database can be merged, I find it more clear like this (It is maybe a mistake)

SGaist

Do you mean you find it more clear to connect your application to two different databases ?

helenebro

Yes, because my application is the merge of different application which has a data on database

SGaist

That I understood, but still, deploying several databases for one application sounds like an overkill.

helenebro

Ok, I will merge my two databases

lqsa

In my app, the error

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

solves when pass the database on QSqlQuery constructor.

In your case could be something like QSqlQuery(dbMyClass) for querys on MyClass and QSqlQuery(dbApp) for querys on MyApp.