SqlConnection

Since: BlackBerry 10.0.0

#include <bb/data/SqlConnection>

To link against this class, add the following line to your .pro file: LIBS += -lbbdata

Connects to an SQL database and executes commands asynchronously.

The SqlConnection class is the public class that applications use to create a connection to an SQL database and execute SQL commands asynchronously in another thread.

Performing long operations in a separate (non-main) thread will ensure that the overall application responsiveness (for example, swift response to button clicks) is maintained even when database operations are being performed. 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.

Here's an example of how to connect to an SQL database using SqlConnection and handle the results:

void MyClass::onLoadData()
{
    // First time only: create an SqlConnection (called mpSqlConnector here) to establish
    // a connection thread to a database and process multiple queries during the life of the
    // application.
    if (!mpSqlConnector) {
        // A) Establish connection thread to database
        mpSqlConnector = new SqlConnection("contacts.db", this);

        // B) Use signals and slots to get results data when it's ready
        connect( mpSqlConnector, SIGNAL(reply(const bb::data::DataAccessReply&)),
                this, SLOT(onLoadResultData(const bb::data::DataAccessReply&)) );
    }

    // C) Load 20 rows, add them to model using onLoadResultData()
    mpSqlConnector->beginTransaction();
    DataAccessReply reply = mpSqlConnector->executeAndWait("select * from contact order by id limit 20");
    onLoadResultData(reply);
    if (reply.hasError()) {
        mpSqlConnector->rollbackTransaction();
    }
    else {
        // D) Load another 1000 rows
        mpSqlConnector->execute("select * from contact order by id limit 1000 offset 20");
        mpSqlConnector->endTransaction();
    }
}

void MyClass::onLoadResultData(const DataAccessReply& reply) {
    if (reply.hasError()) {
        // This adds message to console - better to inform the user by updating the UI
        qWarning() << "Error while retrieving data from SQL database: " << reply;
    }
    else {
        // Append data to the end of QListDataModel (called mDataModel here)
        QVariant results = reply.result();
        mDataModel->append(results.value<QVariantList>());
    }
}


Overview

Inheritance

bb::data::AsyncDataAccess
bb::data::SqlConnection

Public Functions Index

SqlConnection (const QString &dbPath, QObject *parent=0)
SqlConnection (const QString &dbPath, const QString &connectionName, QObject *parent=0)
SqlConnection (SqlWorker *worker, QObject *parent=0)
virtual ~SqlConnection ()
voidbeginTransaction (int id=0)
voidendTransaction (int id=0)
voidexecute (const QString &query, const QVariantList &valuesByPosition, int id=0)
voidexecute (const QString &query, const QVariantMap &valuesByName, int id=0)
DataAccessReplyexecuteAndWait (const QString &query, const QVariantList &valuesByPosition, int id=0)
DataAccessReplyexecuteAndWait (const QString &query, const QVariantMap &valuesByName, int id=0)
voidexecuteBatch (const QString &query, const QVariantList &valuesTable, int id=0)
voidrollbackTransaction (int id=0)
AsyncDataAccess (AsyncWorker *worker, QObject *parent=0)Inherited
voidexecute (const QVariant &criteria, int id=0)Inherited
DataAccessReplyexecuteAndWait (const QVariant &criteria, int id=0)Inherited
boolisRunning ()Inherited
voidstart (QThread::Priority priority=QThread::LowPriority)Inherited
voidstop ()Inherited

Signals Index

Only has inherited signals

voidreply (const bb::data::DataAccessReply &replyData)Inherited

Public Functions

