• Coding SQL queries

• Creating and managing a database

• Error codes

• Data types

• SQL keywords used in Symbian OS

To query an existing database which has already been populated, create an RSqlDatabase object and call its RSqlDatabase::Open() function. Once you have finished querying, close the database by calling RSqlDatabase::Close().

_LIT(KCountryDb,"countries.db")

RSqlDatabase countriesDatabase;
TInt err;
err = countriesDatabase.Open(KCountryDb);
// code for SQL queries here
countriesDatabase.Close();

See also: Creating and managing a database.

A SELECT query passes data to the database and also retrieves data. Our example query

"SELECT name FROM countries WHERE population > :Value;"

interrogates a data table 'countries' for the data in the 'names' column if the value of the 'population' column in the current row is greater than the value of the passed-in parameter ':Value'. You therefore have to

• Create an RSqlStatement object

• Prepare the statement

• Index the two columns and the parameter

• Bind a value to the parameter

• Read the result

• Close the RSqlStatement object

Here is some example code.

_LIT(kQueryString,"SELECT name FROM countries WHERE population > :Value";);
_LIT(kName,"name");
_LIT(kPopulation,"population");
_LIT(kVal,":Value");

RSqlStatement myStatement;
TInt err;
err = myStatement.Prepare(countriesDatabase,kQueryString); // prepare
TInt nameIndex = myStatement.ColumnIndex(kName); // index
TInt populationIndex = myStatement.ColumnIndex(kPopulation);
TInt parameterIndex = myStatement.ParameterIndex(kVal);
err = myStatement.BindInt(parameterIndex,10); // bind
err = myStatement.Next(); // execute
TPtrC myData;
myData = myStatement.ColumnTextL(nameIndex); // read return data
err = myStatement.Close(); // close 

An INSERT query passes data to the database but does not retrieve data. This query inserts a new entry into our example 'countries' table.

INSERT INTO countries (name, population) VALUES (:value1, :value2);

In this case you need to perform fewer operations than in the previous example. Since we are not going to read data from a column we do not need to bind to it or use a read function. You need to:

• Create an RSqlStatement object

• Prepare the object

• Index the parameters

• Bind values to the parameters

• Execute the statement

• Close the RSqlStatement object

Note the use of RSqlStatement::BindText() to bind the text string "Italy" to its parameter and the use of RSqlStatement::Exec() to execute a statement which returns no data. Here is some example code.

