IDB logo InstantDB

Basic

Home
Features
Installation
README
Data types
Examples

Example Programs

InstantDB comes with several utility programs, together with their source code. These are intended to introduce users to JDBC programming, and to InstantDB in particular.
  • commsql - a simple command line utility that accepts SQL statements and processes them interactively.
  • sample - sample program that accepts SQL scripts.
  • dump - a utility program that dumps the contents of various InstantDB system files.
  • JDBCAppl - similar to InstantSQL but with a few navigation and SQL builder additions.
  • SQLBuilder - A Swing (JFC) version of the above. Uses Reflection to read meta-data.

commsql

"commsql" - short for Command line SQL - is a very simple program that accepts SQL statements from the command line, and executes them interactively.

It always loads the InstantDB JDBC driver at start up, but you can easily modify the code to load any other drivers you require.

commsql then prompts the user for a URL to connect to. In the case of InstantDB this can be the URL for any InstantDB properties file. The database corresponding to this properties file is then created if necessary and opened.

The user can then type in any valid InstantDB SQL commands. If these produce a results set then the results are displayed.

When you want to exit the program, simply type "." (without the quotes) on a line by itself.

commsql.gif

sample

"sample" was the original program designed to provide a simple illustration of using InstantDB. However, it is now used as the InstantDB test harness, and as such has become considerably more complex.

It always begins by opening a file called "sql1.txt". This is the initial script containing commands to be executed. Every command begins with a command letter, followed by either a SQL statement or further command information. All commands must be terminated with a semi-colon - ';'.

The valid commands are listed below.

  • c command; - execute one of a list of alternative commands
  • d driver; - load a driver, e.g. d jdbc.idbDriver;
  • e sql; - execute command with no results set, e.g. e UPDATE table1 SET col1=col1+1;
  • i table; - get index info for table
  • o url; - open url, e.g. o jdbc:idb=sample.prp;
  • p sql; - create a prepared statement, e.g. SELECT * FROM ?;
  • q sql; - query with results set
  • r count {; - loops round the commands in brackets count times.
  • }; - terminates a loop
  • 's' param1,param2,...; - set parameters on a prepared statement and execute
  • 't' script; - start off a new thread with its own input script
The 'd' command must be issued at the start of the sql1.txt script to load at least one JDBC driver . This will normally be followed by an 'o' command to open a database.

The 'c' is a sort of "catch all" for performing various miscellaneous tasks. It must be followed by one of the textual phrases shown below.

  • c autocommit on; - switches autocommit mode on.
  • c autocommit off; - switches autocommit mode off. All further transactions have to be explicitly committed or rolled back.
  • c break; - a useful "no-op" command. By setting a break point on the line of code in sample.java that handles this, you can get a script to break at any point by simply placing this command wherever you want the break to occur.
  • c use global prepared statement; - use the current "global" prepared statement rather than the thread's own prepared statement.
  • c set global prepared statement; - make the current prepared statement the global statement that other threads can pick up.
  • c use global connection; - use the global Connection object rather than setting up a separate connection for this thread.
  • c set global connection; - make the current Connection object available to other threads.
  • c set isolation READ_UNCOMMITTED; - set the transaction isolation level to Connection.TRANSACTION_READ_UNCOMMITTED;
  • c set isolation SERIALIZABLE; - set the transaction isolation level to Connection.TRANSACTION_SERIALIZABLE;
  • c time; - The time in milliseconds that the previous command took to run.
  • c close; - close the current Connection.
  • c rollback; - rollback the current transaction using the JDBC Connection.rollback() method.
  • c commit; - commit the current transaction using the JDBC Connection.commit() method.
  • c show meta-data; - displays the type info and table list results sets.
  • c wait for children; - wait for all child threads to complete.
  • c exit; - exit the program immediately.

An example script is shown below:

; First load the JDBC driver and open a database.
d jdbc.idbDriver;
o jdbc:idb=sample.prp;

; start off a couple of threads reading from some other scripts
t sql2.txt;
t sql3.txt;

; Record all results
e SET EXPORT "export0.txt" FIXEDLENGTH COLNAMEHEADER ROWNUMBERS CONTROLCOL SUMMARYHEADER;

; Create the table and its index
e DROP TABLE tester;
e CREATE TABLE tester (
	id			int PRIMARY KEY,
	fullName 	CHAR(30),
	email		CHAR(60),
	login		CHAR(8),
	password	CHAR(20) );
e CREATE INDEX loginIndex ON tester ( login );

; put some initial data in the table
e INSERT INTO tester VALUES (1,"Alice","Alice@isp","Alice","Alice");
r 5 {;
    e INSERT INTO tester VALUES (2,"Bob","Bob@isp","Bob","Bob");
};
q SELECT * FROM tester;

; modify the table contents
p UPDATE tester SET fullName=?,email=?, login=?, password=? WHERE id=?;
s 'pete@some','pete','pete','pete',2;
q SELECT * FROM tester;

c close;

dump

"dump" is a simple little class for dumping the contents of some of InstantDBs system files. You can dump the contents of xxx$db$cols, xxx$db$tables, or of the journal file.

You must be in the directory actually containing the file being dumped before issuing the command. Output is normally to standard out, but you can of course redirect to any file if required.

