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><prolog><metadata><keywords/></metadata></prolog><conbody>

    14 <p>This example application demonstrates the use of the SQL API for creating

    15 and querying a database. </p>

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

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

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

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

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

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

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

    23 database with static policy defined and therefore can be accessed by authorised

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

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

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

    27 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.

    28 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

    29 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

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

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

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

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

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

    35 SQL server. </p> <p> <b>Note:</b> You cannot create two databases with the

    36 same name in a location </p><p><b>Related APIs</b></p><ul>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    56 with above specified capabilities. </p> <p> <b>Note:</b> If the given UID

    57 does not match with that of client application (in this case, the SQL example

    58 application) the attempts to create the database fails. </p> <p><b>Security

    59 policy </b> </p> <p>To create a secure database, you need to initially set

    60 up a container that has a set of security policies (<xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref>),

    61 and pass 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

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

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

    64 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

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

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

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

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

    69 policy</p></li>

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

    71 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.

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

    73 with same UID, can perform the copy operation. </p> <p> <b>Note:</b> In this

    74 example, copy function is essentially a <i>file copy</i> as the client does

    75 a copy within its data cage. The copy operation should ideally fail if the

    76 destination database already exists. An application with appropriate permissions

    77 can use the file system to copy the database. </p><p><b>Related APIs</b></p><ul>

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

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

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

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

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

    83 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

    84 APIs</b></p><ul>

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

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

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

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

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

    90 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.

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

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

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

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

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

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

    97 as real, it would return <systemoutput>1000.00</systemoutput>. </p><p><b>Related

    98 APIs</b></p><ul>

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

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

   101 interface is used for executing SELECT sql queries, which return a single

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

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

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

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

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

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

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

   109 </ul> </section>

   110 <section><title>Download</title> <p>Click on the following link to download

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

   112 <section><title>Class summary</title><ul>

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

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

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

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

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

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

   119 </ul></section>

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

   121 how to build an application. </p> <p>The Sql example builds an executable

   122 called <filepath>sqlexample.exe</filepath> in the standard location (<filepath>\epoc32\release\winscw\</filepath> <i>&lt;build_variant&gt;</i> for

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

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

   125 <section id="GUID-4FBE56C6-5DCE-56C5-98EA-75C863E5C5D6"><title>See

   126 also</title><xref href="GUID-582CAA4A-1240-5138-983D-D9C6EEAF5566.dita">SQL Tutorials</xref> </section>

   127 </conbody></concept>