The 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.
The amount of RAM available to an SQLite database for storing internal data structures for schemas and transactions has increased to 16 MB (from 5 MB in 6.0). A query can now be up to 1 MB. In BlackBerry Java SDK 6.0, the query length limit was 4 KB. The file handle limit has increased to 64, allowing you to open up to 56 databases at the same time in In BlackBerry Java SDK 7.0.
In addition, 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. You can also use shared-cache mode to help lower memory usage for multiple connections to the same database.
SQLite as a service
When you create a prepared statement for inserting or updating data, you can use 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 execute a query that doesn't return a result set and you are not binding parameters, you can use Database.executeStatement() to reduce calls over the runtime bridge. This method performs the following operations in native code: prepares the statement, executes the statement, and finalizes the statement.
When using Statement.getCursor() to execute a query that returns result sets, you can now pre-fetch a specified number of rows by using Statement.setCursorBufferSize(). Using this method reduces the use of the runtime bridge. When the cursor moves past the buffered set, a new batch of rows is fetched. You can retrieve the number of rows a cursor buffers with the new method Statement.getCursorBufferSize().
When you retrieve integer values to use as keys in another query, you can use Statement.getIntegers() and Statement.getLongs(). These methods help to simplify and optimize the retrieval of integer columns.
Enhanced functionality for handling blobs
New methods for handling blobs eliminate the need to store the entire blob in application memory, so the amount of available dynamic memory does not limit the blob size that can be read to or written from the database. Database.createBlobOutputStream() creates an OutputStream object that is used to write data into a blob, and Database.createBlobInputStream() creates 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. You can use the bindZeroBlob method to reserve space for blob output when you use Database.createBlobOutputStream() to write data into a blob and execute the Statement with Statement.execute().
Connection memory usage statistics
You can track memory usage for each database connection by using the new methods Database.getCacheUsed(), Database.getSchemaUsed(), and Database.getStatementUsed().
You can retrieve SQLite® column types for specified by rows using the new method Row.getDatabaseColumnTypes().
You can now use a subset of SQLite® PRAGMA statements. The net.rim.device.api.database.Pragma class contains constants for each supported pragma. The following pragmas are supported:
Attaching and detaching databases
You can attach databases to, and detach databases from an existing database connection, two new methods have been added, Database.attach() and Database.detach(). These methods are designed to allow you to 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 is now supported. For example, you can now create a table that has French language collation enabled on a column. The following collation sequences are supported:
Behavior changes for buffered cursors
The BufferedCursor.isEmpty() method no longer moves through the cursor under any circumstances, and no longer returns false when the cursor position is one more than the last row available.
The BufferedCursor.prev() method repositions the cursor to the initial position (-1) when it is called multiple times.
To provide feedback on this deliverable, visit www.blackberry.com/docsfeedback.