Querying a database

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

1. 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.

2. 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>;
                 

3. 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