JDB SQL Reference

Chapter 5. Using JISQL

JISQL is a graphical tool you can use to:

Create a JDB database
Create database tables for a new or existing JDB database
Display table definitions for the current database
Write and execute interactive SQL scripts for use with your JDB databases

Starting JISQL

To start JISQL, do either of the following:

At the command line, type:

$SMBASE/util/jisql (under UNIX)

$SMBASE\util\jisql (under Windows)

Under Windows, choose the JDB ISQL icon.

The JDB ISQL window opens.

The JDB ISQL window provides an area into which you enter a SQL script. Menu options allow you to:

Execute SQL commands, either from the scripting area or from a file.
Select options for executing the SQL script.
Connect to a JDB database.
Create a JDB database.
Drop a JDB database.

To exit JISQL, choose FileExit.

JDB performs an automatic COMMIT when you leave a JISQL session. Issue a $ROLLBACK macro command if you do not want to save your database changes.

JDB Database Connections

Before you can create or view database tables or perform any other database operations, you must connect to the database. (If your SQL script includes a command to connect to the database, you need not connect as described here before executing the script.)

To connect to an existing database:

Choose DatabaseConnect. A file selection dialog box opens.
Specify the name of the database, and choose OK. The file selection dialog box closes, and you return to the JDB ISQL window.

To connect to a new database:

When you create a new database, you must connect to it before defining any of its tables.

In the Create Database window, specify that you want to automatically connect to the new database at the time it is created.

Refer to page 5-4 for instructions on creating and connecting to a new database.

To disconnect from the current database:

Choose DatabaseDisconnect.

JDB performs an automatic COMMIT when you close a database connection. Issue a $ROLLBACK macro command if you do not want to save your database changes.

You can be connected to only one database at a time. If you connect to a database while a previous connection is still current, JISQL automatically disconnects from the first database before connecting to the next one.

Executing Operating System Commands from JISQL

To execute an operating system command from JISQL:

Choose OptionsSystem Command. A dialog box opens with a field for you to enter the command.
Enter the desired system command, and choose OK. The command is executed; display output is platform-dependent.
Depending on the platform, if you are not returned to the JDB ISQL window when the command has finished executing, press any key.

Creating a New Database

From the JDB ISQL window, choose DatabaseCreate Database. The Create Database dialog opens.
Enter the name of the database you want to create. You can choose the Browse push button to view the names of existing files from a file browse dialog box. When you have finished with this dialog box, choose OK to return to the Create Database window.
(Optional) Select the Connect After Creation check box to automatically connect to this database after it is created in order to create tables and enter data.
Choose OK.
If you selected the Connect After Creation check box, the status line message confirms that you are connected to the database. If you did not select this check box, the message indicates only that the database was successfully created.

Creating Database Tables

Use the JISQL graphical interface to add tables to a newly created database or to an existing database.

To create a database table:

Connect to the applicable database.
Choose DatabaseCreate Table. The Create Table dialog box opens.
Enter the table name in the Table field
Define each column, one at a time, in the Column Definition Entry area. Refer to page 5-7 for a more detailed explanation of column definition.
Specify the keys for this table. Refer to page 5-8 for information on specifying primary, unique, and foreign keys.
(Optional) Choose the Preview SQL push button to display the SQL command that JISQL will generate to create the table, as it is currently defined.
When you have finished reviewing the SQL command, choose Done to resume in the Create Table window.
Choose OK to create the table you have just defined. A message is displayed confirming that the table has been created.

To populate the table, create and run a SQL script containing the applicable INSERT statements. For information on entering and running SQL scripts under JISQL, refer to page 5-18.

Defining Columns in a Database Table

The Column Definition Entry area of the Create Table window allows you to add, modify, or delete columns in the database table you are creating. In addition, the Create Table window provides push buttons that enable you to rearrange the columns in the table.

To add a new column to the table you are creating:

In the Column Definition Entry area, specify the column name and data type. For some data types, you must also specify the length.
Select the NOT NULL check box if null values are not to be permitted in this column.
Note: NULL values are not permitted in primary key columns.
Choose Add. Once the column is added, its position in the table is shown in the middle portion of the Column No. field. It is also added to the column summary for the table, displayed in the lower portion of the Create Table window.
Repeat the preceding steps for each column you want to define for the table.

