Wednesday, October 7, 2009

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

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


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.


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.


Syntax: COMMIT

Example: -- TOAD: COMMIT

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


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).


Syntax: IF {expression}

Syntax: ELSE

Syntax: ENDIF

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

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.


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.


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.


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.


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.



Example: -- TOAD: ROLLBACK

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


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.


Syntax: UNSET {parameter}

Example: -- TOAD: UNSET a

Remove the value of a predefined variable.


Syntax: WHILE {expression}


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

Statement functions


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.


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

-- 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.


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

-- 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.


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

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

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


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

-- 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.

1 comment:

Anonymous said...

I am getting this error message when the sql is run.

Information:10/21/2010 9:20:23 AM 0:00:00.000: Incorrectly formatted set command (v_product = '125')
TRIAL.sql:1: -- TOAD: SET v_product = '125'

I have been unable to figure out why it doesn't like the format.