8.4. The SQLLine Utility
Prev | Chapter 8. Schema Information | Next

## 8.4. 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. For complete documentation, see the project home page at http://sqlline.sourceforge.net. The remainder of this section discusses scenarios of using SQLLine to assist with understanding and developing Kodo applications.

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

Example 8.7. Connecting to the Database

SQLLine can accept a `kodo.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.7.0)
    Driver: HSQL Database Engine Driver (version 1.7.0)
    Autocommit status: true
    sqlline version 0.7.8

    0: jdbc:hsqldb:tutorial_database>

Example 8.8. 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       |          |         |
    |            |              | JDO_SEQUENCE  | TABLE       |          |         |
    +------------+--------------+---------------+-------------+----------+---------+

    0: jdbc:hsqldb:tutorial_database> !columns ANIMAL

    +------------+--------------+-------------+--------------+------------+--------+
    | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  | COLUMN_NAME  | DATA_TYPE  | TYPE_N |
    +------------+--------------+-------------+--------------+------------+--------+
    |            |              | ANIMAL      | JDOCLASS     | 12         | VARCHA |
    |            |              | ANIMAL      | JDOID        | -5         | BIGINT |
    |            |              | ANIMAL      | JDOVERSION   | 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      | JDOID        | 1        | SYS_PK_A |
    +------------+--------------+-------------+--------------+----------+----------+

Example 8.9. 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;

    +---------------+--------+-------------+---------+--------+
    |   JDOCLASS    | JDOID  | JDOVERSION  |  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 JDOID > 2;

    2 rows affected (0.002 seconds)

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

    +---------------+--------+-------------+---------+--------+
    |   JDOCLASS    | JDOID  | JDOVERSION  |  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)

    0: jdbc:hsqldb:tutorial_database>

Prev | Up | Next
8.3. XML Schema Format | Home | Chapter 9. Runtime Deployment