12 <concept id="GUID-271E14D1-7B9B-5048-B1F0-1E25B4EA4E16" xml:lang="en"><title>SqlExample:

    12 <concept id="GUID-271E14D1-7B9B-5048-B1F0-1E25B4EA4E16" xml:lang="en"><title>SqlExample: Creating and Querying an SQL Database</title><shortdesc>This example application demonstrates the use of the SQL

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

    13 API for creating and querying a database.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

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

    14 <section id="GUID-8941D325-DE9F-571B-9EAB-C8F675155EEE"><title>Description</title> <p><b>Creating the database</b> </p> <p>You can create two types

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

    15 of databases; secure and non-secure. </p> <ul>

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

    16 <li id="GUID-3C94A459-7CD3-5B6C-94D9-235F714FD53E"><p> <b>Non-secure

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

    17 database:</b> A database that can be accessed and updated by any program

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

    18 since no security policy is provided. </p> </li>

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

    19 <li id="GUID-1D128D45-7D64-5BB3-A350-FB13CBD4CEE6"><p> <b>Secure database:</b> A database with static policy defined and therefore can be accessed

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

    20 by authorized clients with specific capabilities. </p> </li>

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

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

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

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

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

    23 a non-secure 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. The database is then closed. Finally the database is deleted

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

    24 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 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 the writable drive on the device

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

    25 or the emulator. </p> <p>If path is not specified, it returns the <codeph>KErrArgument</codeph> error, and if the specified path is not writable

    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.

    26 by SQL server, the application returns <codeph>KErrPermission</codeph> error. In this example, the application will create the database

    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

    27 in its own data area, pass a handle to the database to enable 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

    28 data manipulation by the SQL server. </p> <p><note> You cannot create

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

    29 two databases with the same name in a location </note></p><p><b>Related

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

    30 APIs</b></p><ul>

    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>

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

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

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

    35 database, you need to provide the following: </p> <ul>

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

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

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

    39 application to name the database. </p> <p>The format for naming the

    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>

    40 database would be 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

    41 <li id="GUID-328D11AC-814B-532C-8FEA-A0BB467A0CC2"><p> <codeph>X</codeph> is the drive in which the database is created </p> </li>

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

    42 <li id="GUID-12C3F6BD-2E50-5FFE-8B1C-2B73A40C88E6"><p> <codeph>UID</codeph> is the UID of the application that creates the database </p> </li>

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

    43 <li id="GUID-3FA9C3C5-903B-58CD-B509-335686E72BD4"><p> <codeph>name</codeph> is any valid database name. You should not specify the complete

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

    44 path of the database. </p> </li>

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

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

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

    46 have appropriate capabilities such as <codeph>READUSERDATA</codeph>, <codeph>WRITEUSERDATA</codeph>. The example application uses <codeph>NETWORKCONTROL</codeph> capablity along with above specified capabilities. </p> <p> <note>If the given UID does not match with that of client application

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

    47 (in this case, the SQL example application) the attempts to create

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

    48 the database fails.</note> </p> <p><b>Security policy </b> </p> <p>To create a secure database, you need to initially set up a container

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

    49 that has a set of security policies (<xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref>), 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.

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

    50 The <xref href="GUID-81A285F6-3F87-3E77-9426-61BB16BC7109.dita"><apiname>TSecurityPolicy</apiname></xref> object defines what capabilities

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

    51 the calling application must have in order to perform a specific database

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

    52 operation. To cross check if the database security policy matches

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

    53 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 is called. </p><p><b>Related APIs</b></p><ul>

    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>

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

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

    68 policy</p></li>

    57 security policy</p></li>

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

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

    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.

    59 copies 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. If the database was created using a specific UID, then

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

    60 only the application with same UID, can perform the copy operation. </p> <p><note> In this example, copy function is essentially a <i>file

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

    61 copy</i> as the client does a copy within its data cage. The copy

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

    62 operation should ideally fail if the destination database already

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

    63 exists. An application with appropriate permissions can use the file

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

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

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

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

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

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

    67 then demonstrates attaching two databases. In this example, a non-secure

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

    68 database is attached to a secure database. The attached database is

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

    69 later read from and written to, and the secure database is also written

    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

    70 to before the two 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 APIs</b></p><ul>

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

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

    73 how to prepare and execute a query. </p> <p><b>Simple query</b> </p> <p>A simple query statement is prepared and executed using the <codeph>CSqlExample::DataTypesQueryL()</codeph> function. </p> <p><b>Query

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

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

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

    75 with a large parameter is prepared, executed and the results are written

    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.

    76 to <xref href="GUID-6313887C-8DE0-3702-BF4B-0622BAB2EE8E.dita"><apiname>RSqlParamWriteStream</apiname></xref> stream. A table containing

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

    77 fields of data types; integer, 64-bit integer, float, text and binary

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

    78 is created. It inserts two records into the table and implements the <xref href="GUID-E7B775B0-A609-313A-8BFD-375C81D782A5.dita"><apiname>TSqlScalarFullSelectQuery</apiname></xref> function for 64 bit integer (F2)

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

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

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

    80 the data type which the column does not hold is executed, only to

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

    81 show that this is possible. For example, if a column holding the integer

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

    82 value 1000 is queried as real, it would return <codeph>1000.00</codeph>. </p><p><b>Related APIs</b></p><ul>

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

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

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

    84 interface.</p></li>

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

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

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

    87 which 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

    88 </ul> <p><b>Query returning data being read using streaming</b> </p> <p>The example lastly demonstrates how to prepare and execute a

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

    89 query which returns the data, and read that data from the data stream

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

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

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

    91 class is used for reading a large amount of binary or text data from

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

    92 the column. </p><p><b>Related APIs</b></p><ul>

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

    95 <section id="GUID-EA613161-C0ED-42AD-96D5-DBC9A13643E4"><title>Download</title> <p>Click 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>

   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>

    96 <section id="GUID-E5E5C7EA-7E69-42A9-A51C-9B8C0B596605"><title>Class

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

    97 summary</title><ul>

   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

   105 <section id="GUID-AC702731-D7EE-563C-8AE9-2CFAB58AD97C"><title>Build</title> <p>The Symbian build process describes how to build an application. </p> <p>The Sql example builds an executable called <filepath>sqlexample.exe</filepath> in the standard location (<filepath>\epoc32\release\winscw\</filepath> <i>&lt;build_variant&gt;</i> for CodeWarrior). After launching the

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

   106 executable depending on the emulator, you may need to task away from

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

   107 the app launcher or shell screen to view the console. </p> </section>

   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>

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

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

   126 </link>

   110 Tutorials</linktext></link>