Flow control for your scripts

A feature that has actually been in Toad for a long time but has still been more or less undocumented and I assume since I see no questions about it that nobody is using is a feature we internally refer to as Toad Script.

Note that the examples in this are written to work for SQL Server or MySQL but should work with minimal changes on DB2 as well (The Toad Script part is identical).

Toad script is comprised of two different parts. Script functions and statement functions. A script function looks like a comment and looks something like this.

-- TOAD: SET a = 10

In this case the "-- TOAD:" part indicates that this is a toad script function and all Toad script functions look like this. "SET" is the actual function and "a = 10" are the parameters to the set function. In this case this function sets the predefined parameter "a" to the value 10.

Statement functions are used inside of statements and are unlike script functions not portable to other products than Toad (Since script functions are comments they are just ignored by other products). Here is an example of a statement function.

SELECT {{Unquote(:a)}}

In the example the statement function is in bold and will insert the value of the bind parameter as a literal value before the statement is sent to the server (In the case you want to use a bind variable in places where they are not allowed this is useful).

Before we delve deeper into details lets look at a more useful example to how this could be used.

-- TOAD: SET a = 1
-- TOAD: WHILE a < 10
CREATE TABLE toadscripttest{{Unquote(:a)}} (id INT);
-- TOAD: SET a = a + 1
-- TOAD: ENDWHILE

This example when run will create 10 tables called toadscripttest1 to toadscripttest10. To step through it. The first line sets a predefined bind variable a to 1. The second line checks if the value of a is less than 10 and as long as it is it will repeat everything between this row and the corresponding ENDWHILE line a few lines below. The third line executes a create table statement and inserts the predefined value of :a unquoted. The fourth line increments a with 1 and the last line ends the while statement.

Script functions

ASK

Syntax: ASK {parameter} = {expression}

Example: -- TOAD: ASK a = 'Would you like to continue?'

This method displays a dialog containing the text of the expression with a yes and no button. If the yes button the parameter will be assigned a true value and if the no button is pressed a will be assigned a false value which can be used with either a WHILE or an IF function.

CHDIR

Syntax: CHDIR {expression}

Example: -- TOAD: CHDIR 'c:\'

This function changes the current working directory of the current execution. By default the working directory when executing a script is the location where the script is located. The path can be either absolute or relative to the current working directory of the script execution.

COMMIT

Syntax: COMMIT

Example: -- TOAD: COMMIT

Perform a commit on all database connections for the current toad connection.

GROUPBY

Syntax: GROUPBY {column}[,{column}]

Example: -- TOAD: GROUPBY [Col1],'Col2',Col3

This function instructs that a column in the script result of the statement following this call will be grouped by in the grid initially. The columns can be quoted with either [], '', "" or `` characters or without quotes as long as the column names don't include whitespace or , characters (This method was added in 4.5).

IF - ELSE - ENDIF

Syntax: IF {expression}

Syntax: ELSE

Syntax: ENDIF

Example:
-- TOAD: ASK a = 'Do you agree?'
-- TOAD: IF a
-- TOAD: NOTIFY 'Nice to be in agreement!'
-- TOAD: ELSE
-- TOAD: NOTIFY 'Hopefully you will come around!'
-- TOAD: ENDIF

This function allows for flow control. If the expression specified evaluates to true everything between if and the corresponding endif or else function will be executed. If the else function is specified then everything after that will be executed if the expression evaluates to false.

INCLUDE

Syntax: INCLUDE {expression}

Example: -- TOAD: INCLUDE 'otherscript.sql'

This function allows you to include another file which filename is specified with the expression into the execution of this file.

NOTIFY

Syntax: NOTIFY {expression}

Example: -- TOAD: NOTIFY 'Pekaboo!'

Displays a notification in the lower right corner of your screen with the message specified by the expression.

PRINT

Syntax: PRINT {expression}

Example: -- TOAD: PRINT 'This is probably not that important...'

Displays a message specified by the expression temporarily in the status bar of the Toad window.

PROMPT

Syntax: PROMPT {parameter} = {expression}

Example: -- TOAD: PROMPT a = 'Give me a value!'

This method displays a dialog containing the text of the expression and asks for a value. The value entered will be assigned to the parameter.

