////////////////////////////////////////
///// JavaScript in JDBC Navigator /////
////////////////////////////////////////
JDBC Navigator contains the Rhino Javascript interpreter. It can be accessed
by opening a JavaScript window (in the Misc menu) and typing JavaScript
commands. The JavaScript window acts like a cross between a console (terminal)
and a text editor: you can move the cursor, make selections, and cut & paste,
and you can pass text to the JavaScript interpreter for execution by pressing
Enter. (You can insert a line break without executing anything by pressing
Shift-Enter.)
If you press Enter while there is no selection, the line that the cursor is on
is executed; if there is an active selection, that selection is executed.
The output generated by executing the JavaScript commands is pasted into the
window just below the executed commands.
The output is automatically selected; this is intended to make it easier to
back up, modify, and re-run commands: just hit Backspace and you're back at
the end of the last command. If, on the other hand, you do not want to back
up, but want to keep the output, you can simply press Enter again; in this
case, it does not execute the selection, but moves the cursor to just below
it.
You can interrupt the JavaScript interpreter by pressing Ctrl-. (control-dot).
Note that when you interrupt a JavaScript program that generates a lot of
output, it may take a while before you regain control, because the buffered
output may still be in the process of being displayed.
////////////////////////////////////////////////////////////
///// Summary of JDBC Navigator's JavaScript embedding /////
////////////////////////////////////////////////////////////
void print(...); // Print zero or more objects.
void println(...); // Print zero or more objects, followed
// by a line break.
Object clipboard; // The JDBC Navigator clipboard.
TreeNode[] browsers; // Array containing all open browsers;
// they appear here in the order in
// which they were opened. Each browser
// is represented by a tree node, which
// may optionally contain one or two
// additional levels of tree nodes (for
// the JDBC "catalog" and "schema" name
// spaces); below those are the Table
// objects.
class TreeNode {
String name; // Tree node name -- i.e., the
// connection, catalog, or schema name
int length; // Number of children
// Child nodes/tables appear as additional members; e.g. a Schema node
// containing three tables named "foo", "bar, and "baz", acts as if it has
// these declarations: "Table foo, bar, baz".
// Child nodes can also be retrieved using 0-based array indexing.
void commit(Table, ...); // Commits an arbitrary number of
// tables simultaneously.
// Low-level SQL access... the following functions create Statement,
// PreparedStatement, and CallableStatement objects for the owning
// database connection.
Statement createStatement();
PreparedStatement prepareStatement(String stmt);
CallableStatement prepareCall(String call);
}
class Table {
String name; // Table name
int length; // Number of rows
int width; // Number of columns
String[] columns; // Column names
PrimaryKey pk; // Primary key
ForeignKey[] rks; // Referencing (exported) keys
ForeignKey[] fks; // Foreign (referencing/imported) keys
Index[] indexes; // Indexes
// Table rows can be retrieved using 0-based array indexing.
// The [] operator returns an instance of the Row class; see below.
void load(); // Load/reload table data from the DB
void addRow(); // Add new row at the bottom
void removeRow(int n); // Remove nth row
void commit(); // Commit all changes to the DB
void rollback(); // Roll back all changes since the last
// load/reload
int pk2row(Object, ...); // Convert PK value to row index. PK may
// be specified using an array, or using
// multiple arguments.
Object[] row2pk(int r); // Convert row index to PK value. The
// returned value is suitable for
// passing to pk2row() and fk2rows().
int[] fk2rows(int n, Object, ...); // Convert FK value for FK fks[n]
// to zero or more row indexes. FK may
// be specified using an array, or using
// multiple arguments.
Object[] row2fk(int n, int r); // Convert row index r to FK value for
// FK fks[n]. The returned value is
// suitable for passing to pk2row() and
// fk2rows().
// NOTE: at the end of this file, there is an example showing how to use
// the pk2row(), row2pk(), fk2rows(), and row2fk() methods.
}
class Row {
int length; // Number of columns
// The individual fields of the record (row) can be retrieved by name,
// that is, as if they were additional members; and they can also be
// retrieved using 0-based array indexing.
}
class PrimaryKey {
String name; // Key name
String[] columns; // Key columns
}
class ForeignKey {
String name; // This key's name
String[] columns; // This key's columns
String reftable; // Referenced table
String refname; // Referenced key's name
String[] refcolumns; // Referenced key's columns
}
class Index {
String name; // Index name
String[] columns; // Index columns
boolean unique; // Unique index?
}
class Statement {
int/ResultSet execute(String sql); // Execute a SQL statement. Queries
// return a ResultSet; everything else
// returns an update count.
void close(); // Close the statement and release its
// resources (cursors etc.).
}
class PreparedStatement {
void setObject(int col, Object obj [, int/String type [, int scale]]);
// Sets a parameter. The optional
// 'type' parameter may be an index or
// a String; see java.sql.Types for
// possible values.
int/ResultSet execute(); // Executes the statement. Queries
// return a ResultSet; everything else
// returns an update count.
void close(); // Close the statement and release its
// resources (cursors etc.).
}
class CallableStatement {
void setObject(int col, Object obj [, int/String type [, int scale]]);
// Sets a parameter. The optional
// 'type' parameter may be an index or
// a String; see java.sql.Types for
// possible values.
void registerOutParameter(int/String param, int/String type
[, int/String scale/typeName]);
// Registers an OUT parameter so its
// value so its value can be retrieved
// later, with getObject().
int/ResultSet execute(); // Executes the call. Can return a
// result set or a count.
Object getObject(int/String param);// Retrieves the value of an OUT
// parameter.
void close(); // Close the statement and release its
// resources (cursors etc.).
}
class ResultSet {
int length; // Number of columns in the result set
String[] columns; // Column names
String[] dbtypes; // DB-specific column type names
String[] sqltypes // java.sql.Types column type names
String[] javatypes; // equivalent Java class for col. types
// Result Set columns appear as additional members; they can also be
// retrieved using 0-based array indexing.
boolean next(); // Returns 'false' when no more data is
// available.
void close(); // Close the result set and release its
// resources (cursors etc.).
}
///////////////////////////////////////////////////////
///// A small example to try to clarify the usage /////
///// of the row/key conversion methods /////
///////////////////////////////////////////////////////
Say we have two tables, PERSON and FAMILY_TREE_NODE. PERSON is used to store
data about persons; FAMILY_TREE_NODE is used to describe family relationships
between persons. Let's say the tables are created as follows:
create table PERSON
(
FIRSTNAME VARCHAR2(32) not null,
LASTNAME VARCHAR2(32) not null,
SSN VARCHAR2(9) not null,
PHONE VARCHAR2(20),
ADDRESS1 VARCHAR2(64),
ADDRESS2 VARCHAR2(64),
CITY VARCHAR2(32),
ZIP VARCHAR2(9),
constraint PERSON_PK primary key (SSN, LASTNAME, FIRSTNAME)
);
create table FAMILY_TREE_NODE
(
OWN_SSN VARCHAR2(9) not null,
OWN_FIRSTNAME VARCHAR2(32) not null,
OWN_LASTNAME VARCHAR2(32) not null,
MOM_SSN VARCHAR2(9),
MOM_FIRSTNAME VARCHAR2(32),
MOM_LASTNAME VARCHAR2(32),
DAD_SSN VARCHAR2(9),
DAD_FIRSTNAME VARCHAR2(32),
DAD_LASTNAME VARCHAR2(32),
constraint FTN_PK primary key (OWN_SSN, OWN_FIRSTNAME, OWN_LASTNAME),
constraint FTN_OWN_FK foreign key (OWN_SSN, OWN_LASTNAME, OWN_FIRSTNAME)
references PERSON(SSN, LASTNAME, FIRSTNAME),
constraint FTN_MOM_FK foreign key (MOM_SSN, MOM_LASTNAME, MOM_FIRSTNAME)
references PERSON(SSN, LASTNAME, FIRSTNAME),
constraint FTN_DAD_FK foreign key (DAD_SSN, DAD_LASTNAME, DAD_FIRSTNAME)
references PERSON(SSN, LASTNAME, FIRSTNAME)
);
We have created these tables in the THOMAS schema in an Oracle 8i database.
How do we find all the siblings of Thomas Okken, SSN 123-45-6789?
First, assign the PERSON and FAMILY_TREE_NODE tables to variables, to save some
typing later on. The value of the variable, which in this case is a pretty-
printed version of the table, is printed:
p = browsers[0].thomas.table.person;
ftn = browsers[0].thomas.table.family_tree_node;
If you do not have table viewing windows open on these tables, you may have to
tell JDBC Navigator to load them into memory. You can tell whether or not a
table is loaded by getting its length: if that returns -1, the table is not
loaded yet.
p.load();
ftn.load();
Find the row index of Thomas Okken 123-45-6789 in the PERSON table. Note that
the order in which the key components are specified must match the order in
which they appear in the primary key declaration part of the CREATE statement
(see above):
p.pk2row('123-45-6789', 'Okken', 'Thomas');
If the primary key value does not exist, this will return 'null'; if it does
exist, it returns the row index, which is an integer; if you open a table
viewing window, the row index corresponds to the position of the record in the
window, with the topmost row having index 0.
Who is Tom's mom?
First, find Tom in the FAMILY_TREE_NODE table; then, print that row from the
table to see who his parents are. Note that the first argument to fk2rows() is
0 here, which means we're using the first foreign key, which is the key
representing the person him/herself (key 1 is his/her mom; key 2 is dad):
idx = ftn.fk2rows(0, '123-45-6789', 'Okken', 'Thomas');
if (idx.length == 0)
println("Oops!");
else
ftn[idx[0]];
This last statement prints something like
[ 123-45-6789 Thomas Okken 888-88-8888 Jane Roe 999-99-9999 John Doe ]
Note that the order of the key components in the primary and foreign keys do
not necessarily have to be the same; fk2rows() expects the components in the
order in which they appear in the referenced primary key. This may seem
confusing at first, but it has the advantage that keys returned by p.row2pk()
can be passed to ftn.fk2rows(), and keys returned by ftn.row2fk() can be passed
to p.pk2row(), without having to reshuffle the components first.
Who are Tom's siblings?
tom = ftn.fk2rows(0, '123-45-6789', 'Okken', 'Thomas')[0];
mom = ftn.row2fk(1, tom);
dad = ftn.row2fk(2, tom);
siblings1 = ftn.fk2rows(1, mom);
siblings2 = ftn.fk2rows(2, dad);
The first call returns the row index corresponding to Tom in the
FAMILY_TREE_NODE table; the second and third find the key value for his mom and
dad (i.e. the PK values representing them in the PERSON table); and the fourth
and fifth return the row indexes corresponding to Tom's mother's children, and
Tom's father's children.
Print the details of Tom's siblings:
for (i = 0; i < siblings1.length; i++)
println(p[p.pk2row(ftn.row2fk(0, siblings1[i]))]);
for (i = 0; i < siblings2.length; i++)
println(p[p.pk2row(ftn.row2fk(0, siblings2[i]))]);
Go back.