The Script Executive can be used to view, edit and execute SQL scripts. It can be started from the IBExpert Tools menu, using the respective icon in the Tools toolbar or [Ctrl + F12]. It is used for SQLs covering several rows. The Script Executive can both read and execute scripts.

Although Firebird/InterBase® can also process such procedure definitions in the SQL Editor, it is recommended using the Script Executive for more complex work, as it can do much more than the SQL Editor. There is a wealth of script language extensions including conditional directives, and it can also be used for executing multiple scripts from a single script. The main advantage of the Script Executive is that it displays all DDL and DML scripts of a connected database.

IBExpert version 2020.05.10 introduced support of the SET DBTRIGGERS ON/OFF directive. If DBTRIGGERS is OFF all consequent CONNECT/RECONNECT statements will be executed with the isc_dbp_no_db_triggers flag. Default value is ON. The “Add CONNECT statement” feature adds SET DBTRIGGERS OFF automatically if the “Supress database triggers” option is specified in a database registration record.

The Script Explorer (the left-hand panel) displays all database objects, as well as IBEBlocks and Firebird blocks, used in the current script in a tree structure. It even allows you to find individual statements rapidly by clicking on the object in the tree. The Script Explorer can be blended in and out using the respective icon on the Script Executive toolbar. SQL scripts can be loaded from and saved to file if wished.

Objects may be dragged and dropped from the DB Explorer and SQL Assistant into the code editor window. When an object node(s) is dragged from the DB Explorer or SQL Assistant, IBExpert will offer various versions of text to be inserted into the code editor. It is also possible to customize the highlighting of variables. Use the IBExpert Options menu item, Editor Options / Colors to choose color and font style for variables.

Complete scripts can be transferred from the SQL Editor or extracted directly from the Extract Metadata Editor into the Script Executive using the relevant menu items (please refer directly to these subjects for further details).

Please note that the Script Executive always uses the default client library specified in the IBExpert Options menu item Environment Options / Preferences under Default Client Library, unless it is overridden using the SET CLIENTLIB command.

DML statements can be displayed in the Script Explorer tree. Simply right-click to open the context-sensitive menu and check/uncheck as wished.

The toolbar menu item, Execute charset offers the options ANSI, UTF8 or Ask me. IBExpert offers full Unicode support. The internal representation of all texts in the code editors is Windows Unicode (UTF-16LE, two bytes per character). This allows you to use multilingual characters in your procedures, queries, database object descriptions etc., if you use the UTF8 character set when connecting to your database.

There is also support for Before/After metadata change events. Execution of DDL statements will fire before and after metadata event blocks if they are assigned and the Use current connection option is ON.

The following features are also available for the Script Executive and IBEScript:

• When no password and/or user name are specified in the CONNECT or CREATE DATABASE statements, a login dialog will appear.
• Case-sensitive user and role names (Firebird 3, Firebird 4) in CREATE DATABASE and CONNECT statements should be quoted with double quotes, e.g. CONNECT … USER “SYSDBA” ROLE “Admin_Role” … ;
• It is now also possible to change the connection character set (SET NAMES) and garbage collection option (SET GARBAGE_COLLECT) before the RECONNECT statement. Any SET commands mentioned which are followed by a RECONNECT statement will affect the new connection.

The IBECurrentScriptPath environment variable is initialized internally before executing the script, and its value represents a path to the current script file (if the script is loaded/executed from a file). Typically you can use this variable in INPUT, SET BLOBFILE and SET PARAMFILE directives to specify where IBExpert/IBEScript should search for required files.

Example:

INPUT 'Inputs\data.sql';
...
SET BLOBFILE 'Data\blobs.lob';

When you're working with a database using the UTF8 character set IBExpert performs automatic conversion from UTF8 to Windows Unicode (when opening) and back (when you compile). This applies to Firebird 2.1 and 2.5 databases. For other databases you need to enable this behavior manually (if you really need this!) by flagging the Do NOT perform conversion from/to UTF8 checkbox in the Database Registration Info. As a rule, IBExpert knows when it must convert strings from Windows Unicode to UTF8 but sometimes it is necessary to specify the conversion type manually. This allows you to specify the necessary charset manually.

