Firebird Documentation IndexIsql - Interactive SQL → Isql Set Commands
Firebird Home Firebird Home Prev: Isql CommandsFirebird Documentation IndexUp: Isql - Interactive SQLNext: Isql Show commands

Isql Set Commands

Set
Set Autoddl
Set Bail
Set Blobdisplay
Set Count
Set Rowcount
Set Echo
Set Heading
Set List
Set Names
Set Plan
Set Planonly
Set SQLDA_Display
Set SQL Dialect
Set Stats
Set Time
Set Term
Set Transaction
Set Warnings
Set Width

As explained in the help command, you may enter the help set command to drill down into the various options available for the set command. These are all discussed below. Note that the output from the help set command does not include the set transaction command. The help set command produces the following output (from Firebird 2.5):

SQL> help set;

Set commands:
    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 ROWCOUNT [<n>]     -- limit select stmt to <n> rows, zero is no limit
    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>

All commands may be abbreviated to letters in CAPitals

Note

In the above, the BLOB commands are incomplete. They should be BLOBdisplay. The above is displayed when the set command is executed with no parameters, however, in the following descriptions of the various set commands, I will be using the full BLOBdisplay version of the appropriate commands.

The last line of the above output indicates that these commands can be abbreviated to the letters in capitals. Unfortunately, other than the set autoddl command, none of the others appear to have a short form.

Set

The set command, with no parameters, displays the current settings, as the following example from Firebird 2.5 shows:

SQL> set;

Set commands:
    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 ROWCOUNT [<n>]     -- limit select stmt to <n> rows, zero is no limit
    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>

Set Autoddl

SQL> SET AUTOddl [on | off];

This command sets whether all DDL statements executed will be automatically committed or not. The command without any parameters acts as a toggle and turns autoddl off if it is currently on and vice versa. You may supply a specific parameter to make your intentions clear. The parameter must be one of on or off. The set command, with no parameters, will display the current setting. The default in isql is equivalent to set autoddl on.

Set Bail

SQL> SET BAIL [on | off];

Setting this command determines whether or not isql will "bail out" on any errors when the input command has been used to read a script file. Isql will not exit if it is running in interactive mode, and you cause an error.

Executing this command, without passing a parameter, results in a toggling of the current state. If bail is on, it will trun off and vice versa.

Set Blobdisplay

SQL> SET BLOBdisplay [n | all | off];

This command determines if BLOB column data is to be displayed in the output when a table with BLOB columns is queried. The deafult for this command, if no parameters are passed, is to set BLOB data off - it will not be displayed, only the blob id will be shown.

The blob id is discussed above in the section describing the blobdump and blobview commands.

If all is passed, then all BLOB sub-types will be displayed.

If a number representing the blob sub-type is passed, then only BLOBs with the specififc sub-type will be displayed. The default is 1 for text sub-types.

SQL> -- Don't display any blob data.
SQL> set blob off;

SQL> select proj_desc 
CON> from project
CON> where proj_id = 'HWRII';

        PROJ_DESC 
================= 
             85:e 


SQL> -- Display all blob data.
SQL> set blobdisplay all;

SQL> select proj_desc
CON> from project
CON> where proj_id = 'HWRII';

        PROJ_DESC 
================= 
             85:e 
==============================================================================
PROJ_DESC:  
Integrate the hand-writing recognition module into the
universal language translator.
==============================================================================


SQL> -- Only display type 1 blob data = text.
SQL> set blob 1;

SQL> select proj_desc
CON> from project
CON> where proj_id = 'HWRII';

        PROJ_DESC 
================= 
             85:e 
==============================================================================
PROJ_DESC:  
Integrate the hand-writing recognition module into the
universal language translator.
==============================================================================


SQL> -- Only display blob type 7 = not text!
SQL> set blob 7;

SQL> select proj_desc
CON> from project
CON> where proj_id = 'HWRII';

        PROJ_DESC 
================= 
             85:e 
==============================================================================
PROJ_DESC:  
BLOB display set to subtype 7. This BLOB: subtype = 1
==============================================================================

You will notice in the last example that a message was displayed advising that we are only displaying BLOB data for sub-type 7 and the BLOB data in this table is a sub-type 1, so the data are not displayed.

Set Count

SQL> SET COUNT [on | off];

This command determines whether a line of text is displayed at the end of the output from a DML statement, telling the user how many rows were affected.

SQL> set count on;

SQL> select count(*) from employee;

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

Records affected: 1

The record count is displayed for all DDL operations, not just for a SELECT.

SQL> create table fred( a integer);
SQL> commit;

SQL> insert into fred values (666);
Records affected: 1

SQL> update fred set a = 123 where a = 666;
Records affected: 1

SQL> delete from fred;
Records affected: 1

SQL> commit;

Set Rowcount

SQL> SET ROWCOUNT [n];

Setting rowcount to zero, which is the default when isql is started, results in a select statement returning all rows which meet the criteria in the where clause. There are circumstances where you do not want lots and lots of output scrolling up the screen, so you may set rowcount to a smaller number and all subsequent select statements will only display the first 'n' rows instead of everything.

