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.
ISQL Reference
This section provides reference material for statements specific to ISQL.
This section does not include descriptions of standard FairCom DB SQL statements or statements specific to embedded c-tree SQL. For details on the syntax and semantics of those other FairCom DB SQL statements, refer to the FairCom DB SQL Reference Manual.
@ (Execute)
Syntax
@filename
Description
Executes the SQL statements stored in the specified SQL script file. The statements specified in the file are not added to the history buffer.
Arguments
filename
The name of the script file.
Notes
The GET, START, and @ (execute) statements are similar in that they all read SQL script files. Both GET and START read an SQL script file and append the first statement in it to the history buffer. However, the START statement also executes the script statement and accepts arguments that it substitutes for parameter references in the script statement. The @ (execute) statement, on the other hand, executes all the statements in an SQL script file but does not add any of the statements to the history buffer. The @ statement does not support argument substitution.
Example
The following example shows a simple ISQL script file.
Example ISQL script
connect to demodb;
set echo on ;
create table stores (item_no integer, item_name char(20));
insert into stores values (1001,chassis);
insert into stores values (1002,chips);
select * from stores where item_no > 1001;
set echo off ;
To execute the above statements stored in the file cmdfile, enter:
ISQL> @cmdfile
BREAK
Syntax
BREAK [ ON break_spec [ SKIP n ] ] ;
break_spec::
{ column_name [ , ... ] | ROW | PAGE | REPORT }
Description
The BREAK statement specifies at what point ISQL processes associated DISPLAY and COMPUTE statements. DISPLAY and COMPUTE statements have no effect until you issue a BREAK statement with the same break specification.
A break can be specified on any of the following events:
- Change in the value of a column
- Selection of each row
- End of a page
- End of a report
Only one BREAK statement can be in effect at a time. When a new BREAK statement is entered, it replaces the previous BREAK statement. The BREAK statement can specify one or more columns on which the break can occur.
The BREAK statement without any clauses displays the currently-set break, if any.
Arguments
break_spec
The events that cause an SQL query to be interrupted and the execution of the associated COMPUTE and DISPLAY statements. break_spec can be any of the following values:
| column_name | Causes a break when the value of the column specified by column_name changes. |
| ROW | Causes a break on every row selected by a SELECT statement. |
| PAGE | Causes a break at the end of each page. The end of a page is specified in the SET PAGESIZE statement. See “SET” for details on the SET statement. |
| REPORT | Causes a break at the end of a report or query. |
SKIP n
The optional SKIP clause can be used to skip the specified number of lines when the specified break occurs and before processing of any associated DISPLAY statements.
Examples
The following examples illustrate how various break settings and corresponding DISPLAY statements affect the display of the same query.
ISQL> break
no break specified
ISQL> select customer_name from customers; -- Default display
CUSTOMER_NAME
-------------
Sports Cars Inc.
Mighty Bulldozer Inc.
Ship Shapers Inc.
Tower Construction Inc.
Chemical Construction Inc.
Aerospace Enterprises Inc.
Medical Enterprises Inc.
Rail Builders Inc.
Luxury Cars Inc.
Office Furniture Inc.
10 records selected
ISQL> -- Set DISPLAY values for different breaks:
ISQL> display "Break on change in value of customer_name!" on customer_name;
ISQL> display "Break on every row!" on row;
ISQL> display "Break on page (page size set to 2 lines)" on page;
ISQL> display "Break on end of report!" on report;
ISQL> set pagesize 2
ISQL> break on customer_name
ISQL> select customer_name from customers;
CUSTOMER_NAME
-------------
Sports Cars Inc.
Break on change in value of customer_name!
Mighty Bulldozer Inc.
Break on change in value of customer_name!
Ship Shapers Inc.
Break on change in value of customer_name!
.
.
.
ISQL> break on row
ISQL> select customer_name from customers;
CUSTOMER_NAME
-------------
Sports Cars Inc.
Break on every row!
Mighty Bulldozer Inc.
Break on every row!
Ship Shapers Inc.
Break on every row!
.
.
.
ISQL> break on page
ISQL> select customer_name from customers;
CUSTOMER_NAME
-------------
Break on page (page size set to 2 lines)
CUSTOMER_NAME
-------------
Sports Cars Inc.
Break on page (page size set to 2 lines)
CUSTOMER_NAME
-------------
Mighty Bulldozer Inc.
Break on page (page size set to 2 lines)
.
.
.
ISQL> break on report
ISQL> select customer_name from customers;
CUSTOMER_NAME
-------------
Sports Cars Inc.
Mighty Bulldozer Inc.
Ship Shapers Inc.
Tower Construction Inc.
Chemical Construction Inc.
Aerospace Enterprises Inc.
Medical Enterprises Inc.
Rail Builders Inc.
Luxury Cars Inc.
Office Furniture Inc.
Break on end of report!
10 records selected
ISQL>
CLEAR
Syntax
CLEAR option ;
option::
HISTORY
| BREAK
| COLUMN
| COMPUTE
| DISPLAY
| TITLE
Description
The CLEAR statement removes settings made by the ISQL statement corresponding to option.
Argument
option
Which ISQL statement’s settings to clear:
- CLEAR HISTORY - Clears the ISQL statement history buffer.
- CLEAR BREAK - Clears the break set by the BREAK statement.
- CLEAR COLUMN - Clears formatting options set by any COLUMN statements in effect.
- CLEAR COMPUTE - Clears all the options set by the COMPUTE statement.
- CLEAR DISPLAY - Clears the displays set by the DISPLAY statement.
- CLEAR TITLE - Clears the titles set by the TITLE statement.
Examples
The following example illustrates clearing the DISPLAY and BREAK settings.
ISQL> DISPLAY -- See the DISPLAY settings currently in effect:
display "Break on change in value of customeer_name!" on customer_name
display "Break on every row!" on row
display "Break on page (page size set to 2 lines)" on page
display "Break on end of report!" on report
ISQL> CLEAR DISPLAY
ISQL> DISPLAY
No display specified.
ISQL> BREAK
break on report skip 0
ISQL> CLEAR BREAK
ISQL> BREAK
no break specified
ISQL>
COLUMN
Syntax
COLUMN [ column_name
[ FORMAT " format_string " ] | [ HEADING " heading_text " ] ] ;
Description
The COLUMN statement controls how ISQL displays a column’s values (the FORMAT clause) and specifies alternative column-heading text (the HEADING clause).
The COLUMN statement without any arguments displays the current column specifications.
Arguments
column_name
The name of the column affected by the COLUMN statement. If the COLUMN statement includes column_name but omits both the FORMAT and HEADING clauses, ISQL clears any formatting and headings in effect for that column. The formatting specified for column_name also applies to DISPLAY statements that specify the same column.
FORMAT " format_string "
Specifies a quoted string that formats the display of column values. Valid values for format strings depend on the data type of the column.
| Character | The only valid format string for character data types is of the form “An”, where n specifies the width of the column display. The A character must be upper case. |
| Numeric | Table: Numeric Format Strings for the COLUMN Statement on page 3-18 shows valid format strings for numeric data types. |
| Date-time | Table: Date-Time Format Strings for the COLUMN Statement on page 3-18 shows valid format strings for date-time data types. The format strings consist of keywords that SQL interprets and replaces with formatted values. Any other character in the format string are displayed as literals. The format strings are case sensitive. For instance, SQL replaces ‘DAY’ with all uppercase letters, but follows the case of ‘Day’. Note that the SQL scalar function TO_CHAR offers comparable functionality and is not limited to SQL statements issued within ISQL. See the c-treeSQL Reference Manual for details on TO_CHAR. |
COLUMN format strings also affect display values in DISPLAY statements that specify the same column or a COMPUTE value based on the column.
HEADING “heading_text ”
Specifies an alternative heading for the column display. The default is the column name.
Format String Details
Numeric Format Strings for the COLUMN Statement
| Character | Example | Description |
|---|---|---|
| 9 | 99999 | Number of 9s specifies width. If the column value is too large to display in the specified format, ISQL displays # characters in place of the value. |
| 0 | 09999 | Display leading zeroes. |
| $ | $9999 | Prefix the display with ‘$’. |
| B | B9999 | Display blanks if the value is zero. |
| , | 99,999 | Display a comma at position specified by the comma. |
| . | 99,999.99 | Display a decimal point at the specified position. |
| MI | 99999MI | Display ‘-’ after a negative value. |
| PR | 99999PR | Display negative values between ‘<’ and ‘>’. |
Date-Time Format Strings for the COLUMN Statement
| Character | Description |
|---|---|
| CC | The century as a 2-digit number. |
| YYYY | The year as a 4-digit number. |
| YYY | The last 3 digits of the year. |
| YY | The last 2 digits of the year. |
| Y | The last digit of the year. |
| Y,YYY | The year as a 4-digit number with a comma after the first digit. |
| Q | The quarter of the year as 1-digit number (with values 1, 2, 3, or 4). |
| MM | The month value as 2-digit number (in the range 01-12). |
| MONTH | The name of the month as a string of 9 characters (‘JANUARY’ to ‘DECEMBER ’) |
| MON | The first 3 characters of the name of the month (in the range ‘JAN’ to ‘DEC’). |
| WW | The week of year as a 2-digit number (in the range 01-52). |
| W | The week of month as a 1-digit number (in the range 1-5). |
| DDD | The day of year as a 3-digit number (in the range 001-365). |
| DD | The day of month as a 2-digit number (in the range 01-31). |
| D | The day of week as a 1-digit number (in the range 1-7, 1 for Sunday and 7 for Saturday). |
| DAY | The day of week as a 9 character string (in the range ‘SUNDAY’ to ‘SATURDAY’. |
| DY | The day of week as a 3 character string (in the range ‘SUN’ to ‘SAT’). |
| J | The Julian day (number of days since DEC 31, 1899) as an 8 digit number. |
| TH | When added to a format keyword that results in a number, this format keyword (‘TH’) is replaced by the string ‘ST’, ‘ND’, ‘RD’ or ‘TH’ depending on the last digit of the number. |
| AMPM | The string ‘AM’ or ‘PM’ depending on whether time corresponds to forenoon or afternoon. |
| A.M.P.M. | The string ‘A.M.’ or ‘P.M.’ depending on whether time corresponds to forenoon or afternoon. |
| HH12 | The hour value as a 2-digit number (in the range 00 to 11). |
| HHHH24 | The hour value as a 2-digit number (in the range 00 to 23). |
| MI | The minute value as a 2-digit number (in the range 00 to 59). |
| SS | The seconds value as a 2-digit number (in the range 00 to 59). |
| SSSSS | The seconds from midnight as a 5-digit number (in the range 00000 to 86399). |
| MLS | The milliseconds value as a 3-digit number (in the range 000 to 999). |
Examples
The following examples are based on a table, ORDERS, with columns defined as follows:
ISQL> table orders
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
order_id NOT NULL INT 4
customer_id INT 4
steel_type CHAR 20
order_info CHAR 200
order_weight INT 4
order_value INT 4
order_state CHAR 20
ISQL displays the order_info column, at 200 characters, with lots of blank space preceding the values:
ISQL> select order_info from orders where order_value < 1000000
ORDER_INFO
----------
Solid Rods 5 in. diameter
1 record selected
You can improve formatting by using the character format string to limit the width of the display:
ISQL> column ORDER_INFO format "A28" heading "Details"
ISQL> select order_info from orders where order_value < 1000000;
ORDER_INFO
----------
Solid Rods 5 in. diameter
1 record selected
ISQL> -- Illustrate some options with numeric format strings.
ISQL> -- No column formatting:
ISQL> select order_value from orders where order_value < 1000000;
ORDER_VALUE
-----------
110000
1 record selected
ISQL> -- Format to display as money, and use different heading:
ISQL> column order_value format "$999,999,999.99" heading "Amount"
ISQL> select order_value from orders where order_value < 1000000;
AMOUNT
------
$110,000.00
1 record selected
The following examples use the single-value system table, SYSCALCTABLE, and the sysdate scalar function, to illustrate some date-time formatting. The sysdate function returns today’s date.
ISQL> select sysdate from syscalctable; -- No formatting
SYSDATE
-------
05/07/1998
ISQL> column sysdate format "Day"
ISQL> select sysdate from syscalctable
SYSDATE
-------
Thursday
1 record selected
ISQL> column sysdate format "Month"
ISQL> select sysdate from syscalctable
SYSDATE
-------
May
1 record selected
ISQL> column sysdate format "DDth"
ISQL> select sysdate from syscalctable
SYSDATE
-------
7th
1 record selected
Note: If the select-list of a query includes column titles, they override formatting specified in COLUMN statements for those columns. The following example illustrates this behavior.
ISQL> select fld from syscalctable; -- No formatting
FLD
---
100
1 record selected
ISQL> column fld heading "column title" -- Specify heading in COLUMN statement
ISQL> select fld from syscalctable;
COLUMN TITLE
------------
100
1 record selected
ISQL> select fld "new title" from syscalctable; -- Specify title in select list
NEW TITLE
---------
100
1 record selected
COMPUTE
Syntax
COMPUTE
[ { AVG | MAX | MIN | SUM | COUNT }
OF column_name
IN variable_name
ON break_spec ] ;
break_spec::
{ column_name | ROW | PAGE | REPORT }
Description
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.
COMPUTE statements have no effect until you issue a BREAK statement with the same break_spec.
Issuing the COMPUTE statement without any arguments displays the currently-set COMPUTE specifications, if any.
Arguments
AVG | MAX | MIN | SUM | COUNT
The function to apply to values of column_name. The functions AVG, MAX, MIN, and SUM can be used only when the column is numeric. The function COUNT can be used for any column type.
column_name
The column whose value is to be computed. The column specified in column_name must also be included in the select list of the query. If column_name is not also included in the select list, it has no effect.
variable_name
Specifies the name of the variable where the computed value is stored. ISQL issues an implicit DEFINE statement for variable_name and assigns the variable a value of zero. During query processing, the value of variable_name changes as ISQL encounters the specified breaks.
break_spec
Specifies the set of rows after which ISQL processes the COMPUTE statement. A COMPUTE statement has no effect until you issue a corresponding BREAK statement. See the description of the BREAK statement in "BREAK" for details.
Examples
The following example computes the number of items ordered by each customer.
ISQL> break on customer_name
ISQL> display col 5 "Number of orders placed by", customer_name, "=", n_ord on customer_name
ISQL> compute count of order_id in n_ord on customer_name;
ISQL> select c.customer_name, o.order_id from customers c, orders o
where o.customer_id = c.customer_id;
CUSTOMER_NAME ORDER_ID
------------- --------
Sports Cars Inc. 1
Sports Cars Inc. 2
Number of orders placed by Sports Cars Inc.
= 2
Mighty Bulldozer Inc. 3
Mighty Bulldozer Inc. 4
Number of orders placed by Mighty Bulldozer Inc.
= 2
.
.
.
DEFINE
Syntax
DEFINE [ variable_name = value ] ;
Description
The DEFINE statement defines a variable and assigns an ASCII string value to it. When you refer to the defined variable in DISPLAY statements, ISQL prints the value.
The DEFINE statement is useful if you have scripts with many DISPLAY statements. You can change a single DEFINE statement to change the value in all of the DISPLAY statements that refer to the variable.
Issuing the DEFINE statement without any arguments displays any currently-defined variables, including those defined through the COMPUTE statement.
Arguments
variable_name
Specifies the name by which the variable can be referred to.
value
The ASCII string that is assigned to the variable. Enclose value in quotes if it contains any non-numeric values.
Example
The following example defines a variable called nestate and assigns the value “NH” to it.
ISQL> DEFINE nestate = "NH" ;
DISPLAY
Syntax
DISPLAY { [ col_position ] display_value } [ , ... ] ON break_spec ;
col_position::
{ COL column_number | @ column_name }
display_value::
{ "text string" | variable | column_name }
break_spec::
{ column_name | ROW | PAGE | REPORT }
Description
The DISPLAY statement displays the specified text, variable value, and/or column value after the set of rows specified by break_spec. DISPLAY statements have no effect until you issue a BREAK statement with the same break_spec.
Issuing the DISPLAY statement without any arguments displays the currently set DISPLAY specifications, if any.
Arguments
col_position
An optional argument that specifies the horizontal positioning of the associated display value. There are two forms for the argument:
| COL column_number | Directly specifies the column position of the display value as an integer(1 specifies column 1, 2 specifies column 2, and so on.). |
| @column_name | Names a column in the select list of the SQL query. ISQL aligns the display value with the specified column. |
If the DISPLAY statement omits col_position, ISQL positions the display value at column 1.
display_value
The value to display when the associated break occurs:
| “text string” | If the display value is a text string, ISQL simply displays the text string. |
| variable | If the display value is a variable, ISQL displays the value of the variable when the associated break occurs. The variable argument refers to a variable named in a COMPUTE or DEFINE statement that executes before the query. If variable is undefined, ISQL ignores it. |
| column_name | If the display value is a column name, ISQL displays the value of the column when the associated break occurs. The column specified in column_name must also be included in the select list of the query. If column_name is not also included in the select list, it has no effect. If a COLUMN statement specifies a format for the same column, the formatting also affects the DISPLAY statement. |
break_spec
Specifies the set of rows after which ISQL processes the DISPLAY statement. A DISPLAY statement has no effect until you issue a corresponding BREAK statement. See the description of the BREAK statement in "BREAK" for details of break specifications.
Examples
The following set of examples compute the number of orders placed by each customer and displays the message Number of orders placed by, followed by the customer name and the count of orders.
ISQL> break on customer_name
ISQL> display col 5 "Number of orders placed by", customer_name, "=", n_ord on customer_name
ISQL> compute count of order_id in n_ord on customer_name;
ISQL> select c.customer_name, o.order_id from customers c, orders o
where o.customer_id = c.customer_id;
CUSTOMER_NAME ORDER_ID
------------- --------
Sports Cars Inc. 1
Sports Cars Inc. 2
Number of orders placed by Sports Cars Inc.
= 2
Mighty Bulldozer Inc. 3
Mighty Bulldozer Inc. 4
Number of orders placed by Mighty Bulldozer Inc.
= 2
Ship Shapers Inc. 5
Ship Shapers Inc. 6
Ship Shapers Inc. 7
Number of orders placed by Ship Shapers Inc.
= 3
Tower Construction Inc. 8
Tower Construction Inc. 9
Tower Construction Inc. 10
Number of orders placed by Tower Construction Inc.
= 3
If the select-list of a query includes column titles, they override DISPLAY statements that include variable or column_name display values for those columns:
ISQL> display col 5 "test display. Sum of fld is", tmp on fld;
ISQL> compute sum of fld in tmp on fld;
ISQL> break on fld
ISQL> select fld from syscalctable; -- This works:
FLD
---
100
test display. Sum of fld is 100
1 record selected
ISQL> select fld "column title" from syscalctable; -- DISPLAY is disabled:
COLUMN TITLE
------------
100
1 record selected
EDIT
Syntax
E[DIT] [stmt_num];
Description
The EDIT statement invokes a text editor to edit the specified statement from the statement history buffer. If the statement number is not specified, the last statement in the history buffer is edited. When you exit the editor, ISQL writes the buffer contents as the last statement in the history buffer.
By default, ISQL invokes the vi editor on UNIX and the MS-DOS editor on Windows. You can change the default by setting the EDITOR environment variable:
- On UNIX, set the environment variable at the operating system command level:
setenv EDITOR /usr/local/bin/gmacs
- On Windows, set the environment variable in the initialization file DHSQL.INI in the (%WINDIR%) directory:
EDITOR = c:\msoffice\winword.exe
Examples
The following example uses the ! (shell) command to show the currently-set value of the EDITOR environment variable in the UNIX environment (it shows that it is set to invoke the GNU emacs editor). Then, the example uses the EDIT command to read in the fifth statement in the history buffer into an editing buffer.
ISQL> ! printenv EDITOR
/usr/local/bin/gmacs
ISQL> EDIT 5;
The following example edits the last statement in the history buffer:
ISQL> select * from systable; -- bad table name!
*
error(-20005): Table/View/Synonym not found
ISQL> EDIT -- invoke an editor to correct the error
.
.
.
ISQL> list -- corrected statement is now the current statement:
select * from systables
ISQL> run -- run the corrected statement
.
.
.
EXIT or QUIT
Syntax
EXIT
Description
The EXIT statement terminates the ISQL session.
Related Statements
QUIT and EXIT are synonymous. There is no difference in their effect.
GET
Syntax
G[ET] filename;
Description
The GET statement reads the first SQL statement stored in the specified script file.
Arguments
filename
The name of the script file. ISQL reads the file until it encounters a semicolon ( ; ) statement terminator. It appends the statement to the history buffer as the most-recent statement.
Notes
- Execute the statement read by GET using the RUN statement.
- The GET, START, and @ (execute) statements are similar in that they all read SQL script files. Both GET and START read an SQL script file and append the first statement in it to the history buffer. However, the START statement also executes the script statement and accepts arguments that it substitutes for parameter references in the script statement. The @ (execute) statement, on the other hand, executes all the statements in an SQL script file but does not add any of the statements to the history buffer. The @ statement does not support argument substitution.
Example
Once you refine a query to return the results you need, you can store it in an SQL script file. For example, the file query.sql contains a complex query that joins several tables in a sample database.
Use the GET and RUN statements to read and execute the first statement in query.sql:
ISQL> GET query.sql
SELECT customers.customer_name,
orders.order_info,
orders.order_state,
lot_staging.lot_location,
lot_staging.start_date
FROM customers,
orders,
lots,
lot_staging
WHERE ( customers.customer_id = orders.customer_id ) and
( lots.lot_id = lot_staging.lot_id ) and
( orders.order_id = lots.order_id ) and
( ( customers.customer_name = 'Ship Shapers Inc.' ) AND
( lot_staging.start_date is not NULL ) AND
( lot_staging.end_date is NULL ) )
ISQL> RUN
SELECT customers.customer_name,
orders.order_info,
orders.order_state,
lot_staging.lot_location,
lot_staging.start_date
FROM customers,
orders,
lots,
lot_staging
WHERE ( customers.customer_id = orders.customer_id ) and
( lots.lot_id = lot_staging.lot_id ) and
( orders.order_id = lots.order_id ) and
( ( customers.customer_name = 'Ship Shapers Inc.' ) AND
( lot_staging.start_date is not NULL ) AND
( lot_staging.end_date is NULL ) )
CUSTOMER_NAME ORDER_INFO
------------- ----------
ORDER_STATE LOT_LOCATION START_DATE
----------- ------------ ----------
Ship Shapers Inc. I Beams Size 10
Processing Hot Rolling 12/26/1994
1 record selected
HELP
Syntax
HE[LP] {COMMANDS|CLAUSES};
HE[LP] ;
Description
The HELP statement displays the help information for the specified statement or clause.
Notes
- HELP COMMANDS displays a list of statements for which help text is available.
- HELP CLAUSES display a list of clauses for which help text is available.
- HELP statement with no clauses display the help text for the HELP statement.
Example
The following HELP statement will give a brief description of the SELECT statement.
ISQL> HELP SELECT;
HISTORY
Syntax
HI[STORY];
Description
The HISTORY statement lists the statements in the statement history buffer, along with an identifying number.
Notes
- ISQL maintains a list of statements typed by the user in the statement history buffer. The SET HISTORY statement sets the size of the history buffer.
- The statements LIST, EDIT, HISTORY, and RUN are not added to the history buffer.
- Use HISTORY to obtain the statement number for a particular statement in the history buffer that you want to execute. Then, use the RUN statement with the statement number as an argument to execute that statement. Or, use LIST statement with the statement number as an argument to make the statement the current statement, which can then be executed using RUN without an argument.
Example
The following example illustrates usage of the HISTORY statement.
ISQL> HISTORY -- Display statements in the history buffer
1 start start_ex.sql Ship
2 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
3 select tbl from systables where tbltype = 'T'
ISQL> RUN 2 -- Run the query corresponding to statement 2
SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
CUSTOMER_NAME
-------------
Ship Shapers Inc.
1 record selected
ISQL> HI -- In addition to executing, statement 2 is now the current statement
1 start start_ex.sql Ship
2 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
3 select tbl from systables where tbltype = 'T'
4 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
ISQL> LIST 3 - Display statement 3 and copy it to the end of the history list
select tbl from systables where tbltype = 'T'
ISQL> history -- Statement 3 is now also the current statement
1 start start_ex.sql Ship
2 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
3 select tbl from systables where tbltype = 'T'
4 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
5 select tbl from systables where tbltype = 'T'
HOST or SH or !
Syntax
{ HOST | SH | ! } [host_command];
Description
The HOST (or SH or !) statement executes a host operating system command without terminating the current ISQL session.
Arguments
host_command
The operating system command to execute. If host_command is not specified, ISQL spawns a sub-shell from which you can issue multiple operating system commands. Use the exit command to return to the ISQL> prompt.
Example
Consider a file in the local directory named query.sql. It contains a complex query that joins several tables in a sample database. From within ISQL You can display the contents of the file with the ISQL ! (shell) statement:
ISQL> -- Check the syntax for the UNIX 'more' command:
ISQL> host more
Usage: more [-dfln] [+linenum | +/pattern] name1 name2 ...
ISQL> -- Use 'more' to display the query.sql script file:
ISQL> ! more query.sql
SELECT customers.customer_name,
orders.order_info,
orders.order_state,
lot_staging.lot_location,
lot_staging.start_date
FROM customers,
orders,
lots,
lot_staging
WHERE( customers.customer_id = orders.customer_id ) and
( lots.lot_id = lot_staging.lot_id ) and
( orders.order_id = lots.order_id ) and
( ( customers.customer_name = 'Ship Shapers Inc.' ) AND
( lot_staging.start_date is not NULL ) AND
( lot_staging.end_date is NULL ) ) ;
ISQL> -- Spawn a subshell process to issue multiple OS commands:
ISQL> sh
.
.
.
IF [ NOT ] EXISTS
Syntax
{IF [NOT] EXISTS} | {IF_EXISTS} | {IF_NOT_EXISTS} (<query>)
{BEGIN
<list of statements>
END} | { <statement> }
[ELSE
{BEGIN
<list of statements>
END} | { <statement> } ]
Conditional expressions can be nested. The following example checks for the existence of a table and creates it if it does not exist. The nested condition updates an existing table if it is required.
Example
IF NOT EXISTS (SELECT * FROM systables where tbl = 'custmast')
BEGIN
CREATE TABLE custmast (name CHAR(10), custid INTEGER IDENTITY (1,1), balance MONEY, modtime TIMESTAMP);
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM syscolumns where tbl='custmast' AND col='modtime')
BEGIN
ALTER TABLE custmast ADD (modtime TIMESTAMP);
END
ELSE
BEGIN
SELECT * FROM custmast;
END
END
COMMIT WORK;
The following keywords have been added to the ISQL lexicon to support conditional expressions:
- IF
- IF_EXISTS
- IF_NOT_EXISTS
- BEGIN
- ELSE
- END
Statements that use these keywords as identifiers should enclose them in double quotes.
INDEXES
Syntax
I[NDEXES] [ tablename ] ;
The INDEX statement with no argument displays a list of all the user indexes in the database that are owned by the current user.
With the tablename argument, the INDEX statement displays a brief description of the indexes defined on the specified table.
The index list is sorted by table name and index name in that order.
Example
The following example uses the index list command to display lists of indexes defined on user tables.
ISQL> INDEXES
INDEXNAME TABLENAME ID OWNER TYPE ORDER COMPRESSION METHOD
--------- --------- --- ----- ---- ----- ------------ ------
sys_001_000000041 dept 33 admin U A N B
dno_idx01 emp 35 admin D A N B
sys_001_000000042 emp 34 admin U A N B
ISQL> INDEXES emp ;
INDEXNAME TABLENAME ID OWNER TYPE ORDER COMPRESSION METHOD
--------- --------- --- ----- ---- ----- ------------ ------
deptno_idx01 emp 36 admin D A N B
name_idx01 emp 34 admin D A N B
name_idx02 emp 35 admin D A N B
sys_001_000000041 emp 33 admin U A N B
LIST
Syntax
L[IST] [ stmt_num ];
Description
The LIST statement displays the statement with the specified statement number from the statement history buffer and makes it the current statement by adding it to the end of the history list.
If LIST omits stmt_num, it displays the last statement in the history buffer.
Example
The following example uses the LIST statement to display the 5th statement in the history buffer (select CUSTOMER_NAME from customers) and copy it to the end of the history list. It then executes the now-current statement using the RUN statement:
ISQL> history
1 title
2 title top "fred" skip 5
3 title
4 help title
5 select customer_name from customers
6 display "Display on page break!"
7 display "Test page break display" on page
8 select customer_name from customers
9 select customer_name from customers
10 clear title
ISQL> list 5
select customer_name from customers
ISQL> run
select customer_name from customers
CUSTOMER_NAME
-------------
Sports Cars Inc.
Mighty Bulldozer Inc.
Ship Shapers Inc.
Tower Construction Inc.
Chemical Construction Inc.
Aerospace Enterprises Inc.
Medical Enterprises Inc.
Rail Builders Inc.
Luxury Cars Inc.
Office Furniture Inc.
10 records selected
ISQL>
QUIT or EXIT
Syntax
Q[UIT]
Description
The QUIT statement terminates the current ISQL session.
Related Statements
QUIT and EXIT are synonymous. There is no difference in their effect.
RUN
Syntax
R[UN] [stmt_num];
Description
The RUN statement executes the statement with the specified statement number from the statement history buffer and makes it the current statement by adding it to the end of the history list.
If RUN omits stmt_num, it runs the current statement.
Example
The following example runs the fifth statement in the history buffer.
ISQL> HISTORY
1 title
2 title top "TEST TITLE" skip 5
3 title
4 help title
5 select customer_name from customers
6 display "Display on page break!"
7 display "Test page break display" on page
ISQL> RUN 5
select customer_name from customers
CUSTOMER_NAME
-------------
Sports Cars Inc.
Mighty Bulldozer Inc.
Ship Shapers Inc.
Tower Construction Inc.
Chemical Construction Inc.
Aerospace Enterprises Inc.
Medical Enterprises Inc.
Rail Builders Inc.
Luxury Cars Inc.
Office Furniture Inc.
10 records selected
ISQL>
SAVE
Syntax
S[AVE] filename;
Description
The SAVE statement saves the last statement in the history buffer in filename. The GET and START statements can then be used to read and execute the statement from a file.
If filename does not exist, ISQL creates it. If filename does exist, ISQL overwrites it with the contents of the last statement in the history buffer.
Example
ISQL> ! more test.SQL
test.sql: No such file or directory
ISQL> select customer_name, customer_city from customers;
CUSTOMER_NAME CUSTOMER_CITY
------------- -------------
Sports Cars Inc. Sewickley
Mighty Bulldozer Inc. Baldwin Park
Ship Shapers Inc. South Miami
Tower Construction Inc. Munising
Chemical Construction Inc. Joplin
Aerospace Enterprises Inc. Scottsdale
Medical Enterprises Inc. Denver
Rail Builders Inc. Claymont
.
.
.
ISQL> save test.sql
ISQL> ! ls -al test.sql
-rw-r--r-- 1 ADMIN 51 May 1 18:21 test.sql
ISQL> ! more test.sql
select customer_name, customer_city from customers
ISQL>
SET
Syntax
SET set_option ;
set_option ::
HISTORY number_statements
| PAGESIZE number_lines
| LINESIZE number_characters
| COMMAND LINES number_lines
| REPORT { ON | OFF }
| ECHO { ON | OFF }
| PAUSE { ON | OFF }
| TIME { ON | OFF }
| DISPLAY COST { ON | OFF }
| AUTOCOMMIT { ON | OFF }
| TRANSACTION ISOLATION LEVEL isolation_level
| CONNECTION { database_name | DEFAULT }
| SET INDEX CHECK { ON | OFF }
Description
The SET statement changes various characteristics of an interactive c-treeSQL session.
Arguments
HISTORY
Sets the number of statements that ISQL will store in the history buffer. The default is 1 statement and the maximum is 250 statements.
PAGESIZE number_lines
Sets the number of lines per page. The default is 24 lines. After each number_lines lines, ISQL executes any DISPLAY ON PAGE statements in effect and re-displays column headings. The PAGESIZE setting affects both standard output and the file opened through the SPOOL statement.
LINESIZE
Sets the number of characters per line. The default is 80 characters. The LINESIZE setting affects both standard output and the file opened through the SPOOL statement.
COMMAND LINES
Sets the number of lines to be displayed. The default is 1.
REPORT ON | OFF
SET REPORT ON copies only the results of SQL statements to the file opened by the SPOOL filename ON statement. SET REPORT OFF copies both the SQL statement and the results to the file. SET REPORT OFF is the default.
ECHO ON | OFF
SET ECHO ON displays SQL statements as well as results to standard output. SET ECHO OFF suppresses the display of c-treeSQL statements, so that only results are displayed. SET ECHO ON is the default.
PAUSE ON | OFF
SET PAUSE ON prompts the user after displaying one page of results on the screen. SET PAUSE ON is the default.
TIME ON | OFF
SET TIME ON displays the time taken for executing a database query statement. SET TIME OFF disables the display and is the default.
DISPLAY COST ON | OFF
SET DISPLAY COST ON displays the values the c-treeSQL optimizer uses to calculate the least-costly query strategy for a particular c-treeSQL statement.
The UPDATE STATISTICS statement updates the values displayed by SET DISPLAY COST ON. SET DISPLAY COST OFF suppresses the display and is the default.
AUTOCOMMIT ON | OFF
SET AUTOCOMMIT ON commits changes and starts a new transaction immediately after each SQL statement is executed. SET AUTOCOMMIT OFF is the default. SET AUTOCOMMIT OFF requires that you end transactions explicitly with a COMMIT or ROLLBACK WORK statement.
Note: Be careful using the automatic commit logic when working with cursors. When automatic commit is enabled, the logic will execute a commit operation after each database access, which will close any open cursor. If you receive an error ‑20039 (Open for non-select statement) then most likely your cursor has been closed.
TRANSACTION ISOLATION LEVEL isolation_level
Specifies the isolation level. Isolation levels specify the degree to which one transaction can modify data or database objects being used by another concurrent transaction. The default is 3. See the SET TRANSACTION ISOLATION LEVEL statement in the c-treeSQL Reference Manual for more information on isolation levels.
CONNECTION { database_name | DEFAULT}
Sets the active connection to database_name or to the default connection. See the description of the CONNECT statement in the c-treeSQL Reference Manual for details on connections.
SET INDEX CHECK { ON | OFF}
Turns "index strict mode" on or off (defaults to off).
When "on", queries fail with error -21047 if they take advantage of an index marked as “bad” during import because it has either 1) a null key exclusion activated, or 2) one or more segments with incompatible case (e.g., has UREGSEG enabled).
When "off," the query executes as usual. The idea is to provide a simple mechanism to instruct the SQL engine whether to consider the use of an index that is not "compatible" with SQL due to either null key or UREGSEG settings. Set to “on” to instruct the SQL engine to ignore indexes that have either (or both) of these afflictions, thereby eliminating the possibility of incorrect query results.
Notes
SET REPORT and SET ECHO are similar:
- SET REPORT affects the SPOOL file only, and ON suppresses statement display
- SET ECHO affects standard output only, and OFF suppresses statement display
Other statements control other characteristics of an interactive SQL session:
- The editor invoked by the EDIT statement is controlled by the value of the environment variable EDITOR.
- The file to which interactive c-treeSQL writes output is controlled by the SPOOL filename ON statement.
Examples
ISQL> -- Illustrate PAGESIZE
ISQL> DISPLAY "Here's a page break!" ON PAGE
ISQL> SET PAGESIZE 4
ISQL> BREAK ON PAGE;
ISQL> SELECT TBL FROM SYSTABLES;
TBL
---
sys_chk_constrs
Here's a page break!
TBL
---
sys_chkcol_usage
sys_keycol_usage
Here's a page break!
.
.
.
ISQL> SET DISPLAY COST ON
ISQL> -- Select from the one-record SYSCALCTABLE table:
ISQL> SELECT * FROM SYSCALCTABLE;
Estimated Cost Values :
-----------------------
COST : 8080
CARDINALITY : 200
TREE SIZE : 3072
FLD
---
100
SHOW
Syntax
SHOW [ show_option | SPOOL ] ;
show_option ::
HISTORY
| PAGESIZE
| LINESIZE
| COMMAND LINES
| REPORT
| ECHO
| PAUSE
| TIME
| DISPLAY COST
| AUTOCOMMIT
| TRANSACTION ISOLATION LEVEL
| CONNECTION
Description
The SHOW statement displays the values of the various settings controlled by corresponding SET and SPOOL statements. If the SHOW statement omits show_option, it displays all the ISQL settings currently in effect.
See "SET", "SPOOL", and "EDIT" for details on the settings displayed by the SHOW statement.
Example
ISQL> SHOW
ISQL ENVIRONMENT
____________________
EDITOR ..................... : vi
HISTORY buffer size ........ : 50 PAUSE ..................... : ON
COMMAND LINES .............. : 10 TIMEing command execution.. : OFF
SPOOLing ................... : ON LINESIZE .................. : 78
REPORTing Facility ......... : ON PAGESIZE .................. : 72
Spool File ................. : spool_file
AUTOCOMMIT ................. : OFF ECHO commands ............. : ON
TRANSACTION ISOLATION LEVEL. : 0 (Snapshot)
DATABASE CONNECTIONS
_______________________
DATABASE CONNECTION NAME IS DEFAULT ? IS CURRENT ?
-------- --------------- ------------ -----------
salesdb conn_1 No Yes
SPOOL
Syntax
SPOOL filename [ON] ;
SPOOL OFF ;
SPOOL OUT ;
Description
The SPOOL statement writes output from interactive SQL statements to the specified file.
Arguments
filename ON
Opens the file specified by filename and writes the displayed output into that file. The filename cannot include punctuation marks such as a period (.) or comma (,).
OFF
Closes the file opened by the SPOOL ON statement.
OUT
Closes the file opened by the SPOOL ON statement and prints the file. The SPOOL OUT statement passes the file to the system utility statement pr and the output is piped to lpr.
Example
To record the displayed output into the file called STK, enter:
ISQL> SPOOL STK ON ;
ISQL> SELECT * FROM customer ;
ISQL> SPOOL OFF ;
START
Syntax
ST[ART] filename [ argument ] [ ... ] ;
Description
The START statement executes the first SQL statement stored in the specified script file.
Arguments
filename
The name of the script file. ISQL reads the file until it encounters a semicolon ( ; ) statement terminator.
argument ...
ISQL substitutes the value of argument for parameter references in the script. Parameter references in a script are of the form &n, where n is an integer. ISQL replaces all occurrences of &1 in the script with the first argument value, all occurrences of &2 with the second argument value, and so on. The value of argument must not contain spaces or other special characters.
Notes
- In addition to executing the first statement in the script file, the START statement appends the statement (after any argument substitution) to the history buffer.
- The GET, START, and @ (execute) statements are similar in that they all read SQL script files. Both GET and START read an SQL script file and append the first statement in it to the history buffer. However, the START statement also executes the script statement and accepts arguments that it substitutes for parameter references in the script statement. The @ (execute) statement, on the other hand, executes all the statements in an SQL script file but does not add any of the statements to the history buffer. The @ statement does not support argument substitution.
Example
ISQL> -- Nothing in history buffer:
ISQL> history
History queue is empty.
ISQL> -- Display a script file with the ! shell statement. The script's SQL ISQL> -- statement uses the LIKE predicate to retrieve customer names
ISQL> -- beginning with the string passed as an argument in a START statement:
ISQL> ! more start_ex.sql
SELECT customer_name FROM customers
WHERE customer_name LIKE '&1%';
ISQL> -- Use the START statement to execute the SQL statement in the script
ISQL> -- start_ex.sql. Supply the value 'Ship' as a substitution argument:
ISQL> START start_ex.sql Ship
CUSTOMER_NAME
-------------
Ship Shapers Inc.
1 record selected
ISQL> -- ISQL puts the script statement, after argument substitution,
ISQL> -- in the history buffer:
ISQL> history
1 ! more start_ex.sql
3 START start_ex.sql Ship
4 SELECT customer_name FROM customers
WHERE customer_name LIKE 'Ship%'
TABLE
Syntax
T[ABLE] [ tablename ] ;
Description
The TABLE statement with no argument displays a list of all the user tables in the database that are owned by the current user.
With the tablename argument, the TABLE statement displays a brief description of the columns in the specified table.
Examples
You can use the TABLE statement to see the structure of system tables. Unless you are logged in as the c-treeSQL database administrator (the user ADMIN, by default), you need to qualify the system table name with the administrator user name, as in the following example:
ISQL> table ADMIN.systables
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
id NOT NULL INT 4
tbl NOT NULL VARCHAR 32
creator NOT NULL VARCHAR 32
owner NOT NULL VARCHAR 32
tbltype NOT NULL VARCHAR 1
tblpctfree NOT NULL INT 4
segid NOT NULL INT 4
has_pcnstrs NOT NULL VARCHAR 1
has_fcnstrs NOT NULL VARCHAR 1
has_ccnstrs NOT NULL VARCHAR 1
has_ucnstrs NOT NULL VARCHAR 1
tbl_status NOT NULL VARCHAR 1
rssid NOT NULL INT 4
The following example uses the TABLE command to detail the structure of the tables used in examples throughout this chapter.
ISQL> table - List the sample tables
TABLENAME
---------
customers
lot_staging
lots
orders
quality
samples
ISQL> table customers
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
customer_id NOT NULL INT 4
customer_name CHAR 50
customer_street CHAR 100
customer_city CHAR 50
customer_state CHAR 10
customer_zip CHAR 5
ISQL> table orders
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
order_id NOT NULL INT 4
customer_id INT 4
steel_type CHAR 20
order_info CHAR 200
order_weight INT 4
order_value INT 4
order_state CHAR 20
ISQL> table lots
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
lot_id NOT NULL INT 4
order_id NOT NULL INT 4
lot_units INT 4
lot_info CHAR 200
ISQL> table lot_staging
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
lot_id INT 4
lot_location CHAR 20
start_date DATE
end_date DATE
issues CHAR 200
ISQL> table quality
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
lot_id NOT NULL INT 4
purity DOUBLE 8
p_deviation DOUBLE 8
strength DOUBLE 8
s_deviation DOUBLE 8
comments CHAR 200
ISQL> table samples
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
lot_id INT 4
samples INT 4
comments CHAR 200
ISQL>
TITLE
Syntax
TITLE [
[ TOP | BOTTOM ]
[ [ LEFT | CENTER | RIGHT | COL n ] " text " ] [ ... ]
[ SKIP n ]
] ;
Description
The TITLE statement specifies text that ISQL displays either before or after it processes a query. TITLE with no arguments displays the titles currently set, if any.
Arguments
TOP | BOTTOM
Specifies whether the title is to be printed at the top or bottom of the page. The default is TOP.
LEFT | CENTER | RIGHT | COL n
Specifies the horizontal alignment of the title text: LEFT aligns the text to the left of the display; CENTER centers the text; RIGHT aligns the text to the right (with the right-most character in the column specified by the SET LINESIZE statement). COL n displays the text starting at the specified column (specifying COL 0 is the same as LEFT).
The default is LEFT.
" text "
The text to be displayed.
SKIP n
Skips n lines after a TOP title is printed and before a BOTTOM title is printed. By default, ISQL does not skip any lines.
Examples
The following example shows the effect of specifying a top title without a bottom title, then both a top and bottom title.
ISQL> TITLE "fred"
ISQL> select * from syscalctable;
fred
FLD
---
100
1 record selected
ISQL> TITLE BOTTOM "flintstone"
ISQL> select * from syscalctable;
fred
FLD
---
100
flintstone
1 record selected
The TITLE statement can specify separate positions for different text in the same title:
ISQL> CLEAR TITLE
ISQL> TITLE TOP LEFT "Align on the left!" CENTER "Centered text" RIGHT "Right aligned text!"
ISQL> select * from syscalctable;
Align on the left! Centered text Right aligned text!
FLD
---
100
1 record selected
FairCom DB SQL Built-in Stored Procedures
A variety of built in procedures are available for administrative database tasks. These can be called in the same manner as user defined procedures:
call fc_get_fcproclist();
A result set, if returned, is handled in the standard caller manner.
These stored procedures are useful in iSQL and other SQL clients because they return valuable information about the FairCom SQL engine and perform operations that cannot be done with SQL statements.
Categorized list
Locks
fc_set_blockinglock controls when iSQL and other SQL clients block or return an error when they encounter a record with a read lock or a pending UPDATE or DELETE operation.
Databases
fc_add_db creates a new database. The JSON DB “createDatabase” action is more capable.
fc_get_dblist lists existing databases. The JSON DB “listDatabases” action is more capable.
Users
fc_set_impersonation uses the FairCom task ID to impersonate another SQL connection.
fc_get_taskid returns the FairCom task ID of the SQL connection.
fc_create_user creates a new user. The JSON Admin “createAccount” action is more capable.
fc_get_userlist lists existing users. The JSON Admin “listAccounts” action is more capable.
Tables
fc_set_file_tran_state removes or adds a table to the transaction logs.
fc_check_file_tran_state returns how a table uses the transaction logs.
fc_set_rowid_visible adds a table’s ROWID column (if it exists) to its list of columns.
fc_ptadmin_num purges, reuses, archives, activates, adds, or rebuilds a table partition.
fc_get_partbounds returns the first and last active partition numbers for a partitioned table.
fc_purge_db removes tables from the dictionary when they do not have a data file on disk.
fc_get_hosttablename returns the physical file name for a multi-record type (MRT) table.
SQL Debugging
fc_set_debug sets the debug level in the SQL log.
fc_set_nodename assigns a name to the connection for SQL logging.
Server settings
fc_get_server_version returns the current version of the FairCom server.
fc_set_sysconfig changes system settings dynamically.
fc_get_sysconfig returns system settings.
fc_set_min_card sets the minimum cardinality number for identity columns.
fc_get_min_card returns the minimum cardinality number for identity columns.
fc_set_priority sets the Windows OS priority of the FairCom server.
fc_get_priority returns the Windows OS priority of the FairCom server.
fc_set_selectivity sets the server selectivity.
fc_get_selectivity returns the server selectivity.
Server statistics
fc_get_sqlstats returns SQL performance statistics.
fc_get_transtats returns SQL transaction statistics.
fc_get_cachestats returns server cache statistics.
fc_get_iostats returns IO statistics.
fc_get_memstats returns memory statistics.
fc_get_connstats returns connected user counts.
fc_get_filestats returns open data file counts.
fc_get_isamstats returns ISAM engine statistics.
fc_get_lockstats returns lock statistics.
fc_get_replstats returns replication statistics.
Stored procedures
fc_get_fcproclist returns the list of built-in stored procedures.
fc_add_db( )
Adds an existing FairCom DB SQL database to the list of registered databases.
Parameters (1)
dbname VARCHAR(1024)
Example
call fc_add_db('ctreeSQL');
fc_check_file_tran_state( )
Returns the current FairCom DB transaction mode for the specified table.
Result Set Fields (2)
owner VARCHAR(64)
table VARCHAR(64)
Returned modes:
- 0 No transaction control.
- 1 Transaction control without recoverability. (ctPREIMG)
- 2 Transaction control and recoverability. (ctTRNLOG)
Example
call fc_check_file_tran_state();
owner table_name
----- ----------
admin custmast
admin custorder
admin ordritem
admin itemmast
New Topic (2)
Creates a new FairCom DB SQL user role with the same name as the account.
When RBAC is inactive, fc_create_account is replaced by fc_create_user().
Parameters (2)
name VARCHAR(32)
pass VARCHAR(32)
create_role BIT
Example
call fc_create_account('accounting', 'unbreakable',1);
fc_create_db( )
Creates a new FairCom DB SQL database named dbname.
Parameters (1)
dbname VARCHAR(1024)
Example
call fc_create_db('ctreeSQL');
See also
In V13.0.3 and beyond, if you want to control the case sensitivity of your new table, use fc_createdb() instead.
fc_createdb( )
In V13.0.3 onwards, creates a new FairCom DB SQL database named dbname.
Parameters (2)
dbname VARCHAR(1024)
casesensitive BIT
Example
call fc_createdb('ctreeSQL', 1);
See also
For versions prior to V13.0.3, see fc_create_db() (cannot control case sensitivity).
fc_create_user( )
Creates a new FairCom DB SQL user username with initial password pass.
The username is a user ID that is limited to a 32-byte ASCIIZ string (a null-terminated ASCII string), which implies 31 bytes are available for the User ID.
The ID is limited to alphanumeric characters, hyphens, underscores, and periods. A hyphen character (-) cannot be used as the first character.
The server ignores the case of the ASCII characters. Therefore, the server considers TEST_1.FairCom-4 to be the same as test_1.faircom-4.
When RBAC is active, fc_create_user() is replace by fc_create_account().
Parameters (2)
username VARCHAR(32)
pass VARCHAR(32)
Example
call fc_create_user('accounting', 'unbreakable');
fc_get_cachestats()
Returns FairCom DB SQL current cache subsystem statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_cachestats();
DESCRIPTION VALUE
----------- -----
data buffer requests 9860
data buffer hits 9772
index buffer requests 883
index buffer hits 817
index buffer pages in use 15
max index buffers in use 26
available data cache pages 1253
data cache pages in use 57
max data cache pages in use 57
# of index buffers on upd list (tran) 0
# of index buffers on upd list 1
# of data caches on upd list (tran) 0
# of data caches on upd list 0
avail data file special cache pages 626
actual data file special cache pages 0
maximum data file special cache pages 0
16 records returned
fc_get_connstats()
Returns FairCom DB SQL current connection statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_connstats();
DESCRIPTION VALUE
----------- -----
Number of connected users 1
Maximum connected users 1
Threads in use total (ctaddwork) 1
Max threads in use (ctaddwork) 2
Num threads active in foreground 1
5 records returned
fc_get_dblist( )
Returns a resultset listing registered database names.
Result Set Fields (1)
dbname VARCHAR(1024)
Example
call fc_get_dblist();
dbname
------
demoSQL
ctreeSQL
testing
fc_get_fcproclist( )
Returns a listing of available built-in stored procedures.
Result Set Fields (1)
proclist VARCHAR(1024)
Example
call fc_get_fcproclist();
proclist
--------
fc_create_user
fc_get_userlist
fc_get_fcproclist
fc_set_debug
fc_set_priority
fc_get_priority
fc_set_blockinglock
fc_get_dblist
fc_create_db
fc_add_db
fc_set_min_card
fc_get_min_card
fc_set_selectivity
fc_get_selectivity
fc_get_taskid
fc_check_file_tran_state
fc_get_server_version
fc_set_impersonation
fc_set_file_tran_state
fc_get_filestats()
Returns FairCom DB SQL current physical file control statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_filestats();
DESCRIPTION VALUE
----------- -----
Number of open physical files 8
Number of open logical files 25
Number of c-tree FCBs in use 34
Max physical files opened 8
Max logical files opened 35
Max c-tree FCBs in use 53
Number of virtual files open 0
Number available file control blocks 1001
8 records returned
fc_get_hosttablename()
Retrieves the host table name of a Multi-Record Type (MRT) table. (V12.0.1)
Parameters (1)
Table ID as an INTEGER for which it will retrieve the MRT host name and UID. The returned result set consists of exactly 1 row containing four columns:
- The table UID or the host UID in case of multi-record type (MRT) table
- The c-treeDB table name
- The filesystem path where the table is located
- The filesystem name of the table
Example
call fc_get_hosttablename(1163);
ID NAME PATH PHYSICAL_NAME
-- ---- ---- ------------
1163 syssequences .\ctreeSQL.dbs\ syssequences
fc_get_iostats()
Returns FairCom DB SQL current I/O statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_iostats();
Description Value
----------- -----
number of read operations 1,182
bytes read 1,626,850
number of write operations 33
bytes written 158,080
number of comm read operations 0
comm bytes read 0
number of comm write operations 0
comm bytes written 0
number of log write operations 2
bytes written to log file 16,384
number of log read operations 5
bytes read from log file 17,416
number of log extension operations 0
log file extension bytes 0
14 records returned
fc_get_isamstats()
Returns FairCom DB SQL current ISAM statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_isamstats();
DESCRIPTION VALUE
----------- -----
ISAM record add count 0
ISAM record delete count 0
ISAM record update count 0
ISAM record read count 124
4 records returned
fc_get_lockstats()
Returns FairCom DB SQL current lock statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_lockstats();
DESCRIPTION VALUE
----------- -----
count of lock attempts 249
subcount of hdr lock attempts 2
count of locks denied 0
count of locks blocked 0
subcount of header blocks 0
count of dead locks 0
count of locks freed 249
count of blocks released 0
current count of locks held 0
current count of blocked requests 0
cumulative lock wait time^ 0
cumulative lock wait count 0
maximum elapsed lock wait time^ 0
net locks over unlocks 0
max net locks over unlocks 1
15 records returned
fc_get_memstats()
Returns FairCom DB SQL current memory statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_memstats();
DESCRIPTION VALUE
----------- -----
SQL current aggregate summary 2508841
SQL memory highwater 2908540
Net SQL memory allocations 1880
System memory highwater mark 51370239
System aggregate sum 50968434
5 records returned
fc_get_min_card( )
Returns the minimum cardinality value currently in use by the FairCom DB SQL database engine.
Result Set Fields (1)
value INTEGER
Example
call fc_get_min_card()
value
-----
0
fc_get_priority( )
Returns the FairCom DB SQL client thread priority.
Note: This feature is supported only on the Windows operating system.
Result Set Fields (1)
priority TINYINT
Example
call fc_get_priority()
priority
--------
0
fc_get_partbounds()
fc_get_partbounds(owner, table_name)
Returns the first and last active partition numbers for a partitioned table. This is useful to determine the oldest (Firstactprt) partition to purge, for example. You must be connected to the database that contains the table.
Parameters (2)
------------------
owner VARCHAR(64)
table_name VARCHAR(64)
Result Set Fields (2)
------------------
Firstactprt -- "First Active Partition number"
Lastactprt -- "Last Active Partition number"
fc_get_replstats()
Returns FairCom DB SQL current replication statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_replstats();
DESCRIPTION VALUE
----------- -----
Total successful operations 0
Successful apply operations 1339713297
Successful transaction begins 7233801
Successful transaction aborts 6832485
Successful transaction commits 27
Successful transaction success 19
Successful user-defined entries 30
Successful record adds 20
Successful record updates 25769806224
Successful record deletes 644245094407
Successful physical file opens 42949672970
Successful physical file closes 1134704589799574
Total failed operations 4294967297
Failed apply operations 1
Failed transaction begins 644245094400
Failed transaction aborts 0
Failed transaction commits 0
Failed transaction success 0
Failed record adds 70
Failed record updates 0
Failed record deletes 0
Failed physical file opens 0
Failed physical file closes 0
Log number of current scan pos 0
Offset of current scan pos 0
current status of replication agent 0
current c-tree API function 0
server connection status 0
28 records returned
fc_get_selectivity( )
Returns the selectivity. false if the selectivity is off; true when on.
Result Set Fields (1)
active BIT
Example
call fc_get_selectivity()
active
------
true
fc_get_server_version( )
Returns the version information for the FairCom DB SQL database engine.
Result Set Fields (6)
VERSION VARCHAR(40)
VER_MAJOR INTEGER
VER_MINOR INTEGER
VER_REVISION INTEGER
BUILD_DATE VARCHAR(14)
VER_MINI INTEGER
Example 1: Using ISQL
call fc_get_server_version();
VERSION VER_MAJOR VER_MINOR VER_REVISION BUILD_DATE VER_MINI
------- --------- --------- ------------ ---------- --------
10.0.1.60592(Build-121011) 10 0 60592 121011 1
Example 2: Using DSQL
The following code can be added to DSQL Tutorial 1 to call this function:
printf("\tGet server version info...\n");
pCTSQLCURSOR hCursor = NULL;
CTSQLCHAR CTVersion[64];
CTSQLCHAR CTMajor[64];
rc = ctsqlPrepare(hCmd, "CALL fc_get_server_version()");
if (rc != CTSQLRET_OK)
Handle_Error("fc_get_server_version() Prepare");
rc = ctsqlExecute(hCmd, &hCursor);
if (rc != CTSQLRET_OK)
Handle_Error("fc_get_server_version() Execute");
/* fetch and display each individual record */
while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
{
ctsqlGetChar(hCursor, 0, CTVersion);
ctsqlGetChar(hCursor, 1, CTMajor);
printf("\n\t\t%s - %s\n", CTMajor, CTVersion);
}
ctsqlFreeCursor(hCursor);
The resulting output will appear as follows (notice the version number displayed at the end of INIT):
fc_get_sqlstats()
Returns FairCom DB SQL current SQL subsystem statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_sqlstats();
DESCRIPTION VALUE
----------- -----
SQL memory highwater 2908764
current aggregate sum 2507391
current net SQL memory allocations 1884
dynamic statement cache requests 59
dynamic statement cache hits 44
static statement cache requests 36
static statement cache hits 26
current dynamic stmt cache entries 13
highest dynamic stmt cache entries 14
maximum dynamic stmt cache entries 150
current static stmt cache entries 10
highest static stmt cache entries 10
maximum static stmt cache entries 150
current number of dynamic caches 1
current number of static caches 1
current number of local caches 1
current local stmt cache entries 0
highest local stmt cache entries 0
maximum local stmt cache entries 150
local statement cache requests 0
local statement cache hits 0
Tuple fetches by index only scan 0
Tuple fetches by index scan with record retrieval 76
23 records returned
fc_get_sysconfig()
Returns FairCom DB SQL current server configuration information:
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_sysconfig();
Description Value
----------- -----
REPL_MAPPINGS Enabled 0
Max num segments per index 12
Max num indexes per data file 32
Server path separator 92
Client path separator 92
Server Serial Number 89,123,456
6 records returned
fc_get_taskid( )
Returns the current FairCom DB task ID associated with the FairCom DB SQL connection.
Result Set Fields (1)
id INTEGER
Example
call fc_get_taskid()
id
--
19
fc_get_transtats()
Returns FairCom DB SQL current transaction control statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_transtats();
DESCRIPTION VALUE
----------- -----
# transaction begins 0
# transaction ends 0
# transaction aborts 0
# transaction savepoints 1
# transaction restores 0
# transaction log flush writes 0
DOSFLUSH sync calls 0
transaction log sync calls 2
cumulative transaction time^ 0
cumulative transaction count 0
maximum elapsed tran time^ 0
check point count 1
cumulative checkpoint time^ 0
cumulative checkpoint size 0
checkpoint index buffer writes 0
checkpoint data cache writes 0
log flush count 0
log flush time 0
18 records returned
fc_get_userlist( )
Returns the current list of FairCom DB SQL defined users.
Result Set Fields (3)
USER VARCHAR(32)
DBA VARCHAR(3)
RESOURCE VARCHAR(3)
Example
call fc_get_userlist();
USER DBA RESOURCE
---- --- --------
admin Yes Yes
accounting (null) (null)
guest (null) (null)
sales (null) (null)
fc_ptadmin_num()
Performs partition administration actions to a partitioned table based on the partition number. You must be connected to the database that contains the table. This uses the ptADMINnewthd mode which requires the table to be closed.
Parameters (4)
owner VARCHAR(64)
table_name VARCHAR(64)
action VARCHAR(16)
part_num INTEGER
action is one of the following strings:
( 'purge' | 'reuse' | 'archive' | 'activate' | 'add' | 'rebuild')
Example
>call fc_ptadmin_num('admin', 'custmast', 'purge', 12345);
Requires DBA privilege.
fc_purge_db()
Purge tables that do not exist on disk from system tables.
fc_purge_db()
The stored procedure takes the following arguments:
- table name - This is a matching pattern using the like predicate syntax considering an empty string ('') and '%' (match all). Only tables matching the pattern are affected.
- mode - This parameter can be:
0 - purge missing tables
1 - purge missing tables if created outside SQL (not by the create table statement but "imported")
2 - purge all tables created outside SQL (remove them from SQL but do not delete from disk)
Mode 2 depends on the server being compiled with #define ctSQL_TRACK_ORIGIN active. If this #define is not active, an error is returned.
The fact that fc_purge_db does not delete tables from disk does not depend on the SQL_OPTION DELETE_IMPORTED; it never deletes tables from disk.
This stored procedure produces a resultset containing the list of tables entitled to be purged for which the purging was not successful due to an error, which is reported in the "status" column.
fc_set_blockinglock( )
Sets the blocking lock strategy for FairCom DB SQL queries.
Description
By default, SQL connections block on locks. Situations can arise with mixing SQL operations with existing non-SQL applications. An application may acquire a non-blocking lock, and hold that lock for a length of time, thereby blocking an SQL query which might be expected to return sooner. An "abandoned" lock might indefinitely block the query. To avoid these situation, options are available to change the blocking lock behavior for SQL connections.
Parameters (mode)
mode SMALLINT
Set mode = 1 to enable blocking locks; set mode = 0 to disable blocking locks
A negative mode value enables a blocking lock with a defined timeout value (in seconds) from the absolute value of the parameter.
For example, a value of -2 with a SELECT query times out if it waits on an existing lock for more than 2 seconds.
The scope of the locking behavior is only within the current user session (that is, the current executing SQL thread); it is not global across other connections. Each SQL thread of operation can specify it's own unique locking behavior.
Examples
- Disable blocking locks. Set blocking mode to 0 to cause a SQL statement to fail immediately when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It returns error -17042: CT - Could not obtain data record lock.
call fc_set_blockinglock( 0 );
- Enable blocking locks. Set blocking mode to 1 to cause a SQL statement to block. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted. It waits indefinitely for the lock to be released or an update or delete to be committed or rolled back. This is the default setting.
call fc_set_blockinglock( 1 );
- Enable blocking locks with a timeout.. Set blocking mode to a negative integer, which is the maximum number of seconds you want a SQL statement to block before it fails with error -17156: CT - timeout. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It waits until the lock is released, a pending update or delete is committed or rolled back, or the maximum number of seconds has expired. The following examples sets the timeout to 2 seconds.
call fc_set_blockinglock( -2 );
fc_set_debug( )
Sets the debug level of the FairCom DB SQL database engine for advanced diagnostics.
Parameters (1)
dbg_info VARCHAR(20)
Result Set Fields (1)
DBG_INFO VARCHAR(20)
Example
To enable Java debugging:
call fc_set_debug('NNNNNNNNNNYN');
DBG_INFO
--------
NNNNNNNNNNYN
See Also
- For definitions of the Y/N elements in the TPESQLDBG array, see Advanced FairCom DB SQL Logging
fc_set_file_tran_state( )
Sets the FairCom DB transaction mode for a table.
WARNING: Do not set this value without a complete understanding of c-tree transaction control. There is great potential for data loss if not properly set.
Parameters (3)
owner VARCHAR(64)
table_name VARCHAR(64)
mode TINYINT
Valid modes:
- 0 No transaction control.
- 1 Transaction control without recoverability. (ctPREIMG)
- 2 Transaction control and recoverability. (ctTRNLOG)
Example
call fc_set_file_tran_state( 'admin', 'custmast', 0 );
fc_set_impersonation( )
Enables impersonation of a FairCom DB SQL connection. Specify a taskid of zero to disable impersonation of the connection (default). Specify a taskid of 1 to enable impersonation of the connection by any other connection. Specify a taskid greater than 1 to enable impersonation of the connection only by a connection having that specific task ID.
Parameters (1)
taskid INTEGER
Example
call fc_set_impersonation( 19 )
fc_set_min_card( )
Sets the minimum cardinality value used by the FairCom DB SQL database engine.
Parameters (1)
value INTEGER
Example
call fc_set_min_card( 1000 );
fc_set_nodename( )
Sets the NODE name of the connection. The c-tree NODE name identifies a connection with a string value, that can then be conveniently viewed with monitoring tools.
fc_set_nodename() can be called multiple times within a FairCom DB SQL session to change the node name. It can also be called with an empty string to clear the NODE name.
Parameters (1)
nodename VARCHAR(31)
Example
call fc_set_nodename('accounting');
fc_set_priority( )
Sets the FairCom DB SQL client thread priority.
Note: This feature is supported only on the Windows operating system.
Parameters (1)
priority INTEGER
priority = -3 to +3.
Example
call fc_set_priority( 0 );
WARNING: Use extreme caution when changing this value. If the priority is set too high relative to other tasks, FairCom DB SQL may exhibit unpredictable behavior as other threads may not get enough CPU time by the process scheduler.
fc_set_rowid_visible()
Set an existing ROWID column as visible for a given table.
fc_set_rowid_visible('owner', ‘table’, ‘rowid’, ‘rowid_idx’);
Description
Use this function when you have existing tables that include the hidden $ROWID$ field and do not have a primary key. When you create new tables, use the JSON DB API because it ensures the table works well at all API layers, including the JSON DB, SQL, c-treeDB, and ISAM APIs, The JSON DB API follows best practices and does not create the hidden $ROWID$ field; instead, it automatically creates an id field that is the primary key of the table.
You can optionally create a hidden ROWID column on tables to help bridge between FairCom DB’s SQL and c-treeDB layers. This column has a special internal name of $ROWID$ and has an index. It uses the serial segment auto-numbering index mode, and, thus, creates a unique permanent id value for each row.
The stored procedure, fc_set_rowid_visible(), makes an existing c-treeDB ROWID visible as a column in the table. It also makes its index visible. You must assign a name to the column and index. When the column is visible, you can use it in queries. You cannot update or reposition the ROWID column.
A check is made that the table exists, and has a ROWID field. SQL_ERR_NOTBL or SQL_ERR_NOCOL is returned if not.
A check is made to ensure that the rowid isn’t already exposed with that id name - if so, it fails with a duplicate column exception.
Parameters
owner - Should match existing table owner, or the internal get table ID call doesn’t work.
table - table name with the ROWID to expose.
rowid - symbolic name of the exposed ROWID field
rowid_idx - assigned name for the ROWID index
Example
ISQL> call fc_set_rowid_visible('admin', 'test1', 'id', 'id_idx');
0 records returned
ISQL> select col from syscolumns where tbl='test1';
COL
---
name
age
rating
id
4 records selected
ISQL> table test1
COLNAME NULL ? TYPE LENGTH CHARSET NAME COLLATION
------- ------ ---- ------ ------------ ---------
id NOT NULL BIGINT 8
name CHAR 10
age INT 4
rating TINYINT 1
Limitations
This should only be called once for any given table. Unpredictable results can potentially happen with repeated executions.
fc_set_selectivity( )
Set the selectivity off (0) or on (1)
Parameters (1)
value BIT
Example
call fc_set_selectivity( 1 );
fc_set_sysconfig()
Dynamically enables a limited set of system configuration options as defined by the SetSystemConfigurationOption() API call.
Parameters (2)
option VARCHAR(64)
value VARCHAR(64)
option is one of the following:
'checkpoint_monitor'
'ctstatus_mask'
'diagnostics'
'dynamic_dump_defer'
'function_monitor'
'memory_monitor'
'request_time_monitor'
'vss_writer'
Examples
fc_set_sysconfig('vss_writer', 'YES');
fc_set_sysconfig('diagnostics', 'LOWL_FILE_IO');
fc_set_sysconfig('diagnostics', 'REPLICATE');
fc_set_sysconfig('ctstatus_mast', '~DYNAMIC_DUMP_FILES');
Requires DBA privilege.