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.

Toad for SQL Server snags best of TechEd 2009

I just learned that Toad for SQL Server has snagged the coveted Best of Tech Ed Award in the Database Development category and I just thought I would share that.

I have unfortunately not been able to post as much as I would have wanted on this blog lately because I'm knee deep in the development of the 4.5 release. As this release is out I should hopefully be able to contribute more here.

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.

Code completion revealed

From the first version of Toad we have always had some form of code completion (It was originally called Toad insight or something like that) but it has always been a fairly simple affair where you either got a list of columns in select statements or a long list of objects of many different types completely unaffected by the context in which you invoke the code completion.

Starting in 4.0 the code completion support was completely rewritten to be very context aware. Now Toad should know that if you are writing a select statement and are between SELECT and FROM you probably want a list of columns and if you write JOIN you probably want to join on a foreign key from tables already specified etc. There has been little bit of a backlash on this and I have to admit that this code did have some bugs and when that happened you usually didn't end up with any code completion at all which is worse than what we had in 3.x. Hopefully for the soon to be released 4.1 release most of these should have been fixed and you will really see the benefits of the new framework.

Handling code completion in this way is kind of complicated. The main problem is you can't do strict parsing because we are not dealing with complete statements. Instead we have to do our best at guessing what the you are aiming at writing.

So let me go through the changes in more detail what new features are introduced with this framework.

Smarter auto invocation

The most obvious change that people will immediately see is that as you start typing a select statement the moment you write "FROM" and then space you will be presented with a list of tables and views. Before 4.0 the only time we automatically invoked code completion was when you typed a dot. In 4.0 we have certain "hot tokens" that invoke code completion after you write a space as well. The full list of tokens isn't that important, but if you ever think we are missing something just let us know because it is very easy for us to change it.

Context sensitive object lists

We now look at the statement type to decide what kind of object list you probably are interested. For instance if you write "CALL" or "EXEC" you are probably interested in a list of procedures, when you are writing "UPDATE" you probably want a list of tables while when you are in the "FROM" clause of a select statement you probably want both tables, views and in case it's supported functions returning tables.

Context aware sub-select completion

Toad should now keep track of sub selects so that it knows which objects you can address when you are writing complex sub-selects as shown in the screenshot below.

Auto generated joins

When you are writing select statements we now automatically generate the full join statement based on foreign keys in the database. We can generate both the part following "JOIN", the part following "ON" or in case of a cross join the part in the "WHERE" clause.

Snippet code completion

In some cases the code completion generated still need you to fill in some extra data to be a valid statement. We now do this by inserting an auto generated code snippet. Specifically we use this when you are using code completion on code to let you fill in the parameters of the call we generate.

Complete statement generation

We can now generate the full statement for some statements after you have specified the database object to operate on. Specifically this includes update, insert and delete statements.

Good handling of owners

This only applies to SQL Server, but in 4.0 we really didn't have that good support for handling owners in code completion. As of 4.1 that we now handle owners in a really clever way. Basically it should now "just work". If SQL Server accepts the syntax we should handle it correctly.

Support for local variables in batches

On SQL Server we now keep track of local variables defined in a batch and includes these in the places where they make sense.

Options available for code completion

The option page for code completion has changed quite a bit between 3.x and 4.0.

First some things that haven't changed. The auto list members, quoting, casing, expand "*" and include system objects work exactly as before so I will not go into these (And they should be pretty self explanatory).

Item sort order

This setting now has one more item which is the default and that is the "Each category". This means that the code completion list is sorted per type of items. For instance in a select statement we start with the "*" and then columns followed by variables, snippets and a bunch of other stuff. Chances are that you are looking for the columns and in this case they will be at the top instead of being lost in the list of hundreds of function snippets also included.

Merge secondary items

When you invoke code completion in some cases we have items that are common to choose from and some items that are not so common. Normally the not so common items are not included in the code completion, but if you want to get to them you can still do so by choosing the last item in the list which says (More).

If this option is checked this means that we should generate the code completion list with all items right away instead of separating this into two different lists. This is the default.

Known issues

