isql SET commands

<< isql commands | Firebird Interactive SQL Utility | isql SHOW commands >>

`isql SET` commands

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 AUTOdll 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 AUTOdll`

 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 AUTOdll 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 turn 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 default 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 character set is not NONE, then in situations where the client uses a different character set to the database, it is possible to suffer data corruption as some character sets cannot convert certain 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 SQL 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 statement 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 noncharacter 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