BB10: Cascades: Using SQLite

February 14th at 12:00am

While working on several BB10 applications leading up to launch I became more familiar with how to implement SQLite database access. While it is fairly straight-forward, there are a couple of 'gotchas' to be aware of.

References:

Just a note before reading this...

Reader M. Green sent in a few comments which can help make you more successful when implementing SQLite in your apps:

"After doing some more research I came up with a different solution than you did, and my way seems simpler.

I notice that in your examples you are connecting to the database for each query, then disconnecting again. Although it isn't mentioned in the SqlDataAccess documentation, the docs for SqlConnection say this:

"Also, since SQLite has limitations with the use of multiple connections, having all access to an SQLite database working through a single SqlConnection object is a good pattern."

In my app, I wasn't making a new connection for every query, but I was opening multiple connections. I have some worker classes and each of them made a SQLite connection and used it for queries for the duration of the application run. After adding this functionality I noticed that sometimes my app would spontaneously shut down, and when it did, one of those duplicate connection messages was usually written to the log just before the crash. Even when it didn't crash I was seeing lots of duplicate connection messages as the worker classes tried to talk to the database from different connections.

I realized I was getting these messages because I had multiple connections that would simultaneously try to access the database file, so I created a single SqlDataAccess at the app level and gave all the worker classes a pointer to it. This way there is only ever one SQLite connection in my app and I never get the duplicate connection messages any more. Even better, the app stopped randomly crashing. At their peak, the worker classes were maintaining around 25 connections the old way, and it just looks like SQLite chokes on that many connections."

I'd like to thank Mr. Green for sending this along and encourage anyone else to send in suggestions as well (see the About page for contact information).

 

Available Options

There are 2 different ways to access the SQLite database in BB10: SqlDataAccess provided by RIM and QT SQL provided by the QT framework. I only found out about the QT method after getting the RIM method to work, so this article focuses on SqlDataAccess.

 

Get Setup To Use SqlDataAccess

The BlackBerry developer pages list everything that you need to do to get started:

  • In your project's .pro file, add this line:

    LIBS += -lbbdata

  • In your C++ class, add these lines:

    #include<bb/cascades/Application>
    #include <bb/data/SqlDataAccess>
    usingnamespacebb::cascades;
    usingnamespacebb::data;

 

Create an SQLite Database

Now that you have your project file setup correctly you can create an SQLite database for use by your app. While there may be a way to get SqlDataAccess to create a database for you, I haven't gone down that path. Easier to have a template database setup how I want it to work.

On Windows, I like to use SqliteBrowser to create and edit databases.

 

Non Parameterized SQL Statements

*Important Note*: SqlDataAccess is not thread safe. Be sure to implement appropriate locking before calling SqlDataAccess.

Now that you have your project setup, you can perform 'Raw' SQL Queries against the database using a method like this (taken from a sample project with a Data Access Layer class):

QVariant DAL::ExecuteQuery(QString query) {

    SqlDataAccess DataAccess(DbLocation);  // DbLocation is a private class variable that points to the SQLite database on the file system

    QVariant Results = DataAccess.execute(query);

    if (DataAccess.hasError()) {
        DataAccessError err = DataAccess.error();
        QString error = err.errorMessage();
    }

    DataAccess.disconnect();  // Disconnect when you are done to prevent stale connection messages

    return Results;
}

 

This method performs the following actions:

  • Instantiates an instance of SqlDataAccess with the path to the SQLite database
  • Executes the query against the database and stores the results as a QVariant
  • Checks for errors and stores them in a QString variable if found
  • Disconnects from SqlDataAccess
  • Returns the results

 

 

Here is a sample method that uses the above SQL access method to query information from a database:

QString DAL::GetSetting(QString name){
    QString GetSetting = "SELECT Value from Settings where Name = '" + name + "'";
    QVariantList Result = ExecuteQuery(GetSetting).value< QVariantList>(); // Cast return type as a list for further processing

    QVariantMap ResultMap = Result[0].value< QVariantMap>(); // Cast the first result as a Map to extract the data
    QString Value = ResultMap["Value"].value< QString>();  // Extract the string data
    qDebug() << "Setting value" << Value;

    return Value;
}

 

