Using Data Sources

Sophisticated applications may need to test the same script using a whole range of values for their input. For example you may want to test all the boundary conditions for an operation – what happens when somebody orders quantities of 0, 5, 10, 1000 of a particular item? Or perhaps you want to test that ordering of every item in your warehouse works. Doing these kind of operations where you want to run the same script over many different values for a parameter (or variable) is greatly eased by connecting your scripts to a Data Source.

Badboy supports reading values for variables through ODBC. This means that if you set up an ODBC data source (which could be anything from an Oracle database to a comma separated text file) then Badboy can read variable values from it and when variables are incremented in your script they will iterate through the values in your source.

This help uses an Excel file as an example – but remember that you can use any ODBC source.

Data Source Requirements

By default Badboy requires data sources to satisfy certain requirements. (Note: you can change or avoid these requirements if you know how to write SQL for your Data Source. See the information on Using Custom SQL later in this topic for more information.)

1. All the values for all variables must appear in a single table
   
   
2. The table must have column headings that match the variable names that you want to be read
   
   
3. The values for the variables should appear under the corresponding headings
   
   
4. You may optionally include a column called “SEQ”. If this column exists then it will be used to order the values that are read from the data source. It must be a sortable data type (plain integer values are suggested). Note that if the column does not exist then there are no guarantees about the order in which the values will be used.

Here is how an example Excel spreadsheet satisfying these criteria might look:

This shows how you would create an excel file to load values for variables “Pieces” and “weight”. Note that all names are case sensitive, and that in some cases the ODBC may convert the names to upper case without asking you. You will need to experiment with your data source to get this right.

Adding a Data Source

Data Sources are configured in Badboy by adding "Data Source" items from your Toolbox into your script.

The image below shows how a Data Source item looks after adding to a script:

Setting the Properties

When you add a Data Source item to your script you will be given a choice of all the ODBC data sources on your system. Some data sources must be set up in advance using the Control Panel on your computer (from the Start menu choose Settings->Control Panel and look for the “Data Sources” applet, which may be under Administrative Tools depending on your version of windows). Other data sources, for example Excel files, can be accessed directly from Badboy. For Excel files there should be an “Excel Files” option in the drop down menu (you must have Excel along with its ODBC drivers installed). Select this option and Badboy will read the file and show you the worksheets inside.

You should then select the worksheet from which you would like Badboy to load values. Once you have selected the worksheet, Badboy will return you to the Data Source Properties page where you can select other preferences for how data should be loaded. The diagram below shows the Properties page for configuring a Data Source:

Controlling the Format of Loaded Data

You can review how the loaded data will look by clicking the "Format..." button. This will cause Badboy to load the data from your Data Source and show it to you. If the values do not appear in the format that you want then you can choose from among some format options on this screen to change how they are loaded.

The diagram below shows an example of this:

Playing Data Source Items

Data is loaded when a Data Source item is played as part of the script execution. Hence after defining your data source you won't see any values loaded until you cause it to be played, for example, by right clicking on the item and selecting 'Play'.

Note: prior versions of Badboy loaded data from data sources when Badboy was started. If you have legacy scripts with data sources attached then they will still behave this way. New scripts, however, should use the methods described in this section.

Using Data Source Values

Badboy will automatically read the next value from your data source each time a variable is incremented. This could be as a result of an auto-increment (performed at the start of the script when a variable is flagged for auto-increment) or as a result of an explicit increment placed in your script.

Looping Over Values in a Data Source

A very common scenario is to load values from a data source and then repeat a Step for each row of values in the data source. Badboy makes this very easy to do by using the looping properties of Steps. You can do this by taking the following actions:

• Add the data source to your script to load the data
• After the data source, add a new Step
• Put the actions that you want to occur for each row of the data set into the new Step
• Play the data source once to load the variables
• Set the new Step to loop over one of the variables loaded by your data source

When you have finished, your Script would look something like the figure below:

Advanced Options

Badboy offers some advanced options that you can use to load data in more sophisticated ways. These are:

• Using Custom SQL If you know how to write SQL that your Data Source understands then you can make up your own SQL statement to load data from your data source. Badboy will try to execute this statement against the Data Source and it will treat the returned columns and rows as a table from which to load data. You should make sure that your SQL statement returns appropriately named columns that Badboy can understand. By default, Badboy will convert the columns names to variable names.
• Custom Variable Mapping By default Badboy tries to use the columns returned from your table (or from your Custom SQL, if you have chosen that option) as variable names. If you want to change how the columns get matched to variables, choose the "Custom" option in the lower portion of the Data Source Properties dialog. This will show you a list of the variables you have defined to choose from. You can then "check" the ones that you want Badboy to map data to from your data source. When the Data Source item is played, Badboy will then load each checked variable, in order, from the corresponding column from the Data Source. In this way you can map any column from your Data Source to any variable that you have defined in Badboy.