SQL DB overview

Purpose

The SQL DB library allows developers to access and query relational databases using the SQL query language. It also provides functionality for securing databases and maintaining data integrity.

Intended audience:

This document is intended to be used by Symbian platform licensees and third party application developers.

SQL DB library details

The DLL that provides the functionality and the library to which your code must link is identified below.

Architectural relationship

The SQL database service is implemented using the standard Symbian platform client-server model.

Description

The SQL DB library allows you to access and query databases.

Accessing and querying databases

By accessing a database is meant creating, opening and closing it. By querying a database is meant any read or write operation performed by an SQL query, not just data retrieval.

Accessing databases

Database access operations include

• creating and deleting databases

• creating security policies and applying them to databases,

• attaching and detaching databases to each other, and

• opening and closing databases.

Querying databases

Database query operations include:

• preparing an SQL query for use within Symbian platform,

• indexing variables inside the query to correspond to data table columns,

• binding values to variables inside the query, and

• executing the query,

• using the result of the query, and

• closing the query.

Key SQL DB classes

The key classes that make up the SQL DB component are as follows:

1. RSqlDatabase

   This class represents a database and provides the functionality to:

   • creates a secure shared, non-secure shared or private database

   • opens and closes named (and previously created) databases

   • attaches/detaches existing databases

   • copies named databases

   • deletes named databases

   • retrieves the security policy

   • sets the isolation level (currently supported levels - EReadUncommited, ESerializable)

   • Executes SQL statements (however, this functionality is intended for SQL statements that do not return data rows – see RSqlStatement for executing queries that return data).

   • retrieves database file size and free space

   • compacts the database

   • manages reserved drive space

   The main responsibilities of this class are:

   1. create a secure or a non-secure new database,

   2. open an existing database,

   3. close a database,

   4. copy a database,

   5. delete a database,

   6. attach a secondary database to a primary database,

   7. detach a secondary database from a primary database.

2. RSqlStatement

   This class represents an SQL statement and provides the functionality to:

   1. preparing SQL statements for execution

   2. resetting previously prepared SQL statements

   3. closing SQL statements

   4. binding substitutable parameters into prepared SQL statements

   5. executing prepared SQL statements (two methods provided: (a) Next() for SELECT statements and (b) Exec() for everything else)

   6. iteration through the result set.

   7. Retrieving the column count of the retrieved record(s)

   8. Retrieving the type, declared type, index, size and value of a particular column of the retrieved record(s)

   The main responsibilities of this class are:

   • prepare a statement,

   • index parameters of a statement to columns of a data table,

   • bind values to parameters of the statement,

   • reset the values of parameters of the statement,

   • execute the statement,

   • read the result of the statement,

   • close the statement.

3. RSqlSecurityPolicy

   This class holds the collection of user permissions that make up a security policy for a secure database.

Using SQL DB

The main uses of the SQL DB are:

• access a database (create or opening an existing database),

• give a database a security policy, if needed,

• query a database (select, insert, modify and delete data),

• close a database (and possibly delete it).