ROLLBACK

Syntax: ROLLBACK

Example: -- TOAD: ROLLBACK

Perform a rollback on all database connections for the current toad connection.

SET

Syntax: SET {parameter} = {expression}

Example: -- TOAD: SET a = (10 + 20) / 10 * 5

This method will set the value of a predefined parameter to the value of an expression.

UNSET

Syntax: UNSET {parameter}

Example: -- TOAD: UNSET a

Remove the value of a predefined variable.

WHILE - ENDWHILE

Syntax: WHILE {expression}

Syntax: ENDWHILE

Example:
-- TOAD: SET a = 1
-- TOAD: WHILE a < 10
-- TOAD: PRINT 'We are counting up. Now we are at ' || a
-- TOAD: SET a = a + 1
-- TOAD: ENDWHILE

Statement functions

Format

Syntax: {{Format({Format String} ...)}}

Example: SELECT {{Format('Hello {0}', 'World')}}

This method takes a string and formats it using the .Net String.Format method format and returns the result.

Quote

Syntax: {{Quote({literal})}}

Example:
-- TOAD: SET database = 'AdventureWorks'
-- TOAD: SET owner = 'Person'
-- TOAD: SET table = 'Address'
select * from {{Quote(:database)}}.{{Quote(:owner)}}.{{Quote(:table)}}

This function takes the passed in value and inserts the value as a quoted (if needed) SQL literal.

Round

Syntax: {{Round({number}[, {decimals}])}}

Example:
-- TOAD: SET meaning = 41.52
select {{Round(:meaning)}}, {{Round(:meaning, 1)}}

This function takes a numerical value and return it rounded to either an integer or to a specific number of decimals.

StringQuote

Syntax: {{StringQuote({literal}[, {decimals}])}}

Example:
-- TOAD: SET helloworld = 'Hello World'
select {{StringQuote(:helloworld)}}

This function takes the passed in value and inserts the value as a quoted string.

Unquote

Syntax: {{Unquote({literal})}}

Example:
-- TOAD: SET from = 'FROM'
select * {{Unquote(:from)}} AdventureWorks.Person.Address

This function takes the passed in value and inserts the value right into the script as a string.

Inside the application data directory

If you have ever looked in the Toad data directory you were probably a little bit bewildered about all the stuff in there. This article will try to shed some light on what all the files in there are used for by the Toad application.

First off all before we start off here, lets begin with something really basic. If you don't know where the application data directory is there is a very easy way to find it. Just go into the Help|About of Toad and click on the Application Data Directory link.

This will bring up a windows explorer window showing the data directory. Just one note, if you want to tinker with something in here be sure to shut down Toad first.

So now you see the stuff in the directory. Lets start going through the files to see what they are all used for.

Name	Description
Action.log	This file tracks your usage within Toad. If you agree to share this with us (And please do, it really does help us) it's contents will be sent automatically to us periodically. We collect the data regardless though and it will be included in a support bundle.
Connections.xml	This file contains all the contents of your connection manager (Or new connection dialog if that is your fancy).
ConnectionTrace.log	Created to contain the SQL Output connection trace log file when you log to file.
ExceptionTrace.log	This file contains a log of all exceptions that have been encountered while running the application. This file helps us a lot when you generate a support bundle.
Settings.xml	Global settings configuration file. This file contains a lot of application settings save in a SOAP formatted XML file.
SqlServerPlanPrefs.xml	File used to generate SQL Server execution plans.
ToadSupportBundle.zip	The last support bundle you have generated.

There can be some more log files generated too by setting different command line option to generate additional debug information. All these files will then be included in the support bundle when generated.

Now lets look at what is in all the sub directories and what they contain.

