You Are Here:

Community: Wiki

This page was last modified on 9 October 2008, at 18:01.

CS000929 - Using SQL API with scalar queries

From Forum Nokia Wiki



ID CS000929 Creation date May 2, 2008
Platform S60 3rd Edition, FP2 Tested on devices Nokia 6220 Classic
Category Symbian C++ Subcategory Files/Data


Keywords (APIs, classes, methods, functions): RSqlDatabase, TSqlScalarFullSelectQuery, RSqlDatabase::Open(), RSqlDatabase::Close(), TSqlScalarFullSelectQuery::SelectIntL(), TSqlScalarFullSelectQuery::SelectTextL()

Overview

The TSqlScalarFullSelectQuery class is used in situations where the SELECT SQL query refering to several data fields returns only one row with a single column value. When compared to class RSqlStatement, TSqlScalarFullSelectQuery is more efficient and recommended to use when possible. The class instance is created with a parameter that refers to the opened database. TSqlFullSelectQuery has a number of methods that take the SELECT SQL query as a parameter and return values of different data types. This snippet shows how to use methods TSqlScalarFullSelectQuery::SelectIntL() and TSqlScalarFullSelectQuery::SelectTextL().

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 need to be created before executing this code snippet. See CS000925 - Using SQL API for creating non-secure and secure databases.


Source file

#include <e32base.h>
#include <SqlDb.h>
 
void ScalarQueriesL()
{
RSqlDatabase database;
_LIT(KNonSecureDbName, "\\nonsecure.db");
_LIT(KSecureDbName, "[E80000AF]secure.db");
 
TInt error = database.Open(KNonSecureDbName);
 
if (error == KErrNone)
{
CleanupClosePushL(database);
 
//count how many rows is in the database
_LIT(KSqlSelectCount,"SELECT COUNT (*) FROM MOVIES");
TSqlScalarFullSelectQuery sqlSelectCountQuery(database);
TInt count = sqlSelectCountQuery.SelectIntL(KSqlSelectCount);
 
//get the max year value (the newest movies)
_LIT(KSqlSelectMax,"SELECT MAX(YEAR) FROM MOVIES");
TSqlScalarFullSelectQuery sqlSelectMaxQuery(database);
TInt max = sqlSelectMaxQuery.SelectIntL(KSqlSelectMax);
 
//get the average year value
_LIT(KSqlSelectAvg,"SELECT AVG(YEAR) FROM MOVIES");
TSqlScalarFullSelectQuery sqlSelectAvgQuery(database);
TInt avg = sqlSelectAvgQuery.SelectIntL(KSqlSelectAvg);
 
CleanupStack::PopAndDestroy(); //database
}
else
{
//open database failed
}
 
error = database.Open(KSecureDbName);
 
if (error == KErrNone)
{
CleanupClosePushL(database);
 
_LIT(KSqlSelectFirstBook,"SELECT TITLE FROM BOOKS WHERE ID=1");
 
TSqlScalarFullSelectQuery fullSelectQuery(database);
HBufC* titleBuf = HBufC::NewLC(5);
TPtr title = titleBuf->Des();
TInt ret = fullSelectQuery.SelectTextL(KSqlSelectFirstBook, title);
 
//if ret > 0 buffer reallocation is needed
if(ret > 0)
{
titleBuf = titleBuf->ReAllocL(ret);
CleanupStack::Pop();
CleanupStack::PushL(titleBuf);
title.Set(titleBuf->Des());
ret = fullSelectQuery.SelectTextL(KSqlSelectFirstBook, title);
//do something with the data...
}
else
{
//do something with the data...
}
 
CleanupStack::PopAndDestroy(2);//buf, database
}
else
{
//open database failed
}
 
 
}

Postconditions

The TSqlScalarFullSelectQuery class is used to get COUNT, MAX, and AVG values from the database nonsecure.db and one specific textual value from the dateabase [UID3]secure.db.


See also

Related Wiki Articles

No related wiki articles found

Rate This

 
Bookmark this page: DeliciousDiggFacebookGoogleYahooStumbleUponRedditDiigoTechnocratiTwitter  Share this page Share this page Print this Page Print this page Invite a friend Invite a friend
京ICP备05048969号    Email Newsletters Press Terms & Conditions Privacy Policy Sitemap Contact Us © 2009 Nokia 
RDF Facets: qdcZidentifierQSxhttpE3aE2fE2fwikiE2eforumE2enokiaE2ecomE2findeE78E2ephpE2fJavaE5fVerifiedE5fE28PortuguE25C3E25AAsE29X qdcZtypeQUqfnZE45E78cludedFromGeneralE4cistingsQ qdcZtypeQUqfntypeZCommunityContentQ qdcZtypeQUqfntypeZE52esourceQ qdcZtypeQUqfntypeZWebpageQ qdcZtypeQUqfntypeZWikiContentQ qdcZtypeQUqmarsZManagedE52esourceQ qdcZtypeQUqwebZInformationE52esourceQ qdcZtypeQUqwebZPageQ qdcZtypeQUqwebZE52esourceQ qdcZtypeQUqrdfsZE52esourceQ qfnZtypeQUqfntypeZCommunityContentQ qfnZtypeQUqfntypeZE52esourceQ qfnZtypeQUqfntypeZWebpageQ qfnZtypeQUqfntypeZWikiContentQ qmarsZlanguageQUxhttpE3aE2fE2fswE2enokiaE2ecomE2flanguageE2d1E2fenX qrdfZtypeQUqfnZE45E78cludedFromGeneralE4cistingsQ qrdfZtypeQUqfntypeZCommunityContentQ qrdfZtypeQUqfntypeZE52esourceQ qrdfZtypeQUqfntypeZWebpageQ qrdfZtypeQUqfntypeZWikiContentQ qrdfZtypeQUqmarsZManagedE52esourceQ qrdfZtypeQUqwebZInformationE52esourceQ qrdfZtypeQUqwebZPageQ qrdfZtypeQUqwebZE52esourceQ qrdfZtypeQUqrdfsZE52esourceQ