The Script page includes other features, such as code completion (please refer to Code Insight for details) and [F1] context-sensitive keyword help - both familiar from the SQL Editor. The SQL Editor menu can be called by right-clicking in the script area.

The Advanced progess option: the progress bar is shown on the Script Executive toolbar, and if this option is enabled (default), IBExpert roughly calculates and displays the remaining time using known script size and time already spent, and scrolls the script text editor accordingly to the part of the script currently being executed.

Following statement execution, the Script page displays any errors highlighted in red. Any error messages which may appear on the Messages page may be consulted in the Firebird 2.1 error codes documentation. Using the

icon, the script can be executed step by step.

Any errors appearing in the lower Messages box may be saved to file if wished, using the right-click menu item Save Messages Log …

The Statements page displays a list of individual statements in grid form:

These statements may be removed from the script simply by unchecking the left-hand boxes. One, several or all statements may be checked or unchecked using the right-click menu. Breakpoints can be specified or removed simply by clicking (or using the space bar) to the left of the selected statement in the BP column.

back to top of page

Currently the following options are available which apply to the current instance of the Script Executive:

• Abort script on error
• Rollback on abort
• Fire event blocks: this affects only the current instance of the Script Executive.

back to top of page

If you need to insert Unicode text strings into your database, you need to knowm what character set is used for the connection. If a non-unicode charset is used you should not convert your strings to unicode as the server will do this. For example:

But if you connect to your database with a UTF8 charset you should use unicode strings instead:

To convert strings from/to unicode use the corresponding items in the code editor's popup menu:

back to top of page

Simply use the following syntax:

connect 'server:c:\my_db.gdb' ...;

input 'c:\my_scripts\f2.sql';
input 'c:\my_scripts\f1.sql';
input 'c:\my_scripts\f3.sql';

back to top of page

The following is an example illustrating the creation of multiple csv files from a script:

shell del C:\list.dat nowait;    --deleting the old file 
shell del C:\*.csv nowait;    --deleting the old csv files

connect 'localhost:C:\employee.fdb' user 'SYSDBA' password 'masterke';  
--connect to employee example database

output 'C:\list.dat';    --record the following result as a simple text file, based on each unique employee, 
we create a new output ...;select ... ;output; line in the dat file
 
