Categories: Symbian C++ | Code Examples | Files/Data

This page was last modified 20:39, 28 April 2008.

From Forum Nokia Wiki

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's 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.

NamedDataBase.cpp

CMyDBClass::~CMyDBClass()
{
	iItemsDatabase.Close();
	iFsSession.Close();
}
    
void CMyDBClass::ConstructL()
{
	User::LeaveIfError(iFsSession.Connect());
	
	TFileName DBFileName;
	// 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();
		}
		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

//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:
    RDbNamedDatabase iItemsDatabase;
    RFs iFsSession;
};