To use the Database Query (ODBC)Closed You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. tool or to add an ODBC Memory definitionClosed Memory definitions specify the data, generated by a task at run-time, to be memorised. You can save memorised data in an internal Repository, in an ODBC or OLEDB data source, or in an OLEDB data source via a stored procedure., you must create connections to ODBC data sources first — you do this in the ODBC Connections interface.

You open this interface from the resources tree — expand System > Tools > Data Connectors or Input, and double-click Database Query (ODBC) in the items list.

Database Query (ODBC) - Global Configurations

Click the image to view full size.

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 standard connection to an ODBC data source within the same network or domain.

If you are connecting to a remote ODBC data source where the BPA Platform Server instance has no direct connectivity to its location (for example, BPA Platform is partner-hosted in the cloud and the data source exists within a customer's own network) then you must use Add Relay to create the connection using our Remote Data Relay. Continue with Creating a Remote Data Relay ODBC Connection.

The differing connection types are denoted in the Relay column to identify where a connection is standard (No) or is configured to use the Remote Data Relay (Yes).

Note: You may be restricted as to the number of ODBC connections you can create. For more information, or consult your Codeless Platforms account manager or partner.

Creating a Standard Connection

Create a standard connection if your ODBC data source exists within the same network or domain as your BPA Platform Server instance. This can be local to BPA Platform, or on a separate machine.

Selecting an ODBC Driver

Expanding ODBC Drivers displays all ODBC drivers available on the client machine.

Database Query (ODBC) - Global Configurations - Available ODBC Sources

Note: The selected driver must always be present on the BPA Platform server machine for a standard connection. If using the BPA Platform client on a remote machine within the same network or domain as the server to configure your connection or any task step, ensure that the driver is also available on the server machine. Without this, any tasks using the connection will fail at runtime.

Once a driver has been selected, add the connection details to the database. The configuration interface is different for every driver available; the following example is shown when an SQL Server driver has been selected:

Database Query (ODBC) - Global Configurations - Example SQL Server Configuration

Connecting to Data Sources

Expanding Data Sources displays all data sources available on the client machine. If none are available, you must add them to the machine's ODBC data sources (Control Panel > Administrative Tools > ODBC Data Sources). For more information, refer to your Microsoft documentation.

Note: The selected data source must always be present on the BPA Platform server machine for a standard connection. If using the BPA Platform client on a remote machine within the same network or domain as the server to configure your connection or any task step, ensure that the driver is also available on the server machine. Without this, any tasks using the connection will fail at runtime.

Connecting to File Data Sources

Expanding File Data Sources displays all data sources available on the client machine. If none are available, you must add them to the machine's ODBC data sources (Control Panel > Administrative Tools > ODBC Data Sources). For more information, refer to your Microsoft documentation.

Note: The selected data source must always be present on the BPA Platform server machine for a standard connection. If using the BPA Platform client on a remote machine within the same network or domain as the server to configure your connection or any task step, ensure that the driver is also available on the server machine. Without this, any tasks using the connection will fail at runtime.

Creating a Remote Data Relay ODBC Connection

Create a Remote Data Relay connection if your ODBC data source exists externally to the BPA Platform Server instance and with no direct connectivity. For example, BPA Platform is partner-hosted in the cloud and the customer ODBC data source is within the customer's own network or domain.

Connecting to an ODBC Data Source using Remote Data Relay

Selecting Add Relay within the ODBC Connections window opens the ODBC Relay Connection ConfiguratorClosed You use the Configurator to install third-party software that may be required by available tools. Connection to required agents are also configured here. Note that you can only launch the Configurator if logged into the machine with Administrator rights..

Click the image to view full size.

You configure the connection to the Remote Data Relay using the Relay Settings button. Full details on how to configure the relay connection are described in the Remote Data Relay Connection Setup.

Database Query (ODBC) - ODBC Relay Connection Configurator

Click the image to view full size.

Once a valid Remote Data Relay connection is created, the Load button is enabled. Selecting this will present a list of drivers sourced from the remote machine hosting your ODBC data source.

Choose a driver from the list. If the driver you require is not present, then you need to ensure this is present on the remote machine before Refreshing the Drivers list to display it.

After selecting the driver, enter the Connection String. For commonly used drivers, the list of expected Driver Properties is presented for completion in the grid view. The full string appears in the Connection String box.

Database Query (ODBC) - ODBC Relay Connection Configurator

Click the image to view full size.

Alternatively, paste a pre-configured Connection String in the corresponding text box. This will automatically create the properties and their values in the Driver Properties grid view.

Further properties can be manually added to the Connection String or removed, if required. The Driver Properties grid view will automatically update as changes are made.

Note: Any password values are obfuscated for security reasons from the Connection String once they have been saved. They will still appear in the Driver Properties grid view and can either be amended or removed accordingly.

Test the connection to confirm that the connection to the ODBC data source has been successfully created before saving and closing the ODBC Relay Connection Configurator.