FirebirdSQL logo

Version

This manual describes the isql utility in Firebird version 1.5 up to and including Firebird 5.0.

Setting The ISC_USER And ISC_PASSWORD Environment Variables

An environment variable is a named object that contains information used by one or more applications.They are global to their specific operating systems.The Firebird server recognises and uses certain environment variables configured in Windows, Linux and other Unix systems.

The ISC_USER and ISC_PASSWORD environment variables in Firebird allow you to login without having to explicitly specify a username and password in the commandline utilities and client applications.

Caution

When running commandline utilities like isql, gbak, gstat, and gfix, Firebird will search to see if the ISC_USER and ISC_PASSWORD environment variables are set.If you do not provide a username and password while connecting to a database locally, Firebird will let you log in provided it finds these variables.

For security reasons, it is not advisable to specify the SYSDBA username and password using these two environment variables, especially on an insecure computer.

The ISC_USER and ISC_PASSWORD environment variables may be set to start isql locally.To set the environment variables:

  • In Windows, this is done in the Control Panel → System → Advanced → Environment Variables, or through Windows Explorer → right-click on This PC → Properties → Advanced System Settings → Environment Variables.Any changes made here will be permanent.You may also define these variables in a command window prior to running any of the Firebird utilities, such as isql.For example:

    C:\> set ISC_USER=sysdba
    C:\> set ISC_PASSWORD=secret
    C:\> isql my_employee
    
    SQL>
  • In Linux and Unix platforms, this depends on the type of shell being used and how the desktop is configured.Please refer to your operating system documentation to set environmental variables.For the bash shell, the following example shows the process:

    $ export ISC_USER=sysdba
    $ export ISC_PASSWORD=secret
    $ /opt/firebird/bin/isql my_employee
    
    SQL>

SQL Dialects

Firebird supports three SQL dialects in each client and database server.These SQL dialects are differentiated in the context of the date-time format, and the precision of a numerical data type.The dialects serve to instruct the Firebird server on how to process features implemented in legacy Borland Interbase databases, earlier than version 6.0.Dialects are set up at runtime and can be changed for the client at connection time or with a SET SQL DIALECT command.

Note

Dialect 2 is only used when converting a dialect 1 database to a dialect 3 database.

The following table illustrates the differences between the dialects.

Table 1. SQL Dialects
SQL Dialect 1 Dialect 2 Dialect 3

Date

Date & Time (Timestamp)

ERROR Message

Date only

Time Stamp

Timestamp (v.6.x only)

Timestamp

Timestamp

Time

Error message

Error message

Time only

<"quoted item">

String

Error message

Symbol only

Precision: 1/3 =

0.3333333…​ (double precision)

0

0

Numeric 11

double precision

64 bit int

64 bit int

Note

Currently, it is possible to create databases in Dialect 1 and 3 only, however it is recommended that you use Dialect 3 exclusively, since Dialect 1 is deprecated.Dialect 2 cannot be used to create a database since it only serves to convert Dialect 1 to Dialect 3.

When connecting to a database using isql, the utility takes on the dialect of the database, unless you specify otherwise.Dialects cannot be set as a parameter of a CREATE DATABASE statement.So, when creating a database using isql, the database will be in the dialect that is current in isql at the time the CREATE DATABASE statement is issued.You may set the dialect using the isql utility in two ways:

  • When you start isql type:

    isql -sql_dialect n

    (where n refers to the dialect number)`

  • Within a SQL script or isql session, type:

    SQL> SET SQL DIALECT n;
    Note

    Prior to Firebird 2.0 when isql disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which lead to some inappropriate warning messages.This has been fixed in 2.0.

Terminator Character

The default terminator symbol in isql is the semicolon (‘;’).Statements will only be executed if they end with a semicolon.However, you can configure isql to use a different symbol — any printable character, or characters, from the first 127 characters of the ASCII subset — by using the [isql-set-term] command.

Note

The default terminator maybe changed in all instances except in the case of Procedural SQL or PSQL.PSQL does not accept any terminator other than a semicolon.

To change the terminator character to a tilde (‘~’) enter the following code:

SQL> SET TERM ~ ;

You must terminate this command with the current terminator!Changing the terminator is useful if you wish to type in a PSQL function as the following example shows.Because PSQL will only accept the semicolon as a terminator, isql needs to know which semicolon is being used for the PSQL code and which is being used to terminate the SQL commands being entered.

SQL> set term ~ ;

SQL> create procedure test_proc (iInput integer = 666)
CON> returns (oOutput integer)
CON> as
CON> begin
CON>   oOutput = iInput;
CON>   suspend;
CON> end~

SQL> set term ; ~

SQL> commit;

SQL> select * from test_proc;

     OOUTPUT
============
         666

You can see that within the code for the procedure itself, the terminator is the semicolon.However, outside the actual procedure code, the terminator is the tilde (‘~’).Isql is processing a single CREATE PROCEDURE command, but within that one SQL statement, there are multiple embedded PSQL statements:

oOutput = iInput;
suspend;

These have the semicolon terminator, as required by PSQL.The end of the CREATE PROCEDURE command is indicated by the use of the tilde as the terminator:

end~

You can, if desired, change the terminator because you prefer something other than a semicolon.You don’t have to be writing PSQL code to change it.

SQL> -- Change terminator from ; to +
SQL> set term + ;

SQL> select count(*) from employee+

       COUNT
============
          42

SQL> -- Change terminator from + to 'fred'
SQL> set term fred +

SQL> select count(*) from employee fred

       COUNT
============
          42

SQL> -- Change back from 'fred' to ;
SQL> set term ; fred

However, you must be careful not to pick a terminator character that will cause SQL statements to fail due to the terminator being used at some point within the SQL statement.

SQL> select 600+60+6 as The_Beast from rdb$database;

            THE_BEAST
=====================
                  666


SQL> set term + ;
SQL> select 600+60+6 as The_Beast from rdb$database+

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Unexpected end of command - line 1, column 8
...

SQL> set term ; +

The presence of the terminator within an expression has caused the "unexpected end of command" error.The SQL parser within the Firebird database engine has determined that "select 600" is not a valid statement.For this reason, it is best to always choose a character, or characters, that will not confuse the parser.

SQL> set term #;

SQL> select 600+60+6 as The_Beast from rdb$database#

            THE_BEAST
=====================
                  666

Isql Prompts

The SQL> prompt

As shown above, the normal isql prompt for input is the SQL> prompt.This indicates that the previous command has been completed and isql is now waiting for a new command to process.

The CON> prompt

The CON> or Continuation prompt is displayed if users press kbd:[Enter] without ending a SQL statement with a terminator.For example:

SQL> HELP
CON>

Whenever you see the CON> prompt, you may either continue entering the remainder of the statement or command, or enter a terminator to terminate the statement.When you press kbd:[Enter], the statement will be executed in the latter case.