SQL> set count on;
SQL> set rowcount 0;

SQL> select emp_no from employee;

 EMP_NO 
======= 
      2 
      4 
...
    144 
    145 

Records affected: 42


SQL> set rowcount 10;
SQL> select emp_no from employee;

 EMP_NO 
======= 
      2 
      4 
...
     15 
     20 

Records affected: 10

There is no indication that rowcount is restricting the number of rows returned, it is the responsibility of the user to remember, or check whether rowcount is on or off. Using rowcount can lead to confusion about exactly how many rows there are in a table!

Set Echo

SQL> SET ECHO [ON | OFF];

The default is on if you do not supply a value. This command causes all the SQL commands being executed to be displayed on the output device prior to their execution. You may wish to turn echo off as part of a script file although the isql default is for echo to be off.

SQL> set echo on;

SQL> select count(*) from rdb$database;
select count(*) from rdb$database;

       COUNT 
============ 
           1 


SQL> set echo off;
set echo off;

SQL> select count(*) from rdb$database;

       COUNT 
============ 
           1

This command can be handy in a script file. If you receive an error, it can sometimes be difficult to determine the exact SQL statement that caused it. If you set echo on in your script, you will at least be able to determine exactly which statement failed.

Set Heading

SQL> SET HEADING [ON | OFF];

This command turns the display of column headings on or off as desired. If no parameter is supplied to the command, it toggles the current state of the heading display.

SQL> set heading off;

SQL> select count(*) from employee;

          42 


SQL> set heading on;

SQL> select count(*) from employee;

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

Set List

SQL> SET LIST [ON | OFF];

This command controls how the data returned by a select statement will be displayed. The default setting to to display the data in tabular form with optional column headings at the top of each 'page'. Setting the list mode to on results in a different format where each column heading is displayed on the left and the column data on the right. This repeats for each and every row returned by the query.

As with other commands, not providing a value to the command results in a toggle of the current setting.

SQL> set list off;

SQL> select emp_no, first_name, last_name, salary
CON> from employee;

 EMP_NO FIRST_NAME      LAST_NAME                           SALARY 
======= =============== ==================== ===================== 
      2 Robert          Nelson                           105900.00 
      4 Bruce           Young                             97500.00 
      5 Kim             Lambert                          102750.00 
      8 Leslie          Johnson                           64635.00
...


SQL> set list on;

SQL> select emp_no, first_name, last_name, salary
CON> from employee;

EMP_NO                          2
FIRST_NAME                      Robert
LAST_NAME                       Nelson
SALARY                          105900.00

EMP_NO                          4
FIRST_NAME                      Bruce
LAST_NAME                       Young
SALARY                          97500.00
...

Set Names

SQL> SET NAMES [character_set];

This command defines the character set to be used in subsequent database transactions. If the default database charcter set is not NONE, then in situations where the client uses a different character set to the database, it is possible to suffer from data corruption as some character sets cannot convert some characters to a suitable character in another character set.

If you don't pass a character set, the default will be to use the NONE character set.

You can determine a list of the valid character sets to use with the following query:

SQL> set width RDB$CHARACTER_SET_NAME 30;

SQL> select RDB$CHARACTER_SET_NAME
CON> from RDB$CHARACTER_SETS
CON> order by 1;

RDB$CHARACTER_SET_NAME         
============================== 
ASCII                          
BIG_5                          
CP943C                         
CYRL                           
DOS437                         
...                   
ISO8859_1                      
ISO8859_13                     
...                    
NONE                           
OCTETS                         
...
UTF8                           
...
WIN1258

Set Plan

SQL> SET PLAN [ON | OFF];

This command determines whether or not isql will display the plan it used to access the data for each statement executed. The isql default is never to display the plan. As with many other commands, not providing a parameter toggles the current state.

SQL> set plan on;

SQL> select emp_no, first_name, last_name
CON> from employee
CON> where emp_no = 107;

PLAN (EMPLOYEE INDEX (RDB$PRIMARY7))

 EMP_NO FIRST_NAME      LAST_NAME            
======= =============== ==================== 
    107 Kevin           Cook                


SQL> update employee
CON> set first_name = 'Norman'
CON> where last_name = 'Cook';

PLAN (EMPLOYEE INDEX (NAMEX))


SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

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

The execution plan is displayed before the output from a select statement.

Set Planonly

SQL> SET PLANONLY [ON | OFF];

This command prevents Firebird from actually executing the SQL statement and instead, simply shows the plan that it would use to access the data. This command relies on the set plan command. If set plan off had been executed, this command would have no effect, so turning planonly on has the additional effect of executing set plan on implicitly. Executing set planonly off does not implicitly execute set plan off.

SQL> set planonly on;

SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

As before, not supplying a parameter toggles the current setting.

Set SQLDA_Display

This is a hidden command which is not mentioned in the output from the help set command. It displays internal details about the SQL statements being executed by isql. This used to be only available in a special debug build, but since version 2.0, it is available in isql.

SQL> set sqlda_display on;

SQL> select count(*) from employee;

INPUT  SQLDA version: 1 sqln: 10 sqld: 0

