QSqlQuery exec returns false

Yihua Liu

const QSqlDatabase db = QSqlDatabase::database(conn_name, false);
if (not db.isValid()) {
    qWarning() << "Database is invalid.";
    return;
}
if (not db.isOpen()) {
    qWarning() << "Database not open.";
    return;
}
QSqlQuery table_query(db);
if (const bool prepared = table_query.prepare(QString("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=:owner")); not prepared) {
    qWarning() << "Failed to prepare SELECT TABLE_NAME query:" << table_query.lastError();
}
table_query.bindValue(":owner", db.userName().toUpper());
table_query.exec();  // set active if return true
if (table_query.isActive()) {
    qWarning() << "Failed to execute SELECT TABLE_NAME query:" << table_query.lastError();
    return;
}

db is valid and is open, and the query is prepared, but exec() returns false and the query is inactive at last.
If I change the prepared statement to a direct exec("...") statement, the error still occurs.
The output is

Failed to execute SELECT TABLE_NAME query: QSqlError("", "", "")

It is weird that the QSqlError is empty so I cannot figure out what is wrong. I also tried to output db.lastError() but it is also an empty QSqlError.
I run the SQL query in my Oracle client and it can properly output all the selected TABLE_NAME items.
I'm using Qt 6.8.0 and Oracle 21.3 client.

Christian Ehrlicher

Then without a prepared query.

JonB

@Yihua-Liu said in QSqlQuery exec returns false:

If I change the prepared statement to a direct exec("...") statement, the error still occurs.

So I think you are saying

table_query.exec("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='owner'")

fails? Try printing table_query.lastError() immediately after the exec(), not table_query.isActive(), just in case that interferes.

Now try just "SELECT * FROM ALL_TABLES", no WHERE or OWNER. If that too fails see if you can execute any SELECT statement, not against a special table like ALL_TABLES. Just e.g. a SELECT 0 is a legal SQL statement!

At the end of this you should know whether you cannot execute any statements at all, you have trouble with ALL_TABLES or you have trouble with binding.

Yihua Liu

@JonB Thanks. If I print table_query.lastError() immediately after exec(), the QSqlError is still empty.
SELECT 0 has no problem, but SELECT * FROM ALL_TABLES still triggers the problem. However, it still confuses me. I read QStringList QOCIDriver::tables(QSql::TableType type) const function in qsql_oci.cpp:

const auto tableQuery = "select owner, table_name from all_tables where "_L1;
const QString where = make_where_clause(user, AndExpression);
t.exec(tableQuery + where);

If I write db.tables(), my program can successfully read all the table names, so to select from all_tables seems to be no problem. Are there any additional special steps to read all_tables from an Oracle database by QOCI driver?

Christian Ehrlicher

Case sensitive database maybe?

JonB

@Christian-Ehrlicher
I wondered about that, but https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/ALL_TABLES.html gives the names in all capitals and the OP claims the query (as-is, I presume!) works

I run the SQL query in my Oracle client and it can properly output all the selected TABLE_NAME items.

Christian Ehrlicher

Then without a prepared query.

Yihua Liu

@Christian-Ehrlicher Hello, without a prepared query the problem still occurs. Besides, if I try to exec something like

SELECT SOME_FIELD_NAME FROM SOME_TABLE_NAME

the problem also occurs

JonB

@Yihua-Liu
Since I asked and you said SELECT 0 does not even work you can forget everything else.

I suspect you now need someone else to try with your version of Qt and of the Oracle client. If you can try a different version of Qt and/or a different version of Oracle client (I don't know how that works) that would be great. Otherwise you had better say exactly how you got your Qt (e.g. did you compile it yourself? with what config?) and similarly for whatever the Qt-to-Oracle library/driver is?

Yihua Liu

This post is deleted!