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.
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
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.
Click on the following link to download the example: SqlExample.zip
Click: browse to view the example code.
RSqlDatabase
RSqlStatement
RSqlColumnReadStream
RSqlParamWriteStream
RSqlSecurityPolicy
TSqlScalarFullSelectQuery
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.