<?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-C60DAE3D-7FB9-5619-9E5D-476A430705AF" xml:lang="en"><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 id="GUID-AF611AF2-85EC-4F02-B43F-A0EFAAEA9CAB"><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)
    &lt;OK - no more records&gt;;
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><p> This section deals with finding and returning the first matching
record</p><p>  only. Getting all matches in a database is briefly discussed
at the end of this</p><p> section. </p> </section>

<section id="GUID-8F3A39B6-6514-5A48-BE2E-7893D7625A9C-GENID-1-10-1-22-1-1-6-1-1-9-1-8-1-8-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 href="GUID-CCB9C61B-FB28-5CD9-A366-4A9584097897.dita"><linktext>Inserting
a row into a                 table</linktext></link>
<link href="GUID-B61EA8C5-0966-51DE-AC73-01DD34C7D3CC.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                 stream</linktext></link>
<link href="GUID-C474376E-1766-5781-B5BF-3786C5B4D72E.dita"><linktext>Scalar queries</linktext>
</link>
</related-links></concept>