The syntax is as follows:

  • java dump dbase t - This dumps the dbase$db$tables table contents of the InstantDB database called "dbase" to standard out.
  • java dump dbase t id - Dumps information from the dbase$db$cols table for the table with TableID "id". ID can be obtained from a dump of the dbase$db$tables table. If ID is 0, then information belonging to all tables is dumped.
  • java dump dbase j - dumps the contents of the journal file.
As dump.java is included, you have access to the structure of these system files. You are welcome to use this knowledge to develop more user friendly utilities. However, you should be aware that the structure is likely to change over time.

JDBCAppl

JDBCAppl illustrates InstantDB being accessed from a Java Applet. Opening the ex1.htm example in your browser will show JDBCAppl at work. The instructions for using JDBCAppl are included in ex1.htm.

JDBCAppl tries to load the RmiJdbc SQL driver and the jdbc-odbc bridge in addtion to jdbc.idbDriver. It ignores any errors if any of these loads fail.

jdbcappl.gif

Also included in ex1.htm, is a sample piece of JavaScript. This illustrates how a Java Applet's public methods and properties can be accessed using the JavaScript object model.

There are two issues that you'll have to consider in order to get the ex1.htm example to work properly:

  1. JavaScript compatability.
  2. Applet security.

Internet Explorer 3.01 (build 4.70.1215) or above runs the JavaScript OK on both '95 and NT 4. There were problems with its object model in version 3.00. All versions of Netscape that've been tried appear to run the JavaScript OK.

Internet Explorer also seems quite happy to let a local applet perform local file access (InstantDB needs permission to read and write to the disk in order to operate). Note however that you should add the path for the InstantDB and JDBCAppl classes to the class path in the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\Java VM\Classpath
The proper way to get the applet to work with Netscape Communicator is to sign it with a digital certificate that is either trusted, or has itself been signed by a trusted certification authority. Netscape's web site has full details on how to do this.

As a work around, you can include the following import statement in the applet:

import netscape.security.PrivilegeManager;

Then add the following lines to the method requesting the additional priviliges:
PrivilegeManager.enablePrivilege("UniversalPropertyRead");
PrivilegeManager.enablePrivilege("UniversalPropertyWrite");         
PrivilegeManager.enablePrivilege("UniversalFileRead");
PrivilegeManager.enablePrivilege("UniversalFileWrite");	         
PrivilegeManager.enablePrivilege("UniversalFileDelete");

(The file JDBCAppl.nets is simply a modified version of JDBAppl.java that already contains the above changes).

Finally find Communicator's prefs.js file (mine was in c:\program files\netscape \users \default), and add the following line:

user_pref("signed.applets.codebase_principal_support", true);
When compiling the applet, ensure that c:\progra~1 \netscape \communicator \program \java \classes \java40.jar (or wherever your netscape java classes are), is included in your CLASSPATH.

Thanks to Alok Singh for providing this information.

There's a class called JDBCmain that provides a main method and frame for JDBCAppl, In order to run JDBCAppl outside of a browser all you have to do is run JDBCmain, just as you would any other Java Application. It can take an optional URL as a parameter.

SQLBuilder

SQLBuilder is a Swing (JFC) application which allows you to navigate through database tables and columns. It's similar to JDBCAppl except that it has the following additional capabilities:
  1. Database meta-data and ResultSet meta are both populated using Reflection.
  2. It lets you specify a row limit on queries.
  3. Simple queries can be built simply by selecting columns.

sqlbuilder.gif

You must have a JDK 1.1 compatable virtual machine and Swing installed to use this application. By default, it uses the old Swing package hierarchy. The source contains commented out imports for use with JDK 1.2.

SQLBuilder has two main user interface areas. A small row of buttons allows the user to select the JFC look and feel. Below this is the main SQLBuilder interface. This consists of four tabbed panels.

The first tabbed panel is the Connection Panel. Choose a JDBC driver from the drop down list and then enter a url. If you're opening an InstantDB database then you can use the Browse button to select a properties file. Some databases may also require a username and password to be entered.

When all the required data has been supplied, click on the Connect button. The status changes to "Connecting..." and finally to "Connected". The connect button then changes to a "Disconnect" button.

Switching to the Dbase Data Panel, you can see the database meta-data for this database.

The Query Panel provides a drop down list of the available tables. For the currently selected table, a second drop down list shows the available columns.

Selecting a table causes its columns to be entered in the columns drop down list box. It also sets up a default query to select everything from the table.

Selecting a column causes the query to change to that column only. Selecting more columns causes them to be added, one by one, to the query.

The Row Limit box allows you limit the number of rows returned by the query and the Submit button submits the currently entered query. You can also enter any other query in the Query text box.

When connected to an InstantDB database, the contents of the results table will faithfully represent the query results. This takes advantage of InstantDBs results set navigation API calls. For other databases, the table is always 1000 rows long and you can only move forward through the ResultSet. To enable proper navigational capabilities, the application would have to read and cache all the results set. (If anyone wants to add this capability then feel free - mail it to us and we'll include what you do in a future release).

The Result Data Panel shows ResultSet meta-data for the current ResultSet. This is mostly column based data. Selecting a column from the drop down list, select the column for which meta-data is displayed.

Thanks to Mark Ford for providing the changes to SQLBuilder to avoid some bugs and add layout managers to the main panels.