SQLite

General enhancements

  • BlackBerry® Java® SDK 7.0 uses SQLite® version 3.7.2. BlackBerry Java SDK 6.0 used SQLite 3.6.21. For a description of the new features in SQLite, see the SQLite documentation, available at www.sqlite.org
  • Databases can now be significantly larger and more complex. The dynamic memory limit has increased to 16 MB (from 5 MB in 6.0). This limit refers to the amount of RAM available to an SQLite database for storing internal data structures for schemas and transactions

  • Queries can be much longer, which improves compatibility with databases that were created on other platforms. A query can now be up to 1 MB. In BlackBerry Java SDK 6.0, the query length limit was 4 KB

  • In BlackBerry Java SDK 7.0, up to 56 databases can be opened at the same time. This is because the file handle limit is increased to 64

  • You can now choose to use a write-ahead log instead of the rollback journal. The write-ahead log provides increased concurrency because writing to the database does not block reading. You can use a write-ahead log by setting the WAL option in the journal_mode pragma

  • Shared-cache mode is now enabled. This mode results in lower memory usage for multiple connections to the same database

SQLite as a service

For BlackBerry Java SDK 7.0, the underlying implementation of SQLite was redesigned in order to improve performance. SQLite now runs as a service, and database operations use a runtime bridge to transfer data between Java and native code. Several methods were added to help you use the runtime bridge efficiently.

  • When you create a prepared statement for inserting or updating data, you can use two new methods, Statement.executeInsert() and Statement.executeUpdate(), to reduce the number of calls over the runtime bridge. These methods perform the following operations in native code: bind SQL parameters, execute the statement, reset the statement, and clear bindings. In addition, executeInsert() returns the last inserted row ID

  • When you are running a query that doesn't return a result set and you are not binding parameters, you can use the new method Database.executeStatement(), which reduces calls over the runtime bridge by performing the following operations in native code: prepare the statement, execute the statement, and finalize the statement

  • When you are executing a query that returns result sets, you continue to use Statement.getCursor(), but you can now pre-fetch a specified number of rows by using Statement.setCursorBufferSize(). This reduces the use of the runtime bridge. When the cursor moves past the buffered set, a new batch of rows is automatically fetched. You can retrieve the number of rows a cursor will buffer with the new method Statement.getCursorBufferSize()

  • When you are retrieving integer values to use as keys in another query, you can use two new methods, Statement.getIntegers() and Statement.getLongs(). These methods simplify and optimize the retrieval of integer columns

Improved functionality for handling blobs

New methods for handling blobs eliminate the need to store the entire blob in memory, so the amount of available dynamic memory does not limit the blob size that can be read to or written from the database.

  • You can use the new method Database.createBlobOutputStream() to create an OutputStream object that is used to write data into a blob

  • You can use the new method Database.createBlobInputStream() to create an InputStream object that is used to read data from a blob

Reserving space for blobs

The Statement.bindZeroBlob() method allows you to bind a series of null bytes to a parameter in a Statement. The bindZeroBlob() method can be useful to reserve space for blob output when you are using Database.createBlobOutputStream() to write data into a blob and are executing the Statement with Statement.execute().

Per connection memory usage statistics

You can track memory usage for each database connection using the new methods Database.getCacheUsed(), Database.getSchemaUsed(), and Database.getStatementUsed().

You can retrieve SQLite column types for specified rows using the new method Row.getDatabaseColumnTypes().

Pragma support

The SQLite PRAGMA statement is now supported with a subset of pragmas. The net.rim.device.api.database.Pragma class contains constants for each supported pragma.

You can use the following pragmas in your SQLite application:

  • collation_list: returns a list of the collating sequences defined for the current database connection. Language collation is a new feature of the Database API in BlackBerry Java SDK 7.0; the supported languages are listed below

  • compile_options: returns the names of compile-time options used when building SQLite

  • database_list: returns one row for each database attached to the current database connection

  • foreign_keys: queries, sets or clears the enforcement of foreign key constraints. This setting is equivalent to using the foreign_key_constraints option in the DatabaseOptions class

  • freelist_count: returns the number of unused pages in the database, which you can use to determine when to run the VACUUM command

  • journal_mode: gets or sets the journal mode of the current database connection. The available settings provide different performance and data safety levels. The options are:
    • DELETE: deletes the rollback journal at the end of a transaction

    • 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

  • journal_size_limit: gets or sets the journal size limit in bytes. This is useful when journal_mode is set to PERSIST

  • page_count: returns the total number of pages in the database

  • synchronous: sets the synchronous mode for your database. The options are FULL (the safest but slowest mode, and the default), NORMAL, and OFF

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

Attaching and detaching databases

To allow you to attach and detach databases to an existing database connection, two new methods have been added, Database.attach() and Database.detach(). You can attach up to ten databases to a connection. You can also use the DETACH command in an SQL statement, but you cannot use the ATTACH command.

Language collation

Language collation is now supported. You can, for example, create a table that has French language collation enabled on a column. The following collation sequences are supported:

  • Standard
  • Afrikaans
  • Arabic
  • Catalan
  • Czech
  • French
  • Hebrew
  • Hong Kong Chinese
  • Hungarian
  • Japanese
  • Korean
  • Pinyin
  • Polish
  • Spanish
  • Taiwanese Chinese
  • Turkish
  • Thai
  • Romanian

Behavior changes for buffered cursors

The behavior of the BufferedCursor.isEmpty() method was changed so that this method no longer moves through the Cursor under any circumstances. BufferedCursor.isEmpty() also no longer returns false when the Cursor position is one more than the last Row available.

The BufferedCursor.prev() method was changed so that it repositions the cursor to the initial position (-1) when it is called multiple times.

Next topic: Security

Was this information helpful? Send us your comments.