mwasoftware.co.uk

The TIBXScript Script Engine

TIBXScript script engine runs an SQL script from a file or stream. The text is parsed into SQL statements which are executed in turn. The intention is to be compatible with Firebird's ISQL command line utility, but with extensions:

  • All DML and DDL Statements are supported.

  • CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.

  • The following SET statements are supported:

    • SET SQL DIALECT

    • SET TERM

    • SET AUTODDL

    • SET BAIL

    • SET ECHO

    • SET COUNT

    • SET STATS

    • SET NAMES <character set>

  • New Command: RECONNECT. Performs a commit followed by disconnecting and reconnecting to the database.

  • Procedure Bodies (BEGIN .. END blocks) are self-delimiting and do not need an extra terminator. If a terminator is present, this is treated as an empty statement. The result is ISQL compatible, but does not require the use of SET TERM.

  • DML statements may have arguments in IBX format (e.g UPDATE MYTABLE Set data = :mydata). Arguments are valid only for BLOB columns and are resolved using the GetParamValue event. This returns the blobid to be used. A typical use of the event is to read binary data from a file, save it in a blob stream and return the blob id.

  • The simple XML formats for binary blob data (see 7.6.1) and array data (see 7.6.2) as export by TIBExtract (see 7.6) are supported.

  • C++ style comment lines.

Select SQL statements are not directly supported but can be handled by an external handler (OnSelectSQL event). If the handler is not present then an exception is raised if a Select SQL statement is found.

Properties:

Database

Link to a TIBDatabase component

Transaction

Link to a TIBTransaction. Defaults to internal transaction (concurrency, wait)

AutoDDL

When true, DDL statements are always committed after execution

Echo

When true, all SQL statements are echoed to log

StopOnFirstError

When true the script engine terminates on the first SQL Error.

IgnoreGrants

When true, grant statements are silently discarded. This can be useful when applying a script using the Embedded Server.

ShowAffectedRows

When true, the number of affected rows is written to the log after a DML statement is executed.

ShowPerformanceStats

When true, performance statistics (in ISQL format) are written to the log after a DML statement is executed.

DataOutputFormatter

Identifies a Data Output Formatter component used to format the results of executing a Select Statement.

 

Events:

GetParamValue

called when an SQL parameter is found (in PSQL :name format). This is only called for blob fields. Handler should return the BlobID to be used as the parameter value. If not present an exception is raised when a parameter is found.

Hint: use TIBBlobStream to create and read the blob from a file.

OnOutputLog

Called to write SQL Statements to the log (stdout)

OnErrorLog

Called to write all other messages to the log (stderr)

OnProgressEvent

Progress bar support. If Reset is true the value is maximum value of progress bar. Otherwise called to step progress bar.

OnSelectSQL

handler for select SQL statements. If not present, then the DataOutputFormatter is used to process select SQL statements. If neither an OnSelect Handler or a DataOutputFormatter is defined then select statements. result in an exception.

An OnSelectSQL handler may either process the select statement itself or call TIBXScript.DefaultSelectSQLHandler to invoke default processing as described above.

OnSetStatement

Handler for unrecognised SET Statements.

 

Usage:

The following TIBXScript functions may be used to execute an SQL statement or script:

function RunScript(SQLFile: string): boolean; overload;

function RunScript(SQLStream: TStream): boolean; overload;

function RunScript(SQLLines: TStrings): boolean; overload;

function ExecSQLScript(sql: string): boolean;

An SQL script may be passed as a File, a stream, a TStrings or as a single string. The above functions differ only in the way the script is provided. Otherwise, they are identical. The script is parsed into statements and executed one statement at a time in the order given in the script. The function returns true if all statements have been successfully executed and false otherwise.

Examples

Two example programs are provided in the ibx/examples directory that illustrate the use of TIBXScript in both GUI and console mode. These are:

  1. ibx/examples/scriptengine

  2. ibx/examples/fbsql

The Script Engine Example

This example application illustrates use of the TIBXScript SQL script engine. It works with the example employee database and comes with various test scripts to illustrate how it works. These are all located in the "tests" directory.

Compile and run the application after first ensuring that the example employee database is available on the local server. If it is on a remote server, then you will have to adjust the IBDatabase1.DatabaseName property accordingly.

You can just type SQL queries into the left hand text box and click on "Execute" to run them. The results appear in the right hand text box. Select queries are supported by opening a new dynamically created window with a grid containing the query results. This window is non-modal and multiple query results can be shown simultaneously. The grid is a TIBDynamicGrid and clicking on the column header will resort the grid using the selected column.

