SQlite Db open more than one

gfxx

I try to open more than one Sqlite db in same Mainwindows .... so I can operate with data became from more than one DB. I'm not expert and until now i use Sqlite for store data like login or other simple action. Usually in My Mainwidows.cpp:

QSqlDatabase Db1;
QSqlDatabase Db2;

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

/* some code like set tobblebutton as false and similar code .... */

    Db1 = QSqlDatabase::addDatabase("QSQLITE");
    Db2 = QSqlDatabase::addDatabase("QSQLITE");
    Db1.setDatabaseName("/home/mypath1.sqlite3");
    Db2.setDatabaseName("/home/mypath2.sqlite3");

    if (!Db1.open() && !Db2.open())
    {  qDebug << "all work ok";}
   else{ qDebug << " one or other DB can not open";}

/* other things.......*/

these my usage (maybe wrong and this is not know by my) ....... but when start app QTcreator write these messages ....

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.

These is not understand well .... because all all work ok .... only if try to change Db2 path for use same Db with newer data .... I can see Db2 is not use at all from app .... so think connection is removed as debugger messages .... but not receive other warning or error ..... plus if return on old Db2 path al work again ok .... so I'm confused ... can be everithing ... eventually can be some stupid error in new db but non one .LastError call in all query send some warning ... so not understand.

For sure My usage of 2 Db is not correct .... but why?

regards

ChrisW67

You need to specify a unique connection identifier in one or both connections. That is, use the second argument to QSqlDatabase::addDatabase().

Use the relevant connection name when you call QSqlDatabase::database()

gfxx

I see thanks .... now qt not send that messages .... but other one ..... think because too old style SQlQuery ..... from long time (QT4) i call query in these manner .....

/* in MainWindows*/
Db1 = QSqlDatabase::addDatabase("QSQLITE", "dbOne");
Db2 = QSqlDatabase::addDatabase("QSQLITE", "dbTwo");
Db1.setDatabaseName("/home/mypath1.sqlite3");
Db2.setDatabaseName("/home/mypath2.sqlite3");
/* end of MainWindows*/

