Introduction to the Database Query (OLEDB) Tool
What is the OLEDB Query Tool?
The Database Query (OLEDB) tool allows the extraction of specific information sets from relational database systems such as ERP, Financial, Accounting, MRP, Production, CRM, HR, Project, and bespoke systems.
Once obtained, the data is then available to other task steps for manipulation or delivery, or both.
The Database Query (OLEDB) tool has comprehensive OLEDB support for both modern and legacy platforms.
OLEDB Query Tool Features
- Support for SQL 89 & 92 and variations thereof
- System, user, and file DSNs
- Forward-only or scrollable cursors
- Catalogues and schemas
- Support for all primary SQL clauses
- System tables, user tables, views
- Inner joins, outer joins, formula joins, Cartesian product
- Formula columns
- Table aliasing
- Flexible pre-built date criteria
- Drag-and-drop visual criteria building with nested parentheses (brackets)
- Common and custom predicates
- SQL free-type mode
Video Overview
White Paper - Database Query (OLEDB)
OLEDB Query Tool Technical Summary
Working with other Tools
The OLEDB query Tooltool can directly interact with the following tools:
Consuming from Other Tools
The Database Query (OLEDB) tool does not consume objects outputted by any other tool. Instead, it directly queries the database.
Exposing to Other Tools
The OLEDB Query tool exposes objects which can be directly consumed by the following tools:
| Icon | Tool Name | Tool Category |
|---|---|---|
 | Convert Recordset to XML | Format |
 | Create Workflow Job | Format |
 | Format as Flat File | Format |
 | Format as HTML | Format |
 | Format as HTML Pro | Format |
 | Format as Text | Format |
 | Run Crystal Report | Format |
 | Run Microsoft Reporting Services | Format |
 | Run Microsoft Word (Merge) | Format |
 | Transform Data | Format |
 | Save File | Output |
 | Transfer File (FTP) | Output |
 | Call COM Object | Execute |
 | Run External Program | Execute |
 | Filter Data | General |
