SQlite Db open more than one

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

@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

@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.

gfxx

@jsulm have you see posted image? ..... 0Kb and 1,4Mb .... same name (can not show it sorry). I think is not possible but it appear ....

JonB

@gfxx
No operating system permits two identically named files in the same directory. Please go and deal with this yourself, it's neither a Qt problem nor one other people can help you with.

same name (can not show it sorry).

Looks from the shot that the names are not the same, in the area you have scrubbed out. So that's for you to address.

gfxx

@JonB Sorry but not agree .... if cancel that file .... and after QT5 app write that error think Qt is problem not SO. But these SO is Ubuntu 20.04 not a perfect example of integrity .... but I think problem was QT/Sqlite related. If try to rename it SO advise me that other file with same name exist .... so SO do the right work .... problem was: Why app write a new db file if no one command these????

jsulm

@gfxx In the picture I saw the actual file name was hidden. That 0b file could be a lock file generated by SQLite while the DB is open. That is also why it disappears when the DB is not open anymore. So, ignore that file and consentrate on the actual DB file. I repeat my question: does your database contain a table named Table1? This is the question you should answer first before you care about other files...

jsulm

@gfxx said in SQlite Db open more than one:

Why app write a new db file if no one command these????

As I wrote above: that is probably a temporary file created by SQLite when you open a DB...

JonB

@gfxx said in SQlite Db open more than one:

Sorry but not agree ....

You are incorrect, whether you agree or not. Nothing to do with Qt. since the OS does not support identically named files in the same directory you might sit and think about what you claiming. By my eye judgement, I would say the second one (non-0-length) is 1 character longer. Maybe it's a space....

If you scrub names out nobody here can help you. If you had done @jsulm's ls -lh you would know by now, or ask a colleague to look at it for you.

gfxx

@jsulm said in SQlite Db open more than one:

I repeat my question: does your database contain a table named Table1?

these is the problem Table1 exist .... because I know can be tired ... I open SQlite db browser -> modify table -> at that point I copy the table name and paste it on QT app code .... the same with db name and path using terminal .... so can not make disctraction error ....

JonB

@gfxx
This is because you are not opening the second, 1,4MB one, because it does not have the name you are using to open it by. Hence as I said SQLite will create what you do pass as an brand new, empty database file.....

gfxx

@JonB real sorry .... you are in right ..... sorry at all .... using ls on folder I discover that for some reasons db2 path have a "spaces" before .sqlite3 ..... not see at all and all my code is write whithout spaces ... maybe i insert spaces for distractions last time I've copied db name path ....

JonB

@gfxx
:) Which is what I suggested! :) One just has to be logical about these things. A GUI dialog like you show with proportional fonts for names is not great to spot this! That's why ls -lh is a good idea :) Anyway rename the "proper" database file to remove the spaces and then see how you go.

gfxx

@JonB now it work as expected .......