To change or delete a column's definition:

You can modify or delete a column at any point prior to completing the table definition.

Specify the applicable column by doing either of the following:
- Select its entry in the summary area of the Create Table window.
- Choose the Column No. up/down indicators in the Column Definition Entry area; continue choosing the appropriate indicator until the desired column definition is displayed. The column number of the current column is shown in the middle of the Column No. field. Click on < to display the previous column, or > to display the next column. Click on |< to display the first column in the table, or >| to display the last.
  
  The definition for the specified column is shown in the Column Definition Entry area.
Change any of the column definition parameters as desired.
Note: You cannot remove NOT NULL from a primary key column.
Do either of the following, depending on the desired results:
- Choose Modify to change the column definition.
- Choose Delete to remove the column from the table.

To change the order of columns in the table:

Select a column in the summary area of the Create Table window. Choose the Move Up or Move Down push button to move it one place up or down. Continue until the column is in the desired location.

Defining Keys for a Database Table

Push buttons in the Create Table window allow you to define primary, unique, and foreign keys into the table. Refer to page 2-4 for an explanation of primary keys. Refer to page 2-6 for an explanation of foreign keys.

Define all columns that will be keys into the table. If you are defining foreign keys, the referenced table must have been created previously.
Choose the applicable push button: Primary Key, Unique Key, or Foreign Key. The corresponding key definition window opens.
Create, modify, or delete the applicable key definition. Refer to page 5-9 for instructions on using the Primary and Unique Key Definition windows. Refer to page 5-11 for instructions on using the Foreign Key Definition window.
When you are done with the key definition, choose OK. You return to the Create Table window.
If any column required for a key was not defined as NOT NULL when it was created, JISQL makes the necessary change to the column definition and displays an appropriate message. Acknowledge the message by choosing OK.
Continue creating, modifying, and deleting keys for the table as needed. You can create a new key or modify or delete an existing key at any point prior to completing the table definition.

Primary Key and Unique Keys

The Primary Key and Unique Key Definition windows are similar in appearance and function. Each consists of:

A text area showing the SQL definition generated for each key defined on the screen. When you want to modify or delete an existing key, select it from this area. As you create or modify a key definition, its SQL text area is updated to reflect changes.
Push buttons (Add New and Delete) to specify that you want to add a new key or delete an existing one.
A Select Columns area listing the table columns not used in the selected key.
A Key Columns area listing, in order, the table columns belonging to the selected key.
Push buttons (Add and Remove) to add a selected column in the Select Columns area to the key and to remove a selected column in the Key Columns area from the key.
Push buttons (Move Up and Move Down) to rearrange the order of columns in the selected key.

From the Create Table window, choose the Primary Key button to open the Primary Key Definition window, or choose Unique Key to open the Unique Key Definition window.

When you are finished working in the Primary Key or Unique Key Definition window, choose OK to save your changes and return to the Create Table window; or choose Cancel to return without saving your changes.

Once the applicable key definition window is open, you can add, modify, or delete keys as follows:

To add a new primary key or unique key:

On the Primary Key or Unique Key Definition window (as applicable), choose Add New. All the table columns are listed in the Select Columns area. If a primary key is already defined for the table, the Add New push button is not available, since only one primary key statement is permitted. Either delete the existing key or modify it.
For each column you want in the key, select the column from the Select Columns area and choose Add. The column name is removed from the Select Columns area and appears in the Key Columns area.
To change the order of a column in the key, select it in the Key Columns area and choose the Move Up or Move Down push button to move it to the desired location.

To modify an existing primary key or unique key:

On the Primary Key or Unique Key Definition window (as applicable), select the SQL definition corresponding to the key you want to modify. The Select Columns and Key Columns areas reflect the current definition of the key.
Select the column you want to add to the key from the Select Columns area and choose Add. The column name is removed from the Select Columns area and appears in the Key Columns area.
Select the column you want to remove from the key from the Key Columns area and choose Remove. The column name is removed from the Key Columns area and appears in the Select Columns area.
To change the order of a column in the key, select it in the Key Columns area and choose the Move Up or Move Down push button to move it to the desired location.

To delete an existing primary or unique key:

