Terminator character

<< SQL dialects | Firebird Interactive SQL Utility | isql prompts >>

Terminator character

The default terminator symbol for the Firebird database is the semicolon (;). Statements will only be executed if they end with a semicolon. However, you may use isql to change the symbol to any printable character, or characters, from the first 127 characters of the ASCII subset, by using the 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 tilda (~) enter the following code: 

 SQL> SET TERM ~ ;

You must terminate this command with the current terminator of course! 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, then 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 withing the code for the procedure itself, the terminator is the semicolon. However, outside of 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, 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

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

See also:
SET TERM
CREATE PROCEDURE

back to top of page
<< SQL dialects | Firebird Interactive SQL Utility | isql prompts >>