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-271E14D1-7B9B-5048-B1F0-1E25B4EA4E16" xml:lang="en"><title>SqlExample:

    13 Creating and Querying an SQL Database</title><shortdesc>This example application demonstrates the use of the SQL API for

    14 creating and querying a database.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

    15 <section id="GUID-8941D325-DE9F-571B-9EAB-C8F675155EEE"><title>Description</title> <p><b>Creating

    16 the database</b> </p> <p>You can create two types of databases; secure and

    17 non-secure. </p> <ul>

    18 <li id="GUID-3C94A459-7CD3-5B6C-94D9-235F714FD53E"><p> <b>Non-secure database:</b> A

    19 database that can be accessed and updated by any program since no security

    20 policy is provided. </p> </li>

    21 <li id="GUID-1D128D45-7D64-5BB3-A350-FB13CBD4CEE6"><p> <b>Secure database:</b> A

    22 database with static policy defined and therefore can be accessed by authorized

    23 clients with specific capabilities. </p> </li>

    24 </ul> <p><b>Creating a non-secure database</b> </p> <p>The application first

    25 creates an <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita"><apiname>RSqlDatabase</apiname></xref> object and creates a non-secure

    26 SQL database using the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-0F4435A7-C28C-342F-AC90-73FABDE6F5DD"><apiname>RSqlDatabase::Create()</apiname></xref> function.

    27 The database is then closed. Finally the database is deleted using the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-27BE67CC-037F-3533-A3B4-E3CC4879EA1C"><apiname>RSqlDatabase::Delete()</apiname></xref> function. </p> <p>The

    28 format for naming the non-secure database would be the following: </p> <codeblock id="GUID-B8E067E8-99B1-56EF-9646-589D38156F90" xml:space="preserve">X:&lt;path&gt;&lt;database-name&gt;.db</codeblock> <p>where, <codeph>X</codeph> is

    29 the writable drive on the device or the emulator. </p> <p>If path is not specified,

    30 it returns the <codeph>KErrArgument</codeph> error, and if the specified path

    31 is not writable by SQL server, the application returns <codeph>KErrPermission</codeph> error.

    32 In this example, the application will create the database in its own data

    33 area, pass a handle to the database to enable the data manipulation by the

    34 SQL server. </p> <p><note> You cannot create two databases with the same name

    35 in a location </note></p><p><b>Related APIs</b></p><ul>

    36 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita"><apiname>RSqlDatabase</apiname></xref> - A handle to a SQL database.</p></li>

    37 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-0F4435A7-C28C-342F-AC90-73FABDE6F5DD"><apiname>RSqlDatabase::Create()</apiname></xref></p></li>

    38 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-27BE67CC-037F-3533-A3B4-E3CC4879EA1C"><apiname>RSqlDatabase::Delete()</apiname></xref></p></li>

    39 </ul> <p><b>Creating a secure database</b> </p> <p>To create a secure database,

    40 you need to provide the following: </p> <ul>

    41 <li id="GUID-AF260BF8-D103-5A08-8BF4-3B8F9F658DBF"><p>UID of the application </p> </li>

    42 <li id="GUID-BCA90A2B-241A-5636-9A2C-080DF6EAAF61"><p>security policy </p> </li>

    43 </ul> <p><b>UID </b> </p> <p>You need to use the UID of the example application

    44 to name the database. </p> <p>The format for naming the database would be

    45 the following: </p> <codeblock id="GUID-768E0B87-B08B-5484-AED0-072D3840CC5B" xml:space="preserve">X:&lt;UID&gt;&lt;name&gt;.db</codeblock> <p>where, </p> <ul>

    46 <li id="GUID-328D11AC-814B-532C-8FEA-A0BB467A0CC2"><p> <codeph>X</codeph> is

    47 the drive in which the database is created </p> </li>

    48 <li id="GUID-12C3F6BD-2E50-5FFE-8B1C-2B73A40C88E6"><p> <codeph>UID</codeph> is

    49 the UID of the application that creates the database </p> </li>

    50 <li id="GUID-3FA9C3C5-903B-58CD-B509-335686E72BD4"><p> <codeph>name</codeph> is

    51 any valid database name. You should not specify the complete path of the database. </p> </li>

    52 </ul> <p>To successfully create and use a secure database, you must have appropriate

    53 capabilities such as <codeph>READUSERDATA</codeph>, <codeph>WRITEUSERDATA</codeph>.

    54 The example application uses <codeph>NETWORKCONTROL</codeph> capablity along

    55 with above specified capabilities. </p> <p> <note>If the given UID does not

    56 match with that of client application (in this case, the SQL example application)

    57 the attempts to create the database fails.</note> </p> <p><b>Security policy </b> </p> <p>To

    58 create a secure database, you need to initially set up a container that has

    59 a set of security policies (<xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref>), and pass

    60 it to the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-0F4435A7-C28C-342F-AC90-73FABDE6F5DD"><apiname>RSqlDatabase::Create()</apiname></xref> function. The <xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref> object

    61 defines what capabilities the calling application must have in order to perform

    62 a specific database operation. To cross check if the database security policy

    63 matches with the policy used when the database was created, the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-43E42D5C-62B8-34A7-AF2E-D62432DDB9DE"><apiname>RSqlDatabase::GetSecurityPolicy()</apiname></xref> function

    64 is called. </p><p><b>Related APIs</b></p><ul>

    65 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-0F4435A7-C28C-342F-AC90-73FABDE6F5DD"><apiname>RSqlDatabase::Create()</apiname></xref></p></li>

    66 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-43E42D5C-62B8-34A7-AF2E-D62432DDB9DE"><apiname>RSqlDatabase::GetSecurityPolicy()</apiname></xref></p></li>

    67 <li><p><xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref> - Class representing a generic security

    68 policy</p></li>

    69 </ul> <p><b>Copying one database to another</b> </p> <p>The example copies

    70 one database to another using the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-D8B48367-D20B-337E-B41A-1E02119D91D6"><apiname>RSqlDatabase::Copy()</apiname></xref> function.

    71 If the database was created using a specific UID, then only the application

    72 with same UID, can perform the copy operation. </p> <p><note> In this example,

    73 copy function is essentially a <i>file copy</i> as the client does a copy

    74 within its data cage. The copy operation should ideally fail if the destination

    75 database already exists. An application with appropriate permissions can use

    76 the file system to copy the database.</note> </p><p><b>Related APIs</b></p><ul>

    77 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-D8B48367-D20B-337E-B41A-1E02119D91D6"><apiname>RSqlDatabase::Copy()</apiname></xref></p></li>

    78 </ul> <p><b>Attaching the database</b> </p> <p>The example application then

    79 demonstrates attaching two databases. In this example, a non-secure database

    80 is attached to a secure database. The attached database is later read from

    81 and written to, and the secure database is also written to before the two

    82 databases are deleted using the <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-27BE67CC-037F-3533-A3B4-E3CC4879EA1C"><apiname>RSqlDatabase::Delete()</apiname></xref> function. </p><p><b>Related

    83 APIs</b></p><ul>

    84 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-27BE67CC-037F-3533-A3B4-E3CC4879EA1C"><apiname>RSqlDatabase::Delete()</apiname></xref></p></li>

    85 </ul> <p><b>Querying the database</b> </p> <p>The example then demonstrates

    86 how to prepare and execute a query. </p> <p><b>Simple query</b> </p> <p>A

    87 simple query statement is prepared and executed using the <codeph>CSqlExample::DataTypesQueryL()</codeph> function. </p> <p><b>Query

    88 with a large parameter and writing using streaming</b> </p> <p>A query with

    89 a large parameter is prepared, executed and the results are written to <xref href="GUID-6313887C-8DE0-3702-BF4B-0622BAB2EE8E.dita"><apiname>RSqlParamWriteStream</apiname></xref> stream.

    90 A table containing fields of data types; integer, 64-bit integer, float, text

    91 and binary is created. It inserts two records into the table and implements

    92 the <xref href="GUID-E7B775B0-A609-313A-8BFD-375C81D782A5.dita"><apiname>TSqlScalarFullSelectQuery</apiname></xref> function for 64 bit integer

    93 (F2) and text (F4) fields and checks the returned value. The query for the

    94 data type which the column does not hold is executed, only to show that this

    95 is possible. For example, if a column holding the integer value 1000 is queried

    96 as real, it would return <codeph>1000.00</codeph>. </p><p><b>Related APIs</b></p><ul>

    97 <li><p><xref href="GUID-6313887C-8DE0-3702-BF4B-0622BAB2EE8E.dita"><apiname>RSqlParamWriteStream</apiname></xref> - The write stream interface.</p></li>

    98 <li><p><xref href="GUID-E7B775B0-A609-313A-8BFD-375C81D782A5.dita"><apiname>TSqlScalarFullSelectQuery</apiname></xref> - TSqlScalarFullSelectQuery

    99 interface is used for executing <codeph>SELECT</codeph> sql queries, which

   100 return a single row consisting of a single column value.</p></li>

   101 </ul> <p><b>Query returning data being read using streaming</b> </p> <p>The

   102 example lastly demonstrates how to prepare and execute a query which returns

   103 the data, and read that data from the data stream (<xref href="GUID-35BF7B4A-C4F7-3215-B5DF-6D0682247976.dita"><apiname>RSqlColumnReadStream</apiname></xref>).

   104 The read stream interface class is used for reading a large amount of binary

   105 or text data from the column. </p><p><b>Related APIs</b></p><ul>

   106 <li><p><xref href="GUID-35BF7B4A-C4F7-3215-B5DF-6D0682247976.dita"><apiname>RSqlColumnReadStream</apiname></xref> - The read stream interface.</p></li>

   107 </ul> </section>

   108 <section id="GUID-EA613161-C0ED-42AD-96D5-DBC9A13643E4"><title>Download</title> <p>Click

   109 on the following link to download the example: <xref href="guid-6013a680-57f9-415b-8851-c4fa63356636/zips/guid-ef83061e-0e34-482b-bfcf-267d8972e786.zip" scope="external">SqlExample.zip</xref></p><p>Click: <xref href="guid-6013a680-57f9-415b-8851-c4fa63356636/guid-ef83061e-0e34-482b-bfcf-267d8972e786.html" scope="peer">browse</xref> to view the example code.</p> </section>

   110 <section id="GUID-E5E5C7EA-7E69-42A9-A51C-9B8C0B596605"><title>Class summary</title><ul>

   111 <li><p><xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita"><apiname>RSqlDatabase</apiname></xref></p></li>

   112 <li><p><xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref></p></li>

   113 <li><p><xref href="GUID-35BF7B4A-C4F7-3215-B5DF-6D0682247976.dita"><apiname>RSqlColumnReadStream</apiname></xref></p></li>

   114 <li><p><xref href="GUID-6313887C-8DE0-3702-BF4B-0622BAB2EE8E.dita"><apiname>RSqlParamWriteStream</apiname></xref></p></li>

   115 <li><p><xref href="GUID-56C52D43-C4A9-3B31-B154-CA1E77693F57.dita"><apiname>RSqlSecurityPolicy</apiname></xref></p></li>

   116 <li><p><xref href="GUID-E7B775B0-A609-313A-8BFD-375C81D782A5.dita"><apiname>TSqlScalarFullSelectQuery</apiname></xref></p></li>

   117 </ul></section>

   118 <section id="GUID-AC702731-D7EE-563C-8AE9-2CFAB58AD97C"><title>Build</title> <p>The <xref href="GUID-3100800B-B2F7-50EF-BD4C-3C345ECCB2A5.dita">Symbian build

   119 process</xref> describes how to build an application. </p> <p>The Sql example

   120 builds an executable called <filepath>sqlexample.exe</filepath> in the standard

   121 location (<filepath>\epoc32\release\winscw\</filepath> <i>&lt;build_variant&gt;</i> for

   122 CodeWarrior). After launching the executable depending on the emulator, you

   123 may need to task away from the app launcher or shell screen to view the console. </p> </section>

   124 </conbody><related-links>

   125 <link href="GUID-582CAA4A-1240-5138-983D-D9C6EEAF5566.dita"><linktext>SQL Tutorials</linktext>

   126 </link>

   127 </related-links></concept>