×
Namespaces

Variants
Actions

Database Example

From Nokia Developer Wiki
Jump to: navigation, search
Article Metadata
Article
Created: symbianyucca (09 Apr 2007)
Last edited: hamishwillee (23 Jan 2012)

The CMyDBClass class illustrates how to use the RDbNamedDatabase API with your own application. This example code is made for S60 3rd Edition but if you want to use it on the older platforms just remove the line that utilizes RFs::PrivatePath() method and replace it with a constant path definition.

As shown in the construct method, the database is opened if it exists and created if it doesn't. To change it to accommodate your own data you need to change the table definition in the CreateTableL() method. Also remember to change other functions to use the same table definition.

Library required:

LIBRARY   efsrv.lib //RFs
LIBRARY edbms.lib //RDbNamedDatabase,RDbView


NamedDataBase.cpp

#include "NamedDataBase.h"
 
CMyDBClass::~CMyDBClass()
{
iItemsDatabase.Close();
iFsSession.Close();
}
 
void CMyDBClass::ConstructL()
{
User::LeaveIfError(iFsSession.Connect());
 
TFileName DBFileName;
 
//create private path
User::LeaveIfError(iFsSession.CreatePrivatePath(RFs::GetSystemDrive()));
// private path with no drive on it
iFsSession.PrivatePath(DBFileName);
 
TFindFile PrivFolder(iFsSession);
// find out the drive
if(KErrNone == PrivFolder.FindByDir(DBFileName, KNullDesC))
{
DBFileName.Copy(PrivFolder.File());
DBFileName.Append(KtxDatabaseName);
 
if(BaflUtils::FileExists(iFsSession, DBFileName))
{
User::LeaveIfError(iItemsDatabase.Open(iFsSession,
DBFileName));
ReadDbItemsL(iItemArray);
}
else
{ // no database exists so we make one
User::LeaveIfError(iItemsDatabase.Create(iFsSession,
DBFileName));
// and will create the onlt table needed for it
CreateTableL(iItemsDatabase);
}
}
}
 
void CMyDBClass::CreateTableL(RDbDatabase& aDatabase)
{
// Create a table definition
CDbColSet* columns=CDbColSet::NewLC();
 
// Add Columns
TDbCol id(NCol0,EDbColInt32);
// automatic indexing for items,it is our key field.
id.iAttributes=id.EAutoIncrement;
columns->AddL(id);
 
columns->AddL(TDbCol(NCol1, EDbColText,100));
columns->AddL(TDbCol(NCol2, EDbColText,100));
 
// Create a table
User::LeaveIfError(aDatabase.CreateTable(KtxtItemlist, *columns));
 
// cleanup the column set
CleanupStack::PopAndDestroy(columns);
}
 
void CMyDBClass::ReadDbItemsL(RArray<TExampleItem>& aItemArray)
{
aItemArray.Reset();// first reset the array
 
TFileName QueryBuffer;
// just get all columns & rows
QueryBuffer.Copy(_L("SELECT * FROM "));
QueryBuffer.Append(KtxtItemlist);
 
RDbView Myview;
Myview.Prepare(iItemsDatabase,TDbQuery(QueryBuffer));
CleanupClosePushL(Myview);
Myview.EvaluateAll();
Myview.FirstL();
 
// Just delete one instance of the message
while(Myview.AtRow())
{
Myview.GetL();
 
TExampleItem NewItem;
NewItem.iIndex = Myview.ColInt(1);
NewItem.iName.Copy(Myview.ColDes(2));
NewItem.iValue.Copy(Myview.ColDes(3));
 
aItemArray.Append(NewItem);
Myview.NextL();
}
 
CleanupStack::PopAndDestroy(1); // Myview
}
 
