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