SQL Overview

Provides an SQL database service to applications and other Symbian OS software components.

The interface provides the behaviour for creating, modifying and querying relational databases using SQL query language statements. It also provides for securing databases and maintaining data integrity.

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

Applications use the client facing API, implemented as a client-side DLL. The purpose of the API is to :

• Check application requests

• Marshall queries

• Submit queries to the server side

• Collect results and error codes, and relay them back to the originator of the request.

The server side is an executable running in its own process and incorporates an SQL engine. The server is not directly acessible to client applications, and it can only be reached via the client-side DLL.

• Database

• Security policies

• Isolation levels

• SQL statements

• Using SQL in Symbian OS

An SQL database is represented by an instance of the RSqlDatabase class, which forms a handle to the database.

A database can be either secure or non-secure. A secure database is one that is created with a collection of security policies to govern access to the database and to individual database objects, i.e. database tables.

The class provides functionality to:

• create a secure or a non-secure new database : RSqlDatabase::Create()

• open an existing database : RSqlDatabase::Open()

• close a database : RSqlDatabase::Close()

• copy a database : RSqlDatabase::Copy()

• delete a database : RSqlDatabase::Delete()

• attach another database to a database that is already open so that the attached database can be accessed through the primary database : RSqlDatabase::Attach()

• detach a database that was previously attached : RSqlDatabase::Detach()

The RSqlDatabase class description provides additional detail.

Databases in Symbian OS have a naming convention; the naming convention for secure database is different to the naming convention for a non-secure database.

Naming convention for a secure database

The naming convention for a secure database is of this form:

X:[<UID>]<database-name>.db

• The first two characters are a drive name: if omitted the system drive is used by default

• <UID> is the user ID of the client creating the database: if this is specified wrongly a call to RSqlDatabase::Create() will fail

• <database-name> is the file name of your choice

• .db is the recommended file extension

Naming convention for a non-secure database

This takes the form:

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

• The first two characters are a drive name: if omitted the system drive is used by default

• <path> is the file path

• <database-name> is the file name of your choice

• .db is the recommended file extension

To create a secure database, a collection of security policies are needed to govern access to that database. A security policy is a specification of user permissions and is encapsulated by a TSecurityPolicy type object. The collection of security policies is contained in an RSqlSecurityPolicy object, and this must be created before the database to which it refers is created. Once a database has been created, it is not possible to change security policies.

The RSqlSecurityPolicy class description provides additional detail.

A client of database can specify how it wants a transaction to interact with other transactions that may be running concurrently. The various ways in which transactions can interact (i.e. how one transaction can affect another) are referred to as "transaction isolation levels", and are defined by the values of the TIsolationLevel enum. A client specifies this by calling RSqlDatabase::SetIsolationLevel().

In general, SQL statements are represented by a RSqlStatement object. The class provides functionality to execute all types of SQL statements; this includes SQL statements with parameters.

SQL statements can also be executed using the RSqlDatabase::Exec() functions, but the behaviour of these functions is more limited than that provided by RSqlStatement because:

• they do not return any records if the SQL statement type is "SELECT"

• if an SQL statement contains one or more parameters, they give them default NULL values.

SQL statements can be prepared from 16-bit strings (i.e. taking a TDesC type parameter) or 8-bit strings (i.e. taking a TDesC8 type parameter).

SQL statements can be executed synchronously, or asynchronously, depending on the requirements of the application. The asynchronous versions of RSqlStatement::Exec() and RSqlDatabase::Exec() can be identified by the TRequestStatus reference in the signature.

See also: High level asynchronous service handling.

Using SQL in Symbian OS requires the use of objects and functions that allow you to execute SQL statements such as:

SELECT name FROM countries WHERE population > :Value;

Your choice of objects and functions depends on the content of the query, but you will normally need a selection of the following functionality provided by RSqlStatement. Most functions return an error code to indicate success or failure; KErrNone always indicates success.

Preparation

You can use RSqlStatement::Prepare() to prepare an SQL statement. This takes a reference to an open database and a descriptor containing the SQL string.

Indexing

An SQL statement can contain constants (like name in the example above) which refer to columns of the data table being queried and parameters (like :Value in our example) into which values are substituted during the execution of the query. Meaningful names like name and :Value are labels for an underlying representation in which columns are indexed (numbered) in order of their appearance in the query and so, separately, are parameters.

The list of column names begins with 0 and the list of parameters begins with 1: thus in the above example, country_name has the column index 0 and :Value has the parameter index 1. Parameter names must begin with a colon ":". If your SQL statement contains references to columns or parameters, you must explicitly tell the interface to index them: you do so by calling RSqlStatement::ColumnIndex() and RSqlStatement::ParameterIndex().

ColumnIndex() takes a column name as input and returns the column index as an integer.

ParameterIndex() takes a parameter string as input and returns the parameter index as an integer.

Binding values to parameters

If your SQL statement contains parameters, you assign a value to them, or rather to their indexes, by calling the appropriate bind member function of RSqlStatement function. There are several bind functions, and which one you use depends on the data type of the value: for example, an integer is bound using BindInt(). A list of available data types is given in the appendix of this document.

The parameters taken by a bind function are:

• the integer index of the parameter you are binding to, i.e. the output of ParameterIndex()

• the value you are assigning to the parameter.

Executing the statement

You can now execute the query. An RSqlStatement object has two functions for doing this: Exec() and Next(). You use Exec() with queries containing commands like INSERT which do not return data, and Next() with queries containing commands like SELECT which do. An SQL SELECT query returns a data set holding rows of the database which match the query. A call to a Next() function wrapped around an SQL SELECT query returns a single row of the data set: a second call to Next() returns the next row, and so on until the data set has been exhausted. If you want the entire data set you must call Next() in a loop. Note that an Exec() function wrapped around a SELECT query returns no data.

Note that a variant form of the Exec() function takes a TRequestStatus object as argument and executes asynchronously.

Reading the result of the statement

When a call to RSqlStatement::Next() returns the data held in the columns specified in the query string, you read the data by calling the column function for the appropriate data type. For example, if you used Next() to execute a query on columns containing integer data, the RSqlStatement::ColumnInt() function reads the current value. Text is read with a call to RSqlStatement::ColumnText() and so on: the full list of column fuunctions is given in the appendix to this document.

A column functions takes a column index as its parameter and returns the data held in that column.

Resetting parameter values

You may want to use your SQL query several times in a session with different values; for example, you might use our example query to find rows with Fld1 > 2 and then Fld1 > 4. This means you have to bind new values to parameters, and to do that you must first reset the statement with a call to RSqlStatement::Reset().

Closing the statement

As soon as you have finished using an RSqlStatement object to execute queries you should free up resources by calling its RSqlStatement::Close() member function.