Basic SQL Example: basicsqlexample.cpp

Introduction

The functions demonstrate how to perform several SQL operations.

Code

Defines the functions.

// basicsqlexample.cpp
//
// Copyright (c) Nokia Ltd 2009. All rights reserved.
//
/** This example application demonstrates several SQL operations including:
<ul>
<li>Creating and managing a database</li>
<li>Creating and managing tables</li>
<li>Querying a database</li>
</ul>

@file basicsqlquery.cpp
*/
#include <e32cons.h>
#include <SqlDb.h>
#include "basicsqlexample.h"
/*  ***************************************************  */

/* SQL Statements: */
_LIT(KTabCreate,"CREATE TABLE Pets( person TEXT, cat SMALLINT, dog SMALLINT, rodent SMALLINT, bird SMALLINT);");

/* An INSERT statement. Long version. */
        //Pets of Jon Doe
_LIT(KTabInsert1,"INSERT INTO Pets (person, cat, dog, rodent, bird) VALUES ('Jon Doe', 0, 1, 1, 0);");

/* Simplified insert statement text. */
//Pets of Jane Roe
_LIT(KTabInsert2,"INSERT INTO Pets VALUES ('Jane Roe',2,1,0,0);");
//Pets of Tom Moe
_LIT(KTabInsert3,"INSERT INTO Pets VALUES ('Tom Moe',1,0,0,3);");

/* SQL SELECT Statements. */
_LIT(KSelect1,"SELECT person FROM Pets WHERE cat >= 1;");
_LIT(KSelect2,"SELECT person FROM Pets WHERE cat >= 1 AND dog >= 1;");
_LIT(KSelect3,"SELECT person FROM Pets WHERE rodent >= 1;");

/* Various messages */
_LIT(KTitle, "Basic SQL example");
_LIT(KTextPressAKey, "\n\nPress any key to step through the example\n");
_LIT(KExit,"Press any key to exit the application\n");
_LIT(KPressAKey,"\nPress a key to continue\n");
_LIT(KDatabaseMsg,"\nCreating a database\n");
_LIT(KOpen,"Opening the database\n");
_LIT(KClose,"Closing the database\n");
_LIT(KCreateTable,"\nCreating a table\n");
_LIT(KInsert,"Inserting records into the table\n");
_LIT(KPrepare,"Preparing a query\n");
_LIT(KExecute,"Executing a query\n");

/* Constants used with the SQL operations. */
_LIT(KDatabaseName, "\\Basic_db.db");
_LIT(KPerson,"person");
_LIT(KCat,"cat");
_LIT(KDog,"dog");
_LIT(KRodent,"rodent");
_LIT(KBird,"bird");

/* End of SQL Statements. */

/*  ***************************************************  */
/**
Allocates and constructs a CBasicSqlExample object and 
leaves it on the cleanup stack.
Initialises all member data to their default values.
*/    
CBasicSqlExample* CBasicSqlExample::NewLC()
    {
    CBasicSqlExample* rep = new(ELeave) CBasicSqlExample();
    CleanupStack::PushL(rep);
    rep->ConstructL();
    return rep;
    }
/*  ***************************************************  */
    
/**
Constructor
*/
CBasicSqlExample::CBasicSqlExample()
    {
    }    

void CBasicSqlExample::ConstructL()
    {
    iConsole = Console::NewL(KTitle,TSize(KConsFullScreen,KConsFullScreen));
    }
/*  ***************************************************  */

/**
Destructor
*/
CBasicSqlExample::~CBasicSqlExample()
    {
    iPetDb.Close();

    Prompt(KExit);
    delete iConsole;
    }
/*  ***************************************************  */

void CBasicSqlExample::Prompt()
    {
    iConsole->Printf(KPressAKey);
    iConsole->Getch();
    }
void CBasicSqlExample::Prompt(const TDesC& aText)
    {
    iConsole->Printf(aText);
    iConsole->Printf(KPressAKey);
    iConsole->Getch();
    }

/** Creates a Database 
@leave KErrNotFound, KErrAbort, KErrPermissionDenied,
KErrArgument, system-wide error codes.
*/
void CBasicSqlExample::CreateDatabaseL()
    {
    RSqlDatabase db;

    iConsole->Printf(KDatabaseMsg);
    //create the database
    User::LeaveIfError(db.Create(KDatabaseName));

    iConsole->Printf(KDatabaseName);
    CleanupClosePushL(db);
    
    User::LeaveIfError(db.Exec(KTabCreate));
    CleanupStack::PopAndDestroy(1);
    
    Prompt(KClose);
    }
