Package net.rim.device.api.database

Provides relational database functionality (using SQLite) for applications.

See:
Description

Interface Summary

Cursor	Provides read-only access to results returned by a database query.
Database	Lets you create, modify, and access SQLite databases.
Statement	The representation of a SQL statement.

Class Summary

BufferedCursor	Caches all rows in memory for bidirectional or random access.
CursorEnumeration	Wraps cursor operations in the Enumeration interface.
DatabaseFactory	Creates a new instance of a SQLite database or opens an existing instance.
DatabaseOptions	Defines general database options.
DatabaseSecurityOptions	Defines database security options.
Pragma	String constants for all supported database PRAGMAs.
Row	A collection of variables and values, associated by column names, that is used to define cell values in columns and rows.

Exception Summary

CommandException	Indicates that a command did not meet the required criteria.
DataTypeException	Indicates that the source data type did not match the target data type.
DatabaseBindingException	Indicates the failure to bind the parameter to a SQL statement.
DatabaseException	Indicates that the requested operation could not be performed on the database.
DatabaseIOException	Indicates that the database failed to be opened; created; deleted; or opened or created.
DatabaseOptionsSetException	Indicates that the set method was called with an invalid parameter.
DatabaseOptionsSetInvalidKeyException	Indicates that the set method was called with an invalid key.
DatabaseOptionsSetInvalidValueException	Indicates that the given key was valid, but the provided value was not one of the valid values that corresponds to the provided key.
DatabasePathException	Indicates that the path to a database file is malformed.
SchemaException	Indicates inconsistencies in a schema definition.

Package net.rim.device.api.database Description

Provides relational database functionality (using SQLite) for applications. SQLite databases can be created, manipulated and searched using SQL statements.

For more information about using SQLite, visit www.blackberry.com/go/devguides to read the BlackBerry Java Application Data Storage Guide. For additional resources, visit www.blackberry.com/developers and www.sqlite.org.

An application calls DatabaseFactory.create() and gets back an instance of Database, a relational database handle. All subsequent operations are performed on the database handle by creating SQL statements using Database.createStatement() and then executing the statements.

Statement lifecycle

Here's a typical sequence of database operations executed via SQL statements:

• Create a statement using Database.createStatement(). This returns a Statement object.
• Prepare the statement for execution using Statement.prepare() (you can think of this as a compilation phase).
• Optionally, bind formal parameters in the statement to actual values using Statement.bind(). This is a useful mechanism for re-using a prepared statement with different values.
• If there are no results, execute the statement using Statement.execute().
• If there are results, execute the statement using Statement.getCursor(), which returns a Cursor over the result set. Then iterate over the Cursor row by row.

Using SQL parameters

SQL parameters, also known as SQL statement variables, let you re-use the same statement with different literal values. Use Statement.reset() to reset bindings.

SQL parameters can be used in two main ways:

• A question mark (?) in the statement causes each parameter to be numbered sequentially, starting from 1.
• A question mark followed by an integer (?NNN) in the statement provides each parameter with the number NNN.

Here's an example of a statement using sequentially-numbered parameters:

// Create a SELECT statement with the bounds specified as sequentially assigned parameters.
Statement s = Database.createStatement("SELECT * FROM T WHERE a < ? AND a > ?");
s.prepare();
s.bind(1, upperBound);  // an integer specifying the upper bound.
 s.bind(2, lowerBound);  // an integer specifying the lower bound.
Cursor c = s.getCursor();
 // Now iterate over the data set using the Cursor.
 ...

The statement can also specify explicitly numbered values:

Statement s = Database.createStatement("SELECT * FROM T WHERE a < ?5 AND a > ?12")";
s.prepare();
// In this case, bind() should be used with the value numbers:
s.bind(5, upperBound);   // an integer specifying the upper bound.
s.bind(12, lowerBound);  // an integer specifying the lower bound.
 // Now iterate over the data set using the Cursor.

For getFormalName() to return the parameter name, you must provide a name in the query. For example, when you call getFormalName, the statement "SELECT * FROM T WHERE a = :a" will return :a. When parameters such as a question mark (?) are used as placeholders, the getFormalName() method will not return a parameter name. For example, getFormalName will not return the name for the parameter in this statement: "SELECT * FROM T WHERE a = ?"

Code sample: Creating a SQLite database

This example creates a SQLite database at the root of a media card.

import net.rim.device.api.system.Application;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;

public class CreateDatabase extends Application
{
    public static void main(String[] args)
    {
        CreateDatabase app = new CreateDatabase();
        try
        {
            URI strURI = URI.create("file:///SDCard/test.db"); 
            DatabaseFactory.create(strURI);
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
        }  
    } 
}

Code sample: Adding a table to a SQLite database

import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
import net.rim.device.api.system.Application;

public class AddDatabaseTable extends Application 
{
   public static void main(String[] args)
   {
      AddDatabaseTable app = new AddDatabaseTable();
      try
      {
         URI myURI = URI.create("/SDCard/test.db"); 
         Database d = DatabaseFactory.open(myURI);
         Statement st = d.createStatement( "CREATE TABLE 'People' ( " +
                                              "'Name' TEXT, " +
                                              "'Age' INTEGER )" );
         st.prepare();
         st.execute();
         st.close();
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
        }
   }
}

Code sample: Adding content to a SQLite table

import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
import net.rim.device.api.system.Application;

public class AddDatabaseTable extends Application 
{
    public static void main(String[] args)
    {
        AddDatabaseTable app = new AddDatabaseTable();
        try
        {
            URI myURI = URI.create("/SDCard/test.db"); 
            Database d = DatabaseFactory.open(myURI);
            Statement st = d.createStatement("INSERT INTO People(Name,Age) " +
                                             "VALUES ('John',37)");
            st.prepare();
            st.execute();
            st.close();
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
        }
    }
}