Objects Exposed
The Database Query (OLEDB) tool exposes the following objects which can be directly consumed by the above tools:
- Recordset — Tabular data from any BPA Platform tool capable of exposing such data (see above)
- Step Properties — Standard step properties are available allowing you to use statistical data of the tool
Prerequisites
The Database Query (OLEDB) tool relies on the following third-party software in order to function correctly:
- OLEDB Compliant Data Source
- OLEDB driver
Adding OLEDB Connections
Before adding the Database Query (OLEDB) tool to a task, connections to OLEDB data sources must be configured.
Once configured, all connections can be used by any Database Query (OLEDB) step.
You open the OLEDB Connections dialog box by either:
- Going to Manage > Tools >, then either Data Connectors > Database Query (OLEDB) or Input > Database Query (OLEDB)
- From the Task Manager, expanding BPA Platform > System > Tools > Data Connectors or Input, and double-clicking Database Query (OLEDB) in the Items List
Use Properties to edit an existing connection, Remove to delete an existing connection, and Test to ensure the
highlighted connection can be successfully made.
Click Add to create a new connection; select the required ODBC driver or data source.
Selecting an OLEDB Driver
Platform server, must have the selected driver installed on the machine. Without the driver, the task will fail.
Once a driver has been selected, add the connection details to the database. The configuration window is different for every driver available; the following example is shown when an SQL Server Native Client driver has been selected:
Editing OLEDB Connection Properties
After establishing the basic connection to the relevant OLEDB driver or data source, you can add more advanced configuration, if required. Highlight the relevant connection and click Properties.
General Tab Properties
- Name — The connection name
- Additional Providers Information — Where available, additional information for the connection is displayed
Connection Tab Properties
- Connection String — Contains the full connection string.
- Use Pass-Through — This property is only available with some drivers. You can choose to make the connection directly to the driver (enabled), or use the Microsoft Data Access Objects (DAO) API to make the connection to the driver.
- Reconfigure — If any changes are required, use Reconfigure to open the configuration window.
- Test — Tests the connection to the database.
- Connection Settings — If required, you can set a time-out period for connecting to the database in Seconds.
Cursors Tab Properties
Use the properties in the Cursors tab to change the type of database cursor used when executing queries.
- Basic forward only — Use this option where you expect the task to only use the results from the Database Query (OLEDB) step once. The result set from the queried rows can only be read top-to-bottom. The cursor cannot be reset so the query cannot be re-used. All OLEDB drivers support this mode.
- Advanced cursor attributes
- Re-usable (scrollable) — This property allows the cursor to move backwards and forwards through the result set, thus the Database Query (OLEDB) output can be consumed by multiple steps in the same task.
- Do not reflect changes in underlying data source — The rows returned by a query are cached independently at the time the query is executed. When the result set is actually read, any changes made to the source data since the query was executed are not reflected in the result set.
- Reflect row deletions and changes only — Any changes made to the source data since the query was first executed (updates and deletes to rows in the result set only) are reflected.
- Reflect row deletion, changes and additions — Any changes made to the source data since the query was first executed (updates, inserts, and deletes) are reflected in the result set.
SQL Tab Properties
The properties available in the SQL tab set the supported rules for the selected OLEDB driver for the SQL statements.
For more information, refer to your OLEDB driver documentation.
Step Configuration
Even though the Database Query (OLEDB) tool is found under two different tool categories, they do not affect the functionality of the tool.
To add a new Database Query (OLEDB) step to an existing task, you either:
- Click and drag the Database Query (OLEDB) icon from the Task Browser to the task Design area.
- From the task’s Design tab, right-click on empty space and select Add > then either Data Connectors > Database Query (OLEDB) or Input > Database Query (OLEDB).
For a detailed description of how to create new tasks, refer to the product help.
About the General Tab
The General tab is used to enter the following details for the step:
- Name — Enter a meaningful name for the step
- Description — If required, enter a description of this step
- Connection — You must choose the OLEDB connection for this task step to use..
About the Query Tab
You use the Query tab to build the SQL statement to query the database. You can either use the Design interface to build the statement or create it manually in the SQL tab.
Using the Design Interface
The Design tab allows you to build the SQL statement by dragging-and-dropping database formulas, tables, and table columns from the schema tree on the left:
Tables & Joins
The Tables & Joins pane displays the tables within the query and the relationships (joins) between them.
If not shown, click the icon in the toolbar to display this section.
Columns
The Columns pane is used to specify the columns that are returned in the query. Available database columns are displayed in a tree structure to the left of the interface. Formulas can also be created “on-the-fly” to provide manipulation of data whether for formatting or calculations.
Grouping
The Grouping pane is used to create a query that returns consolidated data and is commonly used in conjunction with aggregation functions such as SUM or COUNT. By selecting certain columns in the Grouping pane the data is consolidated so that no duplicates exist across the fields returned. In turn, the aggregate functions allow information such as totals or averages to be calculated across the records that are consolidated. The pane corresponds directly with the GROUP BY clause in the SQL syntax.
If not shown, click the icon in the toolbar to display this section.
Criteria
The Criteria pane is used to restrict the rows that are returned by the query — the WHERE clause of the SQL statement. The defined criteria are applied before Grouping, if used — see above. Multiple criteria can be added and nested to ensure precedence when using a combination of AND and OR operators.
If not shown, click the icon in the toolbar to display this section.
Having
The Having pane has exactly the same features as the Criteria pane. Columns are added in the same way and criteria selected for filtering is identical. The difference between them is the order in which they are processed when the query is run. The Having filter is always applied after the Grouping filter. For example, a company has offices in different locations:
- Columns are added in the Columns area to provide order and invoice information for all offices.
- A Criteria filter is applied to only include orders received for the current month.
- A Grouping filter is then applied to group all rows by office location.
- Finally the Having filter is applied to only include orders for which an invoice has been raised.
If not shown, click the icon in the toolbar to display this section.
Sorting
Columns are added to the Sorting pane to set the order in which the query results are displayed — the SORT BY clause. The arrow to the left of the added column indicates the direction of the sort (ascending (default) or descending). Double-click the arrow to change the direction.
Columns are sorted in the order they are added but you can change this by dragging-and-dropping columns to new positions. If not shown, click the icon in the toolbar to display this section.
Adding Formulas
Formulas are used to create pseudo-columns only available for use by the task.
For example, to display the monetary value of an item in a different currency, you would create a formula to multiple value by a fixed number. You’d then drag the new formula to Columns to be included in the query.
To create a formula, either:
- Right-click on Formula in the left-hand tree and select New Formula
- Right-click on a table join in the Tables & Joins section and select Convert to Formula (recommended when creating formulas based on the result set of the table join)
Drag-and-drop tables or columns from the Schema tree to the editing area — when converting a join to a formula, the columns are automatically added to the editing area joined with a =, new formulas have a blank editing area.
Choose the Formula Data Type from the drop-down; you must set this if adding columns to the formula which have different data types.
If required, add a short Description for the formula.
The available Functions are dependant on the underlying database engine and the driver in use.
Click Tables to ensure the correct tables are linked to the formula.
Click Parse to ensure your new formula is correct.
Adding Predicates
SQL predicates are logical conditions applied to clauses, functions, and expressions in the statement. Click the icon in the toolbar to add predicates:
Standard SQL predicates are supported and provision is made to support proprietary predicates available with certain drivers:
- ALL — Return all query results as opposed to other predicates that restrict the query results. This predicate is included largely for completeness as the majority of drivers assume the ALL predicate if no other predicate is used.
- DISTINCT — Removes duplicate rows, taking into account only those columns that are requested in the query.
- DISTINCTROW — Removes duplicate rows, taking into account all columns in the source for the tables requested in the query, irrespective of the columns that are actually requested in the query.
- TOP — Return the number of rows specified, starting from the top of the result set. The sorting of the query affects which rows are returned.
- TOP (PERCENT) — Return the percentage of rows specified, starting from the top of the result set. The sorting of the query affects which rows are returned.
- Other predicate — Allows another predicate statement to be manually entered. Such statements are
inserted after any standard predicates selected.
Using SQL Free Type Mode
The SQL tab displays the script version of the SQL statement being build in the Design tab:
If required, you can edit the existing script or create it from scratch using the “SQL free type mode” feature.
The Database Query (OLEDB) tool can detect whether its driver can support a query language, no query language, or both. If a query language is supported, Execute Command is enabled which executes the whole query script entered. If the driver cannot support a query language, Open Table / Object is enabled which only opens the tables and objects stated in the script.
Testing the SQL Statement
From the toolbar, click the icon to test the script. Results are shown in the Results tab. If required, click to the icon to cancel the current test being run.
About the Results Tab
The Results tab displays the results of the SQL statement built in the Query tab — click the execute button first.
If required, to speed up the test you can Limit [the] sample record count to return a reduced number of rows.
About the Options Tab
The Options tab allows you to define how errors in this step are handled at task runtime.
If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task).
