Databases - Firebird / isql

isql

isql is a command-line sql interface. It allows you to enter any sql command - Connect, Create, Select, Insert, and the like - or to run a script containing those commands.

The version that comes with Firebird 1.5 understands dialects.

This program

Can be run stand alone
Can be called via an *.bat file
Can be called via the Windows Explorer context (right click) menu

Running isql via Windows Explorer | Comments Problems - Extra /* Failure Mixing Comment Types
isql Commands | Debugging Scripts - Registry Mods

Running isql via Windows Explorer

The simplest way to run isql (IMHO) is to

Right click an *.sql file
Select Firebird command line isql from the menu

To make this work, I had to modify the registry

This is a possible *.reg file - on some systems, the .sql key may already exist.

[HKEY_CLASSES_ROOT\.sql]
@="sqlfile"

[HKEY_CLASSES_ROOT\sqlfile\shell\isql\command]
@="cmd.exe /k  \"\"C:\\Program Files\\Firebird\\Firebird_1_5\\bin\\isql.exe\" -i \"%l\"\""

Notice that

Double quotes (") and backslashes (\) have to be escaped
```
    \" and \\ 
```
The entire string is in regular double quotes

If you do it manually, enter this

cmd.exe /k ""C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" -i "%l""

cmd.exe /k: This causes the command window to stay open after the script completes execution so that you can see the errors (if any - I usually get a few at first). Double quotes are required around everything after the /k.
"[path]\isql.exe": The double quotes are required around the fully qualified program name (because "Program Files" contains a space), path is usually system dependent
-i "%l": (That is a lower case L) This tells the program to load the file you right clicked and to use the long path name

Comments

There are 2 methods to enter comments

Single line comment - Double dashes (--)

Block Comment - /* .. */

/* any comment, can use several lines */

However, there are several problems with the version of isql (dated 7-14-04) that come with Firebird 1.5.

Extra /*

This problem applies to - isql 7-14-04. Using the /* ... */ syntax to add comments, this example fails. The issue is related to the number of characters between the delimiters and to the fact that the last line of the comment is /* */.

Connect 'E:\NIST\Main Database\FB Databases 2005-02-11\Samples.fdb' 
         USER 'SYSDBA' PASSWORD 'masterkey';

/*   Data entered 02-22-05 - rlc

used in Sample_Timeline_tbl to indicate the type of data 

CREATE TABLE "Data_Type_LU" (
  "Data_Type_ID"   "Domain_ShortString_IDs" NOT NULL,
  "Data_Type_Text" "Domain_Short_Descriptions",
  "Comment"         VARCHAR(200)
);

/* */

with this error

Use CONNECT or CREATE DATABASE to specify a database
Command error: /*   Data entered 02-22-05 - rlc

used in Sample_Timeline_tbl to indicate the type of data

CREATE TABLE "Data_Type_LU" (
  "Data_Type_ID"   "Domain_ShortString_IDs" NOT NULL,
  "Data_Type_Text" "Domain_Short_Descriptions",
  "Comment"

If the comment is shortened to remove the last line of the error message and everything after it, the code does not fail.

Also, if the last line of the comment is changed from

/* */

/*
*/

(ie, place a return between the 2 asterisks) there is no failure.

Failure Mixing Comment Types

If a block comment is immediately followed be a single line double dash (--) comment that ends with a semicolon (;), the script fails.

Connect 'E:\NIST\Main Database\FB Databases 2005-02-11\Samples.fdb' 
         USER 'SYSDBA' PASSWORD 'masterkey';

/* Any comment */

--Delete From "Barcode_Type_LU";

select 'Just a comment' from rdb$database;

produces this error

Use CONNECT or CREATE DATABASE to specify a database
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Unexpected end of command


==============

Just a comment

However, both of these run without error

The semicolon (;) at the end of the single line comment is removed

Connect 'E:\NIST\Main Database\FB Databases 2005-02-11\Samples.fdb' 
         USER 'SYSDBA' PASSWORD 'masterkey';

/* Any comment */

--Delete From "Barcode_Type_LU"

select 'Just a comment' from rdb$database;

An sql statement is placed between the two types of comments

Connect 'E:\NIST\Main Database\FB Databases 2005-02-11\Samples.fdb' 
         USER 'SYSDBA' PASSWORD 'masterkey';