The one known issue in regards to code completion is that we currently do not honor the setting to separate statements with double newlines (On by default I think). For version control to function property with multiple statements in the editor you need to end you statements with ';' (Except for SQL Server which doesn't have this issue).

Connection and transaction handling explained

In an application like Toad connection and transaction handling will invariant become a complicate affair. During the years we have been working on this we have tried to make it as intuitive as possible so that you usually don't have to think about it. However, in some cases you can still run into problems so this post will try to explain how we handle connections and transactions so that you can avoid the pitfalls.

All information in this article refer to the upcoming 4.1 (2.1 for TDA) release or later.

Connections

In Toad we distinguish between two different kinds of connections. The first on we refer to as a Toad connection which is our abstract representation of a user connecting to a database. Each toad connection can be represented by one or more database connections which are actual connections to a database. At a minimum Toad will usually use at least 2 database connections per Toad connection. The first one is used to fetch database meta data about the database. The second one is used for either the data tab or the editor you are looking at.

We need to have a separate connection to inspect the database because some databases (DB2 in particular) have an unnerving habit of creating locks even if you are just reading data so whenever we read data on these connections we always commit after we are done reading and if you were running Toad with auto commit off we would mess up transactions that were open.

By default each editor and data tab will use it's own database connection to keep the transactions apart. You can control this behavior through the option in the screen shot on the right. If you change this all data tabs and editors will share one connection. You will still have two connections though.

Depending on which database you are connecting to Toad does support to use command multiplexing by which I mean executing several statements as one on the same connection. Currently the only provider that uses this functionality right now is Oracle though. If you try to execute something in an editor while another statement is already running and the connection does not support multiplexing you will be presented with the dialog on the left.

Basically you have the choice of how you want to proceed. At the bottom you also see what it is that is already running on the connection. Most choices are pretty self explanatory except the one about opening up a new connection and execute your new statement on that connection. If you do choose this option the new connection will be the one that all the following executions in the editor will use.

When you close an editor the connection associated with it is also closed immediately unless there are open transactions on the connection. If there are transactions the connection will be closed when you commit or rollback the connection.

There are also a bunch of other places where Toad will allocate another database connection. For instance when you are applying changes from database object editors (This is platform specific) or debugging (Which has a tendency to need two database connections per debugging session).

If you want to see how many database connections are actually being used you can see that in the tooltip over the connection in the connection manager.

Current connection and document connection

Two things that many people have problems with is the difference between the current connection and the connection of the current document. The current connection is the connection that any new documents will have and also the connection currently being shown in the Object Explorer. You can also see which connection is current either by the connection being shown in the connection toolbar or through the icon in the connection manager.

The connection of the current document is shown in the lower right corner of the application (Shown to the right here). This button shows the connection of the current document. Also when you click it you can switch to other compatible (And open) connection depending on the document. Some documents don't have connections attached to them and in those cases this button will not be visible. You can also switch the connection of a document by right clicking on the document tab.

Normally this doesn't present any problems because when you switch the current connection we automatically select the first open document that has that connection and also when you switch document we also change the current connection to that of the new current document.

There is one case when the two can differ. If you have two open connections to two different database instances, let's call them A and B. Next you make sure you close all documents that have the database B as the document connection. Then while being on an editor with connection A as the document connection you use the connection toolbar or connection manager to switch to connection B. In this case since there is no document that we can switch to that has connection B as the document connection we will not change the active document. If you now try to execute something in the current document it will execute on connection A even though both the connection manager and the Object Explorer is showing connection B as the current connection. This is an unfortunate exception and there is really nothing we can do about it, we have tried a bunch of different solutions to this problem but they all turned up even more confusing to the end user.

Transactions

Transactions in Toad is really tricky. You control transactions through the controls in the lower right corner of the main window (This has changed for between 4.1 and 4.0). It now looks like on the right.

The two commit and rollback buttons are only visible if you are not in auto commit mode. You switch auto commit mode by clicking the button to the far right and that part should be fairly self explanatory. When you switch auto commit on or off that setting is persisted per connection between sessions so be aware that if you forget to switch auto commit back to on when you close Toad you will still be in auto commit when you start the application back up again and connect to the same connection.

Auto commit is global for the Toad connection and will affect all editors and data tabs for a specific connection when you switch it. As of version 4.1 there is also a new item in this menu which allows you to switch one specific editor to auto commit without affecting the browser or other editors. Also when you use this feature it is not persisted to the next session and you will still be in auto commit when you restart Toad. Normally when you click the commit or rollback buttons it will commit all database connections for the current Toad connection. However, when you are in the auto commit off for the current editor the commit and rollback buttons only affect that editor (Even if you have more than one editor with this mode enabled for the same Toad connection).

All connections start by default in auto commit mode except for the Oracle platform which is in auto commit off by default (This is the platform that has the least problem with unexpected locks of the ones we support). This default (Which only applies when you add new connections in the connection manager) can be changed in the options as shown here on the right.

Inside Auto Commit

Auto commit is implemented in two different ways depending on the platform you are connecting to. For SqlServer and MySQL we rely on the implicit auto commit available on the server. For all other platforms auto commit is implemented by simply issuing a commit after we are done. There is an option to indicate what "done" in this case means. It's in the lower edge of the red rectangle in the screen shot above. You can choose to either commit after each statement or only after you are done executing the entire batch of statements you are executing (If you are executing a script for instance).

Pitfalls of not using auto commit

Some databases (Specifically SqlServer and DB2) have a problem with dropping locks around when you are running with auto commit off. This is particularly acute when you are issuing DDL commands because these have a tendency to create locks on the data dictionary which causes all activity in the database to lock up for all other connections than the one used in the editor. Since the database browser uses a different connection than the editor to fetch meta data about objects using the database browser in this case can completely lock up Toad. There is unfortunately very little we can do about this issue (At least as we have figured out yet), but it is an issue we are thinking a lot about.

Regardless of the database you are connected against though auto commit off can by it's very definition create locks that can stop other users to access parts the database. Because of this there is a timer in Toad (By default it is set to 30 minutes, but you can change this in the Database|Timeout page in the options). After this timer runs out you will be asked to commit or rollback your changes and if you don't reply within a 30 second time period your changes will be rolled back automatically. This is just to make sure you don't forget to commit changes in Toad, go home from work and then have the DBA yelling at you in the morning because you caused the night time batch runs to fail because of your lock.

Reducing startup time

For the upcoming 4.1 release I have actually been able to spend quite a lot of time to reduce startup time in Toad and hopefully there should be a quite noticeable difference. One of the changes made is to make the initialization multithreaded so the improvement should be even more pronounced if you have more than one CPU core.

There are a couple of other things you can do yourself in both 4.1 and versions before that to reduce startup time if you are annoyed by it.

• Keep auto connect's to a minimum.
• Don't show the web page on startup (This one will shave several seconds off your startup time because we don't need to load the IE component).
• Disable the startup croak. This really doesn't save you much so if you don't mind it just leave it as is.
• Don't both show connection manager tool window and new connection dialog on startup. The list of connections is a complicated control that takes a while to create so there really is no need to create two of them. Choose one way of opening your connections.
• Try not to have every open tool window showing. Tool windows that are showing are initialized on startup and makes the application startup slower. Tool windows are not created until they are first shown so if you can't see them we don't waste time on them. Auto hide is also your friend here as the tool window isn't actually created until you zoom it in when auto hidden.
• I might also add a note that collecting usage statistics takes so little time I can hardly measure it when profiling. Please enable it, it is really useful for us to evaluate where to concentrate our efforts for future development.

