Question

In the fourth chapter, we are going to look at Derby's ij tool in a greater detail.

The ij is an interactive scripting tool supplied with Derby. It is a command line client for the Derby database system. It can be used in two ways: either run SQL files or interactively execute SQL statements. The ij is located in the bin directory of the Derby installation directory.

$ ls $DERBY_HOME/bin | grep ij
ij
ij.bat

There are two scripts: the one with the .bat extension is for Windows.

Starting ij

The ij can be started in three basic ways.

$ ij
ij version 10.8
ij> 

Derby ij can be started with the ij script. The above example assumes that we have the Derby's bin directory in our PATH variable. Otherwise we must use the full path to the ij tool.

$ java -cp $DERBY_HOME/lib/derbytools.jar org.apache.derby.tools.ij
ij version 10.8
ij> 

Another way is to execute the compiled Java program. We must have the derbytools.jar in our classpath. The first way does essentially the same within the script file. It also works with environment variables.

$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
ij> 

In the third way, we use the derbyrun.jar file to start the ij tool.

Running SQL scripts

Unlike MySQL or PostgreSQL command line tools, ij is very simplistic. It does not have a history of commands. Cursor keys cannot be used. It is quite inconvenient to use the tool. It is easier to edit SQL statements in a file and run it with the ij tool.

$ cat cars.sql 
SET SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);

A CARS table is created in schema USER12 and five rows are inserted.

ij> RUN 'cars.sql';
ij> SET SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS VALUES(1, 'Audi', 52642);
1 row inserted/updated/deleted
...

We use the RUN command to execute the cars.sql file. The file is located in the same directory, where ij was launched.

ij> SELECT * FROM CARS;
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    
2      |Mercedes            |57127    
3      |Skoda             |9000     
4      |Volvo             |29000    
5      |Bentley             |350000   
6      |Citroen             |21000    
7      |Hummer            |41400    
8      |Volkswagen          |21600    

8 rows selected

We check the data. The table was successfully created.

An SQL file name can be taken by the ij tool as a parameter.

$ cat cars2.sql 
CONNECT 'jdbc:derby:testdb';

SET SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);

We add a CONNECT statement to the cars2.sql file. When we launch the ij tool, we are not yet connected to the database.

ij> DROP TABLE CARS;
0 rows inserted/updated/deleted
ij> EXIT;
$

We drop the table before we execute the cars2.sql script.

$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij cars2.sql

We set the Derby system directory and launch the ij tool with the cars2.sql as a parameter. The CARS table is created again.

Basic commands

We can issue two kinds of commands. Commands specific to the ij tool and SQL statements. Each command in ij is terminated with a semicolon. All ij commands, identifiers, and keywords are case-insensitive.

ij> HELP;
 
 Supported commands include:
 
  PROTOCOL 'JDBC protocol' [ AS ident ];
                 -- sets a default or named protocol
  DRIVER 'class for driver';   -- loads the named class
  CONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ];
                 -- connects to database URL
                 -- and may assign identifier
...

The HELP command shows a list of ij commands

ij> CONNECT 'jdbc:derby:testdb';

The CONNECT command connects to a database. In our case the database name is testdb. This example assumes that we have set the Derby system directory. (More about it in the next section.) Derby by default does not require a user name and a password. We can configure Derby to require it.

ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection

The SHOW CONNECTIONS statement lists all opened connections. In our case we can see an opened connection to the testdb database.

ij> SHOW TABLES;
TABLE_SCHEM     |TABLE_NAME          |REMARKS       
------------------------------------------------------------------------
SYS         |SYSALIASES          |          
SYS         |SYSCHECKS           |          
SYS         |SYSCOLPERMS           | 
...

The SHOW TABLES command shows all tables in a database. There are a few SYS tables.

ij> SHOW TABLES IN USER12;
TABLE_SCHEM     |TABLE_NAME          |REMARKS       
------------------------------------------------------------------------
USER12        |CARS              |          

1 row selected

We can list tables in a specific schema. The SHOW TABLES IN USER12 shows tables in the USER12 schema.

ij> DESCRIBE USER12.CARS;
COLUMN_NAME     |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID          |INTEGER  |0   |10  |10  |NULL    |NULL    |NO    
NAME        |VARCHAR  |NULL|NULL|30  |NULL    |60    |YES   
PRICE         |INTEGER  |0   |10  |10  |NULL    |NULL    |YES   

3 rows selected

