Database Query Tool Overview

The Database Query tool can be used to create SQL queries using drag and drop tools within BPA Platform.

This allows for the extraction of specific information sets from relational database systems such as ERP, Financial, Accounting, MRP, Production, CRM, HR, and bespoke systems.

Once obtained, the data is then available to other task steps in BPA Platform for manipulation, delivery, or both.

There are two variants of the tool, Database Query (ODBC) and Database Query (OLEDB).

Both have has comprehensive support for both modern and legacy SQL standards and the OLEDB version has support for the Call Stored Procedure Tool.

This video will focus on the OLEDB variant of the tool

Slide Title: Prerequisites

The following video is a brief overview of how the Database Query tool functions. If you wish to follow this process in your own environment you will need; The training Database – preferably in the same location as BPA Platform And appropriate Drivers installed for the database. We are using Microsoft SQL Server in this example.

Slide Title: Creating a database connection

To use the Database Query (ODBC) tool, you must first create connections to OLEDB data sources.

Let’s open the connection dialog box in System > Tools > Data Connectors and double click Database Query (OLEDB).

And then click add and select your appropriate OLEDB driver. For this example, we are going to select Microsoft OLEDB provider for SQL Server.

The configuration window is different for every driver available; you may have to refer to the documentation for your chosen Driver. Now, Click Ok.

In this instance the database is located on the same machine as BPA Platform and therefore we can select localhost as the server.

We can also select use trusted connection instead of login and password credentials.
The options button reveals more parameters, which may be beneficial if we have more than one Database available. We will choose our required database; in this example we will select BPATrainingDB. We will leave the rest of the setting at the default value and click OK to finish.

We will now give this connection a unique name, and click ok.

We can then select our connection in the list and select Test and click ok.

After establishing the basic connection to your OLEDB data source, within the Properties section you can add a more advanced configuration, if required…

For more information on this, please refer to the Whitepaper that is available on the codeless platforms Website.

We will close this window.

Slide Title: Step Configuration

Firstly, navigate to your tasks folder, and then create a new Task. Name it Account on Hold and select the design tab.

In the browser, with the tab set to tools, in either Input or Data Connectors, drag a Database Query OLEDB step onto the design area.

In the General tab let’s give this step a meaningful name. (Accounts on hold query)

And then select the connection that we configured previously in the connection dropdown.

In the query tab, can you build the SQL statement to query the database, using either the drag and drop tools or by typing the query manually.

Lets create a basic query to demonstrate the process. We will, start by expanding the three panels.

Locate and drag in the following fields into the Columns pane of the window.
From SALES_LEDGER: drag in: NAME, CONTACT_NAME, TELEPHONE, ACCOUNT_ON_HOLD, BALANCE and PRIOR_YEAR.

From the ACCOUNT_MANAGERS table drag in ACCOUNT_REF.

And from PERSONNEL: drag in PNAME, FIRST_NAME and LAST_NAME

Once you have chosen all your columns, you can now link the tables, Sometimes BPA Platform will do this for you.

Lets expand the tables. We will now link sales ledger account ref to the same field in account managers.

In account managers we will link pref to pref in personnel.

Now we have the fields and joins, lets choose the criteria.

We will need to adjust the criteria pane from the bottom or make it visible using this button.

And then drag account on hold, balance and prior year into the pane.

Now these are in place, in account on hold select the equals criteria and set this to 1, for accounts that are on hold

Set balance is greater than 1000 and prior year is greater than 10,000.

There criteria are automatically set as ‘AND’, lets double click the AND criteria to set this to OR and we can group criteria by dragging one on top of another.

Now we can sort the data, Select this icon to show the sorting pane
And Lets drag in the name field to sort the results.

Double clicking the arrow chooses the sorting order.

We will now click the SQL tab ….And you can see the query that is generated.

Lets test the query by selecting the execute icon.

And in the results tab we can see the query output.

Lets go back to the query tab. The previous method allows you to create drag and drop queries, but it is also possible to create and edit the SQL manually, using the free type mode.

It is worth noting that you will no longer be able to use the drag and drop method if you choose this route. Click yes to continue.

More information on the Database query OLEDB and ODBC tools, can be found in the whitepapers on the Codeless Platforms Website.

We will click ok to close this step and click yes.

Slide Title: Next Steps

You can now see a basic example task of an account on hold notification.

The data obtained from the query can be used in a variety of applications. In this example we have used the format as HTML tool to display the data. This could then be passed to a send email step as a notification.

Here is an example notification that displays the output of the query we just created.

If you would like to make your own task like in this example, please see the training videos section on our website.

And that’s it for this BPA Platform tool overview video, call us for further information or visit Codeless Platforms.com.

Thanks for watching.