|
Basic Topics | Advanced Topics | Reference Topics |
| Documentation Home Page |
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 - Simple command line utility that accepts SQL statements and processes them interactively.
ScriptTool - Sample program that accepts SQL scripts.
dump - 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 - Swing (JFC) version of the above. Uses Reflection to read meta-data.
"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.
"ScriptTool" (originally called "sample") was the original program designed to provide a simple illustration of using InstantDB. However, it is now a generally useful utility program for executing scripts.
Unless you specify a file to open, it reads a file called "sql1.txt" by default. This is the 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.
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.
An example script is shown below:
; First load the JDBC™ driver and open a database.
d org.enhydra.instantdb.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" is a simple little class for dumping the contents of some of InstantDB's 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:
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 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 addition to jdbc.idbDriver. It ignores any errors if any of these loads fail.
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:
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 have 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 privileges:
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 DBBrowser 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 DBBrowser, just as you would any other Java™ Application. It can take an optional URL as a parameter.
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:
You must have a JDK 1.1 compatible 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 InstantDB's 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.