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]](img/note.gif) | 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>