Querying a Database

General Tab

Database-Query-ODBC-General-tab

The Database Query (ODBC) tool is dragged onto the task planner. The step can be given a recognisable name. A connection to the business systems database must be selected from the list. Setting up these connections is covered in TaskCentre Administration – Global Configuration – ODBC / OLE DB connections.

Query Tab

On the second tab of this tool the query that will return the data is defined. A query can be defined using the drag and drop query editor. A knowledge of the structured query language, SQL, is not essential but may be needed for more advanced queries. You can read up on SQL on the w3schools website.

Database-Query-ODBC-Query-tab

On the left are the tables and views available for the connection. Each table or view can be expanded to see the data columns within it.

Database-Query-ODBC-Tables

Dragging the data columns from this list into the columns pane to the right adds them to the data returned by the query and makes them available for subsequent steps. Adding a column from a table to the columns pane automatically adds it to the Tables & Joins pane.

Joins

If data from multiple tables is required then for the query to work joins between the tables must be defined. If the joins are defined in the database then they will automatically appear. If not, dragging a column from one table to a column in another table creates a join. By default a simple join (inner join) is created but this can be changed by right clicking on the arrow between the tables and selecting ‘Join Type’. You can then select alternative types of joins.

Database-Query-Join-Type

Criteria

The criteria pane allows the user to create multiple criteria that limit the records returned to only those matching the criteria. To create a criterion a column can be dragged from the list on the left into the Criteria pane. Database-Query-ODBC-Criteria

A comparison method is selected and a value or values entered (depending on the comparison method) or a value selected from a list of those available in the database. Multiple criteria can be dragged into the criteria pane if required.

Database-Query-ODBC-Multiple-Criteria

By default an ‘And’ is added between the criteria, this can be changed to an ‘Or’ by double clicking the ‘And’ or ‘Or’. Dragging a column onto a criterion creates a nested criterion group.You can move a group by dragging and dropping it into another position in the criteria.

Database-Query-ODBC-Nested-Criteria

Sorting

If it is required to sort the results, the sorting pane can be added by clicking the sort pane button.

Database-Query-ODBC-Sorting

Multiple columns can be dragged from the left hand pane or the columns pane into the sorting pane. The hierarchy of the sort can be changed by dragging the columns into the desired order. Double clicking a column in the sorting pane reverses the order of the sort on that column (ascending or descending).

Testing the Query

The query can be tested by pressing the Test button Database-Query-ODBC-Test-Button.

Database-Query-ODBC-Test-Results

A sample set of records are displayed. This recordset (with the complete data set) is the output from this step.

Manual SQL edits

The above actions generate a SQL statement that is executed at runtime. This SQL statement can be viewed on the Query tab on the SQL sub tab. Optionally, this SQL statement can be manually edited (or rewritten completely). Database-Query-ODBC-Manual-SQL

In order to edit it the Free Type Mode button must be pressed.

To read about tasks which make use of this tool see

Building a scheduled plain text email alert

Building a MS SQL database triggered html email alert

Building a task which triggers when an email is received

Building a task which uses a decision step to control the task process flow

Building a task that sends a Crystal Report by email

Building a task that sends a MS Reporting Services Report by email