UIQ - How to Work with a Database

Often when handling different types of data, it is preferable to store the date in a persistent way so that it will exist after the application is closed. When the amount of information grows, a database and associated tables can be a very helpful option. This task can be very daunting at first sight and this is what this guide aims to ease.

Please note that this guide is only intended as a quick introduction to the subject. When you have your first database up and running you might want to look further into the section on DBMS where there is an abundance of information and documents about this subject.

It is also recommended to see the application example from Symbian in the SDK library in examples\SymbianCompatible\SysLibs\Dbms\Basics

More specifically what will be covered in this document is how to:

• Create a file in the file system for the database,

• Create a database in the file,

• Create a table,

• Add, edit and delete data with SQL commands,

• Retrieve data,

• Open an already existing database.

It is assumed that the reader has knowledge of SQL.

1.1.1 RFs Class

This is a handle to the File Server session. It should be used for all file system manipulation and the program may have arbitrarily many sessions open at the same time.

For more information see: RFs.

1.1.2 CFileStore Class

This is a permanent file store abstract base class. This class is used to derive more specialized file storage classes. When opening a file this class can be used directly, but if you create a new file the more specialized class has to be used.

1.1.3 CPermanentFileStore Class

Permanent file store. Derived from CFileStore.

Interface that supports full manipulation of a file store.

We are going to use this class for the file storage where the database will reside.

1.1.4 RDbStoreDatabase Class

Interface for a DBMS Store database. Used for manipulation of a database.

1.1.5 RDbView Class

When evaluating a SQL-query, the result is stored in this class. The query is prepared in this class and then evaluated.

1.1.6 CDbColSet Class

Manages a set of column definitions which describe a table or row set structure. Column definitions can be added and removed. The set can be iterated over, and ordinals for a column can be looked up.

1.1.7 TFileName Class

This is a buffer used for containing the name of a file. Notice that it is a typedefed TBuf<255>. This means that there might be an allocated overhead for the filename. If you only use one TFileName or if it is heap-based this might not be a problem, but if you use it on the stack then it can be somewhat wasteful. The standard stack size on Symbian OS is only 8Kb and a TFileName occupies 524 bytes regardless of how long the filename is.

So if you are in the process of minimizing your memory usage in the application an alternative can be to use a TBuf<NeededFileNameLength> instead.

Of course you must take precautions to make sure the buffer has the needed amount of allocated space. Also notice that the length of a filename and path may not exceed 256 characters.

See the UIQ API references for: RFs, CFileStore, CPermanentFileStore, RDbStoreDatabase, RDbView, CDbColSet and TFileName.

Use the following #include directives:

//Included for the file storage
#include <s32file.h>

//Included for the database
#include <d32dbms.h>

//Needed for getting the File Server session
#include <EikEnv.h>

This section describes the actual implementation in code.

2.2.1 The .rss file

It is recommended to create strings with values that are known in advance in the resource file and also the resource localization file (*.rls), when possible.

Declare the text resources in your resource (*.rss) file.

//The file name
RESOURCE TBUF r_app_filename_string { buf = STRING_r_app_filename_string; }
//The name of the database that is going to be created in the physical file.
RESOURCE TBUF r_app_database_string { buf = STRING_r_app_database_string; }
//The table name that we are creating
RESOURCE TBUF r_app_tablename_string { buf = STRING_r_app_tablename_string; }


//Below are the fields in the table
RESOURCE TBUF r_app_name_string { buf = STRING_r_app_name_string; }
RESOURCE TBUF r_app_surname_string { buf = STRING_r_app_surname_string; }
RESOURCE TBUF r_app_age_string { buf = STRING_r_app_age_string; }

2) Declare the text string that the text resource is using in your localizable (*.rls) string file.

rls_string STRING_r_app_filename_string "myDBase.db";
rls_string STRING_r_app_database_string "ExampleDBase";
rls_string STRING_r_app_tablename_string "Persons";

rls_string STRING_r_app_name_string "Name";
rls_string STRING_r_app_surname_string "Surname";
rls_string STRING_r_app_age_string "Age";

The use of resource strings that are declared in a localizable string file is the recommended way. See How to localise resources

2.2.2 Creating a file

3) After the resource file, the first order of things is to create the physical file in the file system. This is performed by getting a session of the file server, and then creating the file. Later on in section "2.2.7 Opening a database" it will be described how to check whether a file already exists so that it can be automated if the application should open or create the file.

//We need to include the compiled resource file
#include <ExampleApp.rsg>

...

//Getting the session to the file server
RFs fs = CEikonEnv::Static()->FsSession();

//Allocate a string with the filename
HBufC* KFileName = iEikonEnv->AllocReadResourceLC(R_APP_FILENAME_STRING);

//Creates the private path for a process on the specified drive.
User::LeaveIfError(fs.CreatePrivatePath(EDriveC));

TBuf<128> name;
//Creates the text defining the private path for a process.
User::LeaveIfError(fs.PrivatePath(name));

//Add the filename to the PrivatePath
name.Append(KFileName);

//Create the file in the file system
CFileStore store = CPermanentFileStore::CreateLC(fs, name, EFileRead|EFileWrite);

//Finally you need to set the UID type of the file store.
store->SetTypeL(store->Layout());

2.2.3 Creating a database

4) When we have a persistent file storage created we have to implement the actual database in the file.

//Create the database
RDbStoreDatabase database;
TStreamId id=database.CreateL(store);
                
//We have to set the root stream id in CFileStore
store->SetRootL(id);

