| Firebird Documentation Index → Isql - Interactive SQL → Isql Commands |
|
|
|
Isql commands affect the running of isql itself and do not affect the database or data in any way. These commands are used to display help, run scripts, create listings and so on. You can easily see a list of the available commands by typing the help command which will produce the following output:
SQL> help;
Frontend commands:
BLOBDUMP <blobid> <file> -- dump BLOB to a file
BLOBVIEW <blobid> -- view BLOB in text editor
EDIT [<filename>] -- edit SQL script file and execute
EDIT -- edit current command buffer and execute
HELP -- display this menu
INput <filename> -- take input from the named SQL file
OUTput [<filename>] -- write output to named file
OUTput -- return output to stdout
SET <option> -- (Use HELP SET for complete list)
SHELL <command> -- execute Operating System command in sub-shell
SHOW <object> [<name>] -- display system information
<object> = CHECK, COLLATION, DATABASE, DOMAIN, EXCEPTION, FILTER, FUNCTION,
GENERATOR, GRANT, INDEX, PROCEDURE, ROLE, SQL DIALECT, SYSTEM,
TABLE, TRIGGER, VERSION, USERS, VIEW
EXIT -- exit and commit changes
QUIT -- exit and roll back changes
All commands may be abbreviated to letters in CAPitals
Each of these commands will now be discussed. Note the last line of output from the help command. It explains that each of the commands may be abbreviated to just those letters displayed in capital letters. In the following discussion, the optional characters will be displays, as above, in lower case letters. For example, the input command will be shown as INput to indicate that the characters 'put' are optional.
SQL> BLOBDUMP blob_id filename;
This command allows you to copy a BLOB from the database into an external file. It is the responsibility of the user to ensure that the correct file type is used - don't call an image file something.txt when it should be a jpeg for example.
Blobdump requires two parameters, a blob id and a filename. The latter is simple but the former is more convoluted. You are required to pass the blob id as a pair of hexadecimal numbers, separated by a colon. The first number is the relation id number for the table in question and the second is a sequential number within the database. You will see this pair of numbers when you select any BLOB column's data from a table - it is displayed above the BLOB contents, assuming that the display of BLOBs is turned on. See the set blobdisplay command below for details.
SQL> set blobdisplay off; SQL> select proj_id, proj_desc CON> from project CON> where proj_id = 'MKTPR'; PROJ_ID PROJ_DESC ======= ================= MKTPR 85:10 SQL> blobdump 85:10 project.jpg; SQL> blobdump 85:10 project.txt;
The blob id required in the above example is the '85:10' value. You will notice that I have dumped this BLOB to both a jpeg and a text file. Isql gave no errors for the fact that I attempred to dump the BLOB to a jpeg file when the BLOB in question is text. Attempting to open the jpeg file with any image viewers will, however, result in an error. The text file opens happily in any of the assorted text viewers or editors installed on the system.
SQL> BLOBVIEW blob_id;
This command is similar to blobdump above, but only requires the blob id parameter as the BLOB data will be displayed in an editor.
SQL> blobview 85:10;
The contents of the selected BLOB are displayed in an external editor. When the editor is closed, control returns to isql. You cannot use isql while the editor is open.
BLOBVIEW may return an “Invalid transaction handle” error after you close the editor. This is a known bug. To correct the situation, start a transaction manually, with the command SET TRANSACTION;
SQL> EDIT [filename];
This command allows you to edit an existing file. This may be a file of SQL commands to be used by the isql input command (see below) or any other text file. The file must, however, already exist.
If no filename is supplied, a history of all your previous commands will be displayed for editing. Please note that when you exit from the editor in this case, the commands left in the buffer at the end of the edit will be executed as a script file.
SQL> ADD table_name;
This command, when passed a table name, prompts you for each column's data and adds a row to the table. You may add as many rows as you wish as the command continues until either an error occurs or the RETURN key is pressed with no data. If you wish to set a column to NULL, type it in exactly as shown.
SQL> add country; Enter data or NULL for each column. RETURN to end. Enter COUNTRY>Scotland Enter CURRENCY>GBP Enter COUNTRY> SQL> commit;
SQL> COPY from_table_name to_table_name [other_database];
The copy command allows you to copy most of the structure of a table to a new table, in the current database or to a different one. Unfortunately it has a couple of problems:
It shells out to the command line to do the work, and connects to the receiving database using an application named isql. If, like me, your system has renamed isql to isql-fb, you will actually end up running the wrong isql application and confusing error messages will be the only result.
It assumes that isql will be on the
$PATH or %PATH%.
You need to define ISC_USER and
ISC_PASSWORD for the child
isql process to login to the receiving
database to create the table. This is very
insecure.
Because of the need for ISC_USER and
ISC_PASSWORD, the receiving database must be running
on the same server as the source
database.
The data in the table is not copied to the receiving database. Only the following parts of the table's structure is copied.
Domains required to recreate the table. This only applies if the copy is to another database.
The table itself will be created.
Primary key constraint, if there is one.
The index used to support the primary key constraint, if there is one.
Not all of the table structure is actually copied. Missing are:
Foreign Key constraints.
Check constraints.
Indices other than the primary key index.
Triggers.
All of the table's data.
If you wish to copy to a different database, then the other
database must be on the same server as the current
one. You cannot, for example, connect to a database on a server named
tux, and copy a table to a database running on the server tuxrep. The
copy command has no way to allow you to pass a
username and/or password and, equally, setting ISC_USER
and ISC_PASSWORD only affects databases on the current
server.
tux> $ export ISC_USER=SYSDBA
tux> $ export ISC_PASSWORD=secret
tux> isql employee
Database: employee, User: sysdba
SQL> -- MAke a copy of the employee table into this database.
SQL> copy employee employee_2;
SQL> -- Compare table structures...
SQL> show table employee;
EMP_NO (EMPNO) SMALLINT Not Null
FIRST_NAME (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT VARCHAR(4) Nullable
HIRE_DATE TIMESTAMP Not Null DEFAULT 'NOW'
DEPT_NO (DEPTNO) CHAR(3) Not Null
CHECK (VALUE = '000' OR
(VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
JOB_CODE (JOBCODE) VARCHAR(5) Not Null
CHECK (VALUE > '99999')
JOB_GRADE (JOBGRADE) SMALLINT Not Null
CHECK (VALUE BETWEEN 0 AND 6)
JOB_COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null
SALARY (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
CHECK (VALUE > 0)
FULL_NAME Computed by: (last_name || ', ' || first_name)
CONSTRAINT INTEG_28:
Foreign key (DEPT_NO) References DEPARTMENT (DEPT_NO)
CONSTRAINT INTEG_29:
Foreign key (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
References JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
CONSTRAINT INTEG_27:
Primary key (EMP_NO)
CONSTRAINT INTEG_30:
CHECK ( salary >= (SELECT min_salary FROM job WHERE
job.job_code = employee.job_code AND
job.job_grade = employee.job_grade AND
job.job_country = employee.job_country) AND
salary <= (SELECT max_salary FROM job WHERE
job.job_code = employee.job_code AND
job.job_grade = employee.job_grade AND
job.job_country = employee.job_country))
Triggers on Table EMPLOYEE:
SET_EMP_NO, Sequence: 0, Type: BEFORE INSERT, Active
SAVE_SALARY_CHANGE, Sequence: 0, Type: AFTER UPDATE, Active
SQL> show table employee_2;
EMP_NO (EMPNO) SMALLINT Not Null
FIRST_NAME (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT VARCHAR(4) Nullable
HIRE_DATE TIMESTAMP Not Null DEFAULT 'NOW'
DEPT_NO (DEPTNO) CHAR(3) Not Null
CHECK (VALUE = '000' OR
(VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
JOB_CODE (JOBCODE) VARCHAR(5) Not Null
CHECK (VALUE > '99999')
JOB_GRADE (JOBGRADE) SMALLINT Not Null
CHECK (VALUE BETWEEN 0 AND 6)
JOB_COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null
SALARY (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
CHECK (VALUE > 0)
FULL_NAME Computed by: (last_name || ', ' || first_name)
CONSTRAINT INTEG_93:
Primary key (EMP_NO)
SQL> -- Check indices on both tables...
SQL> show indices employee;
NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)
RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO)
RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY)
RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO)
SQL> show indices employee_2;
RDB$PRIMARY27 UNIQUE INDEX ON EMPLOYEE_2(EMP_NO)
SQL> -- Check data counts on both tables...
SQL> select count(*) from employee;
COUNT
============
42
SQL> select count(*) from employee_2;
COUNT
============
0
The copy command only works provided your isql application is really named isql. In addition, if you have lots of data in the table, you still have to copy it manually as the copy command will only copy the table structure. Remember that the new table will have no triggers, no foreign keys, no indices - other than the primary key one - and no data.
It is possible that the copy command will be removed from isql at some future release.
SQL> INput filename;
This command enables the user to execute a number of commands from a script file rather than manually typing them all into isql at the prompt. The script may contain any mix of DDL and/or DDL commands, along with isql commands to redirect output, change options, etc.
SQL> shell; $ cat test.sql drop table fred; commit; $ exit; SQL> show table fred; A INTEGER Nullable B INTEGER Not Null SQL> input test.sql; SQL> show table fred; There is no table FRED in this database
SQL> OUTput [filename];
This command redirects all output that normally is displayed on the screen, to a specific file. If a file name is supplied, all subsequent output goes to that file and is not displayed on screen. If no file name is supplied, output is once more redirected to the screen.
SQL> output test.log;
SQL> show tables;
SQL> output;
SQL> shell;
$ cat test.log
COUNTRY CUSTOMER
DEPARTMENT EMPLOYEE
EMPLOYEE_PROJECT FRED
JOB PROJECT
PROJ_DEPT_BUDGET SALARY_HISTORY
SALES
There are a number of settings and options that can be changed to suit how you wish isql to operate. These settings are changed by the set command which is discussed below.
SQL> SHELL;
This command allows you to temporarily exit from isql and use a shell session to carry out some further processing. On exiting from the shell, you will return to isql. You cannot use the isql session that activated the shell while the shell session remains open.
SQL> shell;
$ cat test.log
COUNTRY CUSTOMER
DEPARTMENT EMPLOYEE
EMPLOYEE_PROJECT FRED
JOB PROJECT
PROJ_DEPT_BUDGET SALARY_HISTORY
SALES
$ exit
SQL>
There are a number of settings and options that can be changed to suit how you wish isql to operate. The show command allows you to view the way that these have been set up by the set commands, or by other options. These are discussed below.
|
| Firebird Documentation Index → Isql - Interactive SQL → Isql Commands |