Overview

The Interactive Scripting Editor combines the high performance power of the Andi database Query engine with the powerful and easy to use Groovy scripting language. Andi scripting extensions and ready to use examples are all included within the Andi application to help you get started!

Reserved Variable Names

There are several variable names which should not be adjusted within a script. These variables names are bound to the scripting context and provide a direct link to a scripting architecture which makes writing a powerful script easy:

context - General support methods
sqlExecution - Execute SQL statements
results - Access or store query results
connections - Create / access / commit / rollback database connections
spreadsheets - Import from or export to spreadsheets
logger - Script logging services

Note: The names above are to reference bound variables only and should not be used within scripts except to reference Andi provided functionality.

Transactions

By default, all connections created during script execution will have auto-commit turned off. This means that it is the scripts responsibility to control transaction boundaries by executing commit / rollback methods.

If the script ends without a commit taking place, the following warning message will be displayed in the console:

"WARNING: No commits were performed during script execution; uncommitted changes have been rolled back."

If the script ends and at least one commit was performed, the following warning message will be displayed. You can ignore this message if all changes have already been committed:

"WARNING: Rolling back uncommitted changes."

Accessing the Interactive Scripting Editor

To access the Interactive Scripting Editor select the Tools menu, and then the Interactive Scripting Editor:

The Interactive Scripting Editor will appear. It is split into two major sections: the Scripting Editor where Scripts can be created / loaded, and the Console Output where results from the script execution will appear:

Interactive Scripting Editor

The Interactive Scripting Editor is where new scripts can be created, or existing saved scripts can be loaded for script execution:

To load an existing / saved script into the Scripting Editor, select the File menu and then Open. You can then select a file path for where the saved script is stored, and Open it in the Scripting Editor. this makes sharing previously used scripts simple:

Scripting Console Output

Results that have executed from a script will appear in the Console Output section of the Interactive Scripting Editor. The Console menu option can be used to clear the Console (remove all text), save the Console messages to a file, and more:

Export Data to a File

The Andi @export() custom tag can be used to export data to a file. The below example would execute the Query, and export a new spreadsheet with the resulting data:

sqlExecution.executeQuery( 'select * from school @export(/temp/school.xlsx, format=xlsx, tab_name=School)' )

Parallel Processing

Parallel Processing is a powerful scripting feature of Andi that allows for running batch processes simultaneously, which dramatically speeds up the process of executing large, complex SQL.

Introduction to the Interactive Scripting Editor