//And finally commit the changes    
store->CommitL();

2.2.4 Creating a table

Now that we have a database created, it is necessary to create a data table for the data. We create a column set that is used to collect the columns before sending them into CreateTable().

The two first columns are going to be text columns (EDbColText).

//Create a column set
CDbColSet* columns = CDbColSet::NewLC();

//Allocate strings with the table and column names
HBufC* KTable = iEikonEnv->AllocReadResourceLC(R_APP_TABLENAME_STRING);

HBufC* KCol1 = iEikonEnv->AllocReadResourceLC(R_APP_NAME_STRING);
HBufC* KCol2 = iEikonEnv->AllocReadResourceLC(R_APP_SURNAME_STRING);
HBufC* KCol3 = iEikonEnv->AllocReadResourceLC(R_APP_AGE_STRING);

//Add the columns to the column set.
columns->AddL(TDbCol(KCol1,EDbColText));
columns->AddL(TDbCol(KCol2,EDbColText));
columns->AddL(TDbCol(KCol3,EDbColInt32));

//Create the table
User::LeaveIfError(database.CreateTable(KTable, *columns));

2.2.5 Adding, editing and deleting data

A common easy way to perform the tasks mentioned above is to create the appropriate SQL statement (see sections below). After this you only need to execute the statement.

Adding data

_LIT(KSqlStatement,"INSERT INTO Persons VALUES ('Camilla', 'Persson', '36')");
result = database.Execute(KSqlStatement, EDbCompareNormal);

Editing data

_LIT(KSqlStatement,"UPDATE Persons SET Name='Kristina' WHERE Surname='Persson'");
result = database.Execute(KSqlStatement, EDbCompareNormal);

Deleting data

_LIT(KSqlStatement,"DELETE from Persons WHERE Surname='Persson'");
result = database.Execute(KSqlStatement, EDbCompareNormal);

Also, it can be in appropriate to call RDbStoreDatabase::Compact() after deleting items to clean up in the database.

2.2.6 Retrieving data

Retrieving data is quite simple. You only need to create an RDbView object, prepare an SQL statement in the object and finally evaluate the statement. After this the RDbView will contain the data and is easily accessible.

RDbView DbView;

//Create the SQL statement
_LIT(KSqlStatement,"select * from ExampleDBase");

//Prepare the SQL statement
User::LeaveIfError(DbView.Prepare(database, TDbQuery(KSqlStatement, EDbCompareNormal)));

//Evaluate the statement
User::LeaveIfError(DbView.EvaluateAll());

/*
If we want to access all the rows in the RDbView.
- FirstL() places us at the first row of the RDbView.
- AtRow() indicates if we have reached the end of the RDbView. Return EFalse when the end is reached, otherwise ETrue.
- NextL() changes the row to the next one in the RDbView
*/

for(DbView.FirstL();DbView.AtRow();DbView.NextL())
    {
    //Now we can easily access the data
    TPtrC namePtr = DbView.ColDes(1);   
    TPtrC surnamePtr = DbView.ColDes(2);        
    TInt age = DbView.ColInt(3);
    
    // Do something with the data
    ...
    }
    
    

2.2.7 Open a database

Opening a database is really simple. Instead of CPermanentFileStore::CreateLC() and RDbStoreDatabase::CreateL(), which we used in section 2.2.2 and 2.2.3, we use the following code.

...

//Instead of CPermanentFileStore::CreateLC()
CFileStore store = CPermanentFileStore::OpenLC(fs, name, EFileRead|EFileWrite);

...

//Instead of RDbStoreDatabase::CreateL()
database.OpenL(store, store->Root());

....

It is very common to program an application so that it automatically decides if it can open an existing file and if not available, create a new one.

For information regarding this, see: How-to create/open

2.2.8 The cleanup stack

It is very important for you to remember to pop the items on the cleanup stack properly; otherwise a panic will be raised.

Also notice that objects are pushed on and popped off in a strict order: a series of PopAndDestroy() calls must occur in the reverse order of the PushL() calls. This is very important; otherwise you will get a panic message. This is because of the Cleanup Stack being in imbalance.

CleanupStack::PopAndDestroy(KCol3);
CleanupStack::PopAndDestroy(KCol2);
CleanupStack::PopAndDestroy(KCol1);
CleanupStack::PopAndDestroy(KTable);
CleanupStack::PopAndDestroy(Columns);
CleanupStack::PopAndDestroy(Store);
CleanupStack::PopAndDestroy(KFileName);

It is possible to pop and destroy all of the items by calling CleanupStack::PopAndDestroy() without any argument. However, it can be very hard to trace bugs this way. This is why you explicitly call all items in reverse order of their addition. For more information on the cleanup stack see: cleanup stack

DBMS

Database management system. The component that controls the organization, storage and retrieval of data (fields, records and files) in a database.

Heap

The heap is a reserved area of memory with a manager that allocates memory for an object's lifetime. The new operator gets memory from the heap manager and the delete operator returns it. For objects that only exist within a function, or generally any compound statement, stack memory is better as it has less allocation overhead.

Permanent File Store

A file based persistent store in which streams can be created and subsequently re-opened and changed.

SQL

Structured Query Language. A standard language for querying and modifying relational databases.

Stack

Objects, and other variables, that are declared within any compound statement are created on the stack. When execution leaves the compound statement, the object's destructor is called and the stack memory space reclaimed. So stack-based objects are useful for short term work, but if an object has to exist beyond the end of a statement, it must be created on the heap using the new operator.