Basic Topics Release Notes Installation Important Info Sample Applications Data Types	Advanced Topics Networking Tuning Transactions Readonly Mode Import & Export JDBC™ Triggers Encryption User-Defined Functions	Reference Topics Syntax Database Properties Javadoc Debugging Known Bugs
Documentation Home Page

JDBC™ Implementation

Accessing InstantDB Internal Data
Non-Standard Extensions
Scrollable Results Sets
Memory Management

There are really only a couple of things that need to be said about the JDBC™ driver for InstantDB. The first is the all important URL syntax. Originally, InstantDB required URLs of the form:

jdbc:idb=pathname

The "=" has caused several systems to complain, so URLs of the form:

jdbc:idb:pathname

are now accepted as well. pathname is the path to a properties file. The properties file then tells InstantDB where to find or create the database tables. The name of the properties file is also used to derive a name for the database. So renaming a properties file after a database has been created will cause the database to become inaccessible.

The driver supports prepared statements, but not callable ones.

DatabaseMetaData.getIndexInfo and getPrimaryKeys are only partially supported. They correctly identify the indexes corresponding to any given table, together with the columns which are indexed. However, all other information returned by these routines simply takes a default value. This will be fixed in a future release.

The driver passes all of Sun's JDBC™ tests that were not specifically excluded because of its limited feature set.

InstantDB limits the number of simultaneously open ResultSets to one per Statement. As soon as a new SQL statement is executed, any existing ResultSet is closed. To access multiple ResultSets simultaneously, use multiple Statements. This is as per the JDBC™ specification.

It is important to try and close ResultSets explicitly whenever possible. If the garbage collector tries to destroy an open ResultSet, then InstantDB's finalize method will try and close the ResultSet. This can lead to transaction access conflicts with the main thread in some virtual machines. This in turn can lead to deadlocks.

Accessing InstantDB Internal Data

InstantDB provides extensions to its SQL implementation to allow access to internal data which is not stored in any of the metadata tables.

Any expression can contain the following:

__IDB_INTERNAL <param> FROM expr3 IN TABLE expr2

The <param> must be one of a fixed set of string literals which select the internal variable required. Case is not significant. The currently allowed values are:

• cardinality - the position of the column in the table.
• row_count - the total number of rows in the table, including deleted rows.
• del_row_count - the number of deleted rows in the table. You can use any valid column name in a table to select this or the above value.
• last_value - returns the last value inserted in the given column in the given table.
• data_type - returns the java.sql.Types type for the column.
• readers_waiting - the number of connections waiting to get a read lock on the table.
• read_locks - the number of read locks granted so far on a table.
• write_locks - the number of write locks granted so far on a table.
• readers - the number of connections with a read lock on the table.
• writers - the number of connections with a write lock on the table.

The "expr1" expression should evaluate to a column name. It can include any of the usual expression syntax.

The "expr2" parameter is similar to "expr1" but must evaluate to a table name. The table name does not have to be present in the FROM clause of a query.

An example of this type of expression might be:

SELECT tableName, ColName,
__IDB_INTERNAL cardinality FROM ColName IN TABLE tableName
FROM sample$db$cols c, sample$db$tables t
WHERE c.TableID=t.TableID
AND tableName LIKE '%t%' IGNORE CASE;

To get the last value inserted in a column use:

SELECT 
__IDB_INTERNAL last_value FROM TEXT("int1") IN TABLE mytable
FROM mytable

Note that the TEXT() function has to be used around the column name. If this were ommitted then InstantDB would attempt to evaluate the contents of the table "int1".

Non-Standard Extensions

Properties idbConnection.getProperties() provides access to the Properties supplied when the Connection was created by the DriverManager class.

int idbStatement.getBatchCount() returns the number of statements currently batched for execution.

Object idbConnection.getLastValueInserted(String tableName, String columnName) returns the last value inserted into the specified table and column. You should set auto commit off if you are using this in a multi-threaded environment. That way you can perform the explicit sequence: INSERT, getLastValueInserted(), COMMIT.

The following extensions were created before JDBC™ 2.0 was defined. On Java™ 2 platforms, it is recommended that the JDBC™ 2.0 scrollable results set methods be used instead.

JDBC™ provides support for named cursors, which are not currently supported by InstantDB. In order to provide a results set navigation capability, InstantDB provides the following non-standard extensions:

1. int idbResultsSet.getRowCount() - returns the number of rows in the results set.
2. int idbResultsSet.getCurRow() - returns the number of the next row to be returned. This is always in the range 1 to getRowCount().
3. void idbResultsSet.setCurRow(int) - sets the next row that will be returned when ResultSet.next() is called. This must be in the range 1 to getRowCount(), or a SQLException gets thrown.

You only have to use these routines where you need to move around a results set. An example might be where you want to populate a list box more efficiently, or any other situation where the data displayed is determined by user input. Normal ResultSet.next() calls can be used without invoking any of these non-standard extensions. The commsql example program illustrates the use of these extensions.

Scrollable Results Sets

InstantDB supports both scrollable and updatable results sets. However scroll sensitive results sets are not currently supported.

In fact when a statement is created, InstantDB ignores the results sets type requested. All InstantDB results sets are scrollable, updatable and insensitive (even to their own updates).

This means that, if you use a results set to update an underlying table, you will not see that change reflected in the results set itself. However, as soon as you query the table again, any updated values will become visible in the new results set.

Memory Management

There are two basic approaches that JVMs™ can take to garbage collection ("GC"). On the one hand, GC can take place synchronously, where advantage can be taken of the current context to remove any objects which have fallen out of scope. Alternatively, GC can take place asynchronously. The latter approach has to work harder to find objects eligible for destruction since its only means of finding unreferenced objects is to scan them all - which can take some time.

In order to help reduce memory growth, InstantDB makes every effort to set unused object references to null. During extended operations, such as table imports, InstantDB yields control to allow asynchronous GC to take place.

A well written garbage collector will always find unreferenced objects eventually - and that's the key point. There is always some latency before asynchronous garbage collection kicks in. Tests with InstantDB show that, on some JVMs™, this latency can be surprisingly long. It can take up to two thousand (!!) SQL requests before the database memory footprint reaches a steady state (i.e., garbage gets found at the same rate it gets generated).

In order to prevent run-away memory usage there are a few simple rules you should follow.

• After you've finished with a ResultSet or Statement always call the object's close() method, set all references to the object to null and call System.gc().
• Try to ensure that System.gc() gets called as close to the home of an object as possible. If a large object is about to go out of scope, set all references to it to null and call System.gc() before leaving the method.
• Try to allocate Vectors and Hashtables with sufficient room to hold all their elements right from the start. The growth of these objects will not only slow your program down, it will also leave more garbage lying around.
• Try not to get carried away doing String manipulations. Remember that Strings are immutable in Java™. Changing a String involves its deletion followed by the instantiation of a completely new String object. If you're performing a lot of string manipulation then use Stringbuffer rather than String.
• Try to reuse objects wherever possible.
• Make frequent calls to System.gc(), but not too frequent. Calling it too often, especially when memory is running low, will cause your program to slow down dramatically. The InstantDB database properties garbageCollectStatements and garbageCollectPercent can assist in generating regular requests for garbage collection.

Following the above rules will reduce the memory footprint of your applications, and, if done judiciously, will also make your Java™ programs run faster.