/* Any comment */

select 'Just a comment' from rdb$database;

--Delete From "Barcode_Type_LU";

select 'Just a comment' from rdb$database;

In addition, you can not place single line (--) comments inside block comments.

isql Commands

In isql you can enter normal sql commands, terminated by a semicolon.

Several other "special" commands are supported

`? help;`	Show the available "special" commands
`help set;`	Show all the set options
`set;`	Show all the current option settings except dialect
`set terminator ^; set terminator ;^`	Change the command terminator to ^ and back to ; These commands are needed to define triggers and stored procedures
`set echo on;`	I use this to debug script errors when the script is run via a batch file
`input filename;`	Read additional commands from the specified file
`show table;`	List all the user tables
`show table "TableName";`	Show the table's structure - double quotes required for mixed case table names
`show system;`	List all the system tables - you should not edit these
`show sql dialect;`	This is how to see the set option, dialect 3 allows mixed case identifiers
`show domain;`	List the defined domains (field definition aliases)
`show domain "Domain_Name";`	This shows the definition of the alias (domain)
`show generator;`	Lists all generators and their current values

If you enter a command without a terminator (semicolon), the prompt changes from sql> to con> (continuation). This allows you to type commands on several lines - to return to normal mode, enter the current terminator and press enter.

Debugging Scripts

Using isql, it is a challenge to debug scripts - this should help.

When running a script, it is likely that an error will be displayed ... but the command that caused the error will not be displayed.

There are 2 ways to display where the error is

set echo on;

will display all the commands as they are executed. Use

set echo off;

(the default) to hide them again.

You can also use a select statement to write a comment to the screen

select 'Just a comment' from rdb$database;

produces

==============

Just a comment

Registry Mods

These are several registry modifications that will simplify debug. Only the commands are discussed below - a reg file is provided to modify your registry. Please view and verify the content before merging the data ... fixing any paths as appropriate. (For instance, isql may be in a different directory on your system.) Note - if your system already has commands associated with *.sql files, this *.reg file will ignore and replace them. Please check first.

Only show errors	`cmd.exe /k ""C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" -i "%l""`
Show All Allows you to see which lines caused the errors	`cmd.exe /k ""C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" -e -i "%l""`
output -> debug.txt Write to file	`cmd.exe /k ""C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" -e -i "%l" -o debug.txt"`
debug -> notepad Write to file and then display it	`cmd.exe /k ""C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" -e -i "%l" -o debug.txt && notepad.exe debug.txt"`

In one version, the code simply executes the sql file and displays the errors (if any). This simplifies checking for errors. The other version displays the sql text and associated errors - this simplifies debug.

For simple problems, I prefer to display the errors in the command window ... but for more complex errors, or large files where I want to search for the errors, then notepad is more convenient.

A few isql command line options
-i "filename"	Input file - may be fully qualified
-o "filename"	Output file - may be fully qualified
-e	Set echo on - causes the source file to be displayed so that the errors are associated with the correct statements
-a	Display the metadata (table structures, triggers, etc) of an *.fdb file
-u username	sysdba
-p password	masterkey

Reference - A Quick Guide to ISQL's Switches and Commands for Firebird/InterBase

Registry Mods for *.fdb files

This registry modification (associated with *.fdb & *.gdb files) will display the sql necessary to recreate a database - including the tables, triggers, generators, and the like. Notice that this requires a username and password - I have included defaults, yours should be different.

cmd.exe /c "del "%l.sql" & "C:\Program Files\Firebird\Firebird_1_5\bin\isql.exe" 
  -u SYSDBA -p masterkey -a "%l" -o "%l.sql" && notepad.exe "%l.sql""

This command (actually entered on a single line, wrapped for readability) produces a file that contains the sql statements and then opens that file in notepad. Notice that cmd supports multiple commands separated by

&	Execute the next command as soon as possible
&&	Wait for previous command to finish without errors, then continue

Because isql actually appends the data to an existing file, the command string starts with a file delete. The first time this is run, the delete command produces a "File not found" error. The only problem I have with this technique is that an empty command window is displayed until notepad closes.

Author: Robert Clemenzi - clemenzi@cpcug.org