void MainWindow::loadDataSetPerUser(int userIdx){

    QSqlQuery qryAdmin(Database);
    int userPCS = 0;

    idxusermax = 0;
    dataCopyName.clear();

after add "namestring" i receive that error....

QSqlQuery::exec: database not open

multiple time because multiple query ....

thanks

regards

jsulm

@gfxx said in SQlite Db open more than one:

QSqlQuery qryAdmin(Database);

What is Database?
Most probably you are still using default database (the one without explicitly defined name) for your query which is not opened. You need to specify which db you want to use (using its name https://doc.qt.io/qt-6/qsqldatabase.html#database).

gfxx

@jsulm .... i read these righ now and correct it in some manner ..... https://doc.qt.io/qt-6/sql-connecting.html ...

QSqlDatabase Db1;
QSqlDatabase Db2;

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

/* some code like set tobblebutton as false and similar code .... */

    Db1 = QSqlDatabase::addDatabase("QSQLITE", "dbOne");
    Db2 = QSqlDatabase::addDatabase("QSQLITE", "dbTwo");
    Db1.setDatabaseName("/home/mypath1.sqlite3");
    Db2.setDatabaseName("/home/mypath2.sqlite3");

    if (!Db1.open() && !Db2.open())
    {  
      qDebug << " one or other DB can not open"; 
        QMessageBox msgBoxDB;
        msgBoxDB.setStyleSheet("background-color:gray");
        msgBoxDB.setIcon(QMessageBox::Critical);

}
   else{ qDebug << "all work ok";
       Db1 = QSqlDatabase::database("dbOne");
       Db2 = QSqlDatabase::database("dbTwo");
}

/* other things.......*/

so no warning .... but when call some void containing a query about dbTwo and after call other query about dbOne .... receive messages from my app that one db is not opened { qDebug << " one or other DB can not open"; QMessageBox msgBoxDB; .....} ..... after clik ok ... all run .... seems qt close one db when other is call .... is right these?

jsulm

@gfxx said in SQlite Db open more than one:

seems qt close one db when other is call .... is right these?

No. Qt will not close anything as long as you do not ask it to do so.
You logic is wrong, it should be:

if (!Db1.open() || !Db2.open())

Please add error handling to your code to find out why open() fails. https://doc.qt.io/qt-6/qsqldatabase.html#lastError

gfxx

@jsulm said in SQlite Db open more than one:

You logic is wrong, it should be:

You are in right .... sorry .... plus enter LastError ....

    Db1 = QSqlDatabase::addDatabase("QSQLITE", "dbOne");
    Db2 = QSqlDatabase::addDatabase("QSQLITE", "dbTwo");
    Db1.setDatabaseName("/home/mypath1.sqlite3");
    Db2.setDatabaseName("/home/mypath2.sqlite3");

    if (!Db1.open() || !Db2.open())
    {  
      qDebug << " one or other DB can not open"; 
        QMessageBox msgBoxDB;
        msgBoxDB.setStyleSheet("background-color:gray");
        msgBoxDB.setIcon(QMessageBox::Critical);
/* .... other msgBoxCode ...*/
      qDebug() << "error db1 .... " << Db1.lastError() << "... error db2 ..." << Db2.lastError();
}
else{
   qDebug() << "DATABASE IS OPEN BUT CONTROL IT  error db1 .... " << Db1.lastError() << "... DATABASE IS OPEN BUT CONTROL IT  error db2 ..." << Db2.lastError();
}

so obtain:

DATABASE IS OPEN BUT CONTROL IT  error db1 ....  QSqlError("", "", "") ... DATABASE IS OPEN BUT CONTROL IT  error db2 ... QSqlError("", "", "")
QSqlQuery::exec: database not open
QSqlQuery::exec: database not open
QSqlQuery::exec: database not open
QSqlQuery::exec: database not open

because seems or db1 or db2 not open ..... (or better go to close state for some reasons).

regards

jsulm

@gfxx I'm confused by what you posted: "DATABASE IS OPEN BUT CONTROL IT" this is printed in the else part, but it is not clear to which "if" this "else" belongs! Can you please post whole relevant code properly formated, so it can be more easilly understood?

gfxx

@jsulm said in SQlite Db open more than one:

this is printed in the else part, but it is not clear to which "if" this "else" belongs! Can you please post whole relevant code properly formated, so it can be more easilly understood?

whit pleasure ....

QSqlDatabase Db1;
QSqlDatabase Db2;

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

/* some code like set tobblebutton as false and similar code .... */



    Db1 = QSqlDatabase::addDatabase("QSQLITE", "dbOne");
    Db2 = QSqlDatabase::addDatabase("QSQLITE", "dbTwo");
    Db1.setDatabaseName("/home/mypath/DB1.sqlite3");
    Db2.setDatabaseName("/home/mypath/DB2.sqlite3");

    if (!Db1.open() || !Db2.open()) // || !Db3.open())
    {
        QMessageBox msgBoxDB;
        msgBoxDB.setStyleSheet("background-color:gray");
        msgBoxDB.setIcon(QMessageBox::Critical);
        msgBoxDB.setWindowTitle("ERRORE D.B.");
        msgBoxDB.setText("Not Possible to Connect to DB");
        msgBoxDB.setInformativeText("Please restart App");
        msgBoxDB.setStandardButtons(QMessageBox::Ok);
        msgBoxDB.setDefaultButton(QMessageBox::Ok);
        int retDB = msgBoxDB.exec();
        switch (retDB) {
          case QMessageBox::Ok:
              close();
              break;
          default:
              // should never be reached
              break;
        }

        qDebug() << "error db1 .... " << Db1.lastError() << "... error db2 ..." << Db1Lavoro.lastError();

    }
    else{
        qDebug() << "Db1 IS OPEN BUT CONTROL IT  error db1 .... " << Db1.lastError() << "... Db1 IS OPEN BUT CONTROL IT  error db2 ..." << Db2.lastError();
        dbStdND.clear();

        Db1 = QSqlDatabase::database("dbOne");
        Db2 = QSqlDatabase::database("dbTwo");

        StdNameData test;
        QSqlQuery userD(Db1);
        userD.exec("SELECT customerAlias, customerPswd, AdminOnOff, barCode, midiumUser, secret  FROM insertCustomers");
        while (userD.next()){
            test.name = userD.value(0).toString();
            test.pswd = userD.value(1).toString();
            test.adminFlag = userD.value(2).toBool();
            test.barcodeFlag = userD.value(3).toBool();
            test.midiumUser = userD.value(4).toBool();
            test.gcFlag = userD.value(5).toBool();
            dbStdND.append(StdNameData(test));
        }

        model = new QSqlTableModel(this, Db1); 
        model->setTable("insertCustomers");

        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setFilter("customerID > 1");
        model->setSort(0, Qt::AscendingOrder);

        model->setHeaderData(0, Qt::Horizontal, QObject::tr("index"));
        model->setHeaderData(1, Qt::Horizontal, QObject::tr("User Name"));
        model->setHeaderData(2, Qt::Horizontal, QObject::tr("Passworld"));
        model->setHeaderData(3, Qt::Horizontal, QObject::tr("Admin 0/1"));
        model->setHeaderData(4, Qt::Horizontal, QObject::tr("Badge 0/1"));
        model->setHeaderData(5, Qt::Horizontal, QObject::tr("Recipes 0/1"));
        model->setHeaderData(6, Qt::Horizontal, QObject::tr("Not used"));
        model->select();
        ui->tableView_User->setModel(model);
        ui->tableView_User->setColumnHidden(6, true);
        ui->tableView_User->setColumnWidth(0, 60);
        ui->tableView_User->setColumnWidth(1, 125);
        ui->tableView_User->setColumnWidth(2, 125);
        ui->tableView_User->setColumnWidth(3, 90);
        ui->tableView_User->setColumnWidth(4, 90);
        ui->tableView_User->setColumnWidth(5, 90);
        ui->tableView_User->show();

        changeUserGui();

    }


}

MainWindow::~MainWindow()
{
    timer.stop();
    delete ui;
}

ChrisW67

QSqlDatabase Db1;
QSqlDatabase Db2;

Why are these not local stack variables as you see in every example? You should not be holding these, but calling QSqlDatabase::database() when you need to. Does the behaviour change if you fix this?

   Db1 = QSqlDatabase::addDatabase("QSQLITE", "dbOne");
    Db2 = QSqlDatabase::addDatabase("QSQLITE", "dbTwo");
    Db2.setDatabaseName("/home/mypath/DB1.sqlite3");
    Db1.setDatabaseName("/home/mypath/DB2.sqlite3");

So Db2 is connected to a file "DB1.sqlite3", and Db1 to "DB2.sqlite3". Deliberately mixed?

gfxx

This post is deleted!

gfxx

For some reasons. I'm unable to add code to previous post reply ... so make new one here again.

@ChrisW67 said in SQlite Db open more than one:

Db2.setDatabaseName("/home/mypath/DB1.sqlite3");
Db1.setDatabaseName("/home/mypath/DB2.sqlite3");

Is only type error .... should be:

    Db1.setDatabaseName("/home/mypath/DB1.sqlite3");
    Db2.setDatabaseName("/home/mypath/DB2.sqlite3");

i correct on previous post

Why are these not local stack variables as you see in every example?

these because start to use SQlite in 2015 with qt, never use for bigger app or intensive use, but now need it .... at that time with one only db avery app all works without problem ..... the code I use with db seems is too old and call to db seems change a little from 2015 ....

plus if try, every void MainWindows::queryxxx() (I have 30 query to call, with Db1 or Db2 with user choice, so can be
query010() ....) and write on it:

void MainWindow::loadDataSet(){

    Db1 = QSqlDatabase::database("dbOne");
    QSqlQuery qryUser(Db1);
    qryUser.exec("SELECT *  FROM data365");
    while (qryUser.next()){
        dataA[0] = int(qryUser.value(0).toInt() / 1000);
        dataB[0] = int(qryUser.value(1).toInt() / 1000);
        dataC[0] = int(qryUser.value(2).toInt() / 1000);
        dataD[0] = int(qryUser.value(3).toInt() / 1000);
        dataE[0] = int(qryUser.value(4).toInt() / 100);
    }
    qryUser.finish();
    qryUser.clear();
}

Christian Ehrlicher

@gfxx said in SQlite Db open more than one:

QSqlDatabase Db1;
QSqlDatabase Db2;

Do never declare a database connection global or as member - please follow the documentation and only get the db connection via QSqlDatabase::database().

Also please simplify your testcase - a simply function where you open the two databases + a simple select. Everything else just confuses as we don't see what you really do.

int(qryUser.value(0).toInt() / 1000);

This for sure does not calculate what you want.

gfxx

@Christian-Ehrlicher said in SQlite Db open more than one:

QSqlDatabase Db1;
QSqlDatabase Db2;

Do never declare a database connection global or as member -

these for sure is right but nothing changes .....

please follow the documentation and only get the db connection via QSqlDatabase::database().

these changes a lot .... all error disappear ..... but now some modelview not work because not read correctly table in database ..... so a question:

my database is different in filename (db1 and db2), but internally name of table and name of column is equal .... these can "confuse" QSqlite driver? ... these can be the causes of not read of some table? .... so write query in these manner:

QSqlQuery userD(QSqlDatabase::database("db1"));
        userD.exec("SELECT customerAlias, customerPswd, AdminOnOff, barCode, midiumUser, secret  FROM insertCustomers");
/*  other code  few row forgrab data */
QSqlQuery userE(QSqlDatabase::database("db2"));
        userE.exec("SELECT customerAlias, customerPswd, AdminOnOff, barCode, midiumUser, secret  FROM insertCustomers");

can be a problem?

Any how these afternoon try to modify my app with only 2 queryes one for db1 and one for db2 .... so can test in more esyest way the problem. I think original post was solved ... but prefer make a test first.

regards & Thanks a lot

JonB

@gfxx said in SQlite Db open more than one:

. these can "confuse" QSqlite driver?

No. Your code looks OK to issue queries to separate databases.

piervalli

@JonB You should open a single database for instance db1, than attach the second database "ATTACH DATABASE dd2.sqlite3 AS dd2;"

JonB

@piervalli
I answered a question about Qt and multiple database handling. I do not know anything about ATTACH DATABASE, never heard of it in SQL. I have never used SQLite.

gfxx

@JonB @ChrisW67 @jsulm @Christian-Ehrlicher finally I see somethings .... no good at all ....

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



    //** db ****************************************************************************



    QSqlDatabase DB1 = QSqlDatabase::addDatabase("QSQLITE", "db1");
    QSqlDatabase DB2 = QSqlDatabase::addDatabase("QSQLITE", "db2");
    DB1.setDatabaseName("/home/.mypath/db1.sqlite3");  /* exactly with point in front */
    DB2.setDatabaseName("/home/.mypath/db2.sqlite3");

    if (!DB1.open() || !DB2.open())
    {
        QMessageBox msgBoxDB;
        msgBoxDB.setStyleSheet("background-color:gray");
        msgBoxDB.setIcon(QMessageBox::Critical);
        msgBoxDB.setWindowTitle("ERRORE D.B.");
        msgBoxDB.setText("Impossible to connect to data base");
        msgBoxDB.setInformativeText("Please restart App");
        msgBoxDB.setStandardButtons(QMessageBox::Ok);
        msgBoxDB.setDefaultButton(QMessageBox::Ok);
        int retDB = msgBoxDB.exec();
        switch (retDB) {
          case QMessageBox::Ok:
              close();
              break;
          default:
              // should never be reached
              break;
        }

        qDebug() << "error db1 .... " << Database.lastError() << "... error db2 ..." << DatabaseLavoro.lastError();

    }
    else{
        qDebug() << "DATABASE IS OPEN BUT CONTROL IT  error db1 .... " << Database.lastError() << "... DATABASE IS OPEN BUT CONTROL IT  error db2 ..." << DatabaseLavoro.lastError();
        dbStdND.clear();

        StdNameData test;
        QSqlQuery userD(QSqlDatabase::database("db1"));
        userD.exec("SELECT customerAlias, customerPswd, AdminOnOff, barCode, midiumUser, secret  FROM insertCustomers");
        while (userD.next()){
            test.name = userD.value(0).toString();
            test.pswd = userD.value(1).toString();
            test.adminFlag = userD.value(2).toBool();
            test.barcodeFlag = userD.value(3).toBool();
            test.midiumUser = userD.value(4).toBool();
            test.gcFlag = userD.value(5).toBool();
            dbStdND.append(StdNameData(test));
        }
        userD.finish();
        userD.clear();

        model = new QSqlTableModel(this, QSqlDatabase::database("db1"));
        model->setTable("insertCustomers");

        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setFilter("customerID > 1");
        model->setSort(0, Qt::AscendingOrder);

        model->setHeaderData(0, Qt::Horizontal, QObject::tr("index"));
        model->setHeaderData(1, Qt::Horizontal, QObject::tr("User Name"));
        model->setHeaderData(2, Qt::Horizontal, QObject::tr("Passworld"));
        model->setHeaderData(3, Qt::Horizontal, QObject::tr("Admin 0/1"));
        model->setHeaderData(4, Qt::Horizontal, QObject::tr("Badge 0/1"));
        model->setHeaderData(5, Qt::Horizontal, QObject::tr("Recipes 0/1"));
        model->setHeaderData(6, Qt::Horizontal, QObject::tr("Not used"));
        model->select();
        ui->tableView_U->setModel(model);
        ui->tableView_U->setColumnHidden(6, true);
        ui->tableView_U->setColumnWidth(0, 60);
        ui->tableView_U->setColumnWidth(1, 125);
        ui->tableView_U->setColumnWidth(2, 125);
        ui->tableView_U->setColumnWidth(3, 90);
        ui->tableView_U->setColumnWidth(4, 90);
        ui->tableView_U->setColumnWidth(5, 90);
        ui->tableView_U->show();
        
        
        sqlViewF();

    }









void MainWindow::sqlViewF(){


    dbStdND.clear();
    QSqlQuery userF(QSqlDatabase::database("db2"));
    userF.exec("SELECT ID, ard, brd, crd FROM Table1");
    while (userF.next()){    }
    qDebug() << ".... query test db2...." << userF.lastError();
    userF.finish();
    userF.clear();
}

error messages ...

.... query test db2.... QSqlError("1", "Unable to execute statement", "no such table: Table1")

courious things ..... before to start app file /home/.mypath/db2.sqlite3 in /home/.mypath exist and have 1,4Mb of data ..... but after start data a second copy of /home/.mypath/db2.sqlite3 appear in folder but without data or 0Kb .... so actually I have 2 /home/.mypath/db2.sqlite3 file one with 1,4Mb of data and one with 0Kb of data .... everytime try to cancel /home/.mypath/db2.sqlite3 with 0Kb .... but if start app it appear again .... obviusly is an empty file and not contains Table1 .... but other file /home/.mypath/db2.sqlite3 with 1,4Mb of data it contains Table1 ...... why these?!!

Schermata del 2022-07-12 14-36-49.png

jsulm · Schermata del 2022-07-12 14-36-49.png

@gfxx said in SQlite Db open more than one:

so actually I have 2 /home/.mypath/db2.sqlite3

How should that be possible? You can't have two files with exact same name in exact same folder. Can you please show us the exact file names and locations? (like ls -lh in the folder where these files are)?
Does you database really contain a table with that name?

JonB · Schermata del 2022-07-12 14-36-49.png

@gfxx
As @jsulm says. With SQLite if you tell it open a non-existent filename it creates ir with 0 size, so that will be where you are getting your 0-length file which keeps reappearing after you delete it.