Collapse all Symbian^3 Product Developer Library

SqlExample: Creating and Querying an SQL Database

This example application demonstrates the use of the SQL API for creating and querying a database.

Description

Creating the database

You can create two types of databases; secure and non-secure.

Non-secure database: A database that can be accessed and updated by any program since no security policy is provided.
Secure database: A database with static policy defined and therefore can be accessed by authorized clients with specific capabilities.

Creating a non-secure database

The application first creates an RSqlDatabase object and creates a non-secure SQL database using the RSqlDatabase::Create() function. The database is then closed. Finally the database is deleted using the RSqlDatabase::Delete() function.

The format for naming the non-secure database would be the following:

X:<path><database-name>.db

where, X is the writable drive on the device or the emulator.

If path is not specified, it returns the KErrArgument error, and if the specified path is not writable by SQL server, the application returns KErrPermission error. In this example, the application will create the database in its own data area, pass a handle to the database to enable the data manipulation by the SQL server.

Note: You cannot create two databases with the same name in a location

Related APIs

RSqlDatabase - A handle to a SQL database.
RSqlDatabase::Create()
RSqlDatabase::Delete()

Creating a secure database

To create a secure database, you need to provide the following:

UID of the application
security policy

UID

You need to use the UID of the example application to name the database.

The format for naming the database would be the following:

X:<UID><name>.db

where,

X is the drive in which the database is created
UID is the UID of the application that creates the database
name is any valid database name. You should not specify the complete path of the database.

To successfully create and use a secure database, you must have appropriate capabilities such as READUSERDATA, WRITEUSERDATA. The example application uses NETWORKCONTROL capablity along with above specified capabilities.

Note: If the given UID does not match with that of client application (in this case, the SQL example application) the attempts to create the database fails.

Security policy

To create a secure database, you need to initially set up a container that has a set of security policies (TSecurityPolicy), and pass it to the RSqlDatabase::Create() function. The TSecurityPolicy object defines what capabilities the calling application must have in order to perform a specific database operation. To cross check if the database security policy matches with the policy used when the database was created, the RSqlDatabase::GetSecurityPolicy() function is called.

Related APIs

RSqlDatabase::Create()
RSqlDatabase::GetSecurityPolicy()
TSecurityPolicy - Class representing a generic security policy

Copying one database to another

The example copies one database to another using the RSqlDatabase::Copy() function. If the database was created using a specific UID, then only the application with same UID, can perform the copy operation.

Note: In this example, copy function is essentially a file copy as the client does a copy within its data cage. The copy operation should ideally fail if the destination database already exists. An application with appropriate permissions can use the file system to copy the database.

Related APIs

RSqlDatabase::Copy()

Attaching the database

The example application then demonstrates attaching two databases. In this example, a non-secure database is attached to a secure database. The attached database is later read from and written to, and the secure database is also written to before the two databases are deleted using the RSqlDatabase::Delete() function.

Related APIs

RSqlDatabase::Delete()

Querying the database

The example then demonstrates how to prepare and execute a query.

Simple query

A simple query statement is prepared and executed using the CSqlExample::DataTypesQueryL() function.

Query with a large parameter and writing using streaming

A query with a large parameter is prepared, executed and the results are written to RSqlParamWriteStream stream. A table containing fields of data types; integer, 64-bit integer, float, text and binary is created. It inserts two records into the table and implements the TSqlScalarFullSelectQuery function for 64 bit integer (F2) and text (F4) fields and checks the returned value. The query for the data type which the column does not hold is executed, only to show that this is possible. For example, if a column holding the integer value 1000 is queried as real, it would return 1000.00.

Related APIs

RSqlParamWriteStream - The write stream interface.
TSqlScalarFullSelectQuery - TSqlScalarFullSelectQuery interface is used for executing SELECT sql queries, which return a single row consisting of a single column value.

Query returning data being read using streaming

The example lastly demonstrates how to prepare and execute a query which returns the data, and read that data from the data stream (RSqlColumnReadStream). The read stream interface class is used for reading a large amount of binary or text data from the column.

Related APIs

RSqlColumnReadStream - The read stream interface.

Download

Click on the following link to download the example: SqlExample.zip

Click: browse to view the example code.

Class summary

Build

The Symbian build process describes how to build an application.

The Sql example builds an executable called sqlexample.exe in the standard location (\epoc32\release\winscw\ <build_variant> for CodeWarrior). After launching the executable depending on the emulator, you may need to task away from the app launcher or shell screen to view the console.

Parent topic: Persistent Storage Example Code

Related concepts

SQL Tutorials