This example application demonstrates the use of the SQL API for creating and querying a database.
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.
Related APIs
RSqlDatabase - A handle to a SQL database.
Creating a secure database
To create a secure database, you need to provide the following:
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.
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
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.
Related APIs
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
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.
Click on the following link to download the example: SqlExample.zip
Click: browse to view the example code.
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.