SET Commands - Firebird

The SET commands enable you to view and change things about the isql environment. Some are available in scripts.

SET AUTODDL specifies whether DDL statements are committed automatically after being executed or committed only after an explicit COMMIT. It is available in scripts.

SQL> SET AUTODDL [ON | OFF] ; /* default is ON */

SET AUTODDL

SET AUTO (with no argument) simply toggles AUTODDL on and off.

Example:

...
SQL> SET AUTODDL OFF ;
SQL> CREATE TABLE WIZZO (x integer, y integer) ;
SQL> ROLLBACK; /* Table WIZZO is not created */
...
SQL>SET AUTO ON ;
SQL> CREATE TABLE WIZZO (x integer, y integer) ;
SQL> /* table WIZZO is created */

SET BLOBDISPLAY specifies both the subtype of BLOB to display and whether BLOB data should be displayed.

SQL> SET BLOBDISPLAY [ n |ALL |OFF ];
SET BLOB is a shortened version of the same command.

SET BLOBDISPLAY

Example:

...
SQL> SET BLOBDISPLAY OFF ;
SQL> SELECT PROJ_NAME, PROJ_DESC FROM PROJECT ;
SQL> /* rows show values for PROJ_NAME and Blob ID */
...
SQL>SET BLOB 1 ;
SQL> SELECT PROJ_NAME, PROJ_DESC FROM PROJECT ;
SQL> /* rows show values for PROJ_NAME and Blob ID */
SQL> /* and the blob text appears beneath each row */

SET COUNT
toggles off/on whether to display the number of rows retrieved by
queries.

SQL> SET COUNT [ON | OFF] ;

SET BLOBDISPLAY

Example:

...
SQL> SET COUNT ON ;
SQL> SELECT * FROM WIZZO WHERE FAVEFOOD = 'Pizza' ;
SQL> /* displays the data, followed by */
...
40 rows returned

SET ECHO

toggles off/on whether commands are displayed before being executed. The default is ON but you might want to toggle it to OFF if sending your output to a script file.

SQL> SET ECHO [ON | OFF] ; /* default is ON */

SET ECHO

Example script wizzo.sql:

...
SET ECHO OFF;
SELECT * FROM WIZZO WHERE FAVEFOOD = 'Pizza' ;
SET ECHO ON ;
SELECT * FROM WIZZO WHERE FAVEFOOD = 'Sardines' ;
EXIT;
...
SQL > INPUT wizzo.sql ;
WIZTYPE FAVEFOOD
================ ====================
alpha Pizza
epsilon Pizza
SELECT * FROM WIZZO WHERE FAVEFOOD = 'Sardines' ;
WIZTYPE FAVEFOOD
================ ====================
gamma Sardines
lamda Sardines

SET NAMES

specifies the character set that is to be active in database transactions. This is very important if your database’s default character set is not NONE. If the client and database character sets are mismatched, you risk transliteration errors and storing wrong data if you use isql for performing updates or inserts or for searches (including searched updates and deletes).

SET NAMES is available in scripts.

SQL> SET NAMES charset ;

SET NAMES

Example in script:

...
SET NAMES ISO8859_1 ;
CONNECT 'HOTCHICKEN:/usr/firebird/examples/employee.gdb' ;

SET PLAN

specifies whether to display the optimizer’s query plan.

SQL> SET PLAN [ON|OFF ];

SET PLAN

As a shortcut, you can omit ON|OFF and just use SET PLAN as a toggle. Example in a script:

...
SET PLAN ON ;
SELECT JOB_COUNTRY, MIN_SALARY
FROM JOB
WHERE MIN_SALARY > 50000
AND JOB_COUNTRY = 'Sweden';
...
SQL> INPUT iscript.sql
PLAN (JOB INDEX (RDB$FOREIGN3,MINSALX,MAXSALX)
JOB_COUNTRY MIN_SALARY
================ =====================
Sweden 120550.00

SET PLANONLY specifies to prepare SELECT queries and display just the plan, without executing the actual query.

SQL> SET PLANONLY ON | OFF;

The command works as a toggle switch. The argument is optional.

SET SQL DIALECT sets 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 from the one specified in the command, a warning is displayed and you are asked whether you want to commit existing work (if any).

SQL> SET SQL DIALECT n ;

SET SQL DIALECT

Example:

SQL> SET SQL DIALECT 3 ;

SET STATS

specifies whether to display performance statistics following the output of a query.

SQL> SET STATS [ON |OFF];

SET STATS

You can omit ON|OFF and use just SET STATS as a toggle. Figure shows a typical statistics summary being displayed after the output of a query.

SET STATS example

SET STATS example

SET TERM specifies the character that will be used as the command or statement terminator, from the next statement forward. It is available in scripts. See the notes about this command earlier in this chapter.

SQL> SET TERM string ;

SET TERM

Example:

...
SET TERM ^^;
CREATE PROCEDURE ADD_WIZTYPE (WIZTYPE VARCHAR(16), FAVEFOOD VARCHAR(20))
AS
BEGIN
INSERT INTO WIZZO(WIZTYPE, FAVEFOOD)
VALUES ( :WIZTYPE, :FAVEFOOD) ;
END ^^
SET TERM ;^^
...

SET TIME specifies whether to display the time portion of a DATE value (dialect 1 only).

SQL> SET TIME [ON|OFF ];

SET TIME

Example:

SQL> SELECT HIRE_DATE FROM EMPLOYEE WHERE EMP_NO = 145;
HIRE_DATE
------------------
16-MAY-2004
...
SQL>SET TIME ON ;
SQL> SELECT HIRE_DATE FROM EMPLOYEE WHERE EMP_NO = 145;
HIRE_DATE
------------------
16-MAY-2004 18:20:00

SET WARNINGS

specifies whether warnings are to be output. SET WNG, used as a simple toggle, can be substituted.

SQL> SET WARNINGS [ON |OFF ];

SET WARNINGS

Exiting an Interactive isql Session

To exit the isql utility and roll back all uncommitted work, enter

SQL> QUIT;

To exit the isql utility and commit all work, enter

SQL> EXIT;

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics