<?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-C60DAE3D-7FB9-5619-9E5D-476A430705AF"><title>Querying a database</title><shortdesc>This tutorial shows you how to create a simple SQL database query. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody><section id="GUID-0A238212-37C9-5B4B-A449-F8C81CE971A8"><title>Introduction</title> <p>This tutorial shows you how to wrap a SQL query statement into an <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> object to query a database. </p> <p>The SQL statement used for the tutorial is shown here: </p> <p><userinput><parmname>SELECT</parmname> <cmdname>name</cmdname>  <parmname>FROM</parmname> <cmdname>countries</cmdname>  <parmname>WHERE</parmname> <cmdname>population &gt; 10</cmdname> </userinput> </p> <p>The (<codeph>SELECT</codeph>) results of the query will be the value in the '<codeph>name</codeph>' column <codeph>FROM</codeph> the '<codeph>countries</codeph>' table <codeph>WHERE</codeph> the value of the '<codeph>population</codeph>' column of the same record is <codeph>&gt;</codeph> the value specified. </p> </section> <section><title>Procedure</title> <ol id="GUID-2C39BABE-F2D4-510A-A406-D2C48412B6F8"><li id="GUID-56D39707-7D55-5843-8966-36223BB20EFC"><p>Prepare the Statement: </p> <p>The steps to prepare a SQL statement are shown here. </p> <ol id="GUID-8B003014-94BB-5B4E-9D9C-8098A3642EA6"><li id="GUID-A988E274-8B21-5DCB-A616-06CD3C6F5680"><p>Set up some constants used by the SQL statement object to define the SQL query: </p> <p><codeblock id="GUID-37E8467F-7AFC-5DBA-AF29-1A910E5CABC5" xml:space="preserve">_LIT(kName,"name");

_LIT(kPopulation,"population");

_LIT(kVal,":Value");

_LIT(kQueryString,"SELECT name FROM countries WHERE population &gt; :Value");</codeblock> </p> <p>This defines the query parameters. </p> </li> <li id="GUID-39F138F2-591F-586E-A06F-91981A1F6388"><p>Instantiate the <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> SQL statement: </p> <p><codeblock id="GUID-41EB4CD0-E131-5C85-B799-5940853B9F60" xml:space="preserve">RSqlStatement myStatement;</codeblock> </p> </li> <li id="GUID-5FD16C7C-591D-5006-A177-C899314AA798"><p>Define the indices to be used in the search: </p> <p><codeblock id="GUID-351298BC-348F-57E4-BC30-E6706385D8AA" xml:space="preserve">TInt nameIndex = myStatement.ColumnIndex(kName); 

TInt populationIndex = myStatement.ColumnIndex(kPopulation);</codeblock> </p> </li> <li id="GUID-17A647B3-6E8C-54FB-A21E-23425AD343FD"><p>Set the 32-bit integer value for the SQL parameter 'value': </p> <p><codeblock id="GUID-838D0598-1E98-57E8-AC66-68AA590EDFBA" xml:space="preserve">TInt parameterIndex = myStatement.ParameterIndex(kVal);

err = myStatement.BindInt(parameterIndex,10);</codeblock> </p> <p>The SQL parameter to which the integer is being assigned is identified by the constant <codeph>kVal</codeph> from: </p> <p><codeblock id="GUID-71AB4003-65D7-5035-ACFB-E106ACF2AB15" xml:space="preserve">_LIT(kVal,":Value");

...

...WHERE population &gt; :Value")  </codeblock> </p> <p>The parameter plus constant, along with other parts of the statement, are converted into: </p> <p>in SQL syntax. </p> </li> <li id="GUID-AEBF05BB-08E7-5628-8B5F-8CD30F08D638"><p>Prepare the statement: </p> <p><codeblock id="GUID-C518EA02-81AC-5B93-9FAF-1BDC987985DB" xml:space="preserve">TInt err;