_LIT(kQueryString,"INSERT INTO countries (name, population) VALUES (:value1, :value2);");
_LIT(kValue1,":value1);
_LIT(kValue2,":value2");
_LIT(kItaly,"Italy");

RSqlStatement myStatement;
TInt err;
err = myStatement.Prepare(countriesDatabase,kQueryString); // prepare
TInt index1 = myStatement.ParameterIndex(kValue1); // index
TInt index2 = myStatement.ParameterIndex(kValue2);
err = myStatement.BindText(index1,kItaly);
err = myStatement.BindInt(index2,59); // bind
err = myStatement.Exec(); // execute
err = myStatement.Close(); // close

You perform SQL queries containing UPDATE commands according to the same pattern as INSERT statements.

The simplest SQL statements to execute are DELETE statements because they neither pass nor return data. In fact, DELETE statements are so simple that you do not need to create an RSqlStatement to run them. The only thing you have to do is execute the query and you can do this with just the RSqlDatabase object, which has its own RSqlDatabase::Exec() function taking a query string as its parameter.

DELETE FROM countries WHERE population < 2;

If our database object 'countriesDatabase' is open, the code to execute a DELETE statement is this.

_LIT(kQueryString,"DELETE FROM countries WHERE population < 2;");
countriesDatabase.Exec(kQueryString);

It is possible to call a DELETE statement from an RSqlStatement object but it is computationally more expensive and you are recommended not to do so.

If you want a SELECT to return several rows from a data table, you must execute it within a loop. This example uses the same query as in Example 1 and consists of similar C++ code. The difference is that you call RSqlStatement::Next() as the condition of a while loop: the loop terminates when RSqlStatement::Next() fails to return data. In the body of the loop you place the call to the RSqlStatement::ColumnText() function, which now reads a different row of data for each call to RSqlStatement::Next().

_LIT(kQueryString,"SELECT name FROM countries WHERE population > :Value;");
_LIT(kName,"name");
_LIT(kPopulation,"population");
_LIT(kVal,":Value");

RSqlStatement myStatement;
TInt err;
err = myStatement.Prepare(countriesDatabase,kQueryString); // prepare
TInt nameIndex = myStatement.ColumnIndex(kName); // index
TInt parameterIndex = myStatement.ParameterIndex(kVal);
err = myStatement.BindInt(parameterIndex,10); // bind
while((err = myStatement.Next()) == kSqlAtRow) // execute
       {
       myData = myStatement.ColumnText(nameIndex); // read return data
       // do something with myData
       }
err = myStatement.Close(); // close 

If you want to add several rows to a data table you call an INSERT statement within a loop. In this example we insert rows containing the numbers 1 to 10 into our countries table. This time the call to the bind function takes place inside the body of the loop because we are writing the loop counter to the data table. The execute function is RSqlStatement::Exec() as in example 2. After each call to RSqlStatement::Exec() you must call RSqlStatement::Reset() to reverse the effect of the bind function before the loop iterates. In the following example, error checking has been omitted for the sake of simplicity: in practice, each value of the variable err would be subject to further checking.

_LIT(kQueryString,"INSERT INTO countries (rank) VALUES (:value1);");
_LIT(kValue,":value1");

RSqlStatement myStatement;
TInt err;
err = myStatement.Prepare(countriesDatabase,kQueryString); // prepare
TInt index1 = myStatement.ParameterIndex(kValue1); // index
for(TInt i=1;i<=10;i++)
       {
       err = myStatement.BindInt(index1,i);
       err = myStatement.Exec(); // execute
       err = myStatement.Reset()
       }
err = myStatement.Close(); // close

Our example SELECT statement retrieved integer data and held it in a TInt variable. But a data table field often contains large amounts of data which cannot be held in a variable: instead we have to hold it in buffers, pointers to memory or data streams.

To hold data in a buffer you

• Declare an RBuf object

• Determine the amount of memory needed using the RSqlStatement::ColumnSize() function

• Allocate that amount of memory

• Copy to the buffer with a call to the RSqlStatement::ColumnBinary() function

  In the following example, error checking has been omitted for the sake of simplicity: in practice, each value of the variable errwould be subject to further checking.

RSqlStatement myStatement;
TInt err;
...
while((err = myStatement.Next()) == KSqlAtRow)
    {
    RBuf myBuffer; 
    err = myBuffer.CreateL(myStatement.ColumnSize(columnIndex));
    err = myStatement.ColumnBinary(myColumnIndex,myBuffer);
    ...
    // process data
    myBuffer.Close()
    }

You can avoid copying to a buffer by declaring a TPtrC object pointing to an area in memory and holding the data there without making a local copy.

RSqlStatement myStatement;
TInt err;
...
while((err = myStatement.Next()) == KSqlAtRow)
    {
    TPtrC myData;
    TInt err;
    err = myStatement.ColumnBinary(myColumnIndex,myData);
    // process data
    }

You can read data to a data stream by declaring an RSqlColumnReadStream object and calling RSqlColumnReadStream::ColumnBinary().

RSqlStatement myStatement;
TInt err;
TInt myColumnIndex;
RSqlColumnReadStream myStream;
...
// Prepare an SQL statement that references large binary column data
...
CleanupClosePushL(myStream);
while((err = myStatement.Next()) == KSqlAtRow)
         {
        User::LeaveIfError(myStream.ColumnBinary(myStatement,myColumnIndex));
        TInt size = myStatement.ColumnSize(myColumnIndex);
        ...
        // Read the column data into a buffer in one chunk. In practice
        // you might want to retrieve the data in smaller portions.
        RBuf buf;
        buf.CreateL(size);
        CleanupClosePushL(buf);
        myStream.ReadL(buf,size);
        ...
         // Process data
        ...
        // Close the buffer
         CleanupStack::PopAndDestroy();
        }
// Close the stream
    CleanupStack::PopAndDestroy();

Data streams can also be used to write to a database. You declare an RSqlParamWriteStream object and call one of its bind functions.

RSqlStatement myStatement;
TInt err;
TInt myParameterIndex;
RSqlParamWriteStream myStream;
RBuf buf;
...
// Prepare an SQL statement that references a large text parameter
...
CleanupClosePushL(myStream);
while((err = myStatement.Next()) == KSqlAtRow)
        {
        User::LeaveIfErrormyStream.BindTextL(myStatement,myParameterIndex));
        ...
        // ensure that buf has appropriate data
        ...
        // write out the parameter data
        myStream.WriteL(buf);
        ...
        // Commit the stream
        myStream.CommitL();
        }
// Close the stream        
CleanupStack::PopAndDestroy();

