| 1 |
<?xml version="1.0" encoding="UTF-8"?> |
| 2 |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 plus MathML 2.0//EN" "http://www.w3.org/Math/DTD/mathml2/xhtml-math11-f.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><!--This file was converted to xhtml by LibreOffice - see http://cgit.freedesktop.org/libreoffice/core/tree/filter/source/xslt for the code.--><head profile="http://dublincore.org/documents/dcmi-terms/"><meta http-equiv="Content-Type" content="application/xhtml+xml; charset=utf-8"/><title xml:lang="en-US">- no title specified</title><meta name="DCTERMS.title" content="" xml:lang="en-US"/><meta name="DCTERMS.language" content="en-US" scheme="DCTERMS.RFC4646"/><meta name="DCTERMS.source" content="http://xml.openoffice.org/odf2xhtml"/><meta name="DCTERMS.creator" content="Tony Whyman"/><meta name="DCTERMS.issued" content="2016-02-05T16:21:08.651509244" scheme="DCTERMS.W3CDTF"/><meta name="DCTERMS.contributor" content="Tony Whyman"/><meta name="DCTERMS.modified" content="2016-02-15T12:07:15.359684037" scheme="DCTERMS.W3CDTF"/><meta name="DCTERMS.provenance" content="" xml:lang="en-US"/><meta name="DCTERMS.subject" content="," xml:lang="en-US"/><link rel="schema.DC" href="http://purl.org/dc/elements/1.1/" hreflang="en"/><link rel="schema.DCTERMS" href="http://purl.org/dc/terms/" hreflang="en"/><link rel="schema.DCTYPE" href="http://purl.org/dc/dcmitype/" hreflang="en"/><link rel="schema.DCAM" href="http://purl.org/dc/dcam/" hreflang="en"/><style type="text/css"> |
| 3 |
@page { } |
| 4 |
table { border-collapse:collapse; border-spacing:0; empty-cells:show } |
| 5 |
td, th { vertical-align:top; font-size:12pt;} |
| 6 |
h1, h2, h3, h4, h5, h6 { clear:both } |
| 7 |
ol, ul { margin:0; padding:0;} |
| 8 |
li { list-style: none; margin:0; padding:0;} |
| 9 |
<!-- "li span.odfLiEnd" - IE 7 issue--> |
| 10 |
li span. { clear: both; line-height:0; width:0; height:0; margin:0; padding:0; } |
| 11 |
span.footnodeNumber { padding-right:1em; } |
| 12 |
span.annotation_style_by_filter { font-size:95%; font-family:Arial; background-color:#fff000; margin:0; border:0; padding:0; } |
| 13 |
* { margin:0;} |
| 14 |
.Heading_20_2 { font-size:115%; margin-bottom:0.0835in; margin-top:0.139in; font-family:Liberation Sans; writing-mode:page; font-weight:bold; } |
| 15 |
.P1 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 16 |
.P10 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 17 |
.P11 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 18 |
.P12 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 19 |
.P13 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 20 |
.P14 { font-size:130%; font-weight:bold; margin-bottom:0.0835in; margin-top:0.1665in; font-family:Liberation Sans; writing-mode:page; } |
| 21 |
.P15 { font-size:115%; font-weight:bold; margin-bottom:0.0835in; margin-top:0.139in; font-family:Liberation Sans; writing-mode:page; } |
| 22 |
.P16 { color:#808080; font-size:14pt; font-weight:bold; margin-bottom:0.0835in; margin-top:0.0972in; font-family:Liberation Sans; writing-mode:page; } |
| 23 |
.P17 { color:#808080; font-size:14pt; font-weight:bold; margin-bottom:0.0835in; margin-top:0.0972in; font-family:Liberation Sans; writing-mode:page; } |
| 24 |
.P2 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 25 |
.P3 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; font-weight:bold; } |
| 26 |
.P4 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 27 |
.P5 { font-size:10pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:DejaVu Sans Mono; writing-mode:page; } |
| 28 |
.P6 { font-size:12pt; line-height:120%; margin-bottom:0.1in; margin-top:0in; font-family:DejaVu Sans Mono; writing-mode:page; } |
| 29 |
.P7 { font-size:10pt; line-height:120%; margin-bottom:0.1in; margin-top:0in; font-family:DejaVu Sans Mono; writing-mode:page; } |
| 30 |
.P8 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 31 |
.P9 { font-size:12pt; line-height:120%; margin-bottom:0.0972in; margin-top:0in; font-family:Liberation Serif; writing-mode:page; } |
| 32 |
.Bullet_20_Symbols { font-family:OpenSymbol; } |
| 33 |
.Internet_20_link { color:#000080; text-decoration:underline; } |
| 34 |
.T1 { font-weight:bold; } |
| 35 |
.T2 { font-size:10pt; } |
| 36 |
<!-- ODF styles with no properties representable as CSS --> |
| 37 |
.Numbering_20_Symbols .T3 .T4 { } |
| 38 |
</style></head><body dir="ltr" style="max-width:8.2681in;margin-top:0.7874in; margin-bottom:0.7874in; margin-left:0.7874in; margin-right:0.7874in; writing-mode:lr-tb; "><h1 class="P14"><a id="a__The_TIBXScript_Script_Engine"><span/></a>The TIBXScript Script Engine</h1><p class="P1">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:</p><ul><li><p class="P8" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span>SET TERM and Set AutoDDL are both supported<span class="odfLiEnd"/> </p></li><li><p class="P8" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span>New Command: RECONNECT. Performs a commit followed by disconnecting and reconnecting to the database.<span class="odfLiEnd"/> </p></li><li><p class="P8" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span>Procedure Bodies (BEGIN .. END blocks) are self-<span class="T3">delimiting</span> 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.<span class="odfLiEnd"/> </p></li><li><p class="P8" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span>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.<span class="odfLiEnd"/> </p></li><li><p class="P11" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span>C++ style comment lines.<span class="odfLiEnd"/> </p></li></ul><p class="P1">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.</p><h2 class="Heading_20_2"><a id="a__Properties_"><span/></a>Properties:</h2><ul><li><p class="P9" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">Database</span>: Link to TIBDatabase component<span class="odfLiEnd"/> </p></li><li><p class="P9" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">Transaction</span>: Link to Transaction. Defaults to internal transaction (concurrency, wait)<span class="odfLiEnd"/> </p></li><li><p class="P9" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">Echo</span>: boolean. When true, all SQL statements are echoed to log<span class="odfLiEnd"/> </p></li><li><p class="P9" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">StopOnFirstError</span>: boolean. When true the script engine terminates on the first SQL Error.<span class="odfLiEnd"/> </p></li><li><p class="P12" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">IgnoreGrants</span>: When true, grant statements are silently discarded. This can be useful when applying a script using the Embedded Server. <span class="odfLiEnd"/> </p></li></ul><h2 class="Heading_20_2"><a id="a__Events_"><span/></a>Events:</h2><ul><li><p class="P10" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">GetParamValue</span>: 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.<span class="odfLiEnd"/> </p></li><li><p class="P10" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">OnOutputLog</span>: Called to write SQL Statements to the log (stdout)<span class="odfLiEnd"/> </p></li><li><p class="P10" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">OnErrorLog</span>: Called to write all other messages to the log (stderr)<span class="odfLiEnd"/> </p></li><li><p class="P10" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span> <span class="T1">OnProgressEvent</span>: Progress bar support. If Reset is true the value is maximum value of progress bar. Otherwise called to step progress bar.<span class="odfLiEnd"/> </p></li><li><p class="P10" style="margin-left:0cm;"><span class="Bullet_20_Symbols" style="display:block;float:left;min-width:0.635cm;">•</span><span class="T1">OnSelectSQL</span>: handler for select SQL statements. If not present, select SQL statements result in an exception. <span class="odfLiEnd"/> </p></li></ul><h2 class="P15"><a id="a__Examples"><span/></a>Examples</h2><p class="P2">Two example programs are provided in the ibx/examples directory that illustrate the use of TIBXScript in both GUI and console mode. These are:</p><ol><li><p class="P13" style="margin-left:0cm;"><span class="Numbering_20_Symbols" style="display:block;float:left;min-width:0.635cm;">1.</span><a href="#a_ibx_examples_scriptengine" class="Internet_20_link">ibx/examples/scriptengine</a><span class="odfLiEnd"/> </p></li><li><p class="P13" style="margin-left:0cm;"><span class="Numbering_20_Symbols" style="display:block;float:left;min-width:0.635cm;">2.</span><a href="#a_ibx_examples_fbsql" class="Internet_20_link">ibx/examples/fbsql</a><span class="odfLiEnd"/> </p></li></ol><h3 class="P16"><a id="a__The_Script_Engine_Example"><span/></a>The Script Engine Example</h3><p class="P2">This example application illustrates use of the TIBXScript SQL script engine. It works with the example employee database and <span class="T3">comes </span> with various test scripts to illustrate how it works. These are all located in the "tests" directory. </p><p class="P2">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. </p><p class="P2">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. </p><p class="P2">The test scripts are loaded in the left hand text box by clicking on the "Load Script" button. The scripts are: </p><p class="P3">1. CreateCountriesTable.sql </p><p class="P2">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. <span class="T3">At the end of the script, the contents of the new table are displayed.</span></p><p class="P2"> </p><p class="P3">2. CreateCountriesTablewithError.sql </p><p class="P2">This does the same as the above, except that the first insert statement <span class="T4">contains</span> 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. </p><p class="P2"> </p><p class="P3">3. DeptListView.sql </p><p class="P2">This script adds a complex View to the database and tests the script engine in complex scenarios, such as recursive queries. </p><p class="P2"> </p><p class="P3">4. createproc.sql </p><p class="P2">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. </p><p class="P2"> </p><p class="P3">5. ParameterisedQueries.sql </p><p class="P2">This script demonstrates the use of <span class="T4">PSQL</span> 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. </p><p class="P2"> You should locate and return the "flag_en.png" file. </p><p class="P2">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. </p><p class="P2"> </p><p class="P3">6. Reverseall.sql </p><p class="P2">Reverses out the above. </p><p class="P2"> </p><p class="P3">7. SelectQuery.sql </p><p class="P2">Illustrates handling of select queries. </p><h3 class="P17"><a id="a__The_fbsql_Console_Mode_Application"><span/></a>The fbsql Console Mode Application</h3><p class="P4">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.</p><p class="P4">Usage: fbsql <options> <database name></p><p class="P6">O<span class="T2">ptions:</span></p><p class="P7">-a write database metadata to stdout</p><p class="P7">-b stop on first error</p><p class="P7">-e echo sql statements to stdout</p><p class="P7">-f <filename> execute SQL script from file</p><p class="P7">-h show this information</p><p class="P7">-p <password> provide password on command line (insecure)</p><p class="P7">-r <rolename> open database with this rolename</p><p class="P7">-s <sql> Execute SQL text</p><p class="P7">-u <username> open database with this username (defaults to SYSDBA)</p><p class="P4"> </p><p class="P7">Environment Variables:</p><p class="P7">ISC_USER Login user Name</p><p class="P7">ISC_PASSWORD Login password</p><p class="P4">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.</p><p class="P4">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.</p><p class="P4">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:</p><p class="P5">fbsql -a -u SYSDBA -p masterkey employee</p><p class="P4">will write out the metadata for the local employee database to stdout (assuming default password).</p><p class="P5">fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee</p><p class="P4">will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming default password).</p><p class="P5">fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee</p><p class="P4">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.</p><p class="P4">Note that on Linux, to run a program from the command line that is not on the PATH, you need to:</p><p class="P4">cd to the example directory "ibx/examples/fbsql"</p><p class="P4">run the program as "./fbsql" e.g.</p><p class="P5">./fbsql -a -u SYSDBA -p masterkey employee</p></body></html> |
