I cannot modify orcale table data using the qsqltablemodel class.
-
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 11gMy 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?
-
@greenhand
You seem to show the full code, andcreate 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 explicitINSERT
/DELETE
/UPDATE
statement in aQSqlQuery
till that works/does not work.While we are here, for everybody's sanity/safety, please note that
Oracle
is not spelledOrcale
.Although not relevant to your issue, what is the point of yourmodel->database().transaction();
statement? -
@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 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 explicitINSERT
/DELETE
/UPDATE
statement in aQSqlQuery
till that works/does not work. -
@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 forQSqlQuery
, which works, against whateverQTableModel
issues for itsUPDATE
(however you discover that) to see if you can spot a difference.Next you might try
QSqlQueryModel
instead ofQSqlTableModel
, building up fromQSqlQuery
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 theelse
, so it definitely reports what came frommodel->submitAll()
failing and cannot be potentially altered by themodel->database().rollback();
.