To use the Database Query (ODBC) 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
You open this interface from the resources tree — expand System > Tools >
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
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.
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:
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 Configurator 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.
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.
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.
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.