OUTPUT SQLDA version: 1 sqln: 20 sqld: 1
01: sqltype: 496 LONG                    sqlscale: 0 sqlsubtype: 0 sqllen: 4
  :  name: (5)COUNT  alias: (5)COUNT
  : table: (0)  owner: (0)

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

Note that when you run the help set or set commands, no information about this command will be displayed.

Set SQL Dialect

SQL> SET SQL DIALECT {1 | 2 | 3};

This command specifies the Firebird SQL dialect to which the client session is to be changed. If the session is currently attached to a database of a different dialect to the one specified in the command, a warning is displayed. The values permitted are:

  • 1 - which sets the client connection to SQL dialect 1

  • 2 - which sets the client connection to SQL dialect 2.

  • 3 - which sets the client connection to SQL dialect 3.

See Dialects for details of the differences between the three dialects.

SQL> set sql dialect 1;
WARNING: Client SQL dialect has been set to 1 when 
connecting to Database SQL dialect 3 database.
...
SQL> set sql dialect 3;
SQL>

The warning in the above example has had to be split over two lines in order to have it fit on the page. Normally, it consist of a single line.

Set Stats

SQL> SET STATs [ON | OFF];

This command determines whether or not isql should display various statistics about each SQL command executed. As usual, failing to pass a parameter results in the current setting being toggled.

SQL> set stats on;

SQL> select count(*) from employee;

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

Current memory = 10094216
Delta memory = 16
Max memory = 10227608
Elapsed time= 0.00 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 92

Set Time

SQL> SET TIME [ON | OFF];

This command applies to dialect 1 databases only. It causes the time portion to be displayed or not, when the selected data is a column defined with the DATE data type. It has no effect in other dialects.

Set Term

SQL> SET TERM new_terminator current_terminator

This command changes the default statement terminator from a semi-colon to something else as defined in the passed string. This is mostly useful when you are about to enter a string of SQL statements making up a procedure, for example, or a trigger. Isql would attempt to execute each statement when it sees a termianting semi-colon, so you would change the terminator first, then enter the required code. When complete, you would change it back, but when doing so, you must remember to terminate the set term command with the current terminating character(s).

When first started, isql uses the semi-colon as the default terminator.

You can, if desired, simply change the terminator because you prefer something other than a semi-colon. You don't have to be writing procedures in order 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

See the section on the terminator for full details.

Set Transaction

This is another hidden command which is not mentioned in the output from the help set command.

There is a default transaction started for you when you use isql. When you commit or rollback in isql, the default transaction ends, and a new default transaction begins. These transactions are:

  • READ WRITE - meaning that any SQL statment that is executed may make changes in the database.

  • WAIT - meaning that if a row in a table is currently locked by another session, the execution of the statement will appear to hang until the other session either commits or rolls back.

  • SNAPSHOT - meaning that this transaction will be guaranteed a non-volatile view of the data and will be unaffected by any changes made and committed in any other transactions that take place while this one remains unfinished by a commit or rollback.

A full explanation of transactions is beyond the scope of this manual. For more information see The Firebird Book by Helen Borrie.

Set Warnings

SQL> SET {WARNINGS | WNG} [ON | OFF];

This command specifies whether warnings are to be output. A few examples for which isql issues warnings are:

  • SQL statements with no effect.

  • Pending database shutdown.

  • API calls that may be replaced in future versions of Firebird.

  • Expressions that may cause differing results in different versions of Firebird.

  • In Firebird 1.0, SQL statements with ambiguous join specifications. More recent Firebird versions will raise an exception rather than a warning.

As with many of the set commands, set warnings acts as a toggle if no parameter is supplied.

Set Width

Normally the width of a character column in a table defines the width of the output when that column is selected. Using the set width command allows the user to define a wider or narrower output column width.

The format of the command is set width column_or_alias width; The setting remains until changed to a new width, or until cancelled by the set width column_or_alias; command - no width supplied means use the default width setting for this column.

The following example shows the width of the last_name column being amended. The first SELECT shows the default setting which is a wdith of 20 characters (count the '=' in the headings) which is the definition of the last_name column in the employee table. The second shows the width being reduced to 10 characters.

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME            
======= ==================== 
     34 Baldwin              
    105 Bender               
     28 Bennet               
     83 Bishop               
    109 Brown                


SQL> set width last_name 10;

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME  
======= ========== 
     34 Baldwin    
    105 Bender     
     28 Bennet     
     83 Bishop     
    109 Brown      

Emp_no is a smallint data type. Unfortunately, it doesn't appear to be possible to change the width on non-character columns like integer, smallint etc. The set width emp_no 10; command, for example, has no effect, as shown below, which also demonstrates turning off a previous width setting for the last_name column:

SQL> set width last_name;

SQL> set width emp_no 10;

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME            
======= ==================== 
     34 Baldwin              
    105 Bender               
     28 Bennet               
     83 Bishop               
    109 Brown                
Prev: Isql CommandsFirebird Documentation IndexUp: Isql - Interactive SQLNext: Isql Show commands
Firebird Documentation IndexIsql - Interactive SQL → Isql Set Commands