This method performs these actions:

  • Builds a simple SQL Query and stores it as a QString. Be aware that if your method takes integers, you'll need to cast them to QString using QString::number(numericVariableHere) before it will be usable by SqlDataAccess.
  • Execute the query and take the results as a List of results
  • Examines the first result as a QVariantMap as this is how SQL Rows are returned by SqlDataAccess
  • Extract the value from the column named Value as a string
  • Use qDebug() to log the value to the device log
  • Return the QString value

 

Non Parameterized SQL Warnings

When I first started using SqlDataAccess I kept getting this message:

Dec 19 16:15:54.149 com.example.TestApp.testDev_e_TestApp47b4b774.73183421              default   9000  QSqlDatabasePrivate::addDatabase: duplicate connection name '/accounts/1000/appdata/com.example.TestApp.testDev_e_BudgeTech47b4b774/app/native/assets/Databases/test.sq3', old connection removed.


While the message doesn't appear to be harmful, I like to make my code as clean as possible so that when a message does come up it warrants investigation. This message was caused by my not cleaning up SqlDataAccess. That is why I show the use of .disconnect() when I'm finished with the SqlDataAccess object.

 

Parameterized SQL Statements

To protect against SQL Injection it is advisable to use parameterized SQL Statements. Fortunately for us, SqlDataAccess provides a way for us to work with parameterized statements.

Here is a sample method that can perform a parameterized SQL Query against an sqlite database:

QVariant Dal::ExecuteQuery(QString query, QVariantMap parameters) {
    QVariant Results = this->Sda->execute(query, parameters);  // Execute the query with the specified parameters

    if (this->Sda->hasError()) {
        DataAccessError err = this->Sda->error();
        QString error = err.errorMessage();  // If there is an error, get the QString reprsentation
    }

    return Results;  // Return a QVariantList cast as QVariant to the caller
}

 

This method is very similar to the one listed in the previous section on non-parameterized queries. Here's what it  does:

  • Accepts input of a SQL Query as QString along with the query's parameters in a QVariantMap
  • Calls the appropriate execute overload that accepts parameters
  • Checks for errors and stores any error in a QString (useful when running the debugger)
  • Return the QVariantList results cast as a QVariant (seems to be more reliable this way)

 

Here is a sample method which creates a parameterized SQL Query and uses the above method to execute it:

void Dal::UpdateNotes(int scanId, QString notes){
    QVariantMap NotesMap;  // Create a QVariantMap to store the parameters in
    NotesMap["notes"] = notes;  // Add a notes parameter set equal to the QString passed in
    NotesMap["objId"] = scanId;  // Add a objId parameter set equal to the int passed in

    QString NotesUpdateQuery = "UPDATE Objects SET Notes = :notes where Id = :objId"; // When constructing the SQL Query, note the use of a semi-colon to denote the use of a parameter
    this->ExecuteQuery(NotesUpdateQuery, NotesMap);  // Execute the query and parameters
}

A few things to note here:

  • When using a parameterized SQL Query, you don't need to convert non QString types to QString yourself. In this example I'm able to accept an int directly. This comes in handy when you have more gnarly queries you need to put together
  • While we build up our parameters in a QVariantMap in this example, there is another way to do it with a  QVariantList. I find the QVariantMap method to be easier to read, so I use it here
  • When building out a parameterized SQL Query, be sure to use semi-colons to denote a parameterized token
  • When using paramerized SQL Queries, there is no need to wrap string types in single quotes like you would if you were executing the query against the database manually

 

Parameterized Query Errors

Since the Parameterized SQL Queries are a little more complex than the vanilla variety, you may run into an error like this when getting this all put together:

Jan 08 17:05:53.300 com.rubion.TestApp.testDev__TestApp90ea16cd.668038211              default   9000  SqlDataAccess error:  "INSERT INTO TableName (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (':Col1', ':Col2', ':Col3', ':Col4' , :Col5, :Col6, ':Col7' )" . QSqlError::type= "QSqlError::StatementError" , QSqlError::number= -1 , databaseText= "" , driverText= "Parameter count mismatch" 

 

I found 2 ways to get the above parameter count mismatch error:

  • Wrapping my parameters in single quotes (as evidenced in the error text above)
  • Not having an SQLite database on my dev-alpha test device. In this case, the error text above was entirely unhelpful and I had to fumble around for awhile before figuring out what was wrong.