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]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>