On the Primary Key or Unique Key Definition window (as applicable), select the SQL definition corresponding to the key you want to delete. The Select Columns and Key Columns areas reflect the current definition of the key.
Choose Delete. The SQL definition for this key is deleted from the text area, and the Select Columns and Key Columns areas are emptied.

Foreign Keys

The Foreign Key Definition window consists of:

A text area showing the SQL definition that will be generated for each key defined on the screen. When you want to modify or delete an existing key, you select it from this area. As you create or modify a key definition, the SQL text area is updated to reflect any changes.
Push buttons (Add New and Delete) to specify that you want to add a new key or delete an existing one.
A Select Columns area listing the table columns not used in the selected key.
A Select Table/Cols area with:
- An option menu for you to choose the referenced table.
- A listing of the columns in the chosen table not used in the selected key.
Push buttons (Add and Quick Match) to add a selected column in the Select Columns area and its foreign table column reference to the key. The Quick Match button allows you to reference all columns in the current table to identically-named primary key columns in the chosen table without having to explicitly choose any columns from the lists.
A Foreign Key area listing the table columns used in the selected key. Each column name in this area is lined up beside the corresponding column in the Referenced Key area.
A Referenced Key area listing each column in the chosen table that is referenced in the selected key. Each column name in this area is lined up beside the corresponding column in the Foreign Key area.
Push buttons (Move Up and Move Down) to re-arrange the order of columns in the selected key.
Push button (Remove) to remove a selected Foreign Key/Referenced Key column pair from the selected key.

From the Create Table window, choose the Foreign Key button to open the Foreign Key Definition window.

When you are finished working in the Foreign Key Definition window, choose OK to save your changes and return to the Create Table window; or choose Cancel to simply return without saving your changes.

When the Foreign Key Definition window is open, you can add, modify, or delete foreign keys as follows:

To add a new foreign key:

On the Foreign Key Definition window, choose Add New. All the table columns are listed in the Select Columns area.
Select the option menu in the Select Table/Cols area and choose the table to be referenced.
Once you have chosen the table, a list of columns in that table is displayed. Reference the columns for the key in one of the following ways:
- For each column in the foreign key, select the current table column from the Select Columns area and select the foreign column to be referenced from the Select Table/Cols area. Choose Add.
- Choose Quick Match to reference the selected columns in the current table with identically-named primary key columns in the chosen table.
  
  The column names are removed from the Select Columns area and the Select Table/Cols area; they appear in the Foreign Key and Referenced Key areas, respectively.
To change the order of a Foreign Key/Referenced Key column pair in the key, select either column in the pair and choose the Move Up or Move Down push button to move the pair to the desired location.

To modify an existing foreign key:

On the Foreign Key Definition window, select the SQL definition corresponding to the key you want to modify. The Select Columns, Select Table/Cols, Foreign Key, and Referenced Key areas reflect the current definition of the key.
For each column you want to add to the foreign key, do either of the following:
- Select the current table column from the Select Columns area and select the foreign column to be referenced from the Select Table/Cols area. Choose Add.
- Choose Quick Match to reference columns in the current table to identically-named primary key columns in the chosen table.
For each column pair you want to remove from the key, select either column in the pair and choose Remove. The column names are removed from the Foreign Key and Referenced Key areas and appear in the Select Columns and the Select Table/Cols areas, respectively.
To change the order of a column in the key, select it in the Key Columns area and choose the Move Up or Move Down push button to move it one place up or down. Continue until the column is in the desired location.

To delete an existing foreign key:

On the Foreign Key Definition window, select the SQL definition corresponding to the key you want to delete. The Select Columns, Select Table/Cols, Foreign Key, and Referenced Key areas reflect the current definition of the key.
Choose Delete. The SQL definition for this key is deleted from the text area, and the Select Columns, Select Table/Cols, Foreign Key, and Referenced Key areas are emptied.

Maintaining a Database

With JISQL, you can perform the following database maintenance functions without having to write SQL code:

Display the definition of any or all tables in the current database.
Drop a database.
Drop specified tables from a database.

Notes: To perform database maintenance operations involving the data itself, such as populating tables, viewing data, etc., you must explicitly write and execute the required SQL statements. Refer to page 5-18 for information on running SQL interactively using JISQL.

Displaying Database and Table Definitions

