JDB SQL Reference

This chapter describes the utilities available with JDB:

• isql—A command-line interactive SQL utility

• jdbroll—Updates a database using its journal files

• jisql—Graphical interactive SQL editor

• mksql—Outputs SQL statements for the specified database

• tbldata—Imports/exports data to and from a database

isql

Access a command line interactive SQL utility

isql databaseName

databaseName

Specifies the name of the database or @system if a database isn't available.

The isql utility is a command-line interactive editor for JDB that lets you to execute any database statement. It is provided as a command-driven alternative to the JISQL graphical environment described in Chapter 5, "Using JISQL."

While isql and JISQL are similar in many respects, they are not identical. JISQL has a series of macro commands that are not available in isql, so JISQL scripts containing these commands will not run under isql. Although the SQL commands in isql end with a ; as a termination character, all JISQL commands terminate with a ;. Use the interactive SQL capability described here only if you want to bypass the JISQL environment.

Starting ISQL

To start isql, at the command line, type:

$SMBASE/jdb/bin/isql databaseName

where databaseName is the name of an existing database or @system if a database isn't available. The screen displays the following numeric prompt where you can enter any JDB statement: 1>

Creating a New Database

If this is your first JDB session, or if you want to create a new database, first start isql. Generally, on UNIX systems, it is located in $SMBASE/jdb/bin.

isql

At the prompt, logon to

JDB using

@system:

logon @system

Once you are connected, create a new database by typing:

create database databaseName;

databaseName must conform to the file naming conventions of the operating system.

To connect to the new database in order to create database tables and enter data, enter:

logon databaseName

Executing SQL Statements

You can execute any SQL statement available in JDB by ending each database statement with a ; as the command terminator. For example:

1> SELECT title_name, name, genre_code FROM titles;

A line without a trailing semi-colon is concatenated with the next line until a semicolon is reached. Therefore, one statement can span multiple lines.

Executing ISQL Statements

Table A-1 lists the commands available in isql. These commands allow you to edit a query, read in a query file, or execute an operating system command.

Note: These commands do not end with a semi-colon. Also, in order for these commands to be recognized, each must start on the first character of a command line.

Table A-1 Commands for isql

Command	Syntax	Description
clear	clear	Empties the input buffer. Command must start in the first column of a new line.
comment	#	Specifies that the line is a comment.
commit	commit	Saves additions and edits you make to the database since the last commit or rollback or since connecting to the database.
edit	edit	Starts an editing shell for entering statements. The editing program is determined by the environment variable EDITOR or SMEDITOR.
exit	exit	Exits isql.
list	list	Displays last executed command.
logon	logon databaseName logon @system	Connects to another database file or @system.
output	output filename	Redirects output to a file. If you specify output without a file name, it redirects output to the screen.
quit	quit	Quits isql.
read	read filename	Reads and executes the SQL commands in a text file. To execute more than one command, each command must end with a semi-colon (;).
rollback	rollback	Undoes all additions and edits made to the database since the last commit or rollback or since connecting to the database.
system	system commandName	Executes the named operating system command.

Exiting ISQL

To exit isql, type:

exit

An automatic commit is generated when you exit the isql session using either the quit or exit commands. Specify rollback if you do not want to keep your database changes.

Restore a transaction log

jdbroll databaseName journalName [journalName ...]

databaseName

Specifies the name of the database.

journalName

Specifies the name of the journal file(s).

The jdbroll utility allows you to update the database based on your log files.

When you logon to a database for the first time, JDB creates a journal file named j1databaseName. For example, a database having the videobiz would have a journal file named j1videobiz. The next time you log on, the information in the current file (j1videobiz) is copied to the file j0databaseName. If the file already exists, it is overwritten.

Translate an existing JDB database into its CREATE TABLE and INSERT statements

mksql databaseName

databaseName

Specifies the name of the database.

The mksql utility uses an existing JDB database to output a set of SQL statements from which the database could be rebuilt. For each table, it writes a CREATE TABLE statement, followed by a series of INSERT statements for the data in the table.

The following result set illustrates a portion of the output for the actors table in the videobiz database.

Read rows in a database table to/from text files

tbldata [-d delimiter] -x exportFile databaseName tableName

tbldata [-d delimiter] -i importFile databaseName tableName

-d delimiter

Specifies the column delimiter. The delimiter character might need to be enclosed in quotation marks. For example, to specify a space as the delimiter:

tbldata -d " " -x exportFile databaseName tableName

If no delimiter is specified, tbldata uses TAB as the delimiter.

-x

Logs onto the specified database and writes each row of the specified table to the specified text file.

-i

Logs onto the specified database and inserts each row of the specified text file into the specified table.

exportFile

Name of the text file where the data will be written.

importFile

Name of the text file containing the data to be inserted into JDB.

databaseName

Name of the JDB database.

tableName

Name of the database table.

The tbldata utility can be used two different ways:

• With the -x argument to convert rows in a database table to a text file.

• With the -i argument to insert rows into a database table from a text file.

With these options, you must specify both the database and the database table.

When using -i option, the database table must already exist. Also, the column values must be listed in the same order as the columns in the database table.

---

---