I cannot modify orcale table data using the qsqltablemodel class.

greenhand

Hello, everyone. I'm using qsqltablemodel class to add, delete, query and modify the orcale database. I can query the whole table through the qsqltablemodel:: select() function and display it through the qtableview class, but when I modify the data, the database prompts me: "ora-00904:" mestable ". ID: invalid identifier. Unable to execute statement". The same code can modify the SQLite database successfully.

My environment:
windows 10
Qt 5.11.1
Orcale Database 11g

My project file is content(There are only four files):

HEADERS       = \
tableeditor. h \
connection2. h
SOURCES       = main. cpp \
tableeditor. cpp
QT           += sql widgets

main.cpp:

#include <QApplication>

#include "connection2.h"
#include "tableeditor.h"

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    if (!createConnection())
        return 1;

    TableEditor editor("student");
    editor.show();
    return app.exec();
}

connection2.h(Create a SQLite connection and an orcale connection):

#ifndef CONNECTION_H
#define CONNECTION_H
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>

static bool createConnection()
{
    //SQLite
    QSqlDatabase db1 = QSqlDatabase::addDatabase("QSQLITE", "sqliteconnection1");
    db1.setDatabaseName("sqlite.db");
    if (!db1.open()) {
        QMessageBox::critical(0, "Cannot open database1",
                              "Unable to establish a database connection.", QMessageBox::Cancel);
        return false;
    }

    QSqlQuery query1(db1);
    query1.exec("create table student (id int primary key, "
                "name varchar(20))");

    query1.exec("insert into student values(0, 'LiMing')");
    query1.exec("insert into student values(1, 'LiuTao')");
    query1.exec("insert into student values(2, 'WangHong')");

    //Orcale
    QSqlDatabase db2 = QSqlDatabase::addDatabase("QOCI", "orcaleconnection");
    db2.setHostName("127.0.0.1");
    db2.setPort(1521);
    db2.setDatabaseName("orcl");
    db2.setUserName("root");
    db2.setPassword("root");
    if (!db2.open()) {
        QMessageBox::critical(0, "Cannot open database2",
                              "Unable to establish a database connection.", QMessageBox::Cancel);
        return false;
    }

    return true;
}

#endif // CONNECTION_H

tableeditor.h(Class used to display database tables):

#ifndef TABLEEDITOR_H
#define TABLEEDITOR_H

#include <QDialog>

class QDialogButtonBox;
class QPushButton;
class QSqlTableModel;

class TableEditor : public QWidget
{
    Q_OBJECT

public:
    explicit TableEditor(const QString &tableName, QWidget *parent = nullptr);

private slots:
    void submit();

private:
    QSqlTableModel *model;
};

#endif

tableeditor.cpp:

#include <QtWidgets>
#include <QtSql>

#include "tableeditor.h"

TableEditor::TableEditor(const QString &tableName, QWidget *parent)
    : QWidget(parent)
{
    QSqlDatabase db1 = QSqlDatabase::database("sqliteconnection1");
    model = new QSqlTableModel(this,db1);
    model->setTable(tableName);
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();

    QTableView *view = new QTableView;
    view->setModel(model);
    view->resizeColumnsToContents();

    connect(model, &QSqlTableModel::dataChanged, this, &TableEditor::submit);

    QHBoxLayout *mainLayout = new QHBoxLayout;
    mainLayout->addWidget(view);
    setLayout(mainLayout);
}

void TableEditor::submit()
{
    model->database().transaction();
    if (model->submitAll()) {
        model->database().commit();
    } else {
        model->database().rollback();
        QMessageBox::warning(this, tr("Cached Table"),
                             tr("The database reported an error: %1")
                             .arg(model->lastError().text()));
    }
}

Now it is no problem to use SQLite connection query to display and modify student data table. Set tableeditor Line 9 in CPP is modified as "QSqlDatabase db1 = QSqlDatabase:: database (" orcaleconnection ");", At this time, the data table data can be displayed, but errors will be reported when modifying.Does anyone know what this is about?

JonB

@greenhand
You seem to show the full code, and create table table definition, for SQLite, which you say works, but not for Oracle, which you say does not? Why? Can you please show at least the table definition for Oracle?

Temporarily, to diagnose what is going on, try not using the inbuilt submit QSqlTableModel statements. Use an explicit INSERT/DELETE/UPDATE statement in a QSqlQuery till that works/does not work.

While we are here, for everybody's sanity/safety, please note that Oracle is not spelled Orcale.

~~Although not relevant to your issue, what is the point of your model->database().transaction(); statement?~~

SGaist

Hi,

In addition to what @JonB wrote, are you sure that the user you use to connect to your Oracle database has enough rights to actually apply the commands you are trying to do ?

greenhand

@JonB Thank you for your reply. My Oracle data table is defined as follows:

create table student
(
id int primary key,
name varchar(20)
);

insert into student values(0, 'LiMing');
insert into student values(1, 'LiuTao');
insert into student values(2, 'WangHong');

My code can successfully connect to the database and display the contents of the student table of Oracle database。

But when I modified it, an error occurred.

JonB

@JonB said in I cannot modify orcale table data using the qsqltablemodel class.:

Temporarily, to diagnose what is going on, try not using the inbuilt submit QSqlTableModel statements. Use an explicit INSERT/DELETE/UPDATE statement in a QSqlQuery till that works/does not work.

greenhand

@JonB I try to use qsqlquery to modify the data in the table, and then use void tableeditor:: submit() function to submit. The data in the student table is successfully modified, and qtableview also displays the modified value.

JonB

@greenhand said in I cannot modify orcale table data using the qsqltablemodel class.:

and then use void tableeditor:: submit() function to submit

I don't know why you do that, or whether it does anything given that you are using your own QSqlQuery for the data modification.

The idea was to to compare whatever UPDATE statement you compose manually for QSqlQuery, which works, against whatever QTableModel issues for its UPDATE (however you discover that) to see if you can spot a difference.

Next you might try QSqlQueryModel instead of QSqlTableModel, building up from QSqlQuery which you say does work.

Just in case it makes any difference:

    if (model->submitAll()) {
        model->database().commit();
    } else {
        model->database().rollback();
        QMessageBox::warning(this, tr("Cached Table"),
                             tr("The database reported an error: %1")
                             .arg(model->lastError().text()));
    }

Output, or save up, model->lastError().text() as the first thing in the else, so it definitely reports what came from model->submitAll() failing and cannot be potentially altered by the model->database().rollback();.