Search

Database Connector Setup

Version 22.0.8473

Database Connector Setup

The Database connector allows you to integrate various databases into your data flow by pushing or pulling data from these databases. Follow the steps below to connect CData Arc to your database.

Establish Database Connection

Before you can add the Database connector to your flow, you must configure the connection to your database on the local machine.

ODBC

A data source name (DSN) is required to connect to a database via an ODBC driver, and can be created with the Microsoft ODBC Administrator. Arc must use a system DSN connection for the ODBC driver. Once the DSN has been created in the system, select the appropriate DSN from the Data Source Name connector dropdown.

Note: The Database Connector supports ODBC drivers for databases other than the default options. However, custom drivers may require overwriting the queries issued by the connector to fit the syntax expected by the database. Queries can be overwritten via the Write custom query section of an Input Mapping or Output Mapping.

ADO.NET

In order for an ADO.NET provider to be visible to Arc, it must be defined in the machine.config file and registered in the Global Assembly Cache (GAC). This can be accomplished manually by knowledgeable users, but it is recommended to install the database client on the machine hosting Arc, as the database-specific installation wizard will handle defining and registering the provider.

JDBC

In order for a JDBC driver to be visible to Arc, it must be included as part of the Java classpath. Arc includes drivers for MySQL, PostgreSQL, and Derby; refer to the documentation for the Java web servlet to add a different driver to the classpath.

Note: The Database Connector supports JDBC drivers for databases other than the default options. However, custom drivers may require overwriting the queries issued by the connector to fit the syntax expected by the database. Queries can be overwritten via the Write custom query section of an Input Mapping or Output Mapping.

Configure Database Connector

After you configure the database connection, you can add a Database connector to your Arc flow. You must enter the database connection credentials into each instance of the Database connector.

Follow these steps to establish a connection:

  1. Drag the Database connector into an Arc flow and give it a name.

  2. In the Connector Configuration pane, select the Driver Name from the drop-down menu.

  3. Choose the Settings Format and enter the requested information in each field.

  4. Click Test Connection to ensure that Arc can connect to the Database. If an error occurs, check all fields and try again.

  5. Click Save Changes.

Select an Action

After establishing a connection to Database, you must choose the action that the Database connector will perform. The table below outlines each action and where it belongs in an Arc flow.

Action Description Position in Flow
Upsert Inserts or updates Database data. By default, if a record already exists in Database, an update is performed on the existing data in Database using the values provided from the input. End
Lookup Retrieves a value from Database and inserts that value into an already-existing Arc message in the flow.
The Lookup Query determines what value the connector will retrieve from Database. It should be formatted as a SQL query against the Database tables.		 Middle
Select Retrieves data from Database and brings it into Arc.
You can use the Filter panel to add filters to the Select. These filters function similarly to WHERE clauses in SQL.		 Beginning
Execute Stored Procedures Treats data coming into the connector as input for a stored procedure, and then passes the result down the flow.
You can click the Show Sample Data button to provide sample inputs to the selected Stored Procedure and preview the results.		 Middle