QSqlQueryModel and QSqlDatabase::removeDatabase empty data in tableview

[kipalex]

Good afternoon!

I can't understand how to properly use the model to close and delete a connection to the database.

If I delete the QSqlDatabase:: removeDatabase connection, then my table is empty when queried. If I do not delete, a warning appears when the request is repeated (QSqlDatabasePrivate:: addDatabase: duplicate connection name 'SearchByDate', old connection removed.).

// Поиск звонков за выбранный период
void MainWindow::GetSearchByDate()
{
    // Добавляем строку запроса к календарю (переменная)
    QString valueToSearch = ui->lineEditByDate->text();

    // Дату надо переформатировать в формат ToString("yyyy-MM-dd")
    QString Date1 = ui->dateEdit1->date().toString("yyyy-MM-dd");
    QString Date2 = ui->dateEdit2->date().toString("yyyy-MM-dd");
    {
    QSqlDatabase db = QSqlDatabase::addDatabase( "QMYSQL", "SearchByDate");

    db.setHostName( "IP" );
    db.setDatabaseName( "db_name" );
    db.setUserName( "db_user" );
    db.setPassword( "db_pass");

    if( bool ok = db.open() )
    {
        {
        // Вводные данные
        // Запрос для общей таблицы за конкретный день
        QSqlQueryModel *SearchByDate = new QSqlQueryModel();
        SearchByDate->setQuery( "SELECT calldate, src, realdst, billsec, disposition, uniqueid FROM cdr WHERE realdst LIKE '%" + valueToSearch + "%' AND calldate BETWEEN '" + Date1 + "' AND '" + Date2 + "' OR src LIKE '%" + valueToSearch + "%' AND calldate BETWEEN '" + Date1 + "' AND '" + Date2 + "' ", db);

            for (int i = 0; i < SearchByDate->rowCount(); i++)
            {
                ui->tableView1->setModel(SearchByDate);
                // Сортировка через QSortFilterProxyModel
                QSortFilterProxyModel *proxyCurrentDayFull = new QSortFilterProxyModel();
                proxyCurrentDayFull->setSourceModel(SearchByDate);
                ui->tableView1->setModel(proxyCurrentDayFull);
                SearchByDate->setHeaderData( 0, Qt::Horizontal, QObject::tr("Дата звонка") ); // задаем название первого столбца
                SearchByDate->setHeaderData( 1, Qt::Horizontal, QObject::tr("Исходящий номер") ); // задаем название второго столбца
                SearchByDate->setHeaderData( 2, Qt::Horizontal, QObject::tr("Набранный номер") ); // задаем название третьего столбца
                SearchByDate->setHeaderData( 3, Qt::Horizontal, QObject::tr("Продолжительность вызова") ); // задаем название пятого столбца
                SearchByDate->setHeaderData( 4, Qt::Horizontal, QObject::tr("Статус звонка") ); // задаем название шестого столбца
                SearchByDate->setHeaderData( 5, Qt::Horizontal, QObject::tr("Уникальный номер") ); // задаем название седьмого столбца
                ui->tableView1->show();

            }
        }
    }

    else
    {
        QMessageBox::critical(this,"Ошибка подключения к БД", db.lastError().text(), QMessageBox::Ok);
    }
    db.close();
    }
    //QSqlDatabase::removeDatabase("SearchByDate"); // Удаляем наше соединение
}

[Christian Ehrlicher]

@kipalex open the database once and not on every call. Currently you create a new connection every time and since you give them a name the default name is used. Since a database connection needs a unique name you get the warning.

[kipalex]

@Christian-Ehrlicher said in QSqlQueryModel and QSqlDatabase::removeDatabase empty data in tableview:

open the database once and not on every call. Currently you create a new connection every time and since you give them a name the default name is used. Since a database connection needs a unique name you get the warning.

Yes, everything is correct, but I set the connection name. and the connection takes place by that name. But if after completing the connection, I close and delete the data empty. If you do not delete, then there is data.

[Christian Ehrlicher]

@kipalex That's the correct behavior. A QSqlQueryModel needs an open db connection so it can receive the data from the db when it's needed.

[kipalex]

@Christian-Ehrlicher Wait, then it turns out that the correct solution will not delete the existing connection, but check if the connection is to use it.

[Christian Ehrlicher]

@kipalex That's what I said in my first post...

[kipalex]

@Christian-Ehrlicher Good. I tried to execute the request through a connection check and still get a warning.

QSqlDatabase db = QSqlDatabase::addDatabase( "QMYSQL", **"SearchByDate"**);

    db.setHostName( "db_host" );
    db.setDatabaseName( "db_name" );
    db.setUserName( "db_user" );
    db.setPassword( "db_pass");

    **if( QSqlDatabase::contains( "SearchByDate" ) )**
    {
        QSqlDatabase db = QSqlDatabase::database( **"SearchByDate"** );
        // Вводные данные
        // Запрос для общей таблицы за конкретный день
        QSqlQueryModel *SearchByDate = new QSqlQueryModel();
        SearchByDate->setQuery( "SELECT calldate, src, realdst, billsec, disposition, uniqueid FROM cdr WHERE realdst LIKE '%" + valueToSearch + "%' AND calldate BETWEEN '" + Date1 + "' AND '" + Date2 + "' OR src LIKE '%" + valueToSearch + "%' AND calldate BETWEEN '" + Date1 + "' AND '" + Date2 + "' ", db);
        SearchByDate->setHeaderData( 0, Qt::Horizontal, QObject::tr("Дата звонка") ); // задаем название первого столбца
        SearchByDate->setHeaderData( 1, Qt::Horizontal, QObject::tr("Исходящий номер") ); // задаем название второго столбца
        SearchByDate->setHeaderData( 2, Qt::Horizontal, QObject::tr("Набранный номер") ); // задаем название третьего столбца
        SearchByDate->setHeaderData( 3, Qt::Horizontal, QObject::tr("Продолжительность вызова") ); // задаем название пятого столбца
        SearchByDate->setHeaderData( 4, Qt::Horizontal, QObject::tr("Статус звонка") ); // задаем название шестого столбца
        SearchByDate->setHeaderData( 5, Qt::Horizontal, QObject::tr("Уникальный номер") ); // задаем название седьмого столбца
        // Сортировка через QSortFilterProxyModel
        QSortFilterProxyModel *proxyCurrentDayFull = new QSortFilterProxyModel();
        proxyCurrentDayFull->setSourceModel(SearchByDate);
        ui->tableView1->setModel(proxyCurrentDayFull);
        ui->tableView1->show();
        db.close();
    }
    else
    {
        QMessageBox::critical(this,"Ошибка подключения к БД", db.lastError().text(), QMessageBox::Ok);
    }

[Christian Ehrlicher]

@kipalex Did you ever read my posts? Open the db once in an init function, don't close it as long as the model is alive

[kipalex]

@Christian-Ehrlicher Thank you very much!

I put the connection itself in the initialization of the program. And from there I already take it. The warning is gone.

I ask for petitions again, I just have been dealing with QT for a long time.

[Christian Ehrlicher]

That is the way to go. Also don't use a QSqlDatabase object as member. Always reference it through the static functions and it's name.