<?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 id="GUID-22B535FA-6C8A-5B39-9CD7-6E85A86B7C2C" xml:lang="en"><title>Basic
SQL Example: basicsqlexample.cpp </title><shortdesc>A copy of the basicsqlexample.cpp file for the basic SQL example
application. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>
<section id="GUID-B68A6BA4-43F1-4E83-B51F-9203F12BEF1F"><title>Introduction</title> <p>The functions demonstrate how to perform
several SQL operations. </p> </section>
<section id="GUID-0AB66D9A-4485-496A-962C-7680FAEBF9E7"><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:
&lt;ul&gt;
&lt;li&gt;Creating and managing a database&lt;/li&gt;
&lt;li&gt;Creating and managing tables&lt;/li&gt;
&lt;li&gt;Querying a database&lt;/li&gt;
&lt;/ul&gt;

@file basicsqlquery.cpp
*/
#include &lt;e32cons.h&gt;
#include &lt;SqlDb.h&gt;
#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 &gt;= 1;");
_LIT(KSelect2,"SELECT person FROM Pets WHERE cat &gt;= 1 AND dog &gt;= 1;");
_LIT(KSelect3,"SELECT person FROM Pets WHERE rodent &gt;= 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-&gt;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-&gt;Printf(KPressAKey);
    iConsole-&gt;Getch();
    }
void CBasicSqlExample::Prompt(const TDesC&amp; aText)
    {
    iConsole-&gt;Printf(aText);
    iConsole-&gt;Printf(KPressAKey);
    iConsole-&gt;Getch();
    }

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

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

    iConsole-&gt;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&amp; aStatement)
    {
    RSqlStatement stmt;
    iConsole-&gt;Printf(_L("Running Query:\n%S\n"), &amp;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-&gt;Printf(_L("Person=%S\n"), &amp;myData);
        }
    if (rc != KSqlAtEnd)
        {
        _LIT(KErrSQLError, "Error %d returned from RSqlStatement::Next().");
        iConsole-&gt;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 &lt;class T&gt;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-&gt;ExampleL();
        CleanupStack::PopAndDestroy(2); //app, scheduler
        };
    };

GLDEF_C TInt E32Main()
    {
    __UHEAP_MARK;
    CTrapCleanup* cleanup = CTrapCleanup::New();
    if(cleanup == NULL)
        {
        return KErrNoMemory;
        }
    TRunExample&lt;CBasicSqlExample&gt; example;
    // TRunExample&lt;CComplexSqlExample&gt; example2;
    // TRunExample&lt;CEvenMoreComplexSqlExample&gt; 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;
    }</codeblock> </section>
</conbody></concept>