dbmsengine.cpp

Symbian S60 DBMS example

	if(error!=KErrNone)
    {
    	return error;
    }   
	writeStream.Close();
	
    TRAP(err, table.PutL());    // Complete changes (the insertion)
	if(err!=KErrNone)
    {
    	return err;
    }   
    
    CleanupStack::PopAndDestroy(booksColSet);
    table.Close();
	
    return KErrNone;

    }

// ---------------------------------------------------------------------------
// CBookDb::GetAllBooksL()
//
// Get array of all books in database. Format of each array item is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::GetAllBooksL()
    {
    TPtrC author, title;
    TBuf<KDescriptionMaxLength> description;
    TBuf<KBookItemMaxLength> rowText;

    RDbTable table;
    TInt err = table.Open(iBookDb, KBooksTable, table.EReadOnly);
    User::LeaveIfError(err);

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    table.Reset();
    CDbColSet* colSet = table.ColSetL();
    CleanupStack::PushL(colSet);

    for (table.FirstL(); table.AtRow(); table.NextL())
        {
        description.Zero();
        rowText.Zero();

        table.GetL();

        author.Set(table.ColDes(colSet->ColNo(KBooksAuthorCol)));
        title.Set(table.ColDes(colSet->ColNo(KBooksTitleCol)));

        TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
        RDbColReadStream readStream;       // A stream object for long columns
        readStream.OpenLC(table,descrColNo);
        readStream.ReadL(description, table.ColLength(descrColNo));
        readStream.Close();
        CleanupStack::Pop(); //readStream

        rowText.Append(author);
        rowText.Append(KSeparator);
        rowText.Append(title);
        rowText.Append(KSeparator);
        rowText.Append(description);

        resultArray->AppendL(rowText); // Copy rowText to resultArray
        }
    CleanupStack::PopAndDestroy(colSet);
    CleanupStack::Pop(resultArray);
    table.Close();

    return resultArray;
    }

// ---------------------------------------------------------------------------
// CBookDb::GetABookFast()
//
// Get a book using index. Format of the result is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
TInt CBookDb::GetABookFast(const TDesC& aTitle, TDes& aResult)
    {
    TInt err = KErrNone;
    TBuf<KDescriptionMaxLength> description; // Only 128 first characters read
    RDbTable rowset;

    TDbSeekKey seekKey(aTitle); // Initialize one-column seek key

    // Open view to "Books" table. Use index to browse the table.
    err = rowset.Open(iBookDb, KBooksTable, rowset.EReadOnly);
    if(err!=KErrNone)
    {
    	return err;
    }
    err = rowset.SetIndex(KBooksIndexName);
    if(err!=KErrNone)
    {
    	return err;
    }
    // Query colum numbers for author, title, and description
    CDbColSet* colSet=NULL;
    TRAP(err, colSet = rowset.ColSetL());
    if(err!=KErrNone)
    {
    	return err;
    }

    TInt authorColumnNo = colSet->ColNo(KBooksAuthorCol);
    TInt titleColumnNo = colSet->ColNo(KBooksTitleCol);
    TInt descrColumnNo = colSet->ColNo(KBooksDescriptionCol);

    // Search the index for aTitle
    TBool isTitle = false;
    TRAP(err, isTitle = rowset.SeekL(seekKey));
    if(isTitle)
        {
        RDbColReadStream readStream;     // A stream object for long columns
        TRAPD(error, 
        	rowset.GetL();
        	readStream.OpenL(rowset,descrColumnNo);
        	readStream.ReadL(description, rowset.ColLength(descrColumnNo));
        	);
        if(error!=KErrNone)
        {
        	return error;
        }

        readStream.Close();

        aResult.Zero();
        aResult.Append(rowset.ColDes(authorColumnNo));
        aResult.Append(KSeparator);
        aResult.Append(rowset.ColDes(titleColumnNo));
        aResult.Append(KSeparator);
        aResult.Append(description);

        err = KErrNone;
        }
    else
        {
        err = KErrNotFound;
        }

    rowset.Close();
    return err;
    }

