SqlDataAccess

Since: BlackBerry 10.0.0

#include <bb/data/SqlDataAccess>

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

Retrieves and updates data in an SQL database.

The SqlDataAccess class provides a simple interface for retrieving data from or updating data in an SQL database. You can use this class to provide SQL data to a ListView to display the data in your application.

Any valid SQL command can be passed as a string to the execute(query) function. This includes executing a single 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, using the same execute(query) function.

Parameterized queries can be performed using the execute(query, valuesByPosition) and the execute(query, valuesByName) functions.

Batched queries can be performed using the executeBatch(query, valuesTable) function.

The result data that's returned by a SELECT command is of type QVariantList, cast as a QVariant. Each item in the list is a QVariantMap containing the names and values for fields within a record. The value types are the same as those returned by QSqlRecord::value().

This examples shows the loading of SQL data from a database file (contacts.db) into a data model for display in a ListView:

// Create a data model with sorting keys for firstname and lastname
GroupDataModel *model =
  new GroupDataModel(QStringList() << "firstname" << "lastname");

// Load the sql data from contacts table
SqlDataAccess sda("contacts.db");
QVariant list = sda.execute("select * from contact order by firstname");

// Add the data to the model
model->insertList(list.value<QVariantList>());

// Create a ListView control and add the model to the list
ListView *listView = new ListView();
listView->setDataModel(model);


Overview

Public Functions Index

SqlDataAccess (const QString &dbPath, QObject *parent=0)
SqlDataAccess (const QString &dbPath, const QString &connectionName, QObject *parent=0)
virtual ~SqlDataAccess ()
QSqlDatabaseconnection ()
DataAccessErrorerror () const
QVariantexecute (const QString &query)
QVariantexecute (const QString &query, const QVariantList &valuesByPosition)
QVariantexecute (const QString &query, const QVariantMap &valuesByName)
QVariantexecuteBatch (const QString &query, const QVariantList &valuesTable)
boolhasError () const

Public Functions

SqlDataAccess (

Constructs an SqlDataAccess object with the specified path to a database file and 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

SqlDataAccess (

Constructs an SqlDataAccess 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

virtual~SqlDataAccess ()

Destructor.

Since:

BlackBerry 10.0.0

QSqlDatabase connection ()

Returns the database connection object that's being used for data access.

Return:

A QSqlDatabase object representing the database connection.

See also:

QSqlDatabase

Since:

BlackBerry 10.0.0

DataAccessError error ()

Returns an error object for the most recent operation.

Return:

DataAccessError with error type and error message.

Since:

BlackBerry 10.0.0

QVariant execute (

Executes the specified SQL query.

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.

You can use DataAccess::hasError() to determine if the most recent operation resulted in an error, and you can use DataAccess::error() to retrieve information about the error that occurred.

Parameters
query

The SQL command to execute.

Return:

A QVariant that represents the result of the query. If the query is an SQL SELECT command, the return value is a QVariantList of QVariantMap objects. Otherwise, the return value is an empty QVariant.

Since:

BlackBerry 10.0.0

QVariant execute (

Executes the specified 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";
sda.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";
sda.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.

You can use DataAccess::hasError() to determine if the most recent operation resulted in an error, and you can use DataAccess::error() to retrieve information about the error that occurred.

Parameters
query

The parameterized SQL command to execute.

valuesByPosition

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

Return:

A QVariant that represents the result of the query. If the query is an SQL SELECT command, the return value is a QVariantList of QVariantMap objects. Otherwise, the return value is an empty QVariant.

Since:

BlackBerry 10.0.0

QVariant execute (

Executes the specified 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";
sda.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.

You can use DataAccess::hasError() to determine if the most recent operation resulted in an error, and you can use DataAccess::error() to retrieve information about the error that occurred.

Parameters
query

The parameterized SQL command to execute.

valuesByName

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

Return:

A QVariant that represents the result of the query. If the query is an SQL SELECT, the return value is a QVariantList of QVariantMap objects. Otherwise, the return value is an empty QVariant.

Since:

BlackBerry 10.0.0

QVariant executeBatch (

Executes the specified parameterized SQL query multiple times after binding each value set in the valuesTable to placeholders in the query string.

The parameterized SQL query is executed 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 examples describe the three 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);
sda.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;
valuesTable << QVariant::fromValue(contact1);
valuesTable << QVariant::fromValue(contact2);
valuesTable << QVariant::fromValue(contact3);
sda.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;
sda.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.

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 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):
  • sda.connection().transaction();

  • sda.execute(query1);

  • sda.execute(query2);

  • sda.executeBatch(batch1, values);

  • if sda.hasError()
    • sda.connection().rollback();

  • else
    • sda.connection().commit();

Parameters
query

The parameterized SQL command to execute.

valuesTable

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

Return:

A QVariant that represents the result of the query. If the query is an SQL SELECT, the return value is a QVariantList of QVariantMap objects. Otherwise, the return value is an empty QVariant.

Since:

BlackBerry 10.0.0

bool hasError ()

Returns indication of whether or not most recent operation ended with an error.

Return:

true if current error or false if none.

Since:

BlackBerry 10.0.0

Last modified: 2014-06-24



Got questions about leaving a comment? Get answers from our Disqus FAQ.

comments powered by Disqus