Connect to the database whose definitions you want to display.
Choose DatabaseDescribe. The Describe Table window opens, displaying a list of all the tables in the database.
Select the table whose definition you want to display. The column definitions and key information for this table are displayed. Continue in this way to display table definitions one at a time.
Choose Done when you are finished viewing table definitions for this database. You return to the JDB ISQL window.

Dropping Tables

Connect to the database from which you want to drop a table.
Choose DatabaseDrop Table. The Drop Table window opens.
Select the applicable table from the drop-down list for the Table Name field.
Choose OK. A message is displayed confirming that the table has been dropped.

Dropping a Database

Make sure that you are not connected to the database you want to drop.
Choose DatabaseDrop Database. The Drop Database window opens.
Enter the database name, or choose the Browse push button to select the database from a file selection dialog box.
With the database to be dropped specified in the Database Name field, choose OK. A message is displayed confirming that the database has been dropped.

Running SQL Interactively

Using JISQL, you can run SQL commands either by entering them into the onscreen scripting area or by specifying an ASCII file that contains the desired SQL script. In addition, when you create a SQL script in JISQL, and then save it to a file for future use.

Under JISQL, you can execute any SQL statement that is available in JDB. Refer to page 6-1 for a detailed description of the SQL commands that can be used with a JDB database.

Your SQL script can also contain JISQL macro commands. These macros simplify transaction processing and database maintenance. Refer to page 5-21 for a complete description of the JISQL macros.

JISQL runtime options enable you to control the execution and output of your SQL script. Refer to page 5-22 for a description of the available options and commands.

**Table 5-1 JISQL Macro Commands**
Command Syntax	Description
`$COMMIT`	Same as `DBMS COMMIT`. Commits a transaction. Data changes pending in the transaction are applied to the database. (JDB performs an automatic `COMMIT` when you leave a JISQL session or close a database connection.)
`$DESCRIBE` `table-name`	Displays a `CREATE TABLE` statement equivalent to the definition of the specified table. Example: `$DESCRIBE` titles; Output of this macro can be redirected to a file by choosing OptionsOutput to File.
`$DUMP` `table-name`	Displays a `CREATE TABLE` statement and an `INSERT` statement for each row in the table. Example: `$DUMP` tapes; Output of this macro can be re-directed to a file by choosing OptionsOutput to File.
`$LOGON` `database-name`	Connects to the specified database. Example: `$LOGON videobiz`; Since JISQL allows only one database connection at a time, this macro closes the previous connection, if there is one, before initiating a new connection.
`$ROLLBACK`	Same as `DBMS ROLLBACK`. Backs out a transaction. The database is restored to its state prior to the start of the pending transaction.

Executing SQL Scripts

Enter your SQL script into the scripting area. Refer to page 5-18 for instructions on entering and editing SQL scripts.
Connect to the database. (Refer to page 5-3 for instructions on connecting to a database.) Omit this step if your script contains the $LOGON macro to perform the connection.
Choose the desired execution and output options from the Options menu. All the following options are toggles; select as many as are applicable:
Position the starting marker on the line of your script where you want execution to begin. The starting marker appears to the right of the scroll bar for the scripting area. To move the starting marker, click in the space to the right of the scroll bar, lining up the mouse cursor with the SQL statement you want to execute next. Initially, the starting marker is beside the first line of the script.

Output and Execution Options

Choose one of the following execution commands to execute the ISQL script. The commands are available both as push buttons on the screen and as Run menu options:

Run to End—Start batch mode execution from the starting marker. Execution continues to the end of the script unless an error is encountered. The setting of the Continue After Error toggle determines whether execution is terminated at the point of the error or if it continues after the error message has been acknowledged.
Run to Query—Start batch mode execution from the starting marker. Execution stops after the first SQL SELECT statement or JISQL $DESCRIBE or $DUMP macro is encountered or at the end of the script. If an error is encountered, the setting of the Continue After Error toggle determines whether or not execution is terminated.
Single Step—Execute the current line of the script. (If the current line is blank or a comment, the next SQL statement or JISQL macro command encountered is executed.)

As execution proceeds, the script scrolls so that the current line is always in view. A bounce bar highlights the current line.

Caution: JDB does not enforce referential integrity, so an error is not returned if you insert duplicate primary keys. To prevent duplicate insertions of the same statement, you may need to move the starting marker before query execution, clearing the screen, or editing the current statement.

