Would you like to tell us how we are doing?

You bet No thanks

Sorry about the red box, but we really need you to update your browser. Read this excellent article if you're wondering why we are no longer supporting this browser version. Go to Browse Happy for browser suggestions and how to update.

Working with SQLite databases

After you create an SQLite database, you can use SQL statements to do things such as add data, retrieve data, and modify the database.

The list of supported SQL statements and their syntax is available at www.SQLite.org. The Database API does not support FTS2 and RTREE.

There are two ways to execute statements: create a Statement object, or use Database.executeStatement.

Executing statements with a Statement object

The following steps outline how to execute statements with a Statement object:

  1. Create a Statement object using the Database.createStatement method.
  2. Prepare the statement to run by invoking the Statement.prepare method.
  3. Bind parameters in the statement. This is optional, but improves performance when you are running the statement multiple times.
  4. Execute the statement.
    • If the statement might return results (such as a SELECT statement), execute it by calling Statement.getCursor, which returns a Cursor object.

    • If the statement does not return a result set, execute it by calling one of the following methods:
      • Statement.executeUpdate: Use when updating data (with an UPDATE statement) and running the Statement multiple times with bind parameters.
      • Statement.executeInsert: Use when inserting data (with an INSERT statement) and running the Statement multiple times with bind parameters.
      • Statement.execute: Use when you want to use bind parameters.
  5. If the statement returns a result set, retrieve the result set by iterating over the returned cursor row by row. Do this using the Cursor interface, which works in all circumstances but is forward-only. For bidirectional cursor movement, but only for small result sets, use the BufferedCursor class.

You should explicitly close statements to free up resources.

The following example updates rows:

Statement st = d.createStatement(
       "UPDATE Account set Balance = ? WHERE AcctNo > ?"); 
try 
{ 
    st.prepare(); 
    Object[] bindParams = {new Integer (2000), new Integer (100)};
    st.executeUpdate(bindParams); 
} 
finally 
{
    st.close(); 
}

Executing statements without a Statement object

When you are executing a query that does not return a result set and you are not using bind parameters, you do not need to create a Statement object. Instead, you can use Database.executeStatement, which performs a statement prepare, execute, and close in a single call.

The following example creates a table:

Database d = null;
try {
    d = DatabaseFactory.create("hello.db");
    d.executeStatement(
          "CREATE TABLE t (a INTEGER PRIMARY KEY, b BLOB);" );            
} catch (Exception e) {
    System.out.println( e.getMessage() );
} finally {
    try {
        d.close();
    } catch (DatabaseException e) {
    }
}

Using SQL parameters

When you create an SQL statement, you can create SQL parameters in order to reuse the statement with different values. This practice can provide performance benefits. Prepare generic statements that use named variables, and then execute the statements when they are required by iterating through the variable values, binding the values to the named variables in each iteration.

You can choose from the following ways to number the parameters:

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

You can use the Statement.bind() method to provide names for SQL parameters. The bind() method takes the number of the parameter and the value to be bound to it. If you use a number outside of the allowed range, a DatabaseException is thrown. All bindings can be reset using Statement.reset() .

Here's an example of a statement that uses parameters to create an upper bound and lower bound that can be defined each time the statement is run. This example numbers the parameters sequentially.

Statement s = Database.createStatement(
        "SELECT * FROM T WHERE a < ? AND a > ?");
    s.prepare();
    s.bind(1, upperBound);
    s.bind(2, lowerBound);
    Cursor c = s.getCursor();

Here's an example of the same statement, except that explicit numbers are specified for the parameters:

Statement s = Database.createStatement(
        "SELECT * FROM T WHERE a < ?5 AND a > ?12");
    s.prepare();
    s.bind(5, upperBound); 
    s.bind(12, lowerBound);

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

Code sample: Creating a parameterized update

import net.rim.device.api.ui.*;
import net.rim.device.api.ui.component.*;
import net.rim.device.api.ui.container.*;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;
import java.util.*;

