SqlExample: +Creating and Querying an SQL 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.

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.

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.

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.