The DESCRIBE command provides a decription of the specified table or view. If the USER12 schema is not the current schema of the connection, we have to specify it before the table name. The current schema is specified in the connection string as the user name.

ij> DISCONNECT;
ij> SHOW CONNECTIONS;
No connections available.

The DISCONNECT command disconnects from the database. The subsequent SHOW CONNECTIONS command shows no available connections.

ij> EXIT;
$ 

Finally, we exit the ij tool. In case of an embedded environment, it also shuts down the database. Which is equivalent to the CONNECT 'jdbc:derby:testdb;shutdown=true'; command.

The main purpose of the ij tool is to issue SQL commands. We reconnect to the testdb database.

$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:testdb;user=USER12';

Now the current schema is the USER12 schema. When issuing SQL statements for tables located in the USER12 schema, we can omit the schema name.

ij> SELECT * FROM CARS WHERE ID IN (1, 3, 5);
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    
3      |Skoda             |9000     
5      |Bentley             |350000   

3 rows selected

In the above SQL statement, we select all three columns for rows with IDs 1, 3, and 5.

ij> SELECT * FROM APP.FRIENDS;
ID     |NAME           
-------------------------------------
1      |Jane           
2      |Thomas           
3      |Beka           

3 rows selected

Three rows from the FRIENDS table were selected. Since the table is not located in the current schema, we must fully qualify the table name.

ij> INSERT INTO APP.FRIENDS(ID, NAME) VALUES(4, 'Robert');
1 row inserted/updated/deleted
ij> SELECT ID, NAME FROM APP.FRIENDS WHERE ID=4;
ID     |NAME           
-------------------------------------
4      |Robert           

1 row selected

We insert a new row into the FRIENDS table and later select it.

Specifying the Derby system directory

Each time we work with Derby, we have to provide the Derby system directory. In this directory we can find our database files, configuration data or a log file. If we do not provide a Derby system directory a current directory is assumed to be the Derby system one. To provide the Derby system directory, we assign the derby.system.home property. It can be done via JVM option, configuration file, environment variable or in a Java file.

$ ls
cars.sql   dbs  doc~  ij.properties  links   
cars.sql~  doc  dump  lib  

The current working directory has a subdirectory called dbs. We want this directory to be our Derby system directory. The current working directory is the place from where we launch the ij tool.

$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8

Here we specify the Derby system directory with the -D JVM option. The derby.log file is created in the system directory. In our case the log file should appear in the dbs directory. The derby.log file is recreated each time we connect to a Derby database. We can look at the timestamp. If the derby.log file appears ouside the intended directory, we have not set the Derby system directory correctly.

ij> CONNECT 'jdbc:derby:testdb';
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection

Since we have correctly set the Derby system directory, we only specify the database name in the connection URL and omit the full path. The SHOW CONNECTIONS shows the opened connection.

We might not want to specify the Derby system directory each time. We could utilize the DERBY_OPTS environment variable.

$ echo $DERBY_OPTS
-Dderby.system.home=/home/janbodnar/programming/derby/dbs

$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:testdb';
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection

Creating a connection to a Derby database is now a bit easier. We save a few key strokes.

The ij properties

When starting up the ij tool, we can specify properties on the command line or in a properties file. The properties are various parameters taken by the ij tool. They can save us some repetitive work.

$ java -Dij.user=USER12 -Dij.database=testdb -Dij.protocol=jdbc:derby: \
> -Dderby.system.home=/home/janbodnar/programming/derby/dbs \ 
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
CONNECTION0* -  jdbc:derby:testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID = 1;
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    

1 row selected
ij>

We provide three ij properties on the command line with the -D option. The ij.user specifies the user name to establish a connection. The supplied user name becomes the current schema. The ij.database has the database name to which we connect. The ij.protocol property specifies the default protocol and subprotocol portions of the database connection URL. We are ready to launch SQL statements.

In the next example, we create an ij.properties file, where we set three ij properties.

$ cat ij.properties 
ij.user=USER12
ij.database=testdb
ij.protocol=jdbc:derby:

With the cat command, we show the contents of the ij.properties file. We set the same properties as in the first example.

$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij -p ij.properties
ij version 10.8
CONNECTION0* -  jdbc:derby:testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID=2;
ID     |NAME              |PRICE    
------------------------------------------------------
2      |Mercedes            |57127    

1 row selected

The -p option of the ij tool takes the properties file name.

In the chapter, we have covered the ij tool.