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.

Creating and deleting SQLite databases

You can create temporary or permanent databases.

SQLite database files

Each SQLite database is stored in a single file. If you specify only the database name as the parameter value to DatabaseFactory.create(), the database file is created in external media card storage. The default location for the database file is /SDCard/databases/application_name/. The name of the application that creates the database is included in the default path to avoid name collisions.

You cannot store SQLite databases in application storage.

External media card storage is the preferred storage location for databases if the BlackBerry smartphone supports it. On devices that support external media card storage, you can create databases in external media card storage by specifying the path /SDcard/.

If your application is designed to store your SQLite database in built-in media storage, you should implement your application so that it is easy to modify the code to change the storage location of the database. On devices that support built-in media storage, you can create databases in built-in media storage by specifying the path /store/.

When your application is uninstalled, the SQLite databases associated with it are not automatically removed.

About primary keys

Each row in an SQLite table is uniquely identified by a row ID. The row ID is a 64-bit signed integer that uniquely identifies the row. The row ID can be accessed by the names ROWID, OID, or _ROWID_ in place of a column name, unless those names are used as explicitly-declared column names. If the table has a column of type INTEGER PRIMARY KEY, then the name of that column is another alias for the row ID.

When a table may have concurrent inserts, you must ensure that the correct row ID is returned. When you use executeInsert to execute an INSERT statement, synchronization is handled in native code and there is no issue. But if you execute an INSERT statement using a method other than executeInsert, you should execute the INSERT statement and make a call to the Database.lastInsertedRowID method within a synchronized block. The synchronized block should prevent other INSERT statements from executing (and modifying the last inserted row ID).

To retrieve integer values to use as keys in another query, you can use the Statement.getIntegers and Statement.getLongs methods. These methods simplify and optimize the retrieval of integer columns. You can think of the retrieved integer values as foreign keys except that they don't have to refer to another table's primary key.

SQLite automatically creates indexes for columns that have UNIQUE or PRIMARY KEY constraints.

Enforcing foreign key constraints

If you use foreign keys in your database, you can enforce their use by setting the foreign_key_constraints option in the DatabaseOptions class.

You can set the foreign_key_constraints option when you create the database. However, for the foreign key constraints to be in effect, you must also set the option every time you open the database. The following code sample shows how to enforce foreign key constraints when opening or creating a database.

DatabaseOptions dbo = new DatabaseOptions();
dbo.set("foreign_key_constraints","on");
Database d = DatabaseFactory.openOrCreate("test.db", dbo);

You can check whether foreign key constraints are on with the DatabaseOptions.foreignKeyConstraintsOn method.

Character encoding

SQLite databases store strings internally in UTF-8, while Java stores them as UTF-16. The Database API handles the conversion of strings internally, so you don't need to do any encoding or conversion.

The SQLite encoding pragma is not supported in the Database API, so you can't set another encoding. You must use UTF-8 supported characters in your SQLite database.

Using language collations

When your database contains textual data in languages other than English, you can set the language collation so that SQLite sorts columns and performs indexing properly. You can do this with the COLLATE operator when you create the column.

The default Standard collation provides good results for English in normal usage, and also provides the correct ordering for many other commonly used languages. It may not provide correct results for some Latin languages and for ideographic scripts such as Chinese and Japanese.

For more information about changing the default collation, see www.unicode.org.

The following code snippet creates a table with French language collation enabled on a column called a, assuming that database d is open.

d.executeStatement( "CREATE TABLE t1( a COLLATE french );" );

The following language collations are supported in BlackBerry apps:

  • Standard (the default)

  • Afrikaans

  • Arabic

  • Catalan

  • Czech

  • French

  • Hebrew

  • Hong Kong Chinese

  • Hungarian

  • Japanese

  • Korean

  • Pinyin

  • Polish

  • Romanian

  • Spanish

  • Thai

  • Turkish

  • Taiwan Chinese

Creating an SQLite database

You can create an SQLite database using the create or openOrCreate methods in the DatabaseFactory class.

By default, database files are stored on a media card. If you are using a BlackBerry Smartphone Simulator, you might need to simulate a media card.

After creating a database, verify that the database file was created. You can do this in any of the following ways:

  • View the database in a database viewer.
  • Look in the file system for the database file, and verify that it is not zero size.
  • Invoke the DatabaseFactory.exists method.

The following sample code 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();
        Database db = null;
        try
        {
           URI strURI = URI.create("file:///SDCard/test.db");        
           db = DatabaseFactory.create(strURI);
        }
        catch (Exception e)
        {
           System.out.println( e.getMessage() );
        }  finally {
        try {
           db.close();
        }
        catch (DatabaseException e) {
        }
     }
}

