Using SQLite with Qt

Question

I am thinking of using SQLite as a backend DB for a C++ applicatiojn I am writing. I have read the relevant docs on both teh trolltech site and sqlite, but the information seems a little disjointed, there is no simple snippet that shows a complete CRUD example.

I want to write a set of helper functions to allow me to execute CRUD actions in SQLite easily, from my app.

The following smippet is pseudocode for the helper functions I envisage writing. I would be grateful for suggestions on how to "fill up" the stub functions. One thing that is particularly frustrating is that there is no clear mention in any of the docs, on the relationship between a query and the database on which the query is being run - thus suggesting some kind of default connection/table.

In my application, I need to be able to explicitly specify the database on which queries are run, so it would be useful if any answers spell out how to explicitly specify the database/table involved in a query (or other database action for that matter).

My pseudocode follows below:

#include <boost/shared_ptr.hh>

typedef boost::shared_ptr<QSqlDatabase> dbPtr;


dbPtr createConnection(const QString& conn_type = "QSQLITE", const QString& dbname = ":memory:")
{
    dbPtr db (new QSQlDatabase::QSqlDatabase());

    if (db.get())
    {
        db->addDatabase(conn_type);
        db->setDatabaseName(dbname);

        if (!db.get()->open)
            db.reset();
    }

    return db;
}

bool runQuery(const Qstring& sql)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How to iterate over the results of the run query?
}

bool runPreparedStmtQuery(const QString query_name, const QString& params)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How do I pass parameters (say a comma delimited list to a prepared statement ?
    //How to iterate over the results of the run query?
}

bool doBulkInsertWithTran(const Qstring& tablename, const MyDataRows& rows)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How to start/commit|rollback
}

In case what I'm asking is not clear, I am asking what would be the correct wat to implement each of the above functions (possibly with the exception of the first - unless it can be bettered of course).

[Edit]

Clarified question by removing requirement to explicitly specify a table (this is already done in the SQL query - I forgot. Thanks for pointing that out Tom

Answer 1

By default, Qt uses the application's default database to run queries against. That is the database that was added using the default connection name. See the Qt documentation for more information. I am not sure what you mean by the default database table, since the table to operate on is normally specified in the query itself?

To answer your question, here is an implementation for one of your methods. Note that instead of returning a bool I would return a QSqlQuery instance instead to be able to iterate over the results of a query.

QSqlQuery runQuery(const Qstring& sql)
{
    // Implicitly uses the database that was added using QSqlDatabase::addDatabase()
    // using the default connection name.
    QSqlQuery query(sql);
    query.exec();
    return query;
}

You would use this as follows:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setHostName("localhost");
db.setDatabaseName("data.db");
if (!db.open())
{
  raise ...
}

QSqlQuery query = runQuery("SELECT * FROM user;");
while (query.next())
{
  ...
}

Note that it is also possible to explicitly specify for which database a query should be run by explicitly specifying the relevant QSqlDatabase instance as the second parameter for the QSqlQuery constructor:

QSqlDatabase myDb;
...
QSqlQuery query = QSqlQuery("SELECT * FROM user;", myDb);
...