Skip to content
≡

Administrator

Developer

Tools

External Tools

File Management

File Management#

Saving Results#

iSQL enables results returned through iSQL to be saved in a designated file. In the following example, results are stored in the designated file, book.txt, using the SPOOL command.

To cancel this command, use the SPOOL OFF command.

e.g.,

iSQL> SPOOL book.txt    
Spool start. [book.txt]     -- All subsequently executed commands and their results will be written to.
iSQL> SPOOL OFF
Spool Stop                  -- From this point on, no more commands or results will be saved in the file.

Running Scripts#

@ Command#

@file_name[.sql]
or 
START file_name[.sql]

  • file_name[.sql]
    The script file to be executed. If the filename extension is omitted, iSQL assumes the default command file extension (.sql).

When this command is executed, , iSQL executes all of the commands in the specified script file in sequence.

@ command performs the same function as START.

  • An EXIT or QUIT command in the script file terminates iSQL.
  • The script file may include general SQL statements, iSQL commands, references to stored procedures, etc.

The following is an example in which the schema.sql script, which can be found in the $ALTIBASE_HOME/sample/APRE/schema directory, which is the current directory, is executed.

iSQL> START schema.sql      -- The SQL statements in the file are executed.
or 
iSQL> @schema.sql

When specifying a script file, you can use a question mark ("?") to indicate the Altibase home directory ($ALTIBASE_HOME) of the user account. The following is an example in which the schema.sql script, which can be found in the ​$ALTIBASE_HOME/sample/APRE/schema directory, is executed regardless of which directory is the current directory.

iSQL> @?/sample/schema.sql

The question mark ("?") can also be used with the following iSQL commands:

  • edit, save, load, spool, start

The -- or / / characters can be used to insert comments in script files. -- means that everything that follows until the end of the line will be handled as a comment, whereas comments that span several lines are placed between / and /.

@@ Command#

@@file_name[.sql]
  • file_name[.sql]
    This indicates the embedded script to be executed. If the extension is omitted, iSQL assumes the default command file extension(.sql).

Executes the specified script. The functionality of the @@ command is similar to that of the @ command.

This command searches for script files in the same path as the script currently being executed, and is thus useful for executing embedded scripts.

The @@ command can be used for the following purposes:

  • If a script file that contains the text @@file_name.sql is executed, iSQL looks for the file specified by file_name.sql, and executes its contents in sequence. file_name.sql must be located in the same directory as the script file that called it. If no such file exists, iSQL raises an error.

  • If a user inputs @@file_name.sql at the iSQL prompt, the result will be the same as when using iSQL to execute @file_name.sql.

  • The script typically may include SQL statements, iSQL commands, or stored procedures.

  • An EXIT or QUIT command in the script terminates iSQL.

The following is an example of the execution of a.sql, in which schema.sql is referenced, from the $ALTIBASE_HOME directory. In order for this example to be executed without error, a.sql must exist in the $ALTIBASE_HOME/sample/APRE/schema directory alongside schema.sql.

iSQL> @sample/APRE/schema/a.sql

$ cat a.sql
@@schema.sql

Note: The following chapter provides examples of editing the results of a query in an iSQL environment based on the tables created by execution of the above script (see appendix Schema).

Passing parameters through START Command#

START file_name[.sql] [param1 [param2] ...]
@file_name[.sql] [param1 [param2] ...]
@@file_name[.sql] [param1 [param2] ...]
  • [param1 [param2] ...]
    The value to be transferred as a parameter to the script file.

The substitution variables are used if a user wants to specify every time execution is made and not fixating certain values of a SQL statement within the script file. The values to be replaced the substitution variable can be passed as a parameter if the script file is executed with START, @ or @@ command.

The substitution variable within the script file is used with '&' and numbers, and the number signifies the sequence. However, this feature is performed only if the SET DEFINE ON option is specified. Refer to the SET DEFINE(hyperlink) for further information.

For instance, if substitution variables are used in emp.sql file as in the following :

