|
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-C60DAE3D-7FB9-5619-9E5D-476A430705AF" xml:lang="en"><title>Querying |
|
13 a database</title><shortdesc>This tutorial shows you how to create a simple SQL database query. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
|
14 <section id="GUID-0A238212-37C9-5B4B-A449-F8C81CE971A8"><title>Introduction</title> <p>This |
|
15 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 |
|
16 to query a database. </p> <p>The SQL statement used for the tutorial is shown |
|
17 here: </p> <p><userinput><parmname>SELECT</parmname> <cmdname>name</cmdname> <parmname>FROM</parmname> <cmdname>countries</cmdname> <parmname>WHERE</parmname> <cmdname>population > 10</cmdname> </userinput> </p> <p>The (<codeph>SELECT</codeph>) |
|
18 results of the query will be the value in the '<codeph>name</codeph>' column <codeph>FROM</codeph> the |
|
19 '<codeph>countries</codeph>' table <codeph>WHERE</codeph> the value of the |
|
20 '<codeph>population</codeph>' column of the same record is <codeph>></codeph> the |
|
21 value specified. </p> </section> |
|
22 <section id="GUID-AF611AF2-85EC-4F02-B43F-A0EFAAEA9CAB"><title>Procedure</title> <ol id="GUID-2C39BABE-F2D4-510A-A406-D2C48412B6F8"> |
|
23 <li id="GUID-56D39707-7D55-5843-8966-36223BB20EFC"><p>Prepare the Statement: </p> <p>The |
|
24 steps to prepare a SQL statement are shown here. </p> <ol id="GUID-8B003014-94BB-5B4E-9D9C-8098A3642EA6"> |
|
25 <li id="GUID-A988E274-8B21-5DCB-A616-06CD3C6F5680"><p>Set up some constants |
|
26 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"); |
|
27 _LIT(kPopulation,"population"); |
|
28 _LIT(kVal,":Value"); |
|
29 _LIT(kQueryString,"SELECT name FROM countries WHERE population > :Value");</codeblock> </p> <p>This |
|
30 defines the query parameters. </p> </li> |
|
31 <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 |
|
32 statement: </p> <p><codeblock id="GUID-41EB4CD0-E131-5C85-B799-5940853B9F60" xml:space="preserve">RSqlStatement myStatement;</codeblock> </p> </li> |
|
33 <li id="GUID-5FD16C7C-591D-5006-A177-C899314AA798"><p>Define the indices to |
|
34 be used in the search: </p> <p><codeblock id="GUID-351298BC-348F-57E4-BC30-E6706385D8AA" xml:space="preserve">TInt nameIndex = myStatement.ColumnIndex(kName); |
|
35 TInt populationIndex = myStatement.ColumnIndex(kPopulation);</codeblock> </p> </li> |
|
36 <li id="GUID-17A647B3-6E8C-54FB-A21E-23425AD343FD"><p>Set the 32-bit integer |
|
37 value for the SQL parameter 'value': </p> <p><codeblock id="GUID-838D0598-1E98-57E8-AC66-68AA590EDFBA" xml:space="preserve">TInt parameterIndex = myStatement.ParameterIndex(kVal); |
|
38 err = myStatement.BindInt(parameterIndex,10);</codeblock> </p> <p>The SQL |
|
39 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"); |
|
40 ... |
|
41 ...WHERE population > :Value") </codeblock> </p> <p>The parameter plus constant, |
|
42 along with other parts of the statement, are converted into: </p> <p>in SQL |
|
43 syntax. </p> </li> |
|
44 <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; |
|
45 err = myStatement.Prepare(countriesDatabase,kQueryString);</codeblock> </p> <p>This |
|
46 creates a parameterised SQL statement executable. </p> </li> |
|
47 </ol> </li> |
|
48 <li id="GUID-1153684C-697C-5C84-A15C-99C96820C8FB"><p>Run the SQL query: </p> <ol id="GUID-186BD930-F9A1-5244-BEA8-3774DC6D3F91"> |
|
49 <li id="GUID-1D0B5C50-E1C6-51DC-8CCE-2C2B4C33F7F6"><p>Search the records until |
|
50 a match is found: </p> <p><codeblock id="GUID-DEF39928-2DBB-5017-A47C-1BB6475B44B7" xml:space="preserve">while((err = myStatement.Next()) == KSqlAtRow) |
|
51 { |
|
52 Do something with the query results |
|
53 }</codeblock> </p> <p>Next() fires the executable SQL statement and stops |
|
54 at and returns the matched record. </p> <p>Do something if no results are |
|
55 found. </p> <p><codeblock id="GUID-ADD04732-1D9C-5716-B885-14D92D341149" xml:space="preserve">if(err == KSqlAtEnd) |
|
56 <OK - no more records>; |
|
57 else |
|
58 <process the error>;</codeblock> </p> </li> |
|
59 </ol> </li> |
|
60 <li id="GUID-26B8C900-50D3-59D2-BACD-61C825B2A3A4"><p>The query is done and |
|
61 you have the results. In this section we look at a simple way to do something |
|
62 with the results and we close the SQL statement object. </p> <ol id="GUID-79A55F5A-E42B-5AA2-B451-080E36100E0A"> |
|
63 <li id="GUID-5F24A346-F2DA-5544-9AB2-DD046E4D80DD"><p>Get the results of the |
|
64 search: </p> <p><codeblock id="GUID-86F158DF-88FF-5128-9B6E-461D6C3163D7" xml:space="preserve">TPtrC myData; |
|
65 myData = myStatement.ColumnTextL(nameIndex); |
|
66 RDebug::Print(_L("Name=%d\n"), myData);</codeblock> </p> </li> |
|
67 <li id="GUID-0C40380F-9A06-5546-A366-33EBAAA5E9D6"><p>Close the SQL search |
|
68 statement: </p> <p><codeblock id="GUID-1D856776-5557-57EC-8047-270A751405B1" xml:space="preserve">err = myStatement.Close(); </codeblock> </p> <p>When |
|
69 the database search is finished the object should be closed to free up resources. </p> </li> |
|
70 </ol> </li> |
|
71 </ol><p> This section deals with finding and returning the first matching |
|
72 record</p><p> only. Getting all matches in a database is briefly discussed |
|
73 at the end of this</p><p> section. </p> </section> |
|
74 |
|
75 <section id="GUID-8F3A39B6-6514-5A48-BE2E-7893D7625A9C-GENID-1-8-1-21-1-1-6-1-1-9-1-8-1-8-1-3-3"><p>Now that you have |
|
76 performed a basic database query you can start thinking about more advanced |
|
77 querying options. The following will show you how: </p> <ul> |
|
78 <li id="GUID-3F4608F9-92E9-52AA-8843-7535688A0D57"><p> <xref href="GUID-2610E11C-26FA-538E-A3E1-34AADA35F20B.dita">Querying |
|
79 Databases: Basic</xref> </p> </li> |
|
80 <li id="GUID-02919F4F-2F55-5446-9E5B-A1A8CBE9D017"><p> <xref href="GUID-72511204-FC90-54AA-9E2E-833318020318.dita">Reading |
|
81 to a buffer</xref> </p> </li> |
|
82 <li id="GUID-2A715BF4-6BE3-50A0-8F46-DBD28C1494A7"><p> <xref href="GUID-3CCA6503-54DA-5558-85DC-93A22A81F565.dita">Reading |
|
83 to memory</xref> </p> </li> |
|
84 <li id="GUID-A4C14656-EE85-53B8-8859-F7F56BC49004"><p> <xref href="GUID-183280EE-0C57-54FE-8ABB-E1CC3BDE525B.dita">Reading |
|
85 to a data stream</xref> </p> </li> |
|
86 </ul> </section> |
|
87 </conbody><related-links> |
|
88 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
|
89 </link> |
|
90 <link href="GUID-CCB9C61B-FB28-5CD9-A366-4A9584097897.dita"><linktext>Inserting |
|
91 a row into a table</linktext></link> |
|
92 <link href="GUID-B61EA8C5-0966-51DE-AC73-01DD34C7D3CC.dita"><linktext>Deleting |
|
93 Rows from a Table</linktext></link> |
|
94 <link href="GUID-72511204-FC90-54AA-9E2E-833318020318.dita"><linktext>Reading to |
|
95 a buffer</linktext></link> |
|
96 <link href="GUID-3CCA6503-54DA-5558-85DC-93A22A81F565.dita"><linktext>Reading to |
|
97 memory</linktext></link> |
|
98 <link href="GUID-183280EE-0C57-54FE-8ABB-E1CC3BDE525B.dita"><linktext>Reading to |
|
99 a data stream</linktext></link> |
|
100 <link href="GUID-B9A3B17E-BDEB-5F66-968C-080335A721AC.dita"><linktext>Writing to |
|
101 a data stream</linktext></link> |
|
102 <link href="GUID-C474376E-1766-5781-B5BF-3786C5B4D72E.dita"><linktext>Scalar queries</linktext> |
|
103 </link> |
|
104 </related-links></concept> |