These two tips are only for advanced users who don't mind some more drastic methods to reduce startup time.

• If you don't customize your toolbars and menues you can turn off saving and restoring these by unchecking this checkbox.
• If you are ok with wasting some memory you can create a shortcut to run toad /prestart=true and then put that in your startup program folder. This will make Toad start pretty much instantaneous but the feature is not that well tested and might have some issues (Especially in regards to automation and running multiple instances of Toad).

Hopefully these tips will help you get to work faster in the future. As you can see from the previous post on the history of Toad for SqlServer we have already gone from 12 seconds to 5 seconds even though we have added tons of functionality.

Toad version numberings explained

Version numbers between the different components of the different products can be pretty confusing ever for us who work on it. Before 3.0 of the Core (The part of Toad that is shared between the products) each product had it's own release schedule and with that each had it's own release version of this. After release 3.0 we now try to coordinate releases between the different products (They are usually staggered with a few weeks, but they are all based off of the same core version now).

When I talk about versions in this blog I will usually unless otherwise specified refer to the Core version. This should in most cases correspond to the version of the product except for TDA in which case you just reduce 2 major release version.

Also to confuse matters even more a product can choose to skip a release (As for instance the DB2 product is doing now with the 4.1 release). Usually this happens because we need more time on that specific platform to get the features in that we want to have in a specific release.

If you are interested in what Core version your Toad is currently running you can check the .Net assembly version of the ToadCore.dll assembly of your application.

Here is a list of how the different versions of Toad relate to each others and the corresponding Core versions.

Core	Toad for SqlServer	Toad for DB2	Toad for MySQL	Toad for Data Analysis
1.0	-	-	1.0	-
1.1	-	-	1.1	-
1.2	1.0	-	-	-
1.3	-	1.0	-	-
2.0	-	-	2.0	-
2.1	2.0	-	-	-
2.2	-	2.0	-	-
3.0	3.0	-	3.0	-
3.1	3.1	3.0	3.1	1.0
3.2	3.2	3.1	3.2	1.1
4.0	4.0	4.0	4.0	2.0
4.1 1	4.1	-	4.1	2.1
4.5 1	4.5	4.5	4.5	2.5

¹ Since these releases are not done yet this information is subject to change.