Version
This manual describes the isql
utility in Firebird version 1.5 up to and including Firebird 5.0.
This manual describes the isql
utility in Firebird version 1.5 up to and including Firebird 5.0.
ISC_USER
And ISC_PASSWORD
Environment VariablesAn 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 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>
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.
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 |
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
SQL>
promptAs 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.
CON>
promptThe 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.