/*  ***************************************************  */


/** Add a few rows to the table.
This function shows how to insert data in a table. 
*/
void CBasicSqlExample::PopulateDatabaseL()
    {
    RSqlDatabase db;

    // Open the database that was created earlier.
    User::LeaveIfError(db.Open(KDatabaseName));
    CleanupClosePushL(db);
    Prompt(KOpen);
    
    // Inserts the first row.
    User::LeaveIfError(db.Exec(KTabInsert1));
    Prompt(KTabInsert1);
    
    // Inserts the second row.
    User::LeaveIfError(db.Exec(KTabInsert2));
    Prompt(KTabInsert2);
    
    // Inserts the third row.
    User::LeaveIfError(db.Exec(KTabInsert3));
    Prompt(KTabInsert3);
    
    // Close the database.
    // cleanup the database.
    Prompt(KClose);
    CleanupStack::PopAndDestroy(1);
    }
/*  ***************************************************  */

/** Querying the database.
This function shows how to get information from the database.
It provides a basic query.
*/
void CBasicSqlExample::SqlQueryL(const TDesC& aStatement)
    {
    RSqlStatement stmt;
    iConsole->Printf(_L("Running Query:\n%S\n"), &aStatement);
    
    User::LeaveIfError(stmt.Prepare(iPetDb, aStatement));
    CleanupClosePushL(stmt);
    
    TInt personIndex = stmt.ColumnIndex(KPerson); // index.  This doesn't change after query preparation.
    TInt rc = KErrNone;
    while ((rc = stmt.Next()) == KSqlAtRow)
        {
        // Do something with the results 
        TPtrC myData = stmt.ColumnTextL(personIndex); // read return data
        iConsole->Printf(_L("Person=%S\n"), &myData);
        }
    if (rc != KSqlAtEnd)
        {
        _LIT(KErrSQLError, "Error %d returned from RSqlStatement::Next().");
        iConsole->Printf(KErrSQLError, rc);
        }
    Prompt();
    CleanupStack::PopAndDestroy(1);
    }

void CBasicSqlExample::OpenDatabaseL()
    {
    User::LeaveIfError(iPetDb.Open(KDatabaseName));
    }

void CBasicSqlExample::CloseDatabase()
    {
    iPetDb.Close();
    }

void CBasicSqlExample::DeleteDatabase()
    {
    // should be safe if db closed, catch if db left open.
    iPetDb.Close();
    iPetDb.Delete(KDatabaseName);
    }

void CBasicSqlExample::ExampleL()
    {
    TRAPD(err, DoExampleL());
    // Remove database file for next run of example.
    DeleteDatabase();
    User::LeaveIfError(err);
    }

void CBasicSqlExample::DoExampleL()
    {
    // Create a database
    CreateDatabaseL();
    
    // Add table and data
    PopulateDatabaseL();

    // Query the data
    OpenDatabaseL();
    SqlQueryL(KSelect1);
    SqlQueryL(KSelect2);
    SqlQueryL(KSelect3);
    CloseDatabase();
    }

/*  ***************************************************  */

template <class T>class TRunExample
    {
public:
    static void RunExample(){
        // Create an Active Scheduler to handle asychronous calls
        CActiveScheduler* scheduler = new (ELeave) CActiveScheduler;
        CleanupStack::PushL(scheduler);
        CActiveScheduler::Install( scheduler );
        T* app = T::NewLC();
        app->ExampleL();
        CleanupStack::PopAndDestroy(2); //app, scheduler
        };
    };

GLDEF_C TInt E32Main()
    {
    __UHEAP_MARK;
    CTrapCleanup* cleanup = CTrapCleanup::New();
    if(cleanup == NULL)
        {
        return KErrNoMemory;
        }
    TRunExample<CBasicSqlExample> example;
    // TRunExample<CComplexSqlExample> example2;
    // TRunExample<CEvenMoreComplexSqlExample> example3;
    
    TRAPD(err, example.RunExample());
    // TRAP(err, example2.RunExample());
    // TRAP(err, example3.RunExample());
    
    if(err != KErrNone)
        {
        User::Panic(_L("Failed to complete"),err);
        }
    delete cleanup;
    __UHEAP_MARKEND;
    return KErrNone;
    }