Categories: Symbian C++ | Code Examples | Files/Data | S60 3rd Edition, Feature Pack 2

This page was last modified 07:16, 2 May 2008.

From Forum Nokia Wiki

Knowledge Base Home

Overview

This code snippet shows how the SQL API's RSqlDatabase handle class allows the user to interact with the SQL server and create either a non-secure or secure database. Non-secure databases are databases that can be accessed and updated by any program. Secure databases have a defined static policy and can be accessed only by authorized clients with specific capabilities. To create a secure database, the UID of the application that creates the database and security policy are needed.

Another possibility to create a new database is to make a copy of an existing database with the method RSqlDatabase::Copy(). Databases can be deleted using the method RSqlDatabase::Delete().

This snippet can be self-signed.

MMP file

The following libraries are required:

LIBRARY  euser.lib
LIBRARY  sqldb.lib

The following capabilities are needed to test the secure database in this example:

CAPABILITY ReadUserData
CAPABILITY WriteUserData

Source file

#include <e32base.h>
#include <SqlDb.h>
 
void CreateDatabasesL()
  {
//== Create non-secure database ==
  RSqlDatabase database;
 
  _LIT(KNonSecureDbName, "\\nonsecure.db");
  TInt error = database.Create(KNonSecureDbName);
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
 
    //Create MOVIES table
    _LIT(KSqlCreateTableMovies, "CREATE TABLE MOVIES(ID INTEGER, TITLE TEXT, 
YEAR SMALLINT, DURATION SMALLINT, COLOR BOOLEAN)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMovies));
 
    _LIT(KSqlCreateTableMoviesIndex, "CREATE UNIQUE INDEX IDX ON MOVIES (ID)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMoviesIndex));
 
    //Insert data into MOVIES table
    _LIT(KSqlInsertIntoTableMovies, "INSERT INTO MOVIES(ID, TITLE, YEAR, 
DURATION, COLOR) VALUES(1, 'numberOne', 1999, 120, 'true')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableMovies));
 
    CleanupStack::PopAndDestroy(); //database
    }
  else
    {
    //creating database failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
//== Create secure database ==
 
  TSecurityPolicy defaultPolicy;
  RSqlSecurityPolicy securityPolicy;
 
  // Create security policies container object using a default security policy.
  securityPolicy.Create(defaultPolicy);
  CleanupClosePushL(securityPolicy);
 
  // Set up policy to apply to database schema and assign it
  TSecurityPolicy schemaPolicy(ECapabilityReadUserData, ECapabilityWriteUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy,
 schemaPolicy));
 
  // Set up policy to apply to write activity on the database and assign it
  TSecurityPolicy readPolicy(ECapabilityReadUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
 
  // Set up policy to apply to write activity to the database table named
  // "MOVIES" and assign it
  TSecurityPolicy tablePolicy(ECapabilityWriteUserData);
 
  _LIT(KMoviesTableName, "MOVIES");
  User::LeaveIfError(securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KMoviesTableName,
      RSqlSecurityPolicy::EWritePolicy, tablePolicy));
 
  // Create the database, passing the security policies
  _LIT(KSecureDbName, "[E80000AF]secure.db");  //Note: [UID3]
  error = database.Create(KSecureDbName, securityPolicy);
 
  //close RSqlSecurityPolicy object.
  CleanupStack::PopAndDestroy(); //securityPolicy
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
    //Create BOOKS table
    _LIT(KSqlCreateTableBooks, "CREATE TABLE BOOKS(ID INTEGER NOT NULL, 
TITLE TEXT, AUTHOR TEXT)");
    User::LeaveIfError(database.Exec(KSqlCreateTableBooks));
 
    //insert data
    _LIT(KSqlInsertIntoTableBooks, "INSERT INTO BOOKS(ID, TITLE, AUTHOR) 
VALUES(1, 'numberOne', 'author1')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableBooks));
 
    CleanupStack::PopAndDestroy(1); //database
    }
  else
    {
    //database creation failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
  }

Postconditions

Two databases, nonsecure.db and secure.db, have been created and the example rows are inserted into the databases.

nonsecure.db after creation:

TABLE:MOVIES
ID|TITLE    |YEAR|DURATION|COLOR
1 |numberOne|1999|120     |true

[UID3]secure.db after creation:

TABLE:BOOKS
ID|TITLE    |AUTHOR
1 |numberOne|author1

See also

• CS000926 - Using SQL API for attaching and detaching databases
• CS000927 - Using SQL API with SQL statements which do not return data
• CS000928 - Using SQL API with SQL statements which return data
• CS000929 - Using SQL API with scalar queries
• CS000930 - Using SQL API with data streams