void CMyDBClass::DeleteFromDatabaseL(TInt& aIndex)
{
TFileName QueryBuffer;
QueryBuffer.Copy(_L("SELECT * FROM "));
QueryBuffer.Append(KtxtItemlist);
QueryBuffer.Append(_L(" WHERE "));
QueryBuffer.Append(NCol0);
QueryBuffer.Append(_L(" = "));
QueryBuffer.AppendNum(aIndex);
 
iItemsDatabase.Begin();
 
RDbView Myview;
// query buffr with index finds only the selected item row.
Myview.Prepare(iItemsDatabase,TDbQuery(QueryBuffer));
CleanupClosePushL(Myview);
 
Myview.EvaluateAll();
Myview.FirstL();
// we have autoincrement in index so it should be unique
// but just to make sure, we use 'while', instead of 'if'
while(Myview.AtRow())
{
Myview.GetL();
Myview.DeleteL();
Myview.NextL();
}
 
CleanupStack::PopAndDestroy(1); // Myview
iItemsDatabase.Commit();
// compacts the databse, by physicaly removig deleted data.
iItemsDatabase.Compact();
}
 
void CMyDBClass::UpdateDatabaseL(const TDesC& aName,
const TDesC& aValue,
TInt& aIndex)
{
TFileName QueryBuffer;
QueryBuffer.Copy(_L("SELECT * FROM "));
QueryBuffer.Append(KtxtItemlist);
QueryBuffer.Append(_L(" WHERE "));
QueryBuffer.Append(NCol0);
QueryBuffer.Append(_L(" = "));
QueryBuffer.AppendNum(aIndex);
 
iItemsDatabase.Begin();
 
RDbView Myview;
Myview.Prepare(iItemsDatabase, TDbQuery(QueryBuffer));
CleanupClosePushL(Myview);
 
Myview.EvaluateAll();
Myview.FirstL();
 
if(Myview.AtRow())
{
Myview.UpdateL();
Myview.SetColL(2, aName);
Myview.SetColL(3, aValue);
Myview.PutL();
}
 
CleanupStack::PopAndDestroy(1); // Myview
iItemsDatabase.Commit();
}
 
void CMyDBClass::SaveToDatabaseL(const TDesC& aName,
const TDesC& aValue,
TInt& aIndex)
{
TFileName QueryBuffer;
QueryBuffer.Copy(_L("SELECT * FROM "));
QueryBuffer.Append(KtxtItemlist);
 
iItemsDatabase.Begin();
 
RDbView Myview;
Myview.Prepare(iItemsDatabase, TDbQuery(QueryBuffer));
CleanupClosePushL(Myview);
 
Myview.InsertL();
 
Myview.SetColL(2, aName);
Myview.SetColL(3, aValue);
 
Myview.PutL();
 
aIndex = Myview.ColInt(1);// autoincrement gives us unique index.
 
CleanupStack::PopAndDestroy(1); // Myview
iItemsDatabase.Commit();
}

NamedDataBase.h

//Headers needed
#include <f32file.h> //RFs
#include <d32dbms.h> //RDbNamedDatabase,RDbView
 
//database name
_LIT(KtxDatabaseName, "Items.db");
 
// database column names
_LIT(NCol0, "index");
_LIT(NCol1, "name");
_LIT(NCol2, "value");
 
// database table name
_LIT(KtxtItemlist, "itemlist");
 
class TExampleItem
{
public:
TExampleItem(): iIndex(-1){};
public:
TInt iIndex;
TBuf<100> iName, iValue;
};
 
 
class CMyDBClass : public CBase
{
public :
void ConstructL();
~CMyDBClass();
public:
void DeleteFromDatabaseL(TInt& aIndex);
void UpdateDatabaseL(const TDesC& aName, const TDesC& aValue, TInt& aIndex);
void SaveToDatabaseL(const TDesC& aName, const TDesC& aValue, TInt& aIndex);
void ReadDbItemsL(RArray<TExampleItem>& aItemArray);
private:
void CreateTableL(RDbDatabase& aDatabase);
private:
RArray<TExampleItem> iItemArray;
RDbNamedDatabase iItemsDatabase;
RFs iFsSession;
};
This page was last modified on 23 January 2012, at 09:27.
78 page views in the last 30 days.
×