Archived:Using Symbian SQL API with SQL statements which return data
(New page: __NOTOC__ __NOEDITSECTION__ {|style="background:#eceff2" width="660px" border="1" cellpadding="5" cellspacing="0" |- |'''ID''' || |'''Creation date''' || April 24, 2008 |- |'''Plat...) |
m (Protected "Using SQL API with SQL statements which return data" [edit=sysop:move=sysop]) |
Revision as of 10:40, 2 May 2008
| ID | Creation date | April 24, 2008 | |
| Platform | S60 3rd Edition, FP2 | Tested on devices | Nokia 6220 Classic |
| Category | Symbian C++ | Subcategory | Files/Data |
| 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() |
Overview
The RSqlStatement class provides functionality to execute all types of SQL statements when using SQL API. RSqlStatement encapsulates a SQL statement and the data that it returns. RSqlStatement statements can be executed with methods: Exec() and Next(). The method Exec() is used when statement do not return any data and Next() when statement do. This snippet shows some examples 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 e.g. BindText() is one of them. It is a good practise to resolve the placeholder index with RSqlStatement::ParameterIndex() method, and not use hardcoded values.
The method ColumnSize() 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 Column() methods e.g. ColumnText() is one of them.
This snippet can be self-signed.
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
Preconditions
Databases nonsecure.db and [UID3]secure.db needs to be created before this code snippet.
See Using_SQL_API_for_creating_non-secure_and_secure_databases
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
}
}
Postconditions
The RSqlStatement 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.