A scalar query is a SELECT statement which refers to several data fields but returns a single result, for instance a COUNT query on a database column. Symbian OS provides a class TSqlScalarFullSelectQuery, which you are recommended to use to make scalar queries; this is more efficient than RSqlStatement.

TSqlFullSelectQuery has the peculiarity that a class instance is declared with a parameter that refers to the database to be queried. The class supplies a number of functions taking SELECT statements as parameters: which one you use depends on the data type of the return value.

RSqlDatabase myDatabase;
_LIT(kQueryString,"SELECT COUNT (*) FROM myTable");
...
TSqlScalarFullSelectQuery myFullSelectQuery(myDatabase);
TInt recCount = myFullSelectQuery.SelectIntL(kQueryString));

To create a database you declare an RSqlDatabase object and call its Create() function with an appropriate name as argument. This enables you to use the RSqlDatabase object as a handle to the actual database. The procedure for creating, opening and closing a database is slightly different for secure and non-secure databases.

To create a non-secure database you declare an RSqlDatabase object and call RSqlDatabase::Create() with the database name as argument. An optional second parameter, if specified, should point to a descriptor containing information about configuration. A non-secure database is created open: as soon as it is created you can start passing SQL statements to it. You close a database with its Close() function and reopen it with its Open() function.

RSqlDatabase myDatabase;
_LIT(kPath,"ExampleDatabase.db");
myDatabase.Create(kPath,"");
// code to populate the database here
myDatabase.Close();
myDatabase.Open();
// code to query or update the database here
myDatabase.Close();

A database security policy is a specification of user permissions on a database. It is held in an RSqlSecurityPolicy object which you must create before the database which it refers to.

To create a database security policy you must first create a general security policy, a TSecurityPolicy object which comes with null default values. The TSecurityPolicy object is passed to the Create() function of the RSqlSecurityPolicy object and this in turn is passed to the Create() function of the database object. Between the calls to the two Create() functions you have to set the required permissions on the RSqlSecurity policy object. There are three kinds of permissions and each one requires you to declare a separate TSecurityPolicy object, so that you make up to four such declarations in all.

The three kinds of database permissions are 'read', 'write' and 'schema', and they apply to the entire database not to individual tables. The 'schema' permission allows you to create and drop database tables, while the other two allow you to read from and write to existing tables only. They are represented by the three constants

• RSqlSecurityPolicy::ESchemaPolicy

• RSqlSecurityPolicy::EWritePolicy

• RSqlSecurityPolicy::EReadPolicy

The first argument of RSqlSecurityPolicy::SetDBPolicy() is one of these constants; the second argument is the associated TSecurityPolicy object. It is this function which must be called to set permissions between the calls to the two Create() functions of the RSqlSecurityPolicy and RSqlDatabase objects. A security policy once set cannot be changed, but it can be retrieved using the GetSecurityPolicy() function of the database object.

The example code for creating a secure database with read, write and schema permissions looks like this.

TSecurityPolicy defaultPolicy;
RSqlSecurityPolicy securityPolicy;
RSqlDatabase database;
TInt err;
securityPolicy.Create(defaultPolicy);
TSecurityPolicy schemaPolicy;
TSecurityPolicy writePolicy;
TSecurityPolicy readPolicy;
…
err=securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy);
// <check for error>
err=securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy);
// <check for error>
err=securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy);
// <check for error>
err=database.Create(kDatabaseName, securityPolicy);
// <check for error>
securityPolicy.Close();

To manage your databases you also need to know how to attach, detach, copy and delete them.

Attaching a database means making it appear to be part of another database, which is designated the primary database, so that a single SQL statement can be used to query them both. Attachment is a virtual operation which does not modify the file system: each database retains its own configuration and security policy. A primary database can have several attached databases (Symbian OS allows a maximum of 10). An attach operation is reversed by a detach operation. The primary database must be opened before any attachment takes place and must not be closed until all other databases have been detached from it.

You attach a database by calling the RSqlDatabase::Attach() function of the primary database with two parameters: the file name and logical name of the attached database. The logical name is a name of your choice and is used in SQL statements as a prefix to the table names of the attached database. For instance a table 'userID' in an attached database with the logical name 'userDB' is accessed as 'userDB.userID' (a query on 'userID' will also succeed but only if the primary database has no table of that name). If you query the primary database, it will now appear to contain all its own data and also the data held in the attached database. You perform a detachment by calling the RSqlDatabase::Detach() function of the primary database with the logical name of the attached database as its parameter. The example code for attaching and detaching a database looks like this.