// ---------------------------------------------------------------------------
// CBookDb::GetBooksByKeyL()
//
// Get array of books from database according to column name and a search
// pattern. Format of each array item is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::GetBooksByKeyL(const TDesC& aColumnName,
    const TDesC& aSearchString)
    {

    TPtrC author, title;
    TBuf<KDescriptionMaxLength> description;
    TBuf<KBookItemMaxLength> rowText;

	_LIT(KSelect, "SELECT ");
	_LIT(KFrom, "FROM");
	_LIT(KWhere, " WHERE ");
	_LIT(KLike, " LIKE '");
	_LIT(KOrderBy, "' ORDER BY ");
	_LIT(KDot, ", ");

    // Sql: SELECT Author, Title, Description FROM Books
    //      WHERE "aColumnName LIKE aSearchString"
    //      ORDER BY Title, Author
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KSelect);
    sqlStr.Append(KBooksAuthorCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksDescriptionCol);
    sqlStr.Append(KFrom);
    sqlStr.Append(KBooksTable);
    sqlStr.Append(KWhere);
    sqlStr.Append(aColumnName);
    sqlStr.Append(KLike);
    sqlStr.Append(aSearchString);
    sqlStr.Append(KOrderBy);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksAuthorCol);

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    // Create a view on the database
    RDbView view;
    User::LeaveIfError(
        view.Prepare(iBookDb, TDbQuery(sqlStr), view.EReadOnly));
    User::LeaveIfError(view.EvaluateAll());

    CDbColSet* colSet = view.ColSetL();
    CleanupStack::PushL(colSet);

    // Append each result row to array
    for (view.FirstL(); view.AtRow(); view.NextL())
        {

        description.Zero();
        rowText.Zero();

        view.GetL();

        author.Set(view.ColDes(colSet->ColNo(KBooksAuthorCol)));
        title.Set(view.ColDes(colSet->ColNo(KBooksTitleCol)));

        TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
        RDbColReadStream readStream;       // A stream object for long columns
        readStream.OpenLC(view, descrColNo);
        readStream.ReadL(description, view.ColLength(descrColNo));
        readStream.Close();
        CleanupStack::Pop(); //readStream

        rowText.Append(author);
        rowText.Append(KSeparator);
        rowText.Append(title);
        rowText.Append(KSeparator);
        rowText.Append(description);

        resultArray->AppendL(rowText);
        }
    CleanupStack::PopAndDestroy(colSet);
    view.Close();
    CleanupStack::Pop(resultArray);

    return resultArray;

    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveBooks()
//
// Delete a book using title pattern and RDbUpdate (DML)
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveBooks(const TDesC& aTitle, TInt& aResultCount)
    {
    RDbUpdate updOp;

	_LIT(KDeleteFrom, "DELETE FROM ");
	_LIT(KWhere, " WHERE ");
	_LIT(KLike, " LIKE '");
	_LIT(KDot, "'");
	
    // Sql: DELETE FROM Books WHERE Title LIKE 'aTitle'
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KDeleteFrom);
    sqlStr.Append(KBooksTable);
    sqlStr.Append(KWhere);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KLike);
    sqlStr.Append(aTitle);
    sqlStr.Append(KDot);

    // Initialize execution and perform the first step.
    // Note: Execute() returns 0 (=KErrNone), but it does not affect database
    //       until Next() is called.
    TInt incStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
    incStat = updOp.Next(); // This will leave, if Execute() failed.

    while( incStat == 1 ) // Just in case, if the operation has more steps
        {
        incStat = updOp.Next();
        }
    aResultCount = updOp.RowCount();
    updOp.Close();
    return incStat; // KErrNone or system wide error code
    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveAllBooks()
//
// Delete books using asynchronous API. (RDbUpdate and DML)
// This implementation is still synchronous, because it uses
// User::WaitForRequest. Normally asynchronous functionality should be hidden
// into active object and client callback interfaces.
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveAllBooks(TInt& aResultCount)
    {
    _LIT(KDeleteFrom, "DELETE FROM ");

    // Sql: DELETE FROM Books
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KDeleteFrom);
    sqlStr.Append(KBooksTable);

    RDbUpdate updOp;
    TRequestStatus incStat(1);
    TInt updStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
    while (updStat==KErrNone && incStat ==1)
        {
        updOp.Next(incStat);           // Start async operation. It returns
                                       // immediately.
        User::WaitForRequest(incStat); // For simplicity wait completion here.
        }

    aResultCount = updOp.RowCount();
    updOp.Close();

    if(updStat!=KErrNone)
        return updStat;       // System wide error code
    else
        return incStat.Int(); // KErrNone or system wide error code
    }


// ---------------------------------------------------------------------------
// CBookDb::UpdateBookTitle()
//
// Update book title using SQL UPDATE.
// ---------------------------------------------------------------------------
//
TInt CBookDb::UpdateBookTitle(const TDesC& aOldTitleKey,
    const TDesC& aNewTitle)
    {
    _LIT(KSQLUpdateStart, "UPDATE Books SET Title = '");
    _LIT(KSQLUpdateMiddle, "' WHERE Title = '");
    _LIT(KSQLUpdateEnd, "'");

    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KSQLUpdateStart);
    sqlStr.Append(aNewTitle);
    sqlStr.Append(KSQLUpdateMiddle);
    sqlStr.Append(aOldTitleKey);
    sqlStr.Append(KSQLUpdateEnd);

    return iBookDb.Execute(sqlStr);
    }


// ---------------------------------------------------------------------------
// CBookDb::ColumnNamesAndSizesL()
//
// Get array of column names and sizes of the Books table.
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::ColumnNamesAndSizesL()
    {
    RDbTable booksTable;
    TBuf<32> columnNameAndSize;
    _LIT(KDelimiter, ": ");
    _LIT(KNoSize,"No size");

    // Open the Books table.
    TInt err = booksTable.Open(iBookDb, KBooksTable, booksTable.EReadOnly);
    User::LeaveIfError(err);
    
    CleanupClosePushL(booksTable);  // Remember to pop and close

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    // Iterate through the colums of Books table. Extract the column name and
    // column size (size only for text columns).