Querying Databases: Basic

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

This tutorial uses code from the Basic SQL example application.

Assumptions

You have a database. The database has no tables and therefore no data.

SQL statements

The following SQL statements are used for this example:

SELECT person FROM Pets WHERE cat >= 1

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(KSelect1,"SELECT person FROM Pets WHERE cat >= 1;");
```
  This defines the query parameters.
2. Instantiate the RSqlStatement SQL statement:
```
RSqlStatement stmt;
```
3. Prepare the statement:
```
User::LeaveIfError(stmt.Prepare(iPetDb, aStatement));
```
  Creates a parameterised SQL statement executable.
4. Define the indices to be used in the search:
```
    TInt personIndex = stmt.ColumnIndex(KPerson);
```
Execute the SQL query:

The Symbian SQL statement is executed by RSqlStatement::Next().
1. Search the records until a match is found:
```
TInt rc = KErrNone;
while ((rc = stmt.Next()) == KSqlAtRow)
```
  Next() fires the executable SQL statement and stops at and returns the matched record.
Do something with the results:

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 and use the results of the search:
```
...
{
TPtrC myData = stmt.ColumnTextL(personIndex);
iConsole->Printf(_L("Person=%S\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.

Results

The tutorial has demonstrated how to query a Symbian SQL database. Looking through the example application you can work out how easily the query can be changed to meet specific requirements and how the results can be used in different ways.

Querying example

The following code snippet is from the basic example application.

...
...
RSqlStatement stmt;
iConsole->Printf(_L("Running Query:\n%S\n"), &aStatement);
user::LeaveIfError(stmt.Prepare(iPetDb, aStatement));
CleanupClosePushL(stmt);
TInt personIndex = stmt.ColumnIndex(KPerson);
TInt rc = KErrNone;
while ((rc = stmt.Next()) == KSqlAtRow)
  {
  // Do something with the results 
  TPtrC myData = stmt.ColumnTextL(personIndex); // read return data
  iConsole->Printf(_L("Person=%S\n"), &myData);
  }
if (rc != KSqlAtEnd)
  {
  _LIT(KErrSQLError, "Error %d returned from RSqlStatement::Next().");
  iConsole->Printf(KErrSQLError, rc);
  }
  ...
  CleanupStack::PopAndDestroy(1);
}

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

Introduction

Procedure

Results

Querying example

Related concepts