The following sample code adds a table to a 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 {
          Database d = null;
          Statement st = null;
          try {
              URI myURI = URI.create("file:///SDCard/test.db");
              d = DatabaseFactory.open(myURI);
              st = d.createStatement( "CREATE TABLE People ( " +
                                              "Name TEXT, " +
                                              "Age INTEGER )" );          
              st.prepare();
              st.execute();
          }
          catch ( Exception e )
          {
              System.out.println( e.getMessage() );
          }
          finally
          {
              st.close();
              d.close();
          }
      } catch (Exception e) {
          System.out.println( e.getMessage() );
      }
  }
 }

The following code sample adds a schema to an SQLite database.

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 CreateDatabaseSchema extends UiApplication
{
    public static void main(String[] args)
    {
        CreateDatabaseSchema theApp = new CreateDatabaseSchema();
        theApp.enterEventDispatcher();
    }
    
    public CreateDatabaseSchema()
    {
        pushScreen(new CreateDatabaseSchemaScreen());
    }   
}

class CreateDatabaseSchemaScreen extends MainScreen
{
    Database d;
    public CreateDatabaseSchemaScreen()
    {
        LabelField title = new LabelField("SQLite Create " + 
                                          "Database Schema Sample",
                                           LabelField.ELLIPSIS | 
                                           LabelField.USE_ALL_WIDTH);
        setTitle(title);
        add(new RichTextField("Adding a table to a database called " +
                              "MyTestDatabase.db on the SDCard."));
        try
        {
            URI myURI = URI.create("file:///SDCard/Databases/SQLite_Guide/" +
                                   "MyTestDatabase.db"); 
            d = DatabaseFactory.openOrCreate(myURI);
            Statement st = d.createStatement( "CREATE TABLE People ( " +
                                              "Name TEXT, " +
                                              "Age INTEGER )" );
            
            st.prepare();
            st.execute();
            st.close();
            d.close();
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
             
    }
}

Deleting an SQLite database

When your app is removed from a BlackBerry smartphone, permanent databases that are associated with your app are not automatically deleted. You should delete them using the DatabaseFactory.delete() method.

The following code sample deletes an SQLite database.

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 DeleteDatabase extends UiApplication
{
    public static void main(String[] args)
    {
        DeleteDatabase theApp = new DeleteDatabase();
        theApp.enterEventDispatcher();
    }
    
    public DeleteDatabase()
    {
        pushScreen(new DeleteDatabaseScreen());
    }   
}

class DeleteDatabaseScreen extends MainScreen
{
    Database d;
    public DeleteDatabaseScreen()
    {
        LabelField title = new LabelField("SQLite Delete Database Sample",
                                           LabelField.ELLIPSIS | 
                                           LabelField.USE_ALL_WIDTH);
        setTitle(title);
        add(new RichTextField("Deleting a database called " +
                              "MyTestDatabase.db on the SDCard."));
        try
        {
            URI myURI = URI.create("file:///SDCard/Databases/SQLite_Guide/" +
                                   "MyTestDatabase.db"); 
            DatabaseFactory.delete(myURI);       
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
    }
}

Creating and deleting tables

The CREATE TABLE statement creates a permanent table.

When you do not need to store data across BlackBerry smartphone restarts, you should use the CREATE TEMP TABLE statement to create temporary tables, which are more efficient. Temporary tables are stored in a temporary database along with all associated indexes, triggers, and views. The temporary database file is deleted automatically when the database connection is closed.

You can delete a table and its schema with the DROP TABLE statement.

Inserting data

You insert data into a table by executing an INSERT statement. To delete data from a table, use the DELETE statement.

You can execute INSERT statements with the Statement.execute method or the Statement.executeInsert method. The executeInsert method is a bulk operation method that reduces the number of calls over the runtime bridge.

The following code sample inserts table data using Statement.execute().

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 InsertData extends UiApplication
{
    public static void main(String[] args)
    {
        InsertData theApp = new InsertData();
        theApp.enterEventDispatcher();
    }
    
    public InsertData()
    {
        pushScreen(new InsertDataScreen());
    }   
}

class InsertDataScreen extends MainScreen
{
    Database d;
    public InsertDataScreen()
    {
        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 ('John',37)");
            st.prepare();
            st.execute();
            st.close();
            d.close();

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

The following code snippet uses a bulk operation method that reduces the number of calls over the runtime bridge.

Statement st = d.createStatement("INSERT INTO People(Name, Age) VALUES (?, ?)");
 try
 {
     st.prepare();
     Object[] bindParams = {"John", new Integer (37)};
     long rowID = st.executeInsert(bindParams);
 }
 finally 
 {
     st.close();
 }

Updating data

You update data in a table by executing an UPDATE statement.

The following code sample creates a prepared statement that updates data. It executes the statement using the execute method.

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 UpdateData extends UiApplication
{
    public static void main(String[] args)
    {
        UpdateData theApp = new UpdateData();
        theApp.enterEventDispatcher();
    }
    
    public UpdateData()
    {
        pushScreen(new UpdateDataScreen());
    }   
}

class UpdateDataScreen extends MainScreen
{
    Database d;
    public UpdateDataScreen()
    {
       LabelField title = new LabelField("SQLite Update Data Sample",
                                          LabelField.ELLIPSIS | 
                                          LabelField.USE_ALL_WIDTH);
       setTitle(title);
       add(new RichTextField("Trying to update data in MyTestDatabase.db."));
       try
       {
        URI myURI = URI.create("file:///SDCard/Databases/SQLite_Guide/" +
                               "MyTestDatabase.db"); 
        d = DatabaseFactory.open(myURI);
            
        Statement st = d.createStatement("UPDATE People SET Age=38 " +
                                         "WHERE Name='John'");
        st.prepare();
        st.execute();
        st.close();
        d.close();

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

The following code snippet uses a bulk operation method that reduces the number of calls over the runtime bridge. It uses the Statement.executeUpdate method to bind the parameters, execute the statement, and reset the bindings.

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();
}

Deleting data

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 DeleteData extends UiApplication
{
    public static void main(String[] args)
    {
        DeleteData theApp = new DeleteData();
        theApp.enterEventDispatcher();
    }
    
    public DeleteData()
    {
        pushScreen(new DeleteDataScreen());
    }   
}

class DeleteDataScreen extends MainScreen
{
    Database d;
    public DeleteDataScreen()
    {
        LabelField title = new LabelField("SQLite Delete Database Data",
                                           LabelField.ELLIPSIS |
                                           LabelField.USE_ALL_WIDTH);
        setTitle(title);
        add(new RichTextField("Attempting to delete data from " + 
                              "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("DELETE FROM People");
            st.prepare();
            st.execute();
            st.close();
            d.close();

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

Listing database tables

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 ListTables extends UiApplication
{
    public static void main(String[] args)
    {
        ListTables theApp = new ListTables();
        theApp.enterEventDispatcher();
    }
    
    public ListTables()
    {
        pushScreen(new ListTablesScreen());
    }   
}

class ListTablesScreen extends MainScreen
{
    Database d;
    public ListTablesScreen()
    {
        LabelField title = new LabelField("SQLite List Database Tables", 
                                           LabelField.ELLIPSIS | 
                                           LabelField.USE_ALL_WIDTH);
        setTitle(title);
        add(new RichTextField("Attempting to list tables 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("SELECT name FROM " +
                                             " sqlite_master " +
                                             "WHERE type='table'" +
                                             "ORDER BY name");
            
            st.prepare();
            Cursor c = st.getCursor();
            
            Row r;
            int i = 0;
            while(c.next()) 
            {
                r = c.getRow();
                i++;
                add(new RichTextField(i + ". Table: " + r.getString(0)));
            }
            if (i==0)
            {
                add(new RichTextField("There are no tables " +
                                      " in the MyTestDatabase database."));
            }
            st.close();
            d.close();
            
        }
        catch ( Exception e ) 
        {         
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
             
    }
}

Copying a database to an encrypted location

If you copy a SQLite database to an encrypted storage location on a BlackBerry smartphone (eMMC or microSD card), the database can become inaccessible to the Database API. This happens because the media card uses a different type of encryption from that used by the Database API.

To copy a database so that you can access it, you should use the Database API to create an empty database file, truncate the file to zero, and then copy the database into the file.

The following code sample demonstrates this technique.

// Copy a database to an encrypted storage location. 
 
private void copyDBRecommendedWay() {
          
    try { 
        String dbPath = "file:///SDCard/original.db";
        String dbPathCopy = "file:///SDCard/copy.db";             
        Database dbCopy = null;
        try {
            // Delete if there's an existing one.
            DatabaseFactory.delete(URI.create(dbPathCopy));
              
           // Create the database using the DatabaseFactory class.                
            dbCopy = DatabaseFactory.create(URI.create(dbPathCopy));
        } catch (DatabaseException e) {
           System.out.println( "DatabaseException: error code: " 
                  + e.getErrorCode() + " msg: " + e.getMessage());        
        } finally {
            // Close the database.
            dbCopy.close();            
        }
          
        // Open a connection to the database file that was created.
        FileConnection outfc = (FileConnection)Connector.open(dbPathCopy);
           
        // Truncate the file.
        outfc.truncate(0);
                       
        // Write out the downloaded data to FileConnection.openOutputStream().
        OutputStream os = outfc.openOutputStream();
           
        FileConnection infc = (FileConnection)Connector.open(dbPath);        
        InputStream is = infc.openInputStream();
                       
        byte[] buf = new byte[1024];
        int len;
        while ((len = is.read(buf)) > 0){
            os.write(buf, 0, len);
        }
        is.close();
        os.close();                     
           
        // Close the file connection.
        outfc.close();
           
        System.out.println("Copied " + dbPath + " to " + dbPathCopy);
           
        // The database can now be reopened with the DatabaseFactory class.
    } catch (Exception e) {
        System.out.println("Exception: " + e.getMessage());
    }
}