Name	Description
Automation	This is a temporary directory used by automation when running automation scripts.
Cache	This directory is used to spool cache database meta data out to disc if this is enabled. The data in here is in binary format and can always be recreated by fetching it from the database.
CNSScripter	This is a temporary directory used by compare and sync, some script generation for alter dialogs and the script tab in the Object Explorer.
Context	This directory contains settings that depend on the context within Toad. Depending on the sub folder name it contains different information. First have database browser filters, and their named equivalent in two different folders ("Filter" and "NamedFilter" respectively). We also have the RHS layout of each type in the "Type" directory. Finally you have custom data editing statements in the "Adapter" directory.
Keyboard	Contains keyboard layout customizations if you have done any.
Layout	Contains different layouts of parts of the application. Examples of these are the tool tip panes of the editor and column layouts of the messages tool window.
ModuleSettings	This is another directory where we have a lot of settings saved. They are saved in the same format as the Settings.xml file. The files here are divided by functionality.
Plugins	Directory that can be used by different plugins of Toad for whatever they choose.
Session	Files in this directory store desktop configurations. That means they contain toolbar customizations, tool window locations by default but can contain more or less information.
SQLRecall	Data files for SQLRecall.
Templates	This directory has different templates used in different parts of the application. This includes code snippets, some connection startup scripts and DOE templates (Only supported by Toad for DB2 currently).

If you are running into problems which you suspect might be settings related the easiest way to check that out is to just rename the settings directory (You need to close Toad first) and then restart Toad. If this solves the problem the two files that you can almost always copy over from your old directory without any problems is the connections.xml, sessions.tss file and the Layout directory. Replacing these files you can reset all your options "the hard way" without loosing your connection history and the layout of all tool windows which are usually the part that takes the most work to restore in my opinion.

Command line reference

The documentation of what command line options are available on the Toad.exe is not necessarily that well covered in the documentation. True there is rarely any need to specify anything on it unless something goes wrong. I just thought I'd go through what is available.

First of all the format. Toad allows any parameters to be specified regardless of if it knows what they are or not because some plugins might want to read it later on unbeknown to the original startup code. All parameters are specified using this format.

toad /{parameter name}={value} {files to open on startup}

Also if you want to specify an option with space in it you can quote it like this.

toad "/{parameter name}={value}" "{A file to open}"

Here are the following parameters that we support in Toad as of version 4.1.

Parameter	Value	Description
datadir	Directory	Specify an alternative data directory for Toad to read it's settings from instead of the default location.
profile	"true"	Start Toad and generate profiling information. This is sometimes used by us if you Toad is behaving very slow to track down what is wrong. The information generated is included in the support bundle if it is available.
batch	"true"	Run toad in batch mode. This means that it will just open all available files without opening up the user interface and then exit. Used by automation.
prestart	"true"	Pre start Toad. This means that toad is run in the background to be available to "pop up" when you actually want it. This feature is never actually used but if you are ok with pre loading on windows startup you can add toad /prestart=true to your startup menu and have lightning fast startups when you actually need Toad. Will use memory even if you are not using Toad. This is how a lot of other apps fake quick startup times like for instance Adobe Reader.
installinit	"true"	Used by installation to initialize a cache of the available plugins of to the application. When run by this it needs to write to the install directory, but in case you have this which is normally the case you can rerun this as many times you want.
feedback	"true"	Enable usage reporting.
license	"edit"	Display the license entry form on startup.
transpose	"1"	Allow transposing when exporting grids. That means switching X and Y axises of the grid.
exportbenchmark	"1"	Generate a log file to help benchmarking the export engine. The file will be included in your next support bundle
excelverbose	"1"	Generate a lot of verbose info when exporting into Excel.
profilevc	"1"	Generate a profiling log to help benchmarking the version control code. The file will be included in your next support bundle
debuglog	Filename	Generate a log of some debugging events. The file will always be placed in the application data directory.
yukondebuglog	"1"	Generates debugging information for the SqlServer 2005 debugger. The file will be included in your next support bundle.
dbmsverbose	"1"	Generate very verbose DBMS_OUTPUT output.
oraclelastplan	"1"	Include the latest Oracle explain plan to your support bundle.
db2debuglogreports	"1"	Generate debugging information for the DB2 debugger. The file will be included in your next support bundle.
db2debuglog	"1"	Generate debugging information for the db2 command line executions. The file will be included in your next support bundle.

To end with an example running Toad like this will enable usage reporting and using a new application data directory and then open a file on startup.

toad /feedback=true "/datadir=c:\Users\MyUser\My Documents\ToadDir" "c:\Users\MyUser\My Documents\A Sql File.sql"