×
Namespaces

Variants
Actions
(Difference between revisions)

Archived:Using Symbian SQL API with SQL statements which return data

From Nokia Developer Wiki
Jump to: navigation, search
aknyman (Talk | contribs)
(New page: __NOTOC__ __NOEDITSECTION__ {|style="background:#eceff2" width="660px" border="1" cellpadding="5" cellspacing="0" |- |'''ID''' ||   |'''Creation date''' || April 24, 2008 |- |'''Plat...)
 
lpvalente (Talk | contribs)
m (Lpvalente -)
 
(16 intermediate revisions by 5 users not shown)
Line 1: Line 1:
__NOTOC__
+
[[Category:Symbian C++]][[Category:Code Snippet]][[Category:Files/Data]][[Category:S60 3rd Edition FP2]][[Category:Code Snippet]]
__NOEDITSECTION__
+
{{Archived|timestamp=20120313131216|user=roy.debjit| }}
 
+
{|style="background:#eceff2" width="660px" border="1" cellpadding="5" cellspacing="0"
+
|-
+
|'''ID''' ||  
+
|'''Creation date''' || April 24, 2008
+
|-
+
|'''Platform''' || S60 3rd Edition, FP2
+
|'''Tested on devices''' || Nokia 6220 Classic
+
|-
+
|'''Category''' || Symbian C++
+
|'''Subcategory''' || Files/Data
+
|}
+
 
+
 
+
{|style="background:#eceff2" width="660px" border="1" cellpadding="5" cellspacing="0"
+
|-
+
|'''Keywords (APIs, classes, methods, functions):''' RSqlDatabase, RSqlStatement, RSqlDatabase::Open(), RSqlDatabase::Close(), RSqlStatement::Prepare(), RSqlStatement::ParameterIndex(), RSqlStatement::BindInt(), RSqlStatement::ColumnIndex(), RSqlStatement::ColumnTextL(), RSqlStatement::ColumnInt(), RSqlStatement::ColumnSize(), RSqlStatement::Next(), RSqlStatement::Close()
+
|}
+
  
 +
{{ArticleMetaData <!-- v1.2 -->
 +
|sourcecode= <!-- Link to example source code (e.g. [[Media:The Code Example ZIP.zip]]) -->
 +
|installfile= <!-- Link to installation file (e.g. [[Media:The Installation File.sis]]) -->
 +
|devices= Nokia 6220 Classic
 +
|sdk= <!-- SDK(s) built and tested against (e.g. [http://linktosdkdownload/ Nokia Qt SDK 1.1]) -->
 +
|platform= S60 3rd Edition, FP2
 +
|devicecompatability= <!-- Compatible devices (e.g.: All* (must have GPS) ) -->
 +
|dependencies= <!-- Any other/external dependencies e.g.: Google Maps Api v1.0 -->
 +
|signing= <!-- Empty or one of Self-Signed, DevCert, Manufacturer -->
 +
|capabilities= <!-- Capabilities required by the article/code example (e.g. Location, NetworkServices. -->
 +
|keywords= RSqlDatabase, RSqlStatement, RSqlDatabase::Open(), RSqlDatabase::Close(), RSqlStatement::Prepare(), RSqlStatement::ParameterIndex(), RSqlStatement::BindInt(), RSqlStatement::ColumnIndex(), RSqlStatement::ColumnTextL(), RSqlStatement::ColumnInt(), RSqlStatement::ColumnSize(), RSqlStatement::Next(), RSqlStatement::Close()
 +
|language= <!-- Language category code for non-English topics - e.g. Lang-Chinese -->
 +
|translated-by= <!-- [[User:XXXX]] -->
 +
|translated-from-title= <!-- Title only -->
 +
|translated-from-id= <!-- Id of translated revision -->
 +
|review-by= <!-- After re-review: [[User:username]] -->
 +
|review-timestamp= <!-- After re-review: YYYYMMDD -->
 +
|update-by= <!-- After significant update: [[User:username]]-->
 +
|update-timestamp= <!-- After significant update: YYYYMMDD -->
 +
|creationdate= 20080424
 +
|author= [[User:Aknyman]]
 +
<!-- The following are not in current metadata -->
 +
|subcategory= Files/Data
 +
|id= CS000928
 +
}}
 +
 
 
==Overview==
 
==Overview==
  
The <tt>RSqlStatement</tt> class provides functionality to execute all types of SQL statements when using SQL API. <tt>RSqlStatement</tt> encapsulates a SQL statement and the data that it returns. <tt>RSqlStatement</tt> statements can be executed with methods: <tt>Exec()</tt> and <tt>Next()</tt>. The method <tt>Exec()</tt> is used when statement do not return any data and <tt>Next()</tt> when statement do. This snippet shows some examples how to use the <tt>Next()</tt> method.
+
{{Abstract|The {{Icode|RSqlStatement}} class provides functionality to execute all types of SQL statements when using the SQL API. {{Icode|RSqlStatement}} encapsulates an SQL statement and the data that it returns. {{Icode|RSqlStatement}} statements can be executed with the methods {{Icode|Exec()}} and {{Icode|Next()}}. The method {{Icode|Exec()}} is used when the statement does not return any data and {{Icode|Next()}} when the statement returns data. This snippet gives some examples on how to use the {{Icode|Next()}} method.}}
  
Statements use so called placeholders (:) in descriptors to mark positions where dynamic data is inserted at runtime. The user can bind the actual data to placeholders with different <tt>Bind()</tt> methods e.g. BindText() is one of them. It is a good practise to resolve the placeholder index with <tt>RSqlStatement::ParameterIndex()</tt> method, and not use hardcoded values.
+
Statements use so-called placeholders (:) in descriptors to mark positions where dynamic data is inserted at runtime. The user can bind the actual data to placeholders with different {{Icode|Bind()}} methods, such as {{Icode|BindText()}}. It is good practise to resolve the placeholder index with the {{Icode|RSqlStatement::ParameterIndex()}} method, and not use hardcoded values.
  
The method <tt>ColumnSize()</tt> helps when size of returned data is unknown and the user wants to copy it to the allocated buffer. Copy operation into user allocated buffer can be done with <tt>Column()</tt> methods e.g. ColumnText() is one of them.
+
The method {{Icode|ColumnSize()}} helps when the size of returned data is unknown and the user wants to copy it to the allocated buffer. Data can be copied into the user-allocated buffer with {{Icode|Column()}} methods, such as {{Icode|ColumnText()}}.
  
 
This snippet can be self-signed.
 
This snippet can be self-signed.
Line 36: Line 44:
  
 
<code>
 
<code>
LIBRARY euser.lib
+
LIBRARY euser.lib
LIBRARY sqldb.lib
+
LIBRARY sqldb.lib
 
</code>
 
</code>
  
Line 50: Line 58:
 
==Preconditions==
 
==Preconditions==
  
Databases nonsecure.db and [UID3]secure.db needs to be created before this code snippet.
+
Databases nonsecure.db and [UID3]secure.db need to be created before executing this code snippet. See [[Archived:Using SQL API for creating non-secure and secure databases on Symbian]].
 
+
See [[Using_SQL_API_for_creating_non-secure_and_secure_databases]]
+
  
 
<!--
 
<!--
Line 183: Line 189:
 
==Postconditions==
 
==Postconditions==
  
The <tt>RSqlStatement</tt> class is used to fetch data from nonsecure.db and [UID3]secure.db databases. The first statement is used to retrieve a numeric data and the second one is used to retrieve a textual data.
+
The {{Icode|RSqlStatement}} class is used to fetch data from the nonsecure.db and [UID3]secure.db databases. The first statement is used to retrieve numeric data and the second one is used to retrieve textual data.
  
 
==See also==
 
==See also==
  
* [[Using_SQL_API_for_creating_non-secure_and_secure_databases]]
+
* [[Archived:Using SQL API for creating non-secure and secure databases on Symbian]]
* [[Using_SQL_API_for_attaching_and_detaching_databases]]
+
* [[Archived:Using Symbian SQL API for attaching and detaching databases]]
* [[Using_SQL_API_with_SQL_statements_which_do_not_return_data]]
+
* [[Archived:Using Symbian SQL API with SQL statements which do not return data]]
* [[Using_SQL_API_with_scalar_queries]]
+
* [[Archived:Using Symbian SQL API with scalar queries]]
* [[Using_SQL_API_with_data_streams]]
+
* [[Archived:Using Symbian SQL API with data streams]]
 
+
[[Category:Symbian C++]][[Category:Code Examples]][[Category:Files/Data]]
+

Latest revision as of 20:34, 20 August 2012

Archived.pngArchived: This article is archived because it is not considered relevant for third-party developers creating commercial solutions today. If you think this article is still relevant, let us know by adding the template {{ReviewForRemovalFromArchive|user=~~~~|write your reason here}}.

Article Metadata
Tested with
Devices(s): Nokia 6220 Classic
Compatibility
Platform(s): S60 3rd Edition, FP2
S60 3rd Edition FP2
Article
Keywords: RSqlDatabase, RSqlStatement, RSqlDatabase::Open(), RSqlDatabase::Close(), RSqlStatement::Prepare(), RSqlStatement::ParameterIndex(), RSqlStatement::BindInt(), RSqlStatement::ColumnIndex(), RSqlStatement::ColumnTextL(), RSqlStatement::ColumnInt(), RSqlStatement::ColumnSize(), RSqlStatement::Next(), RSqlStatement::Close()
Created: aknyman (24 Apr 2008)
Last edited: lpvalente (20 Aug 2012)

Contents

[edit] Overview

The RSqlStatement class provides functionality to execute all types of SQL statements when using the SQL API. RSqlStatement encapsulates an SQL statement and the data that it returns. RSqlStatement statements can be executed with the methods Exec() and Next(). The method Exec() is used when the statement does not return any data and Next() when the statement returns data. This snippet gives some examples on how to use the Next() method.

Statements use so-called placeholders (:) in descriptors to mark positions where dynamic data is inserted at runtime. The user can bind the actual data to placeholders with different Bind() methods, such as BindText(). It is good practise to resolve the placeholder index with the RSqlStatement::ParameterIndex() method, and not use hardcoded values.

The method ColumnSize() helps when the size of returned data is unknown and the user wants to copy it to the allocated buffer. Data can be copied into the user-allocated buffer with Column() methods, such as ColumnText().

This snippet can be self-signed.


[edit] MMP file

The following libraries are required:

LIBRARY euser.lib
LIBRARY sqldb.lib


The following capabilities are needed to test the example secure database:

CAPABILITY ReadUserData
CAPABILITY WriteUserData

[edit] Preconditions

Databases nonsecure.db and [UID3]secure.db need to be created before executing this code snippet. See Archived:Using SQL API for creating non-secure and secure databases on Symbian.


[edit] Source file

#include <e32base.h>
#include <SqlDb.h>
 
void NextStatementsL()
{
RSqlDatabase database;
_LIT(KNonSecureDbName, "\\nonsecure.db");
_LIT(KSecureDbName, "[E80000AF]secure.db");
 
//== Retrieving numeric data
TInt error = database.Open(KNonSecureDbName);
 
if (error == KErrNone)
{
CleanupClosePushL(database);
 
RSqlStatement sqlSelectFromMoviesTableStatement;
_LIT(KSqlSelectFromMoviesTable, "SELECT * FROM MOVIES WHERE YEAR < :Val1");
TInt ret = sqlSelectFromMoviesTableStatement.Prepare(database, KSqlSelectFromMoviesTable);
 
if(ret == KErrNone)
{
CleanupClosePushL(sqlSelectFromMoviesTableStatement);
TInt paramIndex1 = sqlSelectFromMoviesTableStatement.ParameterIndex(_L(":Val1"));
 
User::LeaveIfError(sqlSelectFromMoviesTableStatement.BindInt(paramIndex1, 2000));
 
TInt columnIndex = sqlSelectFromMoviesTableStatement.ColumnIndex(_L("ID"));
TInt err = KErrNone;
 
while((err = sqlSelectFromMoviesTableStatement.Next()) == KSqlAtRow)
{
TInt id = sqlSelectFromMoviesTableStatement.ColumnInt(columnIndex);
//Do something with id (movie released before year 2000 )
}
if(err == KSqlAtEnd)
{
//OK - no more records
}
else
{
//process the error
}
 
CleanupStack::PopAndDestroy(1); //sqlSelectFromMoviesTableStatement
}
else
{
//prepare sql statement failed
}
CleanupStack::PopAndDestroy(1); //database
}
else
{
//open database failed
}
 
//== Retrieving text data
 
error = database.Open(KSecureDbName);
 
if (error == KErrNone)
{
CleanupClosePushL(database);
 
RSqlStatement sqlSelectFromBooksTableStatement;
_LIT(KSqlSelectFromBooksTable, "SELECT * FROM BOOKS");
TInt ret = sqlSelectFromBooksTableStatement.Prepare(database, KSqlSelectFromBooksTable);
 
if(ret == KErrNone)
{
CleanupClosePushL(sqlSelectFromBooksTableStatement);
 
TInt columnIndex1 = sqlSelectFromBooksTableStatement.ColumnIndex(_L("TITLE"));
TInt columnIndex2 = sqlSelectFromBooksTableStatement.ColumnIndex(_L("AUTHOR"));
 
TInt err = KErrNone;
 
while((err = sqlSelectFromBooksTableStatement.Next()) == KSqlAtRow)
{
// access data with copying:
RBuf title;
TInt titleSize = sqlSelectFromBooksTableStatement.ColumnSize(columnIndex1);
title.CreateL(titleSize);
CleanupClosePushL(title);
User::LeaveIfError(sqlSelectFromBooksTableStatement.ColumnText(columnIndex1,title));
//do something with the data...
CleanupStack::PopAndDestroy(1); //title
 
// ...or access data without copying:
TPtrC author = sqlSelectFromBooksTableStatement.ColumnTextL(columnIndex2);
//do something with the data...
}
if(err == KSqlAtEnd)
{
//OK - no more records
}
else
{
//process the error
}
CleanupStack::PopAndDestroy(1); //sqlSelectFromMoviesTableStatement
}
else
{
//prepare sql statement failed
}
CleanupStack::PopAndDestroy(1); //database
}
else
{
//open database failed
}
 
}

[edit] Postconditions

The RSqlStatement class is used to fetch data from the nonsecure.db and [UID3]secure.db databases. The first statement is used to retrieve numeric data and the second one is used to retrieve textual data.

[edit] See also

This page was last modified on 20 August 2012, at 20:34.
86 page views in the last 30 days.