SELECT ENO, E_LASTNAME FROM EMPLOYEES
 WHERE EMP_JOB = '&1'
  AND SALARY > &2;

If 'programmer' and '2000' are inserted as parameters when executing the START command, 'programmer' and '2000' are replaced into &1 and &2, respectively. Thus, employees whose job is 'programmer' and salary is '2000' are viewed. 

iSQL> SET DEFINE ON;        -- Substitution values are replaced as parameters if it is set to ON.
iSQL> START emp.sql programmer 2000
old   2: WHERE EMP_JOB = '&1'
new   2: WHERE EMP_JOB = 'programmer'
old   3: AND SALARY > &2;
new   3: AND SALARY > 2000;
ENO         E_LASTNAME
-------------------------------------
10          Bae

iSQL outputs SQL commands before and after parameter values are replaced for the command-lines containing substitution variables. SQL commands after replacing values are not output if the SET VERIFY OFF option is specified. The substitution variable can be used for multiple times within a single script and it is not necessary to be used in sequence.

The substitution value can also be replaced with parameters in the following manner.

START emp.sql
...
iSQL> START emp.sql
iSQL> SELECT ENO, E_LASTNAME FROM EMPLOYEES
WHERE EMP_JOB = '&1'
AND SALARY > &2;
Enter value for 1: programmer
old   2: WHERE EMP_JOB = '&1'
new   2: WHERE EMP_JOB = 'programmer'
Enter value for 2: 2000
old   3: AND SALARY > &2;
new   3: AND SALARY > 2000;
ENO         E_LASTNAME            
-------------------------------------
10          Bae                   
1 row selected.

In addition, in order to use specific characters by connecting immediately after the substitution value, a period(.) should be used for distinguishing the substitution value and characters.

SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='&1.0';
Enter value for 1: 2
old   1: SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='&1.0';
new   1: SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='20';

SET DEFINE {ON|OFF}#

This specifies whether or not to replace substitution variables with the parameter values inserted by a user when executing a script file containing the substitution variable through the START, @ or @@ command.

The default value is set to OFF, and substitution variables are not replaced with parameter values. That is, this option should be set to ON when executing a script file containing substitution variables.

SET VERIFY {ON|OFF}#

This specifies whether or not to display SQL statements before and after replacing with the parameter value when executing a script file containing the substitution variable through the START, @ or @@ command.

The default value is set to ON and before and after SQL statements are output.

$ cat Param1.sql
SELECT * FROM T1 WHERE I1 = &1;

iSQL> SET DEFINE ON;
iSQL> SHOW VERIFY;
Verify : On
iSQL> START Param1.sql 5;
iSQL> SELECT * FROM T1
WHERE I1 = &1;
old   2: WHERE I1 = &1;
new   2: WHERE I1 = 5;
T1.I1       T1.I2
---------------------------
5           Hyacinth
1 row selected.

iSQL> SET VERIFY OFF;
iSQL> SHOW VERIFY;
Verify : Off
iSQL> START Param1.sql 5;
iSQL> SELECT * FROM T1
WHERE I1 = &1;
T1.I1       T1.I2
---------------------------
5           Hyacinth
1 row selected.

Saving SQL Statements#

Of the commands currently in the iSQL buffer, the SAVE command saves the most recently executed one in a file.

This file will be created in the current directory.

iSQL> SELECT * FROM book;
iSQL> SAVE book.sql;        -- 'SELECT * FROM book;' is saved in the file book.sql.
Save completed.

Loading SQL Statements#

This function loads the first command in the specified file to the last position in the iSQL buffer.

iSQL> LOAD book.sql
iSQL> SELECT * FROM book;
Load completed.
iSQL> /     -- The results of execution of SELECT * FROM book; can be seen.

Saving DML Statements#

Executed DML statements such as INSERT, UPDATE, DELETE and MOVE are saved in $ALTIBASE_HOME/trc/isql_query.log.

Specify SET QUERYLOGGING ON to use this functionality and OFF to disable it.

