Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Best practice handling multiple SQL queries



  • Hi :-)

    I have a program using SQLite. I use a database class doing the actual communication with the backend. Although in normal operation no errors should occur, I'd like to handle errors and failing queries caused by whatever.

    At the moment, I use helper functions, e. g. queryPrepare(QSqlQuery &query, const QString &text) which does the actual query.prepare(text), and emits an error signal on failure, which is then handled by the main window.

    I could simply use the QSqlQuery's return value and pass it back to the original caller function. But the problem is that for some functions, I use a lot queries. And now I'm thinking about how to handle a possible failure in an elegant and convenient way.

    Is there another way than checking the return value over over again?

    Like

    bool Database::queryPrepare(QSqlQuery &query, const QString &text)
    {
        const bool success = query.prepare(text);
        if (! success) {
            emit someErrorSignal(...);
        }
        return success;
    }
    
    bool Database::queryExec(QSqlQuery &query)
    {
        const bool success = query.exec();
        if (! success) {
            emit someErrorSignal(...);
        }
        return success;
    }
    
    void Database::someFunction()
    {
        QSqlQuery query(m_db);
        
        if (! queryPrepare(query, QStringLiteral("SOME SQL"))) {
            return;
        }
        query.bindValue(0, QStringLiteral("some value"));
        if (! queryExec(query)) {
             return;
        }
    
        if (! queryPrepare(query, QStringLiteral("SOME OTHER SQL"))) {
            return;
        
        query.bindValue(0, QStringLiteral("some other value"));
        if (! queryExec(query)) {
             return;
        }
    }
    

    I don't think I can make someFunction to abort execution or return something if I do something inside queryPrepare or queryExec, can I?

    How would one implement handling execution errors of multiple consecutive queries? Thanks for all help!



  • @l3u_
    Yes you have to check a failure return result on each call to do a return from a calling function. This is not unusual C++ practice.

    Otherwise, you could write your wrapper functions (queryPrepare, queryExec etc.) to use C++ throw. Then you can use a single try ... catch ... in your functions to get an error from any of these return to the catch part of the caller, and return/clean up from there. This is what you call "aborting". Exception throwing is common in other languages/frameworks, less so in Qt. Just be careful if you use this that things get cleaned up correctly when a throw occurs (I don't know whether Qt objects etc. play badly with this).

    Finally, you may be able to reduce the someFunction() functions to a skeleton which you only have to write once and share if you can conveniently pass parameters from the caller to it. For instance, the example as shown could take a list of strings to execute as SQL statements one after the other (and return false if any of them fail). Ease may depend on how many variables you have to pass to it for binding.



  • @JonB Thanks for the throw hint! To be honest, I didn't even know C++ has this! Maybe this is what I need. After all, it's not important which exact query failed, as all have to be processed correctly. I'll have a look at it.


Log in to reply