4.16. 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 Oracle. 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]Note

As a separate utility, Oracle does not provide technical support for SQLLine.

Example 4.23. 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.24. 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.25. 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.26. 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.27. 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.28. Disconnecting from the Database

Use the !quit command to disconnect cleanly.

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

 

Skip navigation bar   Back to Top