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 ISQL compatible but with extensions:
•SET TERM and Set AutoDDL are both supported
•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.
•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.
•Database: Link to TIBDatabase component
•Transaction: Link to Transaction. Defaults to internal transaction (concurrency, wait)
•Echo: boolean. When true, all SQL statements are echoed to log
•StopOnFirstError: boolean. 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.
•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.
•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, select SQL statements result in an exception.
Two example programs are provided in the ibx/examples directory that illustrate the use of TIBXScript in both GUI and console mode. These are:
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.
fbsql is more than just a simple example and is an ISQL replacement console mode program for 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.
Usage: fbsql <options> <database name>
Options:
-a write database metadata to stdout
-b stop on first error
-e echo sql statements to stdout
-f <filename> execute SQL script from file
-h show this information
-p <password> provide password on command line (insecure)
-r <rolename> open database with this rolename
-s <sql> Execute SQL text
-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.
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.
To use, compile the program in the Lazarus IDE and run it from the command line. The above gives 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 -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