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® device 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.

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.

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.

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.

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