apache > db

- Home
- Apache Derby

Font size:

# Using ij

- Connect to a Database
- Create a Database
- Disconnect from a database
- Exit
- Set the display width
- Run SQL Scripts

ij is the interactive SQL scripting tool that comes with Derby. Its many options are documented in the Tools and Utilities Guide. This section touches on just a few features to get you going with the fortunes database.

## Connect to a Database

This first example connects to the fortunes database based on its explicit location in the file system:

Windows:

    C:\> java org.apache.derby.tools.ij
    ij> connect 'jdbc:derby:C:\home\jta\demo\Fortune\fortunes';

UNIX:

    $ java org.apache.derby.tools.ij
    ij> connect 'jdbc:derby:/home/jta/demo/Fortune/fortunes';

This next example uses the derby.system.home property and the FORTUNE_HOME environment variable to specify the location of the Derby system home:

Windows:

    C:\> java -Dderby.system.home=%FORTUNE_HOME% org.apache.derby.tools.ij
    ij> connect 'jdbc:derby:fortunes';

UNIX:

    $ java -Dderby.system.home=$FORTUNE_HOME org.apache.derby.tools.ij
    ij> connect 'jdbc:derby:fortunes';

## Create a Database

To create a database specify the create=true attribute in the connection URL; for example:

    ij> connect 'jdbc:derby:my_fortunes;create=true';

## Disconnect from a database

The disconnect command disconnects from the current database:

    ij> disconnect;

## Exit

The exit command quits out of ij and, in embedded mode, shuts down the Derby database:

    ij> exit;

## Set the display width

When you select fortunes using ij you'll notice that long ones get truncated. For example, connect to the fortunes database and run the query shown below:

    ij> select fortune from fortunes where id=500;
    FORTUNE
    ---------------------------------------------------------------------------
    A man from AI walked across the mountains to SAIL to see the Master,
    Knuth.  When he arrived, the Master was nowhere to be fou&

    1 row selected

That ampersand (&) at the end of the output indicates it was truncated. You can increase the column width dynamically with MaximumDisplayWidth, as shown below:

    ij> maximumDisplayWidth 200;
    ij> select fortune from fortunes where id=500;
    FORTUNE
    ---------------------------------------------------------------------------
    A man from AI walked across the mountains to SAIL to see the Master,
    Knuth.  When he arrived, the Master was nowhere to be found.  "Where is the
    wise one named Knuth?" he asked a passing student.
    	        "&

    1 row selected

You can also set it with the ij.maximumDisplayWidth property when you start ij.

## Run SQL Scripts

You can execute SQL scripts with ij with it already started up, as shown below:

    ij> run 'file.sql';

You can also run SQL scripts from the command line as shown below (the command is split across two lines for readability):

    java -Dderby.system.home=$FORTUNE_HOME
        org.apache.derby.tools.ij create_fortunes.sql