The test scripts are loaded in the left hand text box by clicking on the "Load Script" button. The scripts are:

1. CreateCountriesTable.sql

This adds a new table "COUNTRIES" to the employee database and then populates it with country data including the country name and ISO2 and 3 character shortnames. At the end of the script, the contents of the new table are displayed.

2. CreateCountriesTablewithError.sql

This does the same as the above, except that the first insert statement contains a syntax error. It may be used to experiment with the "Stop on First Error" checkbox, and shows how the script engine can recover and continue from (some) syntax errors.

3. DeptListView.sql

This script adds a complex View to the database and tests the script engine in complex scenarios, such as recursive queries.

4. createproc.sql

This script adds three simple stored procedures. It demonstrates the different ways that procedure bodies can be declared (ISQL compatible, standard terminator and no terminator). Use of comments is also demonstrated.

5. ParameterisedQueries.sql

This script demonstrates the use of PSQL style query parameters for BLOB columns. In this case a new column "Image" is added to the COUNTRY Table and an image in png format (the flag of St George) is added to the entry for England. The value of the Image column is given by a parameter ":MyImage". This is resolved by the application which asks for the file containing the image to be placed in the field.

You should locate and return the "flag_en.png" file.

Note that the interactive resolution of the parameter is an example. The parameter resolution is carried out by an event handler that could, for example, have looked for a file which might conventionally have been called "MyImage.bin" to correspond to the query parameter.

6. Reverseall.sql

Reverses out the above.

7. SelectQuery.sql

Illustrates handling of select queries.

The fbsql Console Mode Application

fbsql is more than just a simple example and is an ISQL replacement console mode program for both interactive and non-interactive use. fbsql uses TIBXScript as its SQL Script Engine and TIBExtract to extract metadata from the database. Select queries are handled by by outputing the query results to stdout in CSV format suitable for loading into a spreadsheet, as insert statements, or in a block format. It also includes an interactive version of TIBXScript.

Usage: fbsql <options> <database name>

Options:

-a write database metadata to stdout
-A write database metadata and table data to stdout
-b stop on first error
-e echo sql statements to stdout
-i <filename> execute SQL script from file
-h show this information
-o <filename> output to this file instead of stdout
-p <password> provide password on command line (insecure)
-r <rolename> open database with this rolename
-s <sql> Execute SQL text
-t specify output format for SQL Statements
      BLK (default) for block format
      CSV (default) for CSV format
      INS (default) for Insert Statement format
-u <username> open database with this username (defaults to SYSDBA)

Environment Variables:

ISC_USER Login user Name

ISC_PASSWORD Login password

Saving the username and/or password as environment variables avoids having to enter them on the command line and is a more secure means of provding the password.

If no password is provided on the command line or through the environment, then the user is prompted for a password to be entered securely.

If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs interactively.

fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File" and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to remember it.

SQL Statements Supported

  • All DML and DDL Statements are supported.

  • CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.

  • Additionally, RECONNECT is interpreted as dropping the connection and reconnecting.

ISQL Command Support

  • SET SQL DIALECT

  • SET TERM

  • SET AUTODDL

  • SET BAIL

  • SET ECHO

  • SET COUNT

  • SET STATS

  • SET NAMES <character set>

  • SET HEADING

  • SET ROWCOUNT

  • SET PLAN

  • SET PLAN ONLY

  • QUIT

  • EXIT

To use, compile the program in the Lazarus IDE and run it from the command line. See above for the command line parameters. For example:

fbsql -a -u SYSDBA -p masterkey employee

will write out the metadata for the local employee database to stdout (assuming default password).

fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee

will dump the employee database, include data, to a text file (employeedump.sql).

fbsql -u SYSDBA -p masterkey -i employeedump.sql

will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE DATABASE" statement is at the start of this file and should be edited to identify the database file that is to be created. Alternatively,

fbsql -u SYSDBA -p masterkey -i employeedump.sql new-employee.fdb

will restore the database to the database file 'new-employee.fdb' provided that it has already been created as an empty database. Note that in this case, the "CREATE DATABASE" statement should remain commented out.

fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee

will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming default password).

fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee

will run the script CreateCountriesTable.sql from the script engine test suite and apply it to the local employee database. Each statement will be echoed to stdout and processing will stop on the first error.

Note that on Linux, to run a program from the command line that is not on the PATH, you need to:

cd to the example directory "ibx/examples/fbsql"

run the program as "./fbsql" e.g.

./fbsql -a -u SYSDBA -p masterkey employee