SqlConnection (

Constructs an SqlConnection object with the specified path to a database file and parent.

If the specified parent is not 0, the ownership of this object will be transferred to the parent.

Parameters
dbPath

The path to the SQL database. It is an absolute or relative file path to the local database. This path is also used as the connection name.

parent

The parent owner or 0. Optional and will default to 0 if not specified.

Since:

BlackBerry 10.0.0

SqlConnection (

Constructs an SqlConnection object with the specified path to a database file, connection name, and parent.

The connectionName is useful to identify the purpose for a particular connection, especially when there are multiple SQL connections used in an application.

If the specified parent is not 0, the ownership of this object will be transferred to the parent.

Parameters
dbPath

The path to the SQL database. It is an absolute or relative file path to the local database.

connectionName

The name of the database connection.

parent

The parent owner or 0. Optional and will default to 0 if not specified.

Since:

BlackBerry 10.0.0

SqlConnection (

Constructs an SqlConnection object with the specified SqlWorker object and parent.

If the specified parent is not 0, the ownership of this object will be transferred to the parent. Also, ownership of the worker will always be managed by this async framework and so the ownership of the worker will be transferred to an internal object in this framework which has affinity for the new thread.

You can customize the standard SqlWorker class by extending SqlWorker and overriding the execute() function to specify your custom behavior.

Parameters
worker

The worker object that performs work in another thread.

parent

The parent owner or 0. Optional and will default to 0 if not specified.

Since:

BlackBerry 10.0.0

virtual~SqlConnection ()

Destructor.

Since:

BlackBerry 10.0.0

void beginTransaction (
  • intid)

Begins an SQL transaction that contains multiple commands.

Typically, each SQL command is executed as an independent transaction (that is, commands are executed in autocommit mode). Any changes are committed when the command ends, or are rolled back (discarded) if the command ends with an error. The beginTransaction() function will take this database connection out of autocommit mode for the duration of this transaction.

Parameters
id

An ID that's used to help match this request with its reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

void endTransaction (
  • intid)

Completes an SQL transaction as successful.

Any changes will be committed to the database. The database connection will return to autocommit mode (that is, each command will be a separate transaction).

Parameters
id

An ID that's used to help match this request with its reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

void execute (

SQL query parameters that are passed to the SqlWorker object for execution in another thread.

The SqlWorker executes the parameterized SQL query after binding the supplied values in the list to placeholders in the query string.

If the same query is executed multiple times the prepared query will be cached and reused with new bound values.

This form of parameterized query uses positional binding since the first value in the list is bound to the first placeholder in the query, the second value to the second placeholder, and so on.

The place holders in the query string can be defined in one of two ways as seen in the following two examples:

// Execute query with positional binding using named placeholders
QVariantList contactValues;
contactValues << 1 << "Mike" << "Chepesky";
sc.execute("insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    contactValues);
// Execute query with positional binding using positional (?) placeholders
QVariantList contactValues;
contactValues << 1 << "Mike" << "Chepesky";
sc.execute("insert into contact (id, firstname, lastname) values (?, ?, ?)",
    contactValues);
The SQL query can be any valid SQL command. If the query is an SQL SELECT command, then the data is returned as a QVariantList and each item in the list is a QVariantMap.

The query can also be a DML (data manipulation language) command such as INSERT, UPDATE or DELETE, or a DDL (data definition language) command such as CREATE TABLE or CREATE INDEX. In these cases, an empty QVariant is returned.

This method returns immediately. Results will be returned later in a DataAccessReply object using the reply() signal.

If the thread has not been started, this method will call start().

Parameters
query

The parameterized SQL command to execute.

valuesByPosition

The list of values to bind to placeholders by position in query.

id

An ID that's used to help match this request with a particular reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

void execute (

SQL query parameters are passed to the SqlWorker object for execution in another thread.

The SqlWorker executes the parameterized SQL query after binding the supplied values in the map to named placeholders in the query string.

If the same query is executed multiple times the prepared query will be cached and reused with new bound values.

This form of parameterized query uses named binding since each value in the map is bound to a named placeholder in the query using the map key. Here is an example:

// Execute query with named binding using named placeholders
QVariantMap contactValues;
contactValues["id"] = 1;
contactValues["firstname"] = "Mike";
contactValues["lastname"] = "Chepesky";
sc.execute("insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    contactValues);
Note that binding to an undefined placeholder will result in undefined behavior.

The SQL query can be any valid SQL command. If the query is an SQL SELECT command, then the data is returned as a QVariantList and each item in the list is a QVariantMap.

The query can also be a DML (data manipulation language) command such as INSERT, UPDATE or DELETE, or a DDL (data definition language) command such as CREATE TABLE or CREATE INDEX. In these cases, an empty QVariant is returned.

This method returns immediately. Results will be returned later in a DataAccessReply object using the reply() signal.

If the thread has not been started, this method will call start().

Parameters
query

The parameterized SQL command to execute.

valuesByName

The map of named values to bind to placeholders in query.

id

An ID that's used to help match this request with a particular reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

DataAccessReply executeAndWait (

SQL query parameters are passed to the SqlWorker object for execution in another thread.

The SqlWorker executes the parameterized SQL query after binding the supplied values in the list to placeholders in the query string.

If the same query is executed multiple times the prepared query will be cached and reused with new bound values.

This form of parameterized query uses positional binding since the first value in the list is bound to the first placeholder in the query, the second value to the second placeholder, and so on.

The place holders in the query string can be defined in one of two ways as seen in the following two examples:

// Execute query with positional binding using named placeholders
QVariantList contactValues;
contactValues << 1 << "Mike" << "Chepesky";
DataAccessReply reply = sc.executeAndWait(
    "insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    contactValues);
// Execute query with positional binding using positional (?) placeholders
QVariantList contactValues;
contactValues << 1 << "Mike" << "Chepesky";
DataAccessReply reply = sc.executeAndWait(
    "insert into contact (id, firstname, lastname) values (?, ?, ?)",
    contactValues);
The SQL query can be any valid SQL command. If the query is an SQL SELECT command, then the data is returned as a QVariantList and each item in the list is a QVariantMap.

The query can also be a DML (data manipulation language) command such as INSERT, UPDATE or DELETE, or a DDL (data definition language) command such as CREATE TABLE or CREATE INDEX. In these cases, an empty QVariant is returned.

The current thread will block until a DataAccessReply object can be returned.

If the thread has not been started, this function will call start().

Parameters
query

The parameterized SQL command to execute.

valuesByPosition

The list of values to bind to placeholders by position in query.

id

An ID that's used to help match this request with its reply. The DataAccessReply that's returned will contain this ID. Defaults to 0 if not specified.

Return:

A DataAccessReply containing the results of the operation.

Since:

BlackBerry 10.0.0

DataAccessReply executeAndWait (

SQL query parameters are passed to the SqlWorker object for execution in another thread.

The SqlWorker executes the parameterized SQL query after binding the supplied values in the map to named placeholders in the query string.

If the same query is executed multiple times the prepared query will be cached and reused with new bound values.

This form of parameterized query uses named binding since each value in the map is bound to a named placeholder in the query using the map key. Here is an example:

// Execute query with named binding using named placeholders
QVariantMap contactValues;
contactValues["id"] = 1;
contactValues["firstname"] = "Mike";
contactValues["lastname"] = "Chepesky";
DataAccessReply reply = sc.executeAndWait(
    "insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    contactValues);
Note that binding to an undefined placeholder will result in undefined behavior.

The SQL query can be any valid SQL command. If the query is an SQL SELECT command, then the data is returned as a QVariantList and each item in the list is a QVariantMap.

The query can also be a DML (data manipulation language) command such as INSERT, UPDATE or DELETE, or a DDL (data definition language) command such as CREATE TABLE or CREATE INDEX. In these cases, an empty QVariant is returned.

The current thread will block until a DataAccessReply object can be returned.

If the thread has not been started, this function will call start().

Parameters
query

The parameterized SQL command to execute.

valuesByName

The map of named values to bind to placeholders in query.

id

An ID that's used to help match this request with its reply. The DataAccessReply that's returned will contain this ID. Defaults to 0 if not specified.

Return:

A DataAccessReply containing the results of the operation.

Since:

BlackBerry 10.0.0

void executeBatch (

SQL query parameters are passed to the SqlWorker object for batch execution in another thread.

The SqlWorker executes the parameterized SQL query once for each item in the valuesTable. Each item in the valuesTable is either a list or a map and it represents a set of values which are bound to placeholders in the query string.

This method can use either positional binding or named binding. With positional binding, each item in the valuesTable is a QVariantList containing a list of values to bind to the corresponding placeholder (first-to-first, second-to-second, and so on) in the query command. With named binding, each item in the valuesTable is a QVariantMap containing name-value pairs used to bind to named placeholders in the query command.

The following three examples describe the different variations:

// Execute batch with positional binding using named placeholders
QVariantList contact1, contact2, contact3;
contact1 << 1 << "Mike" << "Chepesky";
contact2 << 2 << "Westlee" << "Barichak";
contact3 << 3 << "Fred" << "Ziffel";

// valuesTable is a list of lists
QVariantList valuesTable;

// Use QVariant::fromValue() here to avoid copying contents
// of contact1 list into valuesTable list
valuesTable << QVariant::fromValue(contact1);
valuesTable << QVariant::fromValue(contact2);
valuesTable << QVariant::fromValue(contact3);
sc.executeBatch("insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    valuesTable);
// Execute batch with positional binding using positional (?) placeholders
QVariantList contact1, contact2, contact3;
contact1 << 1 << "Mike" << "Chepesky";
contact2 << 2 << "Westlee" << "Barichak";
contact3 << 3 << "Fred" << "Ziffel";

// valuesTable is a list of lists
QVariantList valuesTable;

// Use QVariant::fromValue() here to avoid copying contents
// of contact1 list into valuesTable list
valuesTable << QVariant::fromValue(contact1);
valuesTable << QVariant::fromValue(contact2);
valuesTable << QVariant::fromValue(contact3);
sc.executeBatch("insert into contact (id, firstname, lastname) values (?, ?, ?)", valuesTable);
// Execute batch with named binding using named placeholders
QVariantMap contact1, contact2, contact3;
contact1["id"] = 1;
contact1["firstname"] = "Mike";
contact1["lastname"] = "Chepesky";
contact2["id"] = 2;
contact2["firstname"] = "Westlee";
contact2["lastname"] = "Barichak";
contact3["id"] = 3;
contact3["firstname"] = "Fred";
contact3["lastname"] = "Ziffel";

// valuesTable is a list of maps
QVariantList valuesTable;
valuesTable << contact1 << contact2 << contact3;
sc.executeBatch("insert into contact (id, firstname, lastname) values (:id, :firstname, :lastname)",
    contactValues);
Note that binding to an undefined placeholder will result in undefined behavior.

If an error is detected when executing an SQL query for a particular value set then the processing of the batch is stopped at that point. The effects of an error on the current transaction is covered below.

The SQL query can be any valid SQL command. If the query is an SQL SELECT command, then the data is returned as a QVariantList and each item in the list is a QVariantMap.

The query can also be a DML (data manipulation language) command such as INSERT, UPDATE or DELETE, or a DDL (data definition language) command such as CREATE TABLE or CREATE INDEX. In these cases, an empty QVariant is returned.

This method returns immediately. Results will be returned later in a DataAccessReply object using the reply() signal.

If the thread has not been started, this method will call start().

Transactions

A batch operation such as this should always be performed in a transaction. This will perform much better than if each individual query within the batch is a separate transaction. Results show that using executeBatch with large value sets and failing to use a transaction can result in performance that can be 100X slower.

If a transaction has not been started before this executeBatch method is called then it will automatically use a transaction to ensure good performance. In this case it will do the following internally:
  • Start a new transaction

  • Execute a query for each value set

  • If an error occurs, rollback the transaction

  • If no error occurs, commit the transaction

If a transaction has already been started before this executeBatch method is called then it is up to the calling code to manage the transaction processing. A common case would be when multiple calls to execute() and/or executeBatch() need to be combined into a single transaction to ensure a consistent result. Here is a simple example (minimal error checking):
  • sc.beginTransaction();

  • sc.execute(query1);

  • sc.execute(query2);

  • sc.executeBatch(batch1, values);

  • if sc.hasError()
    • sc.rollbackTransaction();

  • else
    • sc.endTransaction();

Parameters
query

The parameterized SQL command to execute.

valuesTable

The list of value sets to bind to placeholders in query.

id

An ID that's used to help match this request with a particular reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

void rollbackTransaction (
  • intid)

Completes an SQL transaction as a failure and discards any changes.

The database connection will return to autocommit mode (that is, each command will be a separate transaction).

Parameters
id

An ID that's used to help match this request with its reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

AsyncDataAccess (Inherited

Constructs a data access "service" object which will run the given worker task in another thread.

If not 0, the ownership of this object will be transferred to the parent. Also, ownership of the worker will always be managed by this async framework and so the ownership of the worker will be transferred to an internal object in this framework which has affinity for the new thread.

Parameters
worker

The class which will execute work in another thread.

parent

The parent owner or 0. Optional and will default to 0 if not specified.

Since:

BlackBerry 10.0.0

void execute (Inherited

Passes the specified criteria data to the asynchronous worker and queues for execution.

This method returns immediately. Results will be returned in a DataAccessReply object using the reply() signal in this class.

If the thread has not been started, this method will call start().

Parameters
criteria

The criteria data that is passed to the worker to specify the work to be done.

id

An ID that's used to help match this request with a particular reply. The DataAccessReply will contain this ID. Defaults to 0 if not specified.

Since:

BlackBerry 10.0.0

DataAccessReply executeAndWait (Inherited

Passes the specified criteria data to the asynchronous worker for execution.

The current thread will block until a DataAccessReply object can be returned.

If the thread has not been started, this function will call start().

Parameters
criteria

The criteria data that is passed to the worker to specify the work to be done.

id

An ID that's used to help match this request with its reply. The DataAccessReply that's returned will contain this ID. Defaults to 0 if not specified.

Return:

A DataAccessReply containing the results of the operation.

Since:

BlackBerry 10.0.0

bool isRunning ()Inherited

Indicates whether the thread that's performing the asynchronous work is currently running.

Return:

true if the thread is currently running, false otherwise.

Since:

BlackBerry 10.0.0

void start (
  • QThread::Prioritypriority)
Inherited

Starts the asynchronous worker in another thread, ready to accept work.

If not started explicitly, the worker will be started when the first work is executed.

Parameters
priority

The priority of the thread, specified by QThread::Priority. The default value is QThread::LowPriority.

Since:

BlackBerry 10.0.0

void stop ()Inherited

Stops the thread that's performing the asynchronous work.

You can call start() again to initiate a new thread using the same async worker.

Since:

BlackBerry 10.0.0

Signals

(Only has inherited signals)

void reply (Inherited

Emitted when an asynchronous execute operation has completed and has results to return.

Parameters
replyData

The reply data from the execute operation.

Since:

BlackBerry 10.0.0

Last modified: 2014-03-13

comments powered by Disqus