blackberry.com
BlackBerry Dynamics
Runtime library for iOS applications
from the application developer portal

Secure SQL Database programming interface

The secure SQL database is part of the BlackBerry Dynamics secure storage feature.

Data stored in the secure SQL database will be encrypted on the device by the BlackBerry Dynamics runtime.

The BlackBerry Dynamics secure SQL database is based on the SQLite library. Encryption is added by BlackBerry Dynamics, transparently to the application.

The secure SQL database cannot be accessed until BlackBerry Dynamics authorization processing is complete.

As well as the secure SQL database, the BlackBerry Dynamics secure store also includes a secure file system, and a secure store for Core Data.

See also:
GDiOS, for BlackBerry Dynamics authorization
GDFileManager
GDPersistentStoreCoordinator, for the Secure Storage API for Core Data
http://www.sqlite.org/, the home page of the SQLite project.

Overview of the programming interface

BlackBerry Dynamics applications can access the secure database using the normal SQLite programming interface with a number of additional functions, see below.

See also:
http://www.sqlite.org/c3ref/intro.html, for the reference documentation of the normal SQLite programming interface.

Additional Functions

Include the sqlite3enc.h file in order to use these functions.

int sqlite3enc_open(const char *filename, sqlite3 **ppDb);
Use this function to open an encrypted database, in the secure store.

This function can only be called after BlackBerry Dynamics authorization processing is complete, see under GDiOS.

The parameters are the same as the sqlite3_open function in the normal SQLite programming interface, see link above.

int sqlite3enc_import(const char *srcFilename, const char *destFilename);
Use this function to create an encrypted database from a plain SQLite database file. The database file must be in the secure file system, see GDFileManager.

After a succesful import, the database can be opened using sqlite3enc_open, see above.

This function would typically be used on an SQLite database that had been retrieved as a single file from an application server.

This function will not create intermediate sub-directories in the destination path. Intermediate directories must exist prior to calling the function.

This function can only be called after BlackBerry Dynamics authorization processing is complete, see under GDiOS.

Parameters:
srcFilename path, within the secure file system, of the plain SQLite database file to be imported.
destFilename database to be created. If the database already exists, its contents will be overwritten.
Returns:
On success, SQLITE_OK is returned.
Otherwise an SQLite error code is returned.

Code Snippets

The following code snippets illustrate some common tasks.

Open database

 #import <GD/sqlite3enc.h>

 sqlite3 *pDb = NULL;
 char *errorMsg = NULL;
 int returnCode;
 char *databaseName = NULL;

 databaseName = "financials.db";
 returnCode = sqlite3enc_open(databaseName, &pDb);
 if(returnCode!=SQLITE_OK) {
     fprintf(stderr,
         "Error in opening the database. Error: %s", sqlite3_errmsg(pDb)
     );
     sqlite3_close(pDb);
     return;
 }

Delete table

 #import <GD/sqlite3enc.h>

 const char *sqlStatement = NULL;

 sqlStatement =  "DROP TABLE IF EXISTS  stocks";
 if(returnCode!=SQLITE_OK) {
     fprintf(stderr, "Error in dropping table stocks. Error: %s", errorMsg);
 returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
     sqlite3_free(errorMsg);
 }

Create table

 #import <GD/sqlite3enc.h>

 sqlStatement =
     "CREATE TABLE stocks (symbol VARCHAR(5), "
     "purchasePrice FLOAT(10,4), unitsPurchased INTEGER, "
     "purchase_date VARCHAR(10))";
 returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
 if(returnCode!=SQLITE_OK) {
     fprintf(stderr,
         "Error in creating the stocks table. Error: %s", errorMsg
     );
     sqlite3_free(errorMsg);
 }
 sqlite3_close(pDb);

Read data

 #import <GD/sqlite3enc.h>

 sqlStatement = sqlite3_mprintf(
     "SELECT S.symbol, S.unitsPurchased, S.purchasePrice "
     "FROM stocks AS S WHERE S.purchasePrice  >= %f", 30.0
 );

 returnCode = sqlite3_prepare_v2(
     database, sqlStatement, strlen(sqlStatement), &statement, NULL
 );
 if(returnCode!=SQLITE_OK) {
     printf(
         "Error in preparation of query. Error: %s", sqlite3_errmsg(database)
     );
     sqlite3_close(database);
     return -1;
 }
 returnCode = sqlite3_step(statement);
 while(returnCode == SQLITE_ROW){
        char *symbol;
        int  units;
        double price;
        symbol = (char*)sqlite3_column_text(statement, 0);
        units  = sqlite3_column_int(statement, 1);
        price  = sqlite3_column_double(statement, 2);
        printf(
            "We bought %d from %s at a price equal to %.4f\n",
            units, symbol, price
        );
        returnCode = sqlite3_step(statement);
 }