iSQL> SET QUERYLOGGING ON;      -- From this point on, all executed DML statements will be saved in $ALTIBASE_HOME/trc/isql_query.log. 
iSQL> CREATE TABLE T1 ( I1 INTEGER );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1 );
1 row inserted.
iSQL> UPDATE  T1 SET I1 = 2;
1 row updated.
iSQL> SELECT * FROM T1;
I1  
--------------
2
1 row selected.
iSQL> DELETE FROM T1;
1 row deleted.
iSQL> DROP TABLE T1;
Drop success.
iSQL> EXIT

% cat $ALTIBASE_HOME/trc/isql_query.log     # All queries executed since SET QUERYLOGGING ON was executed can be observed.
[2009/09/16 10:36:14] [127.0.0.1:25310 SYS] INSERT INTO T1 VALUES ( 1 )
[2009/09/16 10:36:31] [127.0.0.1:25310 SYS] UPDATE T1 SET I1 = 2
[2009/09/16 10:36:37] [127.0.0.1:25310 SYS] DELETE FROM T1

Editing Query Statements#

Editing the Most Recent Query Statement#

The command edit is provided for creating and editing files in iSQL.

If ed is executed without parameters, a temporary file named iSQL.buf containing the most recently executed query statements will be created, and the following screen will be visible. (To save space, only a few of the blank lines that would be displayed on the screen are shown here.)

iSQL> SELECT sysdate FROM dual;
SYSDATE      
---------------
01-JAN-2000  
1 row selected.

iSQL> ed
SELECT sysdate FROM dual;
~
~
~
"iSQL.buf" 1L, 26C

Editing Existing Files#

If the user wants to edit an existing file, type the file name in iSQL as a parameter when launching the text editor using the "ed" command. When the screen is initialized, blank lines will be displayed as ~ (tilde) characters.

iSQL> ed myquery.sql
"myquery.sql"
INSERT INTO employee(ENO, E_FIRSTNAME, E_LASTNAME, SEX) VALUES(21, 'MSJUNG', 'F');
INSERT INTO employee(ENO, E_FIRSTNAME, E_LASTNAME, SEX, JOIN_DATE)
VALUES(22, 'Joshua', 'Baldwin', 'M', TO_DATE('2001-11-19 00:00:00', 'YYYY-MM-DD HH:MI:SS'));
~
~"myquery.sql"

Editing Query Statements in History Lists#

The user can use the number in the history list to edit previously executed commands. In detail, the query statements are stored in the temporary file iSQL.buf in association with numbers, and can be edited with reference to them. The edited query will be stored again as the most recent record in the history list, and can be executed by entering the '/' (re-execute) character.

iSQL> h
1  : SELECT * FROM customers;
2  : SELECT * FROM employees;
iSQL> 2ed
or
iSQL> 2 ed
SELECT * FROM employees;
~
~
"iSQL.buf"

The command-line parameter 2, which is the name of the file to be edited (iSQL> ed 2), must be distinguished from the number indicating the line in the file to edit.

After editing (employees was replaced with orders)

iSQL> h     -- The history list currently in the iSQL buffer 
1  : SELECT * FROM customers;
2  : SELECT * FROM employees;
 : SELECT * FROM orders;
    -- The query statement edited using the 2 ed command will be saved as the last command in the history list.

iSQL> /     -- The most recently executed command will be executed.
ONO                  ORDER_DATE   ENO         CNO                  GNO         QTY         ARRIVAL_DATE PROCESSING  
-----------------------------------------------------------------------------------------------------------------------------
11290007             29-NOV-2011  12          3                    A111100002  70          02-DEC-2011  C           
11290011             29-NOV-2011  12          17                   E111100001  1000        05-DEC-2011  D 
…
12310012             31-DEC-2011  19          1                    C111100001  250         03-JAN-2012  O 
30 rows selected.

Note#

Use double quotation marks if the file name contains special characters or spaces.

iSQL> SPOOL "file name.txt";
iSQL> START "file name.sql";
iSQL> EDIT "file name.sql";