SELECT distinct 
'OUTPUT C:\'||EMPLOYEE.last_name||'.csv delimiter '';'';'|| 
'SELECT distinct EMPLOYEE.last_name, customer.customer,customer.phone_no '||
'FROM SALES INNER JOIN CUSTOMER ON (SALES.CUST_NO = CUSTOMER.CUST_NO) '||
'INNER JOIN EMPLOYEE ON (SALES.SALES_REP = EMPLOYEE.EMP_NO) where 
EMPLOYEE.last_name=XXXXXX||EMPLOYEE.last_name||''';'||
'OUTPUT;'
FROM SALES INNER JOIN CUSTOMER ON (SALES.CUST_NO = CUSTOMER.CUST_NO) INNER JOIN EMPLOYEE ON 

(SALES.SALES_REP = EMPLOYEE.EMP_NO);
 
output;      --close the dat file 
input 'C:\list.dat';  --execute them 

The data file is created automatically.

The outer query gets one record for each employee, in the inner select, all phone numbers for the employees if customers are selected.

Please also refer to IBEBlock examples: Importing data from a CSV file.

back to top of page

Script language extensions are unique to IBExpert, and offer the developer a number of additional language options. These include, among others, conditional directives, DESCRIBE database objects, as well as SET, SHELL, INSERTEX, OUTPUT and RECONNECT.

Conditional directives control conditional execution of parts of the script. Four types of conditional directives are supported:

• $IFEXISTS
• $IFIBEVERSION
• $IFNOTEXISTS ($IFNEXISTS)
• $ELSE
• $ENDIF

This tests the existence of the specified database object or data and executes the following block of the script if the object or data do exist in the database.

Syntax

1. {$IFEXISTS DOMAIN|TABLE|VIEW|TRIGGER|PROCEDURE|
      EXCEPTION|GENERATOR|UDF|ROLE object_name}
2. {$IFEXISTS select_statement}

Example

The following script drops the exception InvalidUserID if it exists in the database:

{$IFEXISTS EXCEPTION "InvalidUserID"}

   DROP EXCEPTION "InvalidUserID";

The next script alters a procedure:

{$IFEXISTS SELECT RDB$PROCEDURE_NAME
           FROM RDB$PROCEDURES
           WHERE RDB$PROCEDURE_NAME = 'GETDBVER'}

   ALTER PROCEDURE GETDBVER
   RETURNS (
       VER INTEGER)
   AS
   begin
     ver = 2;
     suspend;
   end;

back to top of page

The $IfIBEVersion conditional directive allows you to check the current version of IBExpert/IBEScript.

Syntax

        {$IfIBEVersion <relational_operator> <version_number>}
      ...      
      ...                <relational_operator> = < | > | =< | >= | = | <> |      
<version_number> - version number string without quote char.

Example

   {$IfIBEVersion < 2007.7.16.0}
     execute ibeblock
     as
     begin
       ibec_ShowMessage('Please, update your version of IBExpert/IBEScript!');
     end;
     quit;            

back to top of page

This tests the existence of the specified database object or data and executes the following block of the script if the object or data does not exist in the database.

Syntax

1. {$IFNOTEXISTS DOMAIN|TABLE|VIEW|TRIGGER|PROCEDURE|
      EXCEPTION|GENERATOR|UDF|ROLE object_name}
2. {$IFNOTEXISTS select_statement}

Example

The following script creates a table CUSTOMERS if there is no such table in the database:

{$IFNOTEXISTS TABLE CUSTOMERS}

   CREATE TABLE CUSTOMERS (
     ID          INTEGER NOT NULL PRIMARY KEY,
     FIRST_NAME  VARCHAR(30),
     MIDDLE_NAME VARCHAR(30),
     LAST_NAME   VARCHAR(30));

The next script creates an exception:

{$IFNOTEXISTS SELECT RDB$EXCEPTION_NAME
              FROM RDB$EXCEPTIONS
              WHERE RDB$EXCEPTION_NAME = 'InvalidUserID'}

   CREATE EXCEPTION "InvalidUserID" 'Invalid User Identifier!';

back to top of page

Switches between executing and ignoring the script part are delimited by the previous {$IFEXISTS} or {$IFNOTEXISTS} and the next {$ENDIF}.

Syntax

Example

The following script tests the existence of domain DOM_BOOL in the database. If domain DOM_BOOL cannot be found in the database it will be created. If domain DOM_BOOL already exists in the database it will be altered.

back to top of page

Ends the conditional execution initiated by the last {$IFEXISTS} or {$IFNOTEXISTS} directive.

Syntax

Example

The following script creates a generator:

back to top of page

This example illustrates the use of conditional directives for upgrading databases. Let's assume there is an initial version of your database (version 1):

The next script will upgrade a database of any version < 4 to version 4.

back to top of page

The IBExpert-specific DESCRIBE statement will be translated to the Firebird COMMENT ON statement instead of UPDATE RDB$xxx when executing scripts against Firebird 3 databases.

This changes a domain description.

Syntax

DESCRIBE DOMAIN domain_name 'description';

Description

DESCRIBE DOMAIN changes the description of an existing domain domain_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in DB$FIELDS connected with the specified domain name.

Actually the following statement is executed:

UPDATE RDB$FIELDS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$FIELD_NAME = 'domain_name'

where DESC parameter is filled with the description.

Example

DESCRIBE DOMAIN DOM_BOOL
'Boolean value:
 0 - FALSE
 1 - TRUE';

back to top of page

This changes an exception's description.

Syntax

DESCRIBE EXCEPTION exception_name 'description';

Description

DESCRIBE EXCEPTION changes the description of an existing exception exception_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$EXCEPTIONS connected with the specified exception. Actually the following statement is executed:

UPDATE RDB$EXCEPTIONS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$EXCEPTION_NAME = 'exception_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE EXCEPTION MISSING_USER
'There is no such user!';

back to top of page

This changes a column description.

Syntax

DESCRIBE FIELD column_name TABLE table_name 'description';

Description

DESCRIBE FIELD changes the description of an existing column column_name of table table_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$RELATION_FIELDS connected with the specified column and table names. Actually the following statement is executed:

UPDATE RDB$RELATION_FIELDS
SET RDB$DESCRIPTION = :DESC
WHERE (RDB$RELATION_NAME = 'table_name') AND
      (RDB$FIELD_NAME = 'column_name')

where the DESC parameter is filled with the description.

Example

DESCRIBE FIELD FULL_USER_NAME TABLE USERS
'Full user name.
 Computed, concatenation of FIRST_NAME, MIDDLE_NAME and LAST_NAME';

back to top of page

This changes an UDF description.

Syntax

DESCRIBE FUNCTION function_name 'description';

Description

DESCRIBE FUNCTION changes the description of an existing user-defined function function_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$FUNCTIONS connected with the specified function. Actually the following statement is executed:

UPDATE RDB$FUNCTIONS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$FUNCTION_NAME = 'function_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE FUNCTION COMPARE_BLOBS
'Compares two blob values and returns 1
if both values are equal. In other case returns 0';

back to top of page

This changes a procedure parameter description.

Syntax

DESCRIBE PARAMETER parameter_name PROCEDURE procedure_name 'description';

Description

DESCRIBE PARAMETER changes the description of an existing parameter parameter_name of a specified stored procedure procedure_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$PROCEDURE_PARAMETERS connected with the specified parameter and procedure names. Actually the following statement is executed:

UPDATE RDB$PROCEDURE_PARAMETERS
SET RDB$DESCRIPTION = :DESC
WHERE (RDB$PROCEDURE_NAME = 'procedure_name') AND
      (RDB$PARAMETER_NAME = 'parameter_name')

where the DESC parameter is filled with the description.

Example

DESCRIBE PARAMETER USER_ID PROCEDURE CALC_TRAFFIC
'User ID';

back to top of page

This changes a stored procedure description.

Syntax

DESCRIBE PROCEDURE procedure_name 'description';

Description

DESCRIBE PROCEDURE changes the description of an existing stored procedure procedure_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$PROCEDURES connected with the specified procedure. Actually the following statement is executed:

UPDATE RDB$PROCEDURES
SET RDB$DESCRIPTION = :DESC
WHERE RDB$PROCEDURE_NAME = 'procedure_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE PROCEDURE CALC_TRAFFIC
'Calculates the summary traffic';

back to top of page

This changes a table description

Syntax

DESCRIBE TABLE table_name 'description';

Description

DESCRIBE TABLE changes the description of an existing table table_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column in RDB$RELATIONS connected with the specified table. Actually following statement is executed:

UPDATE RDB$RELATIONS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$RELATION_NAME = 'table_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE TABLE CUSTOMERS
'Customers of our excellent application';

back to top of page

This changes a trigger description

Syntax

DESCRIBE TRIGGER trigger_name 'description';

Description

DESCRIBE TRIGGER changes the description of an existing trigger trigger_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column of RDB$TRIGGERS connected with the specified table. Actually the following statement is executed:

UPDATE RDB$TRIGGERS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$TRIGGER_NAME = 'trigger_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE TRIGGER USERS_BI
'Generates an unique identifier';

back to top of page

This changes a view description

Syntax

DESCRIBE VIEW view_name 'description';

Description

DESCRIBE VIEW changes the description of an existing view view_name. When the IBExpert Script Executive executes this statement it modifies the value of the RDB$DESCRIPTION column of RDB$RELATIONS connected with the specified view. Actually the following statement is executed:

UPDATE RDB$RELATIONS
SET RDB$DESCRIPTION = :DESC
WHERE RDB$RELATION_NAME = 'view_name'

where the DESC parameter is filled with the description.

Example

DESCRIBE VIEW ALL_USERS
'Just all users...:)';

back to top of page

This imports data from a CSV-file into a database table.

Syntax

INSERTEX INTO table_name [(columns_list)]
   FROM CSV file_name
   [SKIP n]
   [DELIMITER delimiter_char]

If this argument isn't specified IBExpert will use a colon as a delimiter.

Description

INSERTEX imports data from a CSV-file into a database table. Values within the CSV-file must be separated with a colon CHAR or any other char. In the latter case it is necessary to specify a delimiter CHAR using the DELIMITER argument. It is also possible to specify non-print characters as a delimiter. For example, if values are separated with tab char (ASCII value $09) it may be specified as DELIMITER #9 or DELIMITER $9.

To ignore unwanted quotes use the QUOTECHAR '“' option.

If a table table_name is missing in the database, it will be created automatically. In this case the number of columns in the newly created table will be equal to the number of values in the first line of the CSV-file. Columns will be named F_1, F_2 etc. The data type of each column is VARCHAR(255).

If the columns_list isn't specified IBExpert will insert data from the very first column. Otherwise data will only be inserted into specified columns. It is possible to skip the first several lines of the CSV-file using the SKIP argument. This may be useful if the first line contains column captions or is empty.

It is also possible to use the INSERTEX command in the SQL Editor.

Examples

Let's consider the use of INSERTEX in the following examples. Assume there is a CSV-file with the following data, delimited with a colon:

C:\Mydata.csv
=======================================================
ID:FIRST_NAME:LAST_NAME:SEX
1:John:Doe:M
2:Bill:Gates:M
3:Sharon:Stone:F
4:Stephen:King:M
=======================================================

The following INSERTEX statement creates a table PEOPLE (if it doesn't already exist) and fills it with data from C:\Mydata.csv:

INSERTEX INTO PEOPLE FROM CSV 'C:\Mydata.csv' DELIMITER ':';

The structure and contents of PEOPLE after the data import are shown below:

The following INSERTEX statement is almost identical to the one above, but here the first line of the CSV-file has been skipped:

INSERTEX INTO PEOPLE FROM CSV 'C:\Mydata.csv' DELIMITER ':' SKIP 1;

The structure and content of the PEOPLE table after import is shown below:

In the next example the PEOPLE table is created first, and then subsequently populated with the data from C:\Mydata.csv:

CREATE TABLE PEOPLE (
    ID         INTEGER NOT NULL,
    FIRST_NAME VARCHAR(30),
    LAST_NAME  VARCHAR(30),
    SEX        CHAR(1));

INSERTEX INTO PEOPLE FROM CSV 'C:\Mydata.csv' DELIMITER ':' SKIP 1;

Below the structure and content of the PEOPLE table after import:

In the next example only three columns (ID, FIRST_NAME and LAST_NAME) are affected:

CREATE TABLE PEOPLE (
    ID         INTEGER NOT NULL,
    FIRST_NAME VARCHAR(30),
    LAST_NAME  VARCHAR(30),
    SEX        CHAR(1));

INSERTEX INTO PEOPLE (ID, FIRST_NAME, LAST_NAME)
    FROM CSV 'C:\Mydata.csv'
    DELIMITER ':' SKIP 1;

The structure and content of the PEOPLE table after import can be seen below:

back to top of page

This redirects the output of SELECT statements to a named file.

Syntax

OUTPUT [filename [DELIMITER delim_char]
                 [QUOTECHAR 'quote_char']
                 [TIMEFORMAT 'time_format']
                 [DATEFORMAT 'date_format']
                 [DECIMALSEPARATOR 'dec_sep']
                 [NULLS]
                 [FIELDNAMES]
                 [ASINSERT | AsUpdateOrInsert [INTO table]]

Description

The OUTPUT operator is intended for redirecting the output of SELECT statements in an external file. With the help of the given operator it is possible to export the data easily into a file with separators or with a fixed column position. OUTPUT without parameters closes the file which was opened with the previous OUTPUT command, and resets all export customizations to default.

If ASINSERT is not specified, blob fields are ignored when outswapping the data. Using ASINSERT even blob values are exported, i.e. an additional file with the extension .lob is created, in which all blob fields are stored.

While outputting into SQL script (ASINSERT is specified) DELIMITER, QUOTECHAR, NULLS and FIELDNAMES arguments are ignored.

Examples

The following script creates a MyData.txt file in the current directory and outputs the data of the SELECT into it, with a fixed column position format. If MyData.txt file already exists in the current directory, the data will be appended to it.

OUTPUT MyData.txt;
SELECT * FROM MY_TABLE;
OUTPUT;

In the next example the data will be exported in the comma-separated values (CSV) format:

OUTPUT 'C:\MyData\MyData.csv' DELIMITER ';'
                              FIELDNAMES
                              QUOTECHAR '"'
                              DECIMALSEPARATOR '.';
SELECT * FROM MY_TABLE;
OUTPUT;

In the following script the data will be exported into SQL script as a set of INSERT operators:

OUTPUT 'C:\MyScripts\Data.sql' ASINSERT INTO "MyTable";
SELECT * FROM MY_TABLE;
OUTPUT;

The next example illustrates usage of the OUTPUT statement together with SHELL.

/* First create a folder C:\MyData*/
SHELL MKDIR C:\MyData;

/* Try to delete mydata.csv */
SHELL DEL C:\MyData\mydata.csv;

/* Redirect output of SELECTs into mydata.csv */
OUTPUT C:\MyData\mydata.csv DELIMITER ';'
                            DATEFORMAT 'MMMM-dd-yyyy'
                            TIMEFORMAT 'hh:nn:ss.zzz'
                            QUOTECHAR '"';

SELECT * FROM MY_TABLE;

/* Close C:\MyData/mydata.csv */
OUTPUT;

/* Try to open just created CSV-file with Windows Notepad */
SHELL notepad.exe C:\MyData\mydata.csv NOWAIT;

/* Try to open C:\MyData\mydata.csv with the application
   associated with CSV files */
SHELL C:\MyData\mydata.csv NOWAIT;

Example using the AsUpdateOrInsert option:

  OUTPUT 'C:\MyScripts\data.sql' ASUPDATEORINSERT;
  SELECT * FROM MYTABLE ORDER BY ID;
  OUTPUT;
  COMMIT;

Example using the OctetsAsHex option:

  OUTPUT 'D:\MyData\data.sql' AS INSERT OctetsAsHex;
  SELECT * FROM MYTABLE
  OUTPUT;

Extended syntax of OUTPUT command:

1.

   output 'E:\data.sql'
   as insert into mytable commit after 1000;
   select * from IBE$$TEST_DATA where F_INTEGER < 3000;
   output;

2.

   output 'E:\data.sql'
   as reinsert into mytable
   commit after 2000;
   select * from IBE$$TEST_DATA where F_INTEGER < 3000;
   output;

3.

   output 'E:\data.sql'
   as execute procedure myproc;
   select * from IBE$$TEST_DATA where F_INTEGER < 3000;
   output;

The ASINSERT option is available for compatibility.

back to top of page

RECONNECT closes the current connection and creates a new one with the same parameters (database, user name, password etc.).

Syntax

RECONNECT; 

back to top of page

IBExpert has introduced the new REINSERT statement. Directly following an INSERT it is possible to perform further INSERTs with new contents.

back to top of page

SET BLOBFILE is a special extension of script language that allows IBExpert's Script Executive to execute scripts containing references to blob field values.

IBExpert uses an original mechanism to extract values of blob fields into a script. This allows you to store the entire database (metadata and data) into script files and execute these scripts with IBExpert. A small example illustrates the method used to extract blob values.

For example, your database has a table named COMMENTS:

CREATE TABLE COMMENTS (
   COMMENT_ID INTEGER NOT NULL PRIMARY KEY,
   COMMENT_TEXT BLOB SUBTYPE TEXT);

This table has three records:

If the Extract BLOBs option is not checked, you will receive the following script:

CREATE TABLE COMMENTS (
   COMMENT_ID INTEGER NOT NULL PRIMARY KEY,
   COMMENT_TEXT BLOB SUBTYPE TEXT);

INSERT INTO COMMENTS (COMMENT_ID) VALUES (1);
INSERT INTO COMMENTS (COMMENT_ID) VALUES (2);
INSERT INTO COMMENTS (COMMENT_ID) VALUES (3);

… and, of course, you will lose your comments if you restore your database from this script.

But if the Extract BLOBs option is checked IBExpert will generate quite a different script:

SET BLOBFILE 'C:\MY_SCRIPTS\RESULT.LOB'; 

CREATE TABLE COMMENTS (
   COMMENT_ID INTEGER NOT NULL PRIMARY KEY,
   COMMENT_TEXT BLOB SUBTYPE TEXT);

INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (1, h0000000_0000000D);
INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (2, NULL);
INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (3, h000000D_0000000F);

Also IBExpert generates a special file with the extension .lob where blob values are stored. In the current example result.lob will be 28 bytes long and its contents will be the first commentAnother comment.

back to top of page

This defines the client library to be used while executing a script.

Syntax

SET CLIENTLIB file_name;

Description

SET CLIENTLIB defines client library which will be used while executing a script. The default client library is gds32.dll.

Example

SET CLIENTLIB 'C:\Program Files\Firebird\Bin\fbclient.dll';

back to top of page

This command enables or disables the login prompt dialog when the user name and/or password within CONNECT or CREATE DATABASE is not specified. The SET LOGIN PROMPT command is useful when you use trusted authentication and don't need a login prompt dialog. The default value is ON.

Example

SET NAMES UTF8;
SET LOGIN PROMPT OFF;
CONNECT ...;
...

back to top of page

PARAM file is an ini-file with param values.

For example, if your script contains some parameterized INSERT/UPDATE/DELETE statements you can define parameter values in an external file (params file):

param1=12-FEB-2003
param2=John Doe
param3=35
...

When IBEScript finds a query with parameters it looks for the values of these parameters in the specified params file.

back to top of page

The SET TRPARAMS command allows you to specify your own parameters of the script transaction instead of default ones.

Syntax

   SET TRPARAMS '<params>';
   where <params> is a list of transaction parameters separated by commas or
   spaces.
        Example:
        SET TRPARAMS 'isc_tpb_concurrency, isc_tpb_nowait';

Note: If the current transaction is active SET TRPARAMS will commit it and, following that, change the transaction parameters.

back to top of page

This allows execution of an operating system command.

Syntax

SHELL os_command [NOWAIT];

Description

The SHELL operator tries to execute the command os_command. If NOWAIT is not specified, the further execution of a script stops before completion of the process created by SHELL operator. Otherwise script execution will be continued immediately after beginning the execution of the command os_command.

Examples

The following script tries to create a folder MyFolder in the current directory:

SHELL mkdir MyFolder;

The following example shows the use of the SHELL command to start Notepad.exe and the loading of C:\MyTexts\Shedule.txt file in it. It is necessary to use NOWAIT here, otherwise it is not possible to execute the script further, and it will be impossible to resume work in IBExpert until the Notepad is closed.

SHELL "notepad.exe C:\MyTexts\Shedule.txt" NOWAIT;

The next example illustrates the use of the SHELL statement together with OUTPUT.

/* First create a folder C:\MyData*/
SHELL MKDIR C:\MyData;

/* Try to delete mydata.csv */
/>SHELL DEL C:\MyData\mydata.csv;

/* Redirect output of SELECTs into mydata.csv */
OUTPUT C:\MyData\mydata.csv DELIMITER ';'
                            DATEFORMAT 'MMMM-dd-yyyy'
                            TIMEFORMAT 'hh:nn:ss.zzz'
                            QUOTECHAR '"';


SELECT * FROM MY_TABLE;

/* Close C:\MyData\mydata.csv */
OUTPUT;

/* Try to open just created CSV-file with Windows Notepad */
SHELL notepad.exe C:\MyData\mydata.csv NOWAIT;

/* Try to open C:\MyData\mydata.csv with the application
   associated with CSV files */
SHELL C:\MyData\mydata.csv NOWAIT;