How to get the correct QSqlError?

deleted385

Here's what I've got in the UI:

The topleft segment is ObjectsView, topright is QueryResultView, in between those is QueryView and at bottom the LogView. Logs in LogView is in last in first order. db is an extern QSqlDatabase db; initialized in the constructor of the ObjectsView and is used in ObjectsView and QueryResultView.

So when I first attached the MakeFile as the database, the ObjectsView executed this:

void ObjectsView::openFileDialog()
{
    ...
    db.setDatabaseName(dialog.selectedFiles().first());
    if(!db.open()) return;
    emit logMessage(db.lastError());  
    QSqlQuery query("SELECT * FROM sqlite_master", db);
    query.setForwardOnly(true);
    if(query.exec()){...}
    else emit logMessage(query.lastError());
    ...
}

logMessage(...) signal is consumed by onMessageReceived slot of LogView this way:

void LogView::onMessageReceived(const QSqlError &msg)
{
    auto cursor = box->textCursor();
    cursor.movePosition(QTextCursor::Start);
    auto time = QDateTime::currentDateTime().toString("hh:mm:ss AP");
    QString log(time + " : ");
    switch (msg.type()) {
        case QSqlError::NoError: log += "No error : " + msg.text(); break;
        case QSqlError::ConnectionError: log += "Connection error : " + msg.text(); break;
        case QSqlError::StatementError: log += "Statement error : " + msg.text(); break;
        case QSqlError::TransactionError: log += "Transaction error : " + msg.text(); break;
        case QSqlError::UnknownError: log += "Unknown error : " + msg.text(); break;
    }
    cursor.insertText(log + '\n');
}

why does it get into this line emit logMessage(db.lastError()); in ObjectsView when I provide it an invalid database file and why does it say NoError? With that MakeFile as the database when I clicked the play button of QueryView, the QueryResultView in a slot executed this:

void QueryResultView::executeQuery(const QString &query)
{
   if(!db.open()) return;
   emit logMessage(db.lastError());
   model->setQuery(query, db);
   while (model->canFetchMore()) model->fetchMore();
   db.close();
   emit logMessage(model->query().lastError());
   table->setModel(model); //EDIT: this line isn't necessary, it should be in the constructor of the view
}

both emit logMessage(db.lastError()) and emit logMessage(model->query().lastError()) said no error! How to get the correct error message?

One more side question, the model in the QueryResultView is a QSqlQueryModel and the table is a QTableView. Whenever I click the play blutton or hit F5 in QueryView, that piece of code in QueryResultView is executed. Do I have to have some delete operation on model and table, if so, how?, or the Qt Framework will do that automatically?

jsulm

@Emon-Haque said in How to get the correct QSqlError?:

if(!db.open()) return;
emit logMessage(db.lastError());

This is wrong - open() returns false in case of error, then you should emit the error...

deleted385

@jsulm, it didn't catch error, it opened an invalid file ie, the MakeFile as database.

jsulm

@Emon-Haque Doesn't change the fact that this part of your code is logically wrong.
Qith SQLite you will not get an error as far as I know if the file is not there. Not sure what happens if the file exists but is not a database file.

JonB

@Emon-Haque said in How to get the correct QSqlError?:

it didn't catch error

Evidently SQLite does not consider/report an error at the time of opening a file, whatever it contains. Only later when it tries to perform some SQL operation on it does it complain.

deleted385

@jsulm, yes that's obviously wrong and thanks for reminding me about that. Before posting topic, I try to make it compact. If I'd done it in correct way, I'd have to add some extra lines, braces, etc.

deleted385

@JonB, when I call model->fetchMore, where model is a QSqlQueryModel, with an invalid file, I get file is not a database Unable to execute statement if I give it some string, If I give it an empty string, it says No Error.

With QSqlQuery query("SELECT * FROM sqlite_master", db); in ObjectsView, I got connection error and a message No query Unable to fetch row. Can I get the same message I got with QSqlQueryModel when it's an invalid file and I gave it some string?

JonB

@Emon-Haque
Well, to an extent you get whatever you get as error messages, I know no more than that!

However:

   model->setQuery(query, db);
   while (model->canFetchMore()) model->fetchMore();

You do not check the result from setQuery() before you proceed to canFetchMore(). Per void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db = QSqlDatabase()):

lastError() can be used to retrieve verbose information if there was an error setting the query.

So you should check model->lastError() immediately after the setQuery()?

deleted385

@JonB, will try that after a break and, hopefully, will let you know what happens.

deleted385

@JonB, Here's what it does:

First, I gave it an invalid database and these

QSqlQuery query("SELECT * FROM sqlite_master", db);
if(query.exec()) ...
else emit logMessage(query.lastError());

gave me 08:14:41 PM : Connection error : No query Unable to fetch row, the error message is incorrect. There was query BUT the database file is invalid. Second, I clicked the play button and in the box there was no text and these

model->setQuery(query, db);
emit logMessage(model->lastError());
while (model->canFetchMore()) model->fetchMore();

gave me 08:14:48 PM : No error : , it's also incorrect. Both the database is invalid and there was no query. Third, I gave SELECT as the query and it gave me 08:14:56 PM : Statement error : incomplete input Unable to execute statement. In this case it didn't say anything about the validity of database.

EDIT
After a while I gave it SELECT * FROM x, with that invalid Makefile as database, it gave me 09:55:22 PM : Statement error : file is not a database Unable to execute statement.

So you see the variations in QSqlError. In all of those cases I should get a single message like SQLite error 26: 'file is not a database'

JonB

@Emon-Haque
All I can say is mostly/frequently/probably those errors come from SQLite, or maybe the SQLite driver, not Qt.

deleted385

These errors also vary depending on which object I'm using (QSqlQueryModel or QSqlQuery ) and with a valid database. Here's the error I got with QSqlQueryModel:

To code is:

else{
    QSqlQueryModel sqlQuery;
    bool gotError = false;
    db.transaction();
    for (int i = 0; i < statements.size(); i++) {
        auto trimmed = statements[i].trimmed();
        if(trimmed.startsWith("--")) continue;
        sqlQuery.setQuery(trimmed);
        if(sqlQuery.query().isSelect()){
            emit widget->logMessage("e,SELECT isn't allowed while executing multiple statements");
            gotError = true;
            break;
        }
        else if(sqlQuery.query().lastError().type() != QSqlError::NoError){
            emit widget->logMessage("e," + sqlQuery.query().lastError().text());
            gotError = true;
            break;
        }
    }
    ...
}

so it's correct, NewTable exists. If I replace those code with these:

else{
    bool gotError = false;
    db.transaction();
    for (int i = 0; i < statements.size(); i++) {
        auto trimmed = statements[i].trimmed();
        if(trimmed.startsWith("--")) continue;
        sqlQuery.clear();
        sqlQuery.prepare(trimmed);
        sqlQuery.exec();
        if(sqlQuery.isSelect()){
            emit widget->logMessage("e,SELECT isn't allowed while executing multiple statements");
            gotError = true;
            break;
        }
        else if(sqlQuery.lastError().type() != QSqlError::NoError){
            emit widget->logMessage("e," + sqlQuery.lastError().text());
            gotError = true;
            break;
        }
    }
    ...
}

where sqlQuery is a global QSqlQuery, I get this error:

that's a misleading error I got! Am I doing something wrong or it's an issue with SQLite?

EDIT
Looks like QSqlQuery doesn't need to be executed to get error. I've to check error immediately after sqlQuery.prepare(trimmed); and if there's no error then I should call sqlQuery.exec(). In a nutshell, Qt executes the query, and figures out the error, before I call exec.