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

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

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

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

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

     6 <!-- Initial Contributors:

     7     Nokia Corporation - initial contribution.

     8 Contributors: 

     9 -->

    10 <!DOCTYPE concept

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

    12 <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

    13 //

    14 // Copyright (c) Nokia Ltd 2009. All rights reserved.

    15 //

    16 /** This example application demonstrates several SQL operations including:

    17 <ul>

    18 <li>Creating and managing a database</li>

    19 <li>Creating and managing tables</li>

    20 <li>Querying a database</li>

    21 </ul>

    22 

    23 @file basicsqlquery.cpp

    24 */

    25 #include <e32cons.h>

    26 #include <SqlDb.h>

    27 #include "basicsqlexample.h"

    28 /*  ***************************************************  */

    29 

    30 /* SQL Statements: */

    31 _LIT(KTabCreate,"CREATE TABLE Pets( person TEXT, cat SMALLINT, dog SMALLINT, rodent SMALLINT, bird SMALLINT);");

    32 

    33 /* An INSERT statement. Long version. */

    34         //Pets of Jon Doe

    35 _LIT(KTabInsert1,"INSERT INTO Pets (person, cat, dog, rodent, bird) VALUES ('Jon Doe', 0, 1, 1, 0);");

    36 

    37 /* Simplified insert statement text. */

    38 //Pets of Jane Roe

    39 _LIT(KTabInsert2,"INSERT INTO Pets VALUES ('Jane Roe',2,1,0,0);");

    40 //Pets of Tom Moe

    41 _LIT(KTabInsert3,"INSERT INTO Pets VALUES ('Tom Moe',1,0,0,3);");

    42 

    43 /* SQL SELECT Statements. */

    44 _LIT(KSelect1,"SELECT person FROM Pets WHERE cat >= 1;");

    45 _LIT(KSelect2,"SELECT person FROM Pets WHERE cat >= 1 AND dog >= 1;");

    46 _LIT(KSelect3,"SELECT person FROM Pets WHERE rodent >= 1;");

    47 

    48 /* Various messages */

    49 _LIT(KTitle, "Basic SQL example");

    50 _LIT(KTextPressAKey, "\n\nPress any key to step through the example\n");

    51 _LIT(KExit,"Press any key to exit the application\n");

    52 _LIT(KPressAKey,"\nPress a key to continue\n");

    53 _LIT(KDatabaseMsg,"\nCreating a database\n");

    54 _LIT(KOpen,"Opening the database\n");

    55 _LIT(KClose,"Closing the database\n");

    56 _LIT(KCreateTable,"\nCreating a table\n");

    57 _LIT(KInsert,"Inserting records into the table\n");

    58 _LIT(KPrepare,"Preparing a query\n");

    59 _LIT(KExecute,"Executing a query\n");

    60 

    61 /* Constants used with the SQL operations. */

    62 _LIT(KDatabaseName, "\\Basic_db.db");

    63 _LIT(KPerson,"person");

    64 _LIT(KCat,"cat");

    65 _LIT(KDog,"dog");

    66 _LIT(KRodent,"rodent");

    67 _LIT(KBird,"bird");

    68 

    69 /* End of SQL Statements. */

    70 

    71 /*  ***************************************************  */

    72 /**

    73 Allocates and constructs a CBasicSqlExample object and 

    74 leaves it on the cleanup stack.

    75 Initialises all member data to their default values.

    76 */    

    77 CBasicSqlExample* CBasicSqlExample::NewLC()

    78     {

    79     CBasicSqlExample* rep = new(ELeave) CBasicSqlExample();

    80     CleanupStack::PushL(rep);

    81     rep->ConstructL();

    82     return rep;

    83     }

    84 /*  ***************************************************  */

    85     

    86 /**

    87 Constructor

    88 */

    89 CBasicSqlExample::CBasicSqlExample()

    90     {

    91     }    

    92 

    93 void CBasicSqlExample::ConstructL()

    94     {

    95     iConsole = Console::NewL(KTitle,TSize(KConsFullScreen,KConsFullScreen));

    96     }

    97 /*  ***************************************************  */

    98 

    99 /**

   100 Destructor

   101 */

   102 CBasicSqlExample::~CBasicSqlExample()

   103     {

   104     iPetDb.Close();

   105 

   106     Prompt(KExit);

   107     delete iConsole;

   108     }

   109 /*  ***************************************************  */

   110 

   111 void CBasicSqlExample::Prompt()

   112     {

   113     iConsole->Printf(KPressAKey);

   114     iConsole->Getch();

   115     }

   116 void CBasicSqlExample::Prompt(const TDesC& aText)

   117     {

   118     iConsole->Printf(aText);

   119     iConsole->Printf(KPressAKey);

   120     iConsole->Getch();

   121     }

   122 

   123 /** Creates a Database 

   124 @leave KErrNotFound, KErrAbort, KErrPermissionDenied,

   125 KErrArgument, system-wide error codes.

   126 */

   127 void CBasicSqlExample::CreateDatabaseL()

   128     {

   129     RSqlDatabase db;

   130 

   131     iConsole->Printf(KDatabaseMsg);

   132     //create the database

   133     User::LeaveIfError(db.Create(KDatabaseName));

   134 

   135     iConsole->Printf(KDatabaseName);

   136     CleanupClosePushL(db);

   137     

   138     User::LeaveIfError(db.Exec(KTabCreate));

   139     CleanupStack::PopAndDestroy(1);

   140     

   141     Prompt(KClose);

   142     }

   143 /*  ***************************************************  */

   144 

   145 

   146 /** Add a few rows to the table.

   147 This function shows how to insert data in a table. 

   148 */

   149 void CBasicSqlExample::PopulateDatabaseL()

   150     {

   151     RSqlDatabase db;

   152 

   153     // Open the database that was created earlier.

   154     User::LeaveIfError(db.Open(KDatabaseName));

   155     CleanupClosePushL(db);

   156     Prompt(KOpen);

   157     

   158     // Inserts the first row.

   159     User::LeaveIfError(db.Exec(KTabInsert1));

   160     Prompt(KTabInsert1);

   161     

   162     // Inserts the second row.

   163     User::LeaveIfError(db.Exec(KTabInsert2));

   164     Prompt(KTabInsert2);

   165     

   166     // Inserts the third row.

   167     User::LeaveIfError(db.Exec(KTabInsert3));

   168     Prompt(KTabInsert3);

   169     

   170     // Close the database.

   171     // cleanup the database.

   172     Prompt(KClose);

   173     CleanupStack::PopAndDestroy(1);

   174     }

   175 /*  ***************************************************  */

   176 

   177 /** Querying the database.

   178 This function shows how to get information from the database.

   179 It provides a basic query.

   180 */

   181 void CBasicSqlExample::SqlQueryL(const TDesC& aStatement)

   182     {

   183     RSqlStatement stmt;

   184     iConsole->Printf(_L("Running Query:\n%S\n"), &aStatement);

   185     

   186     User::LeaveIfError(stmt.Prepare(iPetDb, aStatement));

   187     CleanupClosePushL(stmt);

   188     

   189     TInt personIndex = stmt.ColumnIndex(KPerson); // index.  This doesn't change after query preparation.

   190     TInt rc = KErrNone;

   191     while ((rc = stmt.Next()) == KSqlAtRow)

   192         {

   193         // Do something with the results 

   194         TPtrC myData = stmt.ColumnTextL(personIndex); // read return data

   195         iConsole->Printf(_L("Person=%S\n"), &myData);

   196         }

   197     if (rc != KSqlAtEnd)

   198         {

   199         _LIT(KErrSQLError, "Error %d returned from RSqlStatement::Next().");

   200         iConsole->Printf(KErrSQLError, rc);

   201         }

   202     Prompt();

   203     CleanupStack::PopAndDestroy(1);

   204     }

   205 

   206 void CBasicSqlExample::OpenDatabaseL()

   207     {

   208     User::LeaveIfError(iPetDb.Open(KDatabaseName));

   209     }

   210 

   211 void CBasicSqlExample::CloseDatabase()

   212     {

   213     iPetDb.Close();

   214     }

   215 

   216 void CBasicSqlExample::DeleteDatabase()

   217     {

   218     // should be safe if db closed, catch if db left open.

   219     iPetDb.Close();

   220     iPetDb.Delete(KDatabaseName);

   221     }

   222 

   223 void CBasicSqlExample::ExampleL()

   224     {

   225     TRAPD(err, DoExampleL());

   226     // Remove database file for next run of example.

   227     DeleteDatabase();

   228     User::LeaveIfError(err);

   229     }

   230 

   231 void CBasicSqlExample::DoExampleL()

   232     {

   233     // Create a database

   234     CreateDatabaseL();

   235     

   236     // Add table and data

   237     PopulateDatabaseL();

   238 

   239     // Query the data

   240     OpenDatabaseL();

   241     SqlQueryL(KSelect1);

   242     SqlQueryL(KSelect2);

   243     SqlQueryL(KSelect3);

   244     CloseDatabase();

   245     }

   246 

   247 /*  ***************************************************  */

   248 

   249 template <class T>class TRunExample

   250     {

   251 public:

   252     static void RunExample(){

   253         // Create an Active Scheduler to handle asychronous calls

   254         CActiveScheduler* scheduler = new (ELeave) CActiveScheduler;

   255         CleanupStack::PushL(scheduler);

   256         CActiveScheduler::Install( scheduler );

   257         T* app = T::NewLC();

   258         app->ExampleL();

   259         CleanupStack::PopAndDestroy(2); //app, scheduler

   260         };

   261     };

   262 

   263 GLDEF_C TInt E32Main()

   264     {

   265     __UHEAP_MARK;

   266     CTrapCleanup* cleanup = CTrapCleanup::New();

   267     if(cleanup == NULL)

   268         {

   269         return KErrNoMemory;

   270         }

   271     TRunExample<CBasicSqlExample> example;

   272     // TRunExample<CComplexSqlExample> example2;

   273     // TRunExample<CEvenMoreComplexSqlExample> example3;

   274     

   275     TRAPD(err, example.RunExample());

   276     // TRAP(err, example2.RunExample());

   277     // TRAP(err, example3.RunExample());

   278     

   279     if(err != KErrNone)

   280         {

   281         User::Panic(_L("Failed to complete"),err);

   282         }

   283     delete cleanup;

   284     __UHEAP_MARKEND;

   285     return KErrNone;

   286     }</codeblock> </section> </conbody></concept>