This chapter describes only those statements that are specific to FairCom DB SQL ISQL. See the c-treeSQL Reference Guide for detailed reference information on standard FairCom DB SQL statements that can be issued in other environments.
Starting Interactive SQL
Start FairCom DB SQL ISQL by issuing the ISQL command at the shell prompt. FairCom DB SQL invokes ISQL and displays the ISQL> prompt:
# isql sampledb
c-treeSQL Interactive Interpreter
ISQL>
Issue FairCom DB SQL statements at the ISQL> prompt and terminate them with a semicolon. You can continue statements on multiple lines. ISQL automatically prompts for continuation lines until you terminate the statement with a semicolon.
To execute host operating system commands from the FairCom DB SQL ISQL prompt, type HOST followed by the operating system command. After completion of the HOST statement, the ISQL> prompt returns. To execute FairCom DB SQL scripts from FairCom DB SQL ISQL, type ‘@’ followed by the name of the file containing FairCom DB SQL statements.
To exit the FairCom DB SQL ISQL, type EXIT or QUIT.
You can supply optional switches and arguments to the FairCom DB SQL ISQL command.
Syntax
isql [-s script_file] [-u user_name] [-a password] [-S BASIC | <cert_filename>] [connect_string]
Arguments
-s script_file
The name of a FairCom DB SQL script file that FairCom DB SQL executes when it invokes FairCom DB SQL ISQL.
Note: For Windows platforms, if the file name has a space, such as:
test script.sql
The file name must be enclosed in doubles quotes, such as:
isql -s “test script.sql” testdb
- -u user_name
The user name FairCom DB SQL uses to connect to the database specified in the connect_string. FairCom DB SQL verifies a user name against a corresponding password before it connects to the database. If omitted, the default value depends on the environment. (On Unix, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.) - -a password
The password c-treeSQL uses to connect to the database specified in the connect_string. c-treeSQL verifies the password against a corresponding user name before it connects to the database. If omitted, the default value depends on the environment. (On Unix, the value of the DH_PASSWD environment variable specifies the default password.) - -S -S - TLS/SSL options for secure connection
- BASIC - Basic TLS/SSL encryption
- <cert_filename> - Client cross-check certificate for TLS authentication
- connect_string
A string that specifies which database to connect to. The connect_string can be a simple database name or a complete connect string. For example, to connect to a local database named ‘myDatabase’, you would use the following syntax:
# isql -u ADMIN -a ADMIN myDatabase
To connect to a remote database named c-treeSQL, you would use the 6597@remotehost:database syntax as follows:
# isql -u ADMIN -a ADMIN 6597@hotdog.faircom.com:ctreeSQL
See the CONNECT statement in the c-treeSQL Reference Manual for details on how to specify a complete connect string. If omitted, the default value depends on the environment. (On Unix, the value of the DB_NAME environment variable specifies the default connect string.)
Local User Configuration
ISQL first looks for a file sql_conf in a ./lib subdirectory of your current working directory. This is the area where you call it, not where the executable file resides. A symbolic link is recommended . For example, place a symbolic link for isql in /usr/local/bin (Linux/Unix) and then each user can have an individual ./lib/sql_conf in their working directory.
sql_conf contains a series of line each with this syntax: <keyword>=<value>
Supported configurations:
ISQL_DEFAULT_ISOLATION values: 0,1,2,3
ISQL_AUTOCOMMIT values: Y, ON
ISQL_HISTORY values: number
ISQL_CMD_LINES values: number
ISQL_PAUSE values: Y, ON
ISQL_TIME values: Y, ON
ISQL_REPORT values: Y, ON
ISQL_LINESIZE values: number
ISQL_PAGESIZE values: number
ISQL_ECHO values: Y, ON
ISQL_OUTPUT values: N, OFF
Statement History Support
c-treeSQL ISQL provides statements to simplify the process of executing statements you have already typed. ISQL implements a history mechanism similar to the one found in the csh (C-shell) supported by UNIX.
The following table summarizes the ISQL statements that support retrieving, modifying, and rerunning previously entered statements.
| Statement | Summary |
|---|---|
| HISTORY | Displays a fixed number of statements (specified by the SET HISTORY statement) which have been entered before this statement, along with a statement number for each statement. Other statements take the statement number as an argument. See "HISTORY" for details. |
| RUN [ stmt_num ] | Displays and executes the current statement or specified statement in the history buffer. See "RUN" details. |
| LIST [ stmt_num ] | Displays the current statement or specified statement in the history buffer, and makes that statement the current statement by copying it to the end of the history list. See "LIST" for details. |
| EDIT [ stmt_num ] | Edits the current statement or specified statement in the history buffer, and makes the edited statement the current statement by copying it to the end of the history list. The environment variable EDITOR can be set to the editor of choice. See "EDIT" for details. |
| SAVE filename | Saves the current statement in the history buffer to the specified file, which can then be retrieved through the GET or START statements. See "SAVE" for details. |
| GET filename | Fetches the contents of the specified file, from the beginning of the file to the first semicolon, and appends it to the history buffer. The statement fetched by the GET can then be executed by using the RUN statement. See "GET" for details. |
| START filename [ argument ... ] | Fetches and executes a statement stored in the specified file. Unlike the GET statement, START executes the statement and accepts arguments that it substitutes for parameter references in the statement stored in the file. START also appends the statement to the history buffer. See "START" for details. |
Formatting Output of ISQL Queries
Formatting of database query results makes the output of a database query more presentable and understandable. The formatted output of an ISQL database query can be either displayed on the screen, written to a file, or spooled to a printer to produce a hard copy of the report.
ISQL includes several statements that provide simple formatting of c-treeSQL queries. The following table summarizes the ISQL query-formatting statements.
| Statement | Summary |
|---|---|
| DISPLAY | Displays text, variable values, and/or column values after the specified set of rows (called a break specification). See "DISPLAY" for details. |
| COMPUTE | Performs aggregate-function computations on column values for the specified set of rows, and assigns the results to a variable. DISPLAY statements can then refer to the variable to display its value. See "COMPUTE" for details. |
| BREAK | Specifies at what point ISQL processes associated DISPLAY and COMPUTE statements. BREAK statements can specify that processing occurs after a change in a column’s value, after each row, after each page, or at the end of a query. DISPLAY and COMPUTE statements have no effect until you issue a BREAK statement with the same break specification. See "BREAK" for details. |
| DEFINE | Defines a variable and assigns a text value to it. When DISPLAY statements refer to the variable, ISQL prints the value. See "DEFINE" for details. |
| COLUMN | Controls how ISQL displays a column’s values (the FORMAT clause) and/or specifies alternative column-heading text (the HEADING clause). See "COLUMN" for details. |
| TITLE | Specifies text and its positioning that ISQL displays before or after it processes a query. See "TITLE" for details. |
| CLEAR | Removes settings made by the previous DISPLAY, COMPUTE, COLUMN, BREAK, DEFINE, or TITLE statements. See "CLEAR" for details. |
| SET LINESIZE SET PAGESIZE SET REPORT SET ECHO |
Specifies various attributes that affect how ISQL displays queries and results. |
The rest of this section provides an extended example that illustrates how to use the statements together to improve formatting.
All the examples use the same ISQL query. The query retrieves data about outstanding customer orders. The query joins two tables, CUSTOMERS and ORDERS. The examples for the TABLE statement on "HOST or SH or !" show the columns and data types for these sample tables.
The following example shows the query and an excerpt of the results as ISQL displays them without the benefit of any query-formatting statements.
Example Unformatted Query Display from ISQL
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY
------------- -------------
ORDER_ID ORDER_VALUE
-------- -----------
Aerospace Enterprises Inc. Scottsdale
13 3000000
Aerospace Enterprises Inc. Scottsdale
14 1500000
Chemical Construction Inc. Joplin
11 3000000
Chemical Construction Inc. Joplin
12 7500000
Luxury Cars Inc. North Ridgeville
21 6000000
Luxury Cars Inc. North Ridgeville
20 5000000
Although this query retrieves the correct data, the formatting is inadequate:
- The display for each record wraps across two lines, primarily because of the column definitions for the text columns CUSTOMER_NAME and CUSTOMER_CITY. ISQL displays the full column width (50 characters for each column) even though the contents don’t use the entire width.
- It’s not clear that the values in the ORDER_VALUE column represent money amounts.
The next section shows how to use the COLUMN statement to address these formatting issues.
In addition, you can use DISPLAY, COMPUTE, and BREAK statements to present order summaries for each customer. "Summarizing Data with DISPLAY, COMPUTE, and BREAK Statements" shows how to do this. Finally, you can add text that ISQL displays at the beginning and end of query results with the TITLE statement, as described in "Adding Beginning and Concluding Titles with the TITLE Statement".
All of these statements are independent of the actual query. You do not need to change the query in any way to control how ISQL formats the results.
Formatting Column Display with the COLUMN Statement
You can specify the width of the display for character columns with the COLUMN statement’s “An” format string. Specify the format string in the FORMAT clause of the COLUMN statement. You need to issue separate COLUMN statements for each column whose width you want to control in this manner.
The following example shows COLUMN statements that limit the width of the CUSTOMER_NAME and CUSTOMER_CITY columns, and re-issues the original query to show how they affect the results.
Example Controlling Display Width of Character Columns
ISQL> COLUMN CUSTOMER_NAME FORMAT "A19"
ISQL> COLUMN CUSTOMER_CITY FORMAT "A19"
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 3000000
Aerospace Enterpris Scottsdale 14 1500000
Chemical Constructi Joplin 11 3000000
Chemical Constructi Joplin 12 7500000
Luxury Cars Inc. North Ridgeville 21 6000000
Luxury Cars Inc. North Ridgeville 20 5000000
Note that ISQL truncates display at the specified width. This means you should specify a value in the FORMAT clause that accommodates the widest column value that the query will display.
To improve the formatting of the ORDER_VALUE column, use the COLUMN statement’s numeric format strings. Issue another COLUMN statement, this one for ORDER_VALUE, and specify a format string using the “$”, “9”, and “,” format-string characters:
- The format-string character 9 indicates the width of the largest number. Specify enough 9 format-string characters to accommodate the largest value in the column.
- The format-string character $ directs ISQL to precede column values with a dollar sign.
- The comma (,) format-string character inserts a comma at the specified position in the display.
For the ORDER_VALUE column, the format string “$99,999,999.99” displays values in a format that clearly indicates that the values represent money. (For a complete list of the valid numeric format characters, see COLUMN.)
The following example shows the complete COLUMN statement that formats the ORDER_VALUE column. As shown by issuing the COLUMN statement without any arguments, this example retains the formatting from the COLUMN statements in the previous example.
Example Customizing Format of Numeric Column Displays
ISQL> column order_value format "$99,999,999.99"
ISQL> column; -- Show all the COLUMN statements now in effect:
column CUSTOMER_NAME format "A19" heading "CUSTOMER_NAME"
column CUSTOMER_CITY format "A19" heading "CUSTOMER_CITY"
column ORDER_VALUE format "$99,999,999.99" heading "ORDER_VALUE"
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
.
.
.
Summarizing Data with DISPLAY, COMPUTE, and BREAK Statements
Now that the query displays the rows it returns in a more acceptable format, you can use DISPLAY, COMPUTE, and BREAK statements to present order summaries for each customer.
All three statements rely on a break specification to indicate to ISQL when it should perform associated processing. There are four types of breaks you can specify:
- Column breaks are processed whenever the column associated with the break changes in value.
- Row breaks are processed after display of each row returned by the query.
- Page breaks are processed at the end of each page (as defined by the SET PAGESIZE statement).
- Report breaks are processed after display of all the rows returned by the query.
While DISPLAY and COMPUTE statements specify what actions ISQL takes for a particular type of break, the BREAK statement itself controls which type of break is currently in effect. A consequence of this behavior is that DISPLAY and COMPUTE statements don’t take effect until you issue the BREAK statement with the corresponding break specification.
Also, keep in mind that there can be only one type of break in effect at a time. This means you can format a particular query for a single type of break.
In our example, we are interested in a column break, since we want to display an order summary for each customer. In particular, we want to display the name of the customer along with the number and total value of orders for that customer. And, we want this summary displayed whenever the value in the CUSTOMER_NAME column changes. In other words, we need to specify a column break on the CUSTOMER_NAME column.
Approach this task in two steps. First, devise a DISPLAY statement to display the customer name and confirm that it is displaying correctly. Then, issue COMPUTE statements to calculate the statistics for each customer (namely, the count and sum of orders), and add DISPLAY statement to include those statistics. All of the DISPLAY, COMPUTE and BREAK statements have to specify the same break to get the desired results.
The following example shows the DISPLAY and BREAK statements that display the customer name. The COL clause in the DISPLAY statement indents the display slightly to emphasize the change in presentation.
The following example uses the column formatting from previous examples. Notice that the column formatting also affects DISPLAY statements that specify the same column.
Example Specifying Column Breaks and Values with DISPLAY
ISQL> display col 5 "Summary of activity for", customer_name on customer_name;
ISQL> break on customer_name
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Summary of activity for Aerospace Enterpris
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Summary of activity for Chemical Constructi
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
Summary of activity for Luxury Cars Inc.
.
.
.
Next, issue two COMPUTE statements to calculate the desired summary values.
COMPUTE statements specify a c-treeSQL aggregate function (AVG, MIN, MAX, SUM, or COUNT), a column name, a variable name, and a break specification. ISQL applies the aggregate function to all values of the column for the set of rows that corresponds to the break specification. It stores the result in the variable, which subsequent DISPLAY statements can use to display the result.
For this example, you need two separate compute statements. One calculates the number of orders (COUNT OF the ORDER_ID column) and the other calculates the total cost of orders (SUM OF the ORDER_VALUE column). Both specify the same break, namely, CUSTOMER_NAME. The following example shows the COMPUTE statements, which store the resulting value in the variables num_orders and tot_value.
The following example also issues two more DISPLAY statements to display the variable values. As before, the DISPLAY statements must specify the CUSTOMER_NAME break. They also indent their display further to indicate the relationship with the previously issued DISPLAY.
As before, this example uses the COLUMN and DISPLAY statements from previous examples. ISQL processes DISPLAY statements in the order they were issued. Use the DISPLAY statement, without any arguments, to show the current set of DISPLAY statements in effect. Also, in the query results, notice that the column formatting specified for the ORDER_VALUE column carries over to the tot_value variable, which is based on ORDER_VALUE.
Example Calculating Statistics on Column Breaks with COMPUTE
ISQL> compute count of order_id in num_orders on customer_name
ISQL> compute sum of order_value in tot_value on customer_name
ISQL> display col 10 "Total number of orders:", num_orders on customer_name;
ISQL> display col 10 "Total value of orders:", tot_value on customer_name;
ISQL> display -- See all the DISPLAY statements currently active:
display col 5 "Summary of activity for" ,customer_name on customer_name
display col 10 "Total number of orders:" ,num_orders on customer_name
display col 10 "Total value of orders:" ,tot_value on customer_name
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Summary of activity for Aerospace Enterpris
Total number of orders: 2
Total value of orders: $4,500,000.00
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Summary of activity for Chemical Constructi
Total number of orders: 2
Total value of orders: $10,500,000.00
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
Summary of activity for Luxury Cars Inc.
Total number of orders: 2
Total value of orders: $11,000,000.00
.
.
.
Adding Beginning and Concluding Titles with the TITLE Statement
You can add some finishing touches to the query display with the TITLE statement.
The TITLE statement lets you specify text that ISQL displays before (TITLE TOP) or after (TITLE BOTTOM) the query results.
The title can also be horizontally positioned by specifying the keywords LEFT, CENTER, or RIGHT; or by specifying the actual column number corresponding to the required positioning of the title. Use the SKIP clause to skip lines after a top title or before a bottom title.
The following example uses two TITLE statements to display a query header and footer.
Example Specifying a Query Header and Footer with TITLE
ISQL> TITLE TOP LEFT "Orders Summary" RIGHT "September 29, 1998" SKIP 2;
ISQL> SHOW LINESIZE -- RIGHT alignment of TITLE is relative to this value:
LINESIZE .................... : 78
ISQL> TITLE BOTTOM CENTER "End of Orders Summary Report" SKIP 2;
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
Orders Summary September 29, 1998
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Summary of activity for Aerospace Enterpris
Total number of orders: 2
Total value of orders: $4,500,000.00
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Summary of activity for Chemical Constructi
Total number of orders: 2
Total value of orders: $10,500,000.00
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
Summary of activity for Luxury Cars Inc.
Total number of orders: 2
Total value of orders: $11,000,000.00
.
.
.
Tower Construction Munising 8 $2,000,000.00
Tower Construction Munising 10 $6,000,000.00
Tower Construction Munising 9 $8,000,000.00
Summary of activity for Tower Construction
Total number of orders: 3
Total value of orders: $16,000,000.00
End of Orders Summary Report
23 records selected
ISQL>
The HELP and TABLE Statements
ISQL supports an on-line help facility that can be invoked by using the HELP statement. Typing HELP at the ISQL prompt will display a help file which will list the options accepted by the HELP statement. The various forms of the HELP statement are listed below:
- HELP - Displays the options that can be specified for HELP.
- HELP COMMANDS - Displays all the statements that ISQL accepts.
- HELP command_name - Displays help file corresponding to the specified statement.
TABLE is an ISQL statement that displays all the tables present in the database including any system tables. TABLE can be used also to display the description of a single table by explicitly giving the table name. Both forms of the TABLE statement are shown below:
TABLE;
TABLE table_name;
Transaction Support
A transaction is started with the execution of the first c-treeSQL statement. A transaction is committed using the COMMIT WORK statement and rolled back using the ROLLBACK WORK statement.
If the AUTOCOMMIT option is set to ON, then ISQL treats each c-treeSQL statement as a single transaction. This prevents the user from holding locks on the database for an extended period of time. This is very critical when the user is querying an on-line database in which a transaction processing application is executing in real time.
A set of c-treeSQL statements can be executed as part of a transaction and committed using the COMMIT WORK statement. This is shown below:
<SQL statement>
<SQL statement>
<SQL statement>
COMMIT WORK ;
Instead, a transaction can also be rolled back using the ROLLBACK WORK statement as shown:
<SQL statement>
<SQL statement>
<SQL statement>
ROLLBACK WORK ;
A c-treeSQL statement starting immediately after a COMMIT WORK or ROLLBACK WORK statement starts a new transaction.
Work Flow Control
ISQL provides support for controlling the flow of execution in ISQL by means of the IF_EXISTS / IF_NOT_EXISTS keyword.
IF_EXISTS / IF_NOT_EXISTS imposes conditions on the execution of a single SQL statement or ISQL command or a block of SQL statements and ISQL commands. The SQL statement/statement block following an IF_EXISTS keyword and its condition is executed if the condition is satisfied, i.e., if the SELECT query returns at least one record.
The optional ELSE keyword introduces an alternate SQL statement or a block of SQL statements that is executed when the IF_EXISTS condition is not satisfied i.e., if the SELECT query returns no record. IF_NOT_EXISTS is just the reverse of IF_EXISTS, i.e., the SQL statement/statement block following an IF_NOT_EXISTS keyword and its condition is executed if the condition is not satisfied, i.e., if the SELECT query returns no record.
IF_EXISTS / IF_NOT_EXISTS controls can be nested. There is no limit to the number of nested levels.
If the select query in the condition returns an error due to any reason, the error is returned and none of the conditional blocks is executed.