err = myStatement.Prepare(countriesDatabase,kQueryString);</codeblock> </p> <p>This creates a parameterised SQL statement executable. </p> </li> </ol> </li> <li id="GUID-1153684C-697C-5C84-A15C-99C96820C8FB"><p>Run the SQL query: </p> <ol id="GUID-186BD930-F9A1-5244-BEA8-3774DC6D3F91"><li id="GUID-1D0B5C50-E1C6-51DC-8CCE-2C2B4C33F7F6"><p>Search the records until a match is found: </p> <p><codeblock id="GUID-DEF39928-2DBB-5017-A47C-1BB6475B44B7" xml:space="preserve">while((err = myStatement.Next()) == KSqlAtRow)

 {

  Do something with the query results

 }</codeblock> </p> <p>Next() fires the executable SQL statement and stops at and returns the matched record. </p> <p>Do something if no results are found. </p> <p><codeblock id="GUID-ADD04732-1D9C-5716-B885-14D92D341149" xml:space="preserve">if(err == KSqlAtEnd)

    <OK - no more records>;

else

    &lt;process the error&gt;;</codeblock> </p> </li> </ol> </li> <li id="GUID-26B8C900-50D3-59D2-BACD-61C825B2A3A4"><p>The query is done and you have the results. In this section we look at a simple way to do something with the results and we close the SQL statement object. </p> <ol id="GUID-79A55F5A-E42B-5AA2-B451-080E36100E0A"><li id="GUID-5F24A346-F2DA-5544-9AB2-DD046E4D80DD"><p>Get the results of the search: </p> <p><codeblock id="GUID-86F158DF-88FF-5128-9B6E-461D6C3163D7" xml:space="preserve">TPtrC myData;

myData = myStatement.ColumnTextL(nameIndex);

RDebug::Print(_L("Name=%d\n"), myData);</codeblock> </p> </li> <li id="GUID-0C40380F-9A06-5546-A366-33EBAAA5E9D6"><p>Close the SQL search statement: </p> <p><codeblock id="GUID-1D856776-5557-57EC-8047-270A751405B1" xml:space="preserve">err = myStatement.Close(); </codeblock> </p> <p>When the database search is finished the object should be closed to free up resources. </p> </li> </ol> </li> </ol>  </section> <section id="GUID-8F3A39B6-6514-5A48-BE2E-7893D7625A9C-GENID-1-7-1-21-1-1-6-1-1-10-1-8-1-7-1-3-3"><p>Now that you have performed a basic database query you can start thinking about more advanced querying options. The following will show you how: </p> <ul><li id="GUID-3F4608F9-92E9-52AA-8843-7535688A0D57"><p> <xref href="GUID-2610E11C-26FA-538E-A3E1-34AADA35F20B.dita">Querying Databases: Basic</xref>  </p> </li> <li id="GUID-02919F4F-2F55-5446-9E5B-A1A8CBE9D017"><p> <xref href="GUID-72511204-FC90-54AA-9E2E-833318020318.dita">Reading to a buffer</xref>  </p> </li> <li id="GUID-2A715BF4-6BE3-50A0-8F46-DBD28C1494A7"><p> <xref href="GUID-3CCA6503-54DA-5558-85DC-93A22A81F565.dita">Reading to memory</xref>  </p> </li> <li id="GUID-A4C14656-EE85-53B8-8859-F7F56BC49004"><p> <xref href="GUID-183280EE-0C57-54FE-8ABB-E1CC3BDE525B.dita">Reading to a data stream</xref>  </p> </li> </ul> </section> </conbody><related-links><link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> </link> <link><linktext/></link><link href="GUID-CCB9C61B-FB28-5CD9-A366-4A9584097897.dita"><linktext>Inserting a row into a

                table</linktext> </link> <link href="GUID-3ACF6C11-A9CC-517C-8C7D-578F41F3DDF7.dita"><linktext>Deleting rows from a

                table</linktext> </link> <link href="GUID-72511204-FC90-54AA-9E2E-833318020318.dita"><linktext>Reading to a

                buffer</linktext> </link> <link href="GUID-3CCA6503-54DA-5558-85DC-93A22A81F565.dita"><linktext>Reading to memory</linktext> </link> <link href="GUID-183280EE-0C57-54FE-8ABB-E1CC3BDE525B.dita"><linktext>Reading to a data

                stream</linktext> </link> <link href="GUID-B9A3B17E-BDEB-5F66-968C-080335A721AC.dita"><linktext>Writing to a data