<?xml version="1.0" encoding="utf-8"?>

<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->

<!-- This component and the accompanying materials are made available under the terms of the License 

"Eclipse Public License v1.0" which accompanies this distribution, 

and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->

<!-- Initial Contributors:

    Nokia Corporation - initial contribution.

Contributors: 

-->

<!DOCTYPE concept

  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">

<concept xml:lang="en" id="GUID-22B535FA-6C8A-5B39-9CD7-6E85A86B7C2C"><title>Basic SQL Example: basicsqlexample.cpp </title><prolog><metadata><keywords/></metadata></prolog><conbody><p>A copy of the basicsqlexample.cpp file for the basic SQL example application. </p> <section><title>Introduction</title> <p>The functions demonstrate how to perform several SQL operations. </p> </section> <section><title>Code</title> <p>Defines the functions. </p> <codeblock id="GUID-7DB281F3-2F11-542A-BE71-DDFBDD113A6C" xml:space="preserve">// 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;