public class ParameterizedUpdate extends UiApplication
{
    public static void main(String[] args)
    {
        ParameterizedUpdate theApp = new ParameterizedUpdate();
        theApp.enterEventDispatcher();
    }
    public ParameterizedUpdate()
    {
        pushScreen(new ParameterizedUpdateScreen());
    }
    }
    class ParameterizedUpdateScreen extends MainScreen
    {
        Database d;
        public ParameterizedUpdateScreen()
        {
            LabelField title = new LabelField(
            "SQLite Parameterized Update Sample",
            LabelField.ELLIPSIS |
            LabelField.USE_ALL_WIDTH);
            setTitle(title);
            
            add(new RichTextField("Attempting to update data in " +
              "MyTestDatabase.db on the SDCard."));
            try
            {
               URI myURI = URI.create(
                    "file:///SDCard/Databases/SQLite_Guide/" +
                    "MyTestDatabase.db");
               d = DatabaseFactory.open(myURI);
            
               Statement st = d.createStatement(
                 "UPDATE People SET Age=? WHERE Name=?");
               st.prepare();
            
               Hashtable ht = new Hashtable(2);
               ht.put("Sophie", new Integer(10));
               ht.put("Karen",  new Integer(7));
            
               Enumeration names = ht.keys();
               Enumeration ages  = ht.elements();
            
               while (names.hasMoreElements())
               {
                   Integer iAge   = (Integer)ages.nextElement();
                   String strName = (String)names.nextElement();
                   st.bind(1,iAge.intValue());
                   st.bind(2,strName);
                   st.execute();
                   st.reset();
               }
               st.close();
               d.close();
        }
        catch ( Exception e )
        {
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
    }
}

Code sample: Creating a parameterized insert

import net.rim.device.api.ui.*;
import net.rim.device.api.ui.component.*;
import net.rim.device.api.ui.container.*;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;
import java.util.*;

public class ParameterizedInsert extends UiApplication
{
public static void main(String[] args)
{
   ParameterizedInsert theApp = new ParameterizedInsert();
      theApp.enterEventDispatcher();
}
public ParameterizedInsert()
{
   pushScreen(new ParameterizedInsertScreen());
}

}
class ParameterizedInsertScreen extends MainScreen
{
   Database d;
   public ParameterizedInsertScreen()
   {
      LabelField title = new LabelField("SQLite Insert Data " +
      "Schema Sample",
      LabelField.ELLIPSIS |
      LabelField.USE_ALL_WIDTH);
      setTitle(title);
      add(new RichTextField("Attempting to insert data into " +
      "MyTestDatabase.db on the SDCard."));
    try
    {
       URI myURI = URI.create(
             "file:///SDCard/Databases/SQLite_Guide/" +
             "MyTestDatabase.db");
       d = DatabaseFactory.open(myURI);

       Statement st = d.createStatement("INSERT INTO People(Name,Age) "
       + "VALUES (?,?)");
       st.prepare();

       Hashtable ht = new Hashtable(4);
       ht.put("Sophie", new Integer(6));
       ht.put("Karen",  new Integer(3));
       ht.put("Kevin",  new Integer(82));
       ht.put("Cindy",  new Integer(12));

       Enumeration names = ht.keys();
       Enumeration ages  = ht.elements();

       while (names.hasMoreElements())
       {
          String strName = (String)names.nextElement();
          Integer iAge   = (Integer)ages.nextElement();
          st.bind(1,strName);
          st.bind(2,iAge.intValue());
          st.execute();
          st.reset();
       }
       st.close();
       d.close();

    }
    catch ( Exception e )
    {
       System.out.println( e.getMessage() );
       e.printStackTrace();
    }
}
}

Working with blobs

You can include large binary objects in your SQLite database. For any object over 1 MB, you must use InputStream and OutputStream objects to read and write the blob. This technique avoids the problem of storing the entire blob in memory. If you don't use an input stream and output stream, the amount of available dynamic memory limits the blob size that you can use.

To write data into a blob, create an OutputStream object with the Database.createBlobOutputStream method.

To read data from a blob, create an InputStream object with the Database.createBlobInputStream method.

You can reserve space for blob output using the Statement.bindZeroBlob method. This method binds a series of null bytes to a parameter in a Statement.

You should always explicitly close blobs so they don't exhaust memory before the database is closed.

Out-of-memory errors can still occur if you use the Statement.bind methods that operate on byte arrays or if you directly specify a blob as part of an SQL statement.

The following sample code writes a blob to a table and then reads the blob from the table.

Database d = DatabaseFactory.openOrCreate("hello.db");
d.executeStatement(
 "CREATE TABLE IF NOT EXISTS t (a INTEGER PRIMARY KEY, b BLOB);" );
d.executeStatement( "INSERT INTO t(b) VALUES( ZEROBLOB( 1024 ) );" );
java.io.OutputStream outputStream = 
    d.createBlobOutputStream( "t", "b", d.lastInsertedRowID( ) );
for( int i = 0; i < 1024; ++i ) 
{
    outputStream.write( i % 128 );
}
outputStream.close( );

java.io.InputStream inputStream = 
    d.createBlobInputStream( "t", "b", d.lastInsertedRowID( ) );
for( int i = 0; i < 1024; ++i ) {
    if( i % 128 != inputStream.read( ) ) 
    {
        fail( "write and read mismatch" );
    }
}
inputStream.close( );
d.close();

Using transactions

SQLite statements always run in transactions. If the statement runs successfully, the transaction is automatically committed. If the statement fails, the transaction is rolled back.

By default, a separate transaction is created for each SQLite statement, which is less efficient than running multiple statements in one transaction. You can usually improve performance by explicitly specifying transactions for groups of statements. You can run multiple statements in one transaction using Database.beginTransaction() and Database.commitTransaction() around groups of statements.

Nested transactions are not supported.

Code sample: Using transactions

import net.rim.device.api.ui.*;
import net.rim.device.api.ui.component.*;
import net.rim.device.api.ui.container.*;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;
public class UsingTransactions extends UiApplication
{
    public static void main(String[] args)
    {
        UsingTransactions theApp = new UsingTransactions();
        theApp.enterEventDispatcher();
    }
    public UsingTransactions()
    {
		
    }
}

class UsingTransactionsScreen extends MainScreen
{
    Database d;
    public UsingTransactionsScreen()
    {
       LabelField title = new LabelField(
           "SQLite Using Transactions Sample",
       LabelField.ELLIPSIS |
       LabelField.USE_ALL_WIDTH);
       setTitle(title);
       add(new RichTextField(
           "Updating data in 1 transaction in MyTestDatabase.db."));
       try
       {
          URI myURI = URI.create(
              "file:///SDCard/Databases/SQLite_Guide/" +
              "MyTestDatabase.db");
          d = DatabaseFactory.open(myURI);
			
          d.beginTransaction();
          Statement st = d.createStatement("UPDATE People SET Age=7 " +
              "WHERE Name='Sophie'");
          st.prepare();
          st.execute();
          st.close();
          st = d.createStatement("UPDATE People SET Age=4 " +
              "WHERE Name='Karen'");
          st.prepare();
          st.execute();
          st.close();
          d.commitTransaction();
          d.close();
     }
     catch ( Exception e )
     {
          System.out.println( e.getMessage() );
          e.printStackTrace();
     }
  }
}

Using pragmas

The PRAGMA command is a feature of SQLite that allows you to query schema, query and modify the schema cookie and user cookie, modify the operation of the SQLite library, and debug the library. Supported pragmas are documented in the Pragma class.

The Database API supports the following Pragma commands:

Pragma

Description

collation_list

Gets a list of the collating sequences that are defined for the current database connection.

For example, to get a list of defined collating sequences, execute the SQL statement "PRAGMA collation_list;" and read the resulting rows.

compile_options

Gets the names of compile-time options that are used when building the database.

For example, to get the compilation options for the SQLite library, execute the SQL statement "PRAGMA compile_options;" and read the resulting rows. There will be one option per row.

database_list

Gets one row for each database that is attached to the current database connection.

For example, to get a list of the attached databases for a connection, execute the SQL statement "PRAGMA database_list;" and read the resulting rows. There will be one database name per row.

foreign_keys

Gets or sets the enforcement of foreign key constraints. This setting is equivalent to using the foreign_key_constraints option in the DatabaseOptions class.

For example, to set enforcement of foreign key constraints, execute the SQL statement "PRAGMA foreign_keys;" outside of a transaction.

freelist_count

Gets the number of unused pages in the database, which you can use to determine when to run the VACUUM command.

For example, to get the number of unused pages in a database file, execute the SQL statement "PRAGMA freelist_count;" and read the resulting row.

integrity_check

Performs an integrity check of the entire database, checking for out-of-order records, missing pages, malformed records, and corrupt indexes.

journal_mode

Gets or sets the journal mode of the current database connection. The available settings provide different performance and data safety levels. All of the options except for WAL and OFF use a rollback journal. The options are:

  • DELETE: deletes the rollback journal at the end of a transaction (the default).
  • TRUNCATE: commits transactions by truncating the rollback journal to zero length.
  • PERSIST: prevents other connections from rolling the journal back. In this mode, the rollback journal can become very large, so you may want to monitor it with journal_size_limit.
  • MEMORY: stores the rollback journal in RAM.
  • WAL: causes a write-ahead log to be used instead of a rollback journal.
  • OFF: disables the rollback journal, which disables the atomic commit and rollback capabilities of SQLite.

For example, to check the journaling mode, execute the SQL statement "PRAGMA journal_mode;" and read the resulting row. To change the journaling mode to a write-ahead log, execute the SQL statement "PRAGMA journal_mode = WAL;".

journal_size_limit

Gets or sets the journal size limit, in bytes. This is useful when journal_mode is set to PERSIST.

For example, to check the journal size limit, execute the SQL statement "PRAGMA journal_size_limit;" and read the resulting row. To set the journal size limit to 1000 bytes, execute the SQL statement "PRAGMA journal_size_limit = 1000;".

page_count

Gets the total number of pages in the database.

quick_check

Performs an integrity check of the entire database, checking for out-of-order records, missing pages, and malformed records.

synchronous

Sets the synchronous mode for your database. The options are FULL (the safest but slowest mode, and the default), NORMAL, and OFF. FULL, NORMAL and OFF can also be referenced as 2, 1, and 0, respectively.

For example, to check the synchronous mode, execute the SQL statement "PRAGMA synchronous;" and read the resulting row. To change the synchronous mode to OFF, execute the SQL statement "PRAGMA synchronous = 0;".

user_version

Gets or sets a user version. The user version is not used by SQLite; it is available for your use. The user version is a 32-bit signed integer.

For example, to set the user version to 123, execute the SQL statement "PRAGMA user_version = 123;". To check the user version, execute the SQL statement "PRAGMA user_version;" and read the resulting row.