4.15. The SQLLine Utility
Prev | Chapter 4. JDBC | Next

## 4.15. The SQLLine Utility

The Kodo distribution includes SQLLine, a console-based utility that interacts directly with a database using raw SQL. It is similar to other command-line database access utilities like `sqlplus` for Oracle, `mysql` for MySQL, and `isql` for Sybase/SQL Server. SQLLine can be useful as debugging tool by enabling low-level SQL interaction with the database.

SQLine is open-source software developed by BEA employees. For complete documentation, see the project home page at http://sqlline.sourceforge.net. The remainder of this section discusses scenarios using SQLLine to assist with understanding and developing Kodo applications.

| Note
As a separate utility, BEA does not provide technical support for SQLLine.

Example 4.22. Connecting to the Database

Use the `!connect` command to connect to your database.

    prompt$ java sqlline.SqlLine
    sqlline version 1.0.1
    sqlline> !connect
    Usage: connect <url> <username> <password> [driver]

    sqlline>

Example 4.23. Connecting to the Database via Properties

SQLLine accepts a Kodo JDO properties file as a startup parameter, and will use it to connect to the database:

    prompt$ java sqlline.SqlLine kodo.properties

    Connecting to jdbc:hsqldb:tutorial_database
    Connected to: HSQL Database Engine (version 1.8.0)
    Driver: HSQL Database Engine Driver (version 1.8.0)
    Autocommit status: true
    sqlline version 1.0.1

    0: jdbc:hsqldb:tutorial_database>

Example 4.24. Listing Commands

The `!help` command lists SQLLine command dictionary.

    0: jdbc:hsqldb:tutorial_database> !help
    !all                Execute the specified SQL against all current connections
    !autocommit         Set autocommit mode on or off
    !batch              Start or execute a batch of statements
    !brief              Set verbose mode off
    !call               Execute a callable statement
    !close              Close the current connection to the database
    !closeall           Close all current open connections
    !columns            List all the columns for the specified table
    !commit             Commit the current transaction (if autocommit is off)
    ...

Example 4.25. Examining the Tutorial Schema

SQLLine has various commands to analyze the schema of the database:

    0: jdbc:hsqldb:tutorial_database> !tables

    +------------+--------------+---------------+-------------+----------+---------+
    | TABLE_CAT  | TABLE_SCHEM  |  TABLE_NAME   | TABLE_TYPE  | REMARKS  | TYPE_CA |
    +------------+--------------+---------------+-------------+----------+---------+
    |            |              | ANIMAL        | TABLE       |          |         |
    |            |              | KODOSEQUENCE  | TABLE       |          |         |
    +------------+--------------+---------------+-------------+----------+---------+

    0: jdbc:hsqldb:tutorial_database> !columns ANIMAL

    +------------+--------------+-------------+--------------+------------+--------+
    | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  | COLUMN_NAME  | DATA_TYPE  | TYPE_N |
    +------------+--------------+-------------+--------------+------------+--------+
    |            |              | ANIMAL      | TYP          | 12         | VARCHA |
    |            |              | ANIMAL      | ID           | -5         | BIGINT |
    |            |              | ANIMAL      | VERSION      | 4          | INTEGE |
    |            |              | ANIMAL      | NAME0        | 12         | VARCHA |
    |            |              | ANIMAL      | PRICE        | 7          | REAL   |
    +------------+--------------+-------------+--------------+------------+--------+

    0: jdbc:hsqldb:tutorial_database> !primarykeys ANIMAL

    +------------+--------------+-------------+--------------+----------+----------+
    | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  | COLUMN_NAME  | KEY_SEQ  |    PK_NA |
    +------------+--------------+-------------+--------------+----------+----------+
    |            |              | ANIMAL      | ID           | 1        | SYS_PK_A |
    +------------+--------------+-------------+--------------+----------+----------+

Example 4.26. Issuing SQL Against the Database

Any SQL statement that the database understands can be executed in SQLLine, and the results (if any) will be displayed in a customizable format. The default is a table-like display:

    0: jdbc:hsqldb:tutorial_database> SELECT * FROM ANIMAL;

    +---------------+-----+----------+---------+--------+
    |     TYP       | ID  | VERSION  |  NAME0  | PRICE  |
    +---------------+-----+----------+---------+--------+
    | tutorial.Dog  | 0   | 0        | Binney  | 80.0   |
    | tutorial.Dog  | 1   | 0        | Fido    | 50.0   |
    | tutorial.Dog  | 2   | 0        | Odie    | 30.0   |
    | tutorial.Dog  | 3   | 0        | Tasha   | 75.0   |
    | tutorial.Dog  | 4   | 0        | Rusty   | 25.0   |
    +---------------+-----+----------+---------+--------+
    5 rows selected (0 seconds)

    0: jdbc:hsqldb:tutorial_database> DELETE FROM ANIMAL WHERE ID > 2;

    2 rows affected (0.002 seconds)

    0: jdbc:hsqldb:tutorial_database> SELECT * FROM ANIMAL;

    +---------------+-----+----------+---------+--------+
    |     TYP       | ID  | VERSION  |  NAME0  | PRICE  |
    +---------------+-----+----------+---------+--------+
    | tutorial.Dog  | 0   | 0        | Binney  | 80.0   |
    | tutorial.Dog  | 1   | 0        | Fido    | 50.0   |
    | tutorial.Dog  | 2   | 0        | Odie    | 30.0   |
    +---------------+-----+----------+---------+--------+
    3 rows selected (0 seconds)

Example 4.27. Disconnecting from the Database

Use the `!quit` command to disconnect cleanly.

    0: jdbc:hsqldb:tutorial_database> !quit
    Closing: org.hsqldb.jdbcConnection

Prev | Up | Next
4.14. XML Schema Format | Home | Chapter 5. Persistent Classes