myDatabase.Open();
_LIT(kFileName,"T:\DatabaseFolder\ExampleDatabase.db")
_LIT(kLogicalName,"myAttachedDatabase")
myDatabase.Attach(kFileName,kLogicalName);
// code to query the combined databases here
myDatabase.Detach(kLogicalName);
myDatabase.Close()

A database object of the class RSqlDatabase has functions RSqlDatabase::Copy() and RSqlDatabase::Delete(). Both are static functions: that is, they are not used to copy or delete the calling database object but the one supplied as a parameter of the function. Copy() takes two parameters, the source database file name and the target database file name. Either both databases must be secure or both must be non-secure. If they are both non-secure then there are no restrictions on copying. However, if they are both secure, only the application which created the source database may copy it to the target.

You can configure a database using a string called a configuration descriptor: this is an example of one.

page_size=1000;cache_size=1024;encoding=UTF-8

A configuration descriptor contains three parameters. The page size is the size of the memory pages to be used and must be a multiple of 512 up to a maximum of 4096. The cache size is the size of the cache expressed as the number of pages. The encoding is either UTF-8 or UTF-16. If a database is not explicitly configured it takes a default configuration. A configuration descriptor may be used as the last argument of the Create() function of a database object, in which case it overrides the default configuration. It may also be used as the last parameter of the Open() function of a database object, but only to modify the cache size: the page size and encoding should be omitted.

Several database objects may be connected to the same physical database at the same time. While a write statement is modifying data, the data is said to be uncommitted until the statement has finished execution, at which point it becomes committed. If at the same time a separate read statement is retrieving uncommitted data there is a risk that it will return invalid or inconsistent query results. By default, a write statement blocks all other read and write statements from executing until the data is committed. However, it is sometimes desirable to override this provision in order to enhance the performance of a client application. In this case you alter what is called the isolation level of the client database object by calling its RSqlDatabase::SetIsolationLevel() function. This function has two possible parameters: the constant EReadUncommitted which allows you to read uncommitted data, and the constant ESerializable which restores the default isolation level.

Databases manage the problem of data corruption by logging the transactions taking place, and the log files occupy memory. This has the consequence that a DELETE statement might temporarily increase and not decrease the amount of disk space being used. To ensure that sufficient memory is available for DELETE to free up more memory, an RSqlDatabase object has memory management functions. They are ReserveDriveSpace(), FreeReservedSpace(), GetReserveAccess() and ReleaseReserveAccess().

Identifier Value KSqlErrGeneral -311 KSqlErrInternal -312 KSqlErrPermission -313 KSqlErrAbort -314 KSqlErrBusy -315 KSqlerrLocked -316 KSqlErrNoMem -317 KSqlErrReadOnly -318 KSqlErrInterrupt -319 KSqlErrIO -320 KSqlErrCorrupt -321 KSqlErrNotFound -322 KSqlErrFull -323 KSqlErrCantOpen -324 KSqlErrProtocol -325 KSqlErrEmpty -326 KSqlErrSchema -327 KsqlErrTooBig -328 KSqlErrConstraint -329 KSqlErrMismatch -330 KSqlErrMisuse -331 KSqlErrNoLFS -332 KSqlErrAuthorization -333 KSqlErrFormat -334 KSqlErrRange -335 KSqlErrNotDb -336 KSqlErrStmtExpired -360

Data type Symbian OS data type Bind function Column function Integer ESqlInt BindInt() ColumnInt() 64 bit integer ESqlInt64 BindInt64() ColumnInt64() Real ESqlReal BindReal() ColumnReal() Text ESqlText BindText() ColumnText() Binary ESqlBinary BindBinary() ColumnBinary() Null ESqlNull

These are the SQL keywords which may be used with an RSqlStatement object. Symbian OS does not implement the PRAGMA command. The ATTACH and DETACH commands are deprecated in Symbian OS in favour of the database class functions explained earlier. Statements containing the ATTACH and DETACH commands will fail if applied to a secure database.

• Data definition language (DDL)

• Data modelling language (DML)

• Other keywords

• ALTER TABLE

• CREATE INDEX

• CREATE TABLE

• CREATE TRIGGER

• CREATE VIEW

• CREATE VIRTUAL TABLE

• DROP INDEX

• DROP TABLE

• DROP TRIGGER

• DROP VIEW

• BEGIN TRANSACTION

• COMMIT TRANSACTION

• DELETE

• END TRANSACTION

• INSERT

• ROLLBACK TRANSACTION

• SELECT

• UPDATE

• ANALYZE

• ATTACH DATABASE

• COPY

• DELETE

• DETACH DATABASE

• EXPLAIN

• ON CONFLICT

• REINDEX

• REPLACE

• VACUUM