ISQL — Firebird Interactive SQL Tool

ISQL is a command-line tool for interactive SQL access to databases. It can also be used to perform administrative tasks in command-line environments (like (metadata) updates via a batch file).

General Syntax

isql <options> [<database>]

or

isql -?

Options

-a(ll)	Extract metadata incl. legacy non-SQL tables
-b(ail)	Bail on errors (set bail on)
-c(ache) <num>	Number of cache buffers
-ch(arset) <charset>	Connection Character Set (set names)
-d(atabase) <database>	Database name to put in script creation
-e(cho)	Echo commands (set echo on)
-ex(tract)	Extract metadata
-fet[ch_password] <filename>	Instead of -password: Fetch password from the file so it is not visible in the command line. When <filename> is stdin, the user will be prompted for the password. [Firebird 2.5]
-i(nput) <filename>	Process SQL script file (set input)
-m(erge)	Merge standard error
-m2	Merge diagnostic
-n(oautocommit)	No autocommit DDL (set autoddl off)
-nodbtriggers	Suppresses Database Triggers from running. Only available to the database owner and SYSDBA [Firebird 2.1]
-now(arnings)	Do not show warnings
-o(utput) <filename>	Output file (set output)
-pag(elength) <size>	Page Length
-p(assword) <password>	Connection Password
-q(uiet)	Do not show the message "Use CONNECT..."
-r(ole) <role>	Role Name
-r2 <role>	Role (uses quoted identifier)
-s(qldialect) <dialect>	SQL Dialect (set sql dialect)
-t(erminator) <term>	Command Terminator (set term)
-u(ser) <user>	User Name
-x	Extract Metadata
-z	Show Program and Server Version

Interactive Mode

ISQL enters an interactive mode. Command lines start with a SQL> prompt, continuing lines with a CON> prompt.

NOTE that every command has to be terminated by a semi-colon (;).

C:\Programme\Firebird2\bin>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect elias:apqp user sysdba password masterkey;
Database:  elias:apqp, User: sysdba
SQL> _

You can now enter DDL, DML or special ISQL commands.

You can also specify the database directly in the isql call:

C:\Programme\Firebird2\bin>isql -user SYSDBA -password masterkey elias:apqp
Database:  elias:apqp, User: SYSDBA
SQL> exit;

ISQL Commands

You can get a list of commands by calling the HELP; command.

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 Help
HELP SET	Display Help for the SET command
INput <filename>	Take input from the named SQL file
OUTput [<filename>]	Write output to named file
OUTput	Return output to stdout
SET <option>	Set option. Use HELP SET for a complete list
SHELL <command>	Execute Operating System command in sub-shell
SHOW <object> [<name>]	Display system information on a database object. <object> can be: CHECK, COLLATIONS [FB2.5], DATABASE, DOMAIN, EXCEPTION, FILTER, FUNCTION, GENERATOR,GRANT, INDEX, PROCEDURE, ROLE, SQL DIALECT, SYSTEM, TABLE, TRIGGER, VERSION, VIEW
EXIT	Exit and Commit changes
QUIT	Exit and Roll back changes

SET Options

There are several options that influence the behaviour of ISQL (this list can be retrieved by calling HELP SET;)

SET	Display current SET options
SET AUTOddl	Toggle autocommit of DDL statements
SET BAIL	Toggle bailing out on errors in non-interactive mode
SET BLOB [ALL\|<n>]	Display BLOBS of subtype <n> or ALL
SET BLOB	Turn off BLOB display
SET COUNT	Toggle count of selected rows on/off
SET ECHO	Toggle command echo on/off
SET HEADING	Toggle display of query column titles
SET LIST	Toggle column or table display format
SET NAMES <csname>	Set name of runtime character set
SET PLAN	Toggle display of query access plan
SET PLANONLY	Toggle display of query plan without executing
SET SQL DIALECT <n>	Set SQL Dialect to <n>
SET STATs	Toggle display of performance statistics
SET TIME	Toggle display of timestamp with DATE values
SET TERM <string>	Change statement terminator string
SET WIDTH <col> [<n>]	Set/unset print width to <n> for column <col>

By just calling SET without parameters you can get a list of all settings.

SET TERM

SET TERM is special in that it changes the statement termination character. The default statement termination character is a semi-colon. However, if you want to create or alter a Stored Procedure or Trigger, the semi-colons contained in the body would also terminate the command that define the procedure. So to define a stored procedure:

Use SET TERM to change the statement termination to something that does not occur in your procedure
Create or alter your Stored Procedure or Trigger
Terminate this command with the new termination character
Change the termination character back to a semi-colon using SET TERM

Example

SQL> set term ^ ;
SQL> create procedure Mul (a integer, b integer)
CON>   returns (Result integer)
CON> as begin
CON>   Result = a * b;
CON>   suspend;
CON> end ^
SQL> set term ; ^

Note that the end statement of the procedure is terminated with a caret (^) instead of a semi-colon (;). The caret has been defined as the new termination character by the first SET TERM statement.

Running SQL Scripts

You can run an SQL script by using the -i command line option. To further suppress the "Use CONNECT or CREATE DATABASE to specify a database" message that appears everytime when ISQL starts, use the -q option.

C:\Programme\Firebird2\bin>isql -q -i c:\Scripts\CreateScript.sql

C:\Programme\Firebird2\bin>

In this case, the script must contain a CONNECT or CREATE DATABASE command.

Scripts should also use SET NAMES to define the Client Character Set they use.

Stefan Heymann, last edit 2009-11-20

This documentation is licensed under (choose your favorite): GPL, LGPL, CC, IDPL, GFDL, BSD, (did I forget one?)