Querying -a database

Querying -a databaseThis tutorial shows you how to create a simple SQL database query. -

Introduction

This -tutorial shows you how to wrap a SQL query statement into an RSqlStatement object -to query a database.

The SQL statement used for the tutorial is shown -here:

SELECT name FROM countries WHERE population > 10

The (SELECT) -results of the query will be the value in the 'name' column FROM the -'countries' table WHERE the value of the -'population' column of the same record is > the -value specified.

Procedure

Prepare the Statement:

The -steps to prepare a SQL statement are shown here.
1. Set up some constants -used by the SQL statement object to define the SQL query:
  
  _LIT(kName,"name"); -_LIT(kPopulation,"population"); -_LIT(kVal,":Value"); -_LIT(kQueryString,"SELECT name FROM countries WHERE population > :Value");
  
  This -defines the query parameters.
2. Instantiate the RSqlStatement SQL -statement:
  
  RSqlStatement myStatement;
3. Define the indices to -be used in the search:
  
  TInt nameIndex = myStatement.ColumnIndex(kName); -TInt populationIndex = myStatement.ColumnIndex(kPopulation);
4. Set the 32-bit integer -value for the SQL parameter 'value':
  
  TInt parameterIndex = myStatement.ParameterIndex(kVal); -err = myStatement.BindInt(parameterIndex,10);
  
  The SQL -parameter to which the integer is being assigned is identified by the constant kVal from:
  
  _LIT(kVal,":Value"); -... -...WHERE population > :Value")
  
  The parameter plus constant, -along with other parts of the statement, are converted into:
  
  in SQL -syntax.
5. Prepare the statement:
  
  TInt err; -err = myStatement.Prepare(countriesDatabase,kQueryString);
  
  This -creates a parameterised SQL statement executable.
Run the SQL query:
1. Search the records until -a match is found:
  
  while((err = myStatement.Next()) == KSqlAtRow) - { - Do something with the query results - }
  
  Next() fires the executable SQL statement and stops -at and returns the matched record.
  
  Do something if no results are -found.
  
  if(err == KSqlAtEnd) - <OK - no more records>; -else - <process the error>;
The query is done and -you have the results. In this section we look at a simple way to do something -with the results and we close the SQL statement object.
1. Get the results of the -search:
  
  TPtrC myData; -myData = myStatement.ColumnTextL(nameIndex); -RDebug::Print(_L("Name=%d\n"), myData);
2. Close the SQL search -statement:
  
  err = myStatement.Close();
  
  When -the database search is finished the object should be closed to free up resources.

This section deals with finding and returning the first matching -record

only. Getting all matches in a database is briefly discussed -at the end of this

section.

- -

Now that you have -performed a basic database query you can start thinking about more advanced -querying options. The following will show you how:

Querying -Databases: Basic
Reading -to a buffer
Reading -to memory
Reading -to a data stream

-SQL Overview - -Inserting -a row into a table -Deleting -Rows from a Table -Reading to -a buffer -Reading to -memory -Reading to -a data stream -Writing to -a data stream -Scalar queries - + + + + + +Querying +a databaseThis tutorial shows you how to create a simple SQL database query. +

Introduction

This +tutorial shows you how to wrap a SQL query statement into an RSqlStatement object +to query a database.

The SQL statement used for the tutorial is shown +here:

SELECT name FROM countries WHERE population > 10

The (SELECT) +results of the query will be the value in the 'name' column FROM the +'countries' table WHERE the value of the +'population' column of the same record is > the +value specified.

Procedure

Prepare the Statement:

The +steps to prepare a SQL statement are shown here.
1. Set up some constants +used by the SQL statement object to define the SQL query:
  
  _LIT(kName,"name"); +_LIT(kPopulation,"population"); +_LIT(kVal,":Value"); +_LIT(kQueryString,"SELECT name FROM countries WHERE population > :Value");
  
  This +defines the query parameters.
2. Instantiate the RSqlStatement SQL +statement:
  
  RSqlStatement myStatement;
3. Define the indices to +be used in the search:
  
  TInt nameIndex = myStatement.ColumnIndex(kName); +TInt populationIndex = myStatement.ColumnIndex(kPopulation);
4. Set the 32-bit integer +value for the SQL parameter 'value':
  
  TInt parameterIndex = myStatement.ParameterIndex(kVal); +err = myStatement.BindInt(parameterIndex,10);
  
  The SQL +parameter to which the integer is being assigned is identified by the constant kVal from:
  
  _LIT(kVal,":Value"); +... +...WHERE population > :Value")
  
  The parameter plus constant, +along with other parts of the statement, are converted into:
  
  in SQL +syntax.
5. Prepare the statement:
  
  TInt err; +err = myStatement.Prepare(countriesDatabase,kQueryString);
  
  This +creates a parameterised SQL statement executable.
Run the SQL query:
1. Search the records until +a match is found:
  
  while((err = myStatement.Next()) == KSqlAtRow) + { + Do something with the query results + }
  
  Next() fires the executable SQL statement and stops +at and returns the matched record.
  
  Do something if no results are +found.
  
  if(err == KSqlAtEnd) + <OK - no more records>; +else + <process the error>;
The query is done and +you have the results. In this section we look at a simple way to do something +with the results and we close the SQL statement object.
1. Get the results of the +search:
  
  TPtrC myData; +myData = myStatement.ColumnTextL(nameIndex); +RDebug::Print(_L("Name=%d\n"), myData);
2. Close the SQL search +statement:
  
  err = myStatement.Close();
  
  When +the database search is finished the object should be closed to free up resources.

This section deals with finding and returning the first matching +record

only. Getting all matches in a database is briefly discussed +at the end of this

section.

+ +

Now that you have +performed a basic database query you can start thinking about more advanced +querying options. The following will show you how:

Querying +Databases: Basic
Reading +to a buffer
Reading +to memory
Reading +to a data stream

+SQL Overview + +Inserting +a row into a table +Deleting +Rows from a Table +Reading to +a buffer +Reading to +memory +Reading to +a data stream +Writing to +a data stream +Scalar queries +

\ No newline at end of file