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 id="GUID-22B535FA-6C8A-5B39-9CD7-6E85A86B7C2C" xml:lang="en"><title>Basic

    13 SQL Example: basicsqlexample.cpp </title><shortdesc>A copy of the basicsqlexample.cpp file for the basic SQL example

    14 application. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

    15 <section id="GUID-B68A6BA4-43F1-4E83-B51F-9203F12BEF1F"><title>Introduction</title> <p>The functions demonstrate how to perform

    16 several SQL operations. </p> </section>

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

    18 //

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

    20 //

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

    22 <ul>

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

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

    25 <li>Querying a database</li>

    26 </ul>

    27 

    28 @file basicsqlquery.cpp

    29 */

    30 #include <e32cons.h>

    31 #include <SqlDb.h>

    32 #include "basicsqlexample.h"

    33 /*  ***************************************************  */

    34 

    35 /* SQL Statements: */

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

    37 

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

    39         //Pets of Jon Doe

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

    41 

    42 /* Simplified insert statement text. */

    43 //Pets of Jane Roe

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

    45 //Pets of Tom Moe

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

    47 

    48 /* SQL SELECT Statements. */

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

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

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

    52 

    53 /* Various messages */

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

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

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

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

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

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

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

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

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

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

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

    65 

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

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

    68 _LIT(KPerson,"person");

    69 _LIT(KCat,"cat");

    70 _LIT(KDog,"dog");

    71 _LIT(KRodent,"rodent");

    72 _LIT(KBird,"bird");

    73 

    74 /* End of SQL Statements. */

    75 

    76 /*  ***************************************************  */

    77 /**

    78 Allocates and constructs a CBasicSqlExample object and 

    79 leaves it on the cleanup stack.

    80 Initialises all member data to their default values.

    81 */    

    82 CBasicSqlExample* CBasicSqlExample::NewLC()

    83     {

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

    85     CleanupStack::PushL(rep);

    86     rep->ConstructL();

    87     return rep;

    88     }

    89 /*  ***************************************************  */

    90     

    91 /**

    92 Constructor

    93 */

    94 CBasicSqlExample::CBasicSqlExample()

    95     {

    96     }    

    97 

    98 void CBasicSqlExample::ConstructL()

    99     {

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

   101     }

   102 /*  ***************************************************  */

   103 

   104 /**

   105 Destructor

   106 */

   107 CBasicSqlExample::~CBasicSqlExample()

   108     {

   109     iPetDb.Close();

   110 

   111     Prompt(KExit);

   112     delete iConsole;

   113     }

   114 /*  ***************************************************  */

   115 

   116 void CBasicSqlExample::Prompt()

   117     {

   118     iConsole->Printf(KPressAKey);

   119     iConsole->Getch();

   120     }

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

   122     {

   123     iConsole->Printf(aText);

   124     iConsole->Printf(KPressAKey);

   125     iConsole->Getch();

   126     }

   127 

   128 /** Creates a Database 

   129 @leave KErrNotFound, KErrAbort, KErrPermissionDenied,

   130 KErrArgument, system-wide error codes.

   131 */

   132 void CBasicSqlExample::CreateDatabaseL()

   133     {

   134     RSqlDatabase db;

   135 

   136     iConsole->Printf(KDatabaseMsg);

   137     //create the database

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

   139 

   140     iConsole->Printf(KDatabaseName);

   141     CleanupClosePushL(db);

   142     

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

   144     CleanupStack::PopAndDestroy(1);

   145     

   146     Prompt(KClose);

   147     }

   148 /*  ***************************************************  */

   149 

   150 

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

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

   153 */

   154 void CBasicSqlExample::PopulateDatabaseL()

   155     {

   156     RSqlDatabase db;

   157 

   158     // Open the database that was created earlier.

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

   160     CleanupClosePushL(db);

   161     Prompt(KOpen);

   162     

   163     // Inserts the first row.

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

   165     Prompt(KTabInsert1);

   166     

   167     // Inserts the second row.

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

   169     Prompt(KTabInsert2);

   170     

   171     // Inserts the third row.

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

   173     Prompt(KTabInsert3);

   174     

   175     // Close the database.

   176     // cleanup the database.

   177     Prompt(KClose);

   178     CleanupStack::PopAndDestroy(1);

   179     }

   180 /*  ***************************************************  */

   181 

   182 /** Querying the database.

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

   184 It provides a basic query.

   185 */

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

   187     {

   188     RSqlStatement stmt;

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

   190     

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

   192     CleanupClosePushL(stmt);

   193     

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

   195     TInt rc = KErrNone;

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

   197         {

   198         // Do something with the results 

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

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

   201         }

   202     if (rc != KSqlAtEnd)

   203         {

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

   205         iConsole->Printf(KErrSQLError, rc);

   206         }

   207     Prompt();

   208     CleanupStack::PopAndDestroy(1);

   209     }

   210 

   211 void CBasicSqlExample::OpenDatabaseL()

   212     {

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

   214     }

   215 

   216 void CBasicSqlExample::CloseDatabase()

   217     {

   218     iPetDb.Close();

   219     }

   220 

   221 void CBasicSqlExample::DeleteDatabase()

   222     {

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

   224     iPetDb.Close();

   225     iPetDb.Delete(KDatabaseName);

   226     }

   227 

   228 void CBasicSqlExample::ExampleL()

   229     {

   230     TRAPD(err, DoExampleL());

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

   232     DeleteDatabase();

   233     User::LeaveIfError(err);

   234     }

   235 

   236 void CBasicSqlExample::DoExampleL()

   237     {

   238     // Create a database

   239     CreateDatabaseL();

   240     

   241     // Add table and data

   242     PopulateDatabaseL();

   243 

   244     // Query the data

   245     OpenDatabaseL();

   246     SqlQueryL(KSelect1);

   247     SqlQueryL(KSelect2);

   248     SqlQueryL(KSelect3);

   249     CloseDatabase();

   250     }

   251 

   252 /*  ***************************************************  */

   253 

   254 template <class T>class TRunExample

   255     {

   256 public:

   257     static void RunExample(){

   258         // Create an Active Scheduler to handle asychronous calls

   259         CActiveScheduler* scheduler = new (ELeave) CActiveScheduler;

   260         CleanupStack::PushL(scheduler);

   261         CActiveScheduler::Install( scheduler );

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

   263         app->ExampleL();

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

   265         };

   266     };

   267 

   268 GLDEF_C TInt E32Main()

   269     {

   270     __UHEAP_MARK;

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

   272     if(cleanup == NULL)

   273         {

   274         return KErrNoMemory;

   275         }

   276     TRunExample<CBasicSqlExample> example;

   277     // TRunExample<CComplexSqlExample> example2;

   278     // TRunExample<CEvenMoreComplexSqlExample> example3;

   279     

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

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

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

   283     

   284     if(err != KErrNone)

   285         {

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

   287         }

   288     delete cleanup;

   289     __UHEAP_MARKEND;

   290     return KErrNone;

   291     }</codeblock> </section>

   292 </conbody></concept>