Once you initiate execution of the SQL script, JISQL remains in execution mode until the end of the script is encountered or until you terminate execution by choosing Reset.

To stop execution of a SQL script:

At any time, you can either:

Choose Reset.
Choose RunReset.

The Reset command stops execution of the SQL script, clears the output buffer, and resets the status of the JISQL utility so that you can edit the text of your script or restart execution.

Capturing and Displaying Query Results

When a SQL SELECT statement or JISQL $DESCRIBE or $DUMP macros are executed, the data retrieved can either be saved to an ASCII text file or displayed on the screen.

To save the output to a file:

Choose OptionsOutput to File if the Output to File toggle is not currently selected. A file selection dialog box opens.
Specify the name of the file for the output, and choose OK. It is recommended that you use a *.out extension in naming output files, since only files with this extension appear in the file selection dialog box.
Execute the script. All output generated is saved to the specified file.
Note: When output is saved to a file, select sets generated by SQL QUERY statements are only directed to the file and are not displayed on the screen. Output from $DESCRIBE and $DUMP macros, however, is saved in the file and displayed on the screen.

To display select sets on the screen:

Deselect the Output to File toggle.
Execute the script. When a SQL SELECT statement is executed, the data retrieved are displayed in the lower section of the JDB ISQL window. This area can be scrolled vertically and horizontally to view the select set.

Output from JISQL $DESCRIBE or $DUMP macros is also displayed on the screen.

Creating and Viewing the Log File

You can log and then review the following information about the execution of your SQL scripts:

Type of script execution chosen
Text of each SQL statement or JISQL macro and the line number, as it is encountered
Status of execution for each statement or macro, including any error messages generated
Start and end times of script execution

How to Begin a Log Session

Choose OptionsRecord in Log. On the file selection dialog box, specify the name of the log file.

If you specify the name of an existing file, data from the current log session overwrites the previous contents of the file. Within a log session, however, data are appended to the file, even if you execute more than one script.

To view the information stored in the log file for the current session:

Choose RunView Log File.

To end a log session:

Deselect OptionsRecord in Log.

Sample Log File

The text of log file SESSION.LOG follows:

ISQL FOR Panther, Copyright 1995-2016 Prolifics Inc.
Record Log <SESSION.LOG>: Friday April 18, 2016

<#14>:
*****  Run To End execution from line 14 of 18 at 05:54:34 *****

<#14>:   select * from ads;
         [ERROR] Table not found
********** Execution stop in line 14 of 18 at 05:54:36 ********** 
*****  Run To End execution from line 1 of 18 at 05:54:43 *****

<#4>:    $logon marketing;
         [SUCCESS]

<#6>:    create table ads(ad_num int NOT NULL, magazine char(20) NOT 
         NULL, date datetime NOT NULL, product char(20), cost float, 
         PRIMARY KEY (ad_num));
         [SUCCESS]  0 row(s).

<#9>:    insert into ads values (467, 'PC Week', '1995/04/23 9:00:00', 'HR System', 215.00);
         [SUCCESS]  1 row(s).

<#10>:   insert into ads values (468, 'DBMS Magazine', '1995/04/28 9:00:00', 'Accounting', 550.30);
         [SUCCESS]  1 row(s).

<#11>:   insert into ads values (469, 'Datamation', '1995/07/12 9:00:00', 'HR System', 312.99);
         [SUCCESS]  1 row(s).

<#12>:   select * from ads;
         [SUCCESS]  3 row(s).

<#14>:   $logon videobiz;
         [SUCCESS]

<#16>:   select * from titles where name like 'A%';
         [SUCCESS]  13 row(s).

********* Execution stop in line 19 of 18 at 05:55:03 *********
*****  New Script File  ***** 
***** Run To Query execution from line 1 of 6 at 05:57:22 *****

<#1>:    $logon pubs;
         [SUCCESS]

<#3>:    $describe titles;
         [SUCCESS]

********** Execution stop in line 4 of 6 at 05:57:29 **********
***** Single  Step execution from line 4 of 6 at 05:57:34 *****

<#4>:    select * from titles;
         [SUCCESS]  18 row(s).

********** Execution stop in line 5 of 6 at 05:57:40 **********