Working with SQL

In addition to JSON for simple data storage, the Cascades framework lets you store data in a Structured Query Language (SQL) database. SQL is designed to make it easy to create and maintain relational databases. Information in an SQL database is stored in one or more tables, which are sets of values that are organized in columns. You can query these tables for specific information using a set of criteria, and you can insert, update, or delete data in the tables. For more information about SQL databases, see www.sql.org.

You can use the  SqlDataAccess class to retrieve data from or update data in an SQL database. This class shares much of the same functionality with the  JsonDataAccess class that's used to work with JSON data. However, SqlDataAccess uses just a single function called  execute() to manipulate SQL data. This function takes an SQL statement and executes it on the specified database.

The SqlDataAccess class supports two types of operations on SQL databases: loading SQL data and saving SQL data.

Loading SQL data

The SqlDataAccess class and other supporting classes are included in the bb::data library. To use these classes in your apps, you need to add the following line to the .pro file in your project:

LIBS += -lbbdata

A load operation reads data from an SQL table using the SELECT statement. You can describe the data that you want to load by using SQL clauses, such as FROM and WHERE. Here's how to use a SELECT statement to retrieve all items from a table called Employee in which the salary column contains a value that's greater than 40,000. The results are sorted in ascending order according to the employee's last name.

SELECT *
    FROM Employee
    WHERE salary > 40000
    ORDER BY lastName;

To load SQL data from a file, you first create an  SqlDataAccess object that's associated with the database file that you want to work with. Then, you can call  execute(), specifying the SQL SELECT statement that you want to run.

When a SELECT statement is specified, the execute() function returns a QVariantList, cast as a QVariant. Each item in the QVariantList is a QVariantMap, which represents a single record from the database. The QVariantMap contains the names and corresponding values of fields within the record. Similar to  JsonDataAccess, data models in Cascades support both QVariantList and QVariantMap, which makes it easy to add data that's retrieved using SqlDataAccess directly to a data model.

Here's how to execute the SQL statement above and display the results using a  GroupDataModeland  ListView. The GroupDataModel sorts the data by last name and first name. To learn more about GroupDataModel, see Sorted data models.

// Create the root page and list view
Page *root = new Page;
ListView *listView = new ListView;
 
// Create the data model, specifying sorting keys of "firstName" and "lastName"
GroupDataModel *model = new GroupDataModel(QStringList() << "firstName"
                                           << "lastName");
 
// Create an SqlDataAccess object and load the .db file. The QDir::currentPath()
// function returns the current working directory for the app.
SqlDataAccess sda(QDir::currentPath() + "/app/native/assets/employee.db");
QVariant list = sda.execute("SELECT * FROM Employee WHERE salary > 40000 ORDER
                             BY lastName");
 
// Insert the data into the data model. A QVariant(QVariantList) is returned
// from execute(), and you can provide a QVariantList to a data model directly
// by using insertList().
model->insertList(list.value<QVariantList>());
 
// Set the data model for the list view
listView->setDataModel(model);
 
// Set the content of the page and display it
root->setContent(listView);
app->setScene(root);

The file path that's specified when the SqlDataAccess object is created is constructed using the QDir::currentPath() function. This function returns the current working directory for an app, which is the application's sandbox directory. By adding a relative file path, you can specify the location of the .db file that you want to load. The path "/app/native/assets/" specifies the location of the assets folder in your project, which is where the employee.db file is located. To learn more about the directories that your app can access, see Working with the file system.

Data accessed from the assets folder is read-only and cannot be modified.

Saving SQL data

A save operation executes a single SQL statement that updates the data in the database in some way. You can execute a data manipulation language (DML) statement, such as INSERT, UPDATE, or DELETE, or a data definition language (DDL) statement, such as CREATE TABLE or CREATE INDEX. Here's how to use the UPDATE statement to change the salary of all employees in the Employee table with the last name "Jones" to 50000.

"UPDATE Employee
    SET salary = 50000
    WHERE lastName = 'Jones' ";

To save SQL data to a file, or update the SQL data within a file, first you create an  SqlDataAccess object that's associated with the database file that you want to work with. Then, you can call execute() and specify the DML or DDL statement that you want to run. When one of these types of statements is specified, the execute() function returns an empty QVariant.

Here's how to create an SQL database file that includes a table of employee records. Several employee records are added to the file using INSERT statements.

// Retrieve the working directory, and create a temporary .db file in that
// location
QDir home = QDir::home();
QFile file(home.absoluteFilePath("mySQLFile.db"));
 
// Open the file that was created
if (file.open(QIODevice::ReadWrite)) {
    // Create an SqlDataAccess object
    SqlDataAccess sda(home.absoluteFilePath("mySQLFile.db"));
 
    // Create a table called Employee in the database file
    sda.execute("CREATE TABLE Employee( firstName VARCHAR(50),
                 lastName VARCHAR(50), salary INT);");
     
    // Insert employee records into the table
    sda.execute("INSERT INTO Employee (firstName, lastName, salary)
                 VALUES ('Mike', 'Chepesky', 42000);");
    sda.execute("INSERT INTO Employee (firstName, lastName, salary)
                 VALUES ('Westlee', 'Barichak', 55000);");
    sda.execute("INSERT INTO Employee (firstName, lastName, salary)
                 VALUES ('Ian', 'Dundas', 47000);");
}

Handling errors

Because SqlDataAccess is very similar to JsonDataAccess, you can use the same approach to handle any errors that might occur during load or save operations. To learn about this approach, see Handling errors on the Working with JSON data page.

Last modified: 2014-06-26



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

comments powered by Disqus