Version 23.4.8839

• Overview
• Connector Configuration
  • Settings Tab
    • Driver Configuration
    • Database Connection
    • Mappings
  • Automation Tab
    • Automation Settings
    • Performance
  • Advanced Tab
    • Advanced Settings
    • Miscellaneous
• Establishing a Connection
  • ODBC
  • ADO.NET
  • JDBC
• Input Mappings
  • Creating an Input Mapping
    • UPSERT
    • INSERT
    • LOOKUP
  • Input Mappings as XML
  • Batching Input
  • INSERTing into Child Tables
    • REF
    • LOOKUP
    • Child Table XML
• Output Mappings
  • Creating an Output Mapping
    • Output Filters
  • Output Mappings as XML
  • Batching Output
    • Overwriting the SELECT Query
  • Only Process New or Changed Records
    • Using a Last Modified Column
    • Using a Custom Column
  • SELECTing From Multiple Tables
    • Parent-Child Foreign Key Relationships
    • Parent-Child Output Mapping XML
• Input/Output Mappings
  • Creating an Input/Output Mapping
    • Input Parameters
  • Input/Output Mappings as XML
    • Input XML
    • Output XML

The Database Connector can store and retrieve data from various databases using ODBC and ADO.NET (Windows) or JDBC (Java) drivers.

Overview

Database Connectors connect to one of many different database providers. Each Database Connector instance is configured with the driver appropriate for the target database.

After establishing a connection to the target database, the connector reads the list of tables/views present in the database. Create an Input Mapping or Output Mapping by selecting an available table and choosing columns from within this table. The connector generates an XML schema structure using the selected table and columns.

After an Input Mapping has been generated, the connector will automatically insert data into the database when it receives XML files that match the XML structure of the Input Mapping. Please see the Input Mappings section for more details.

After an Output Mapping has been generated, the connector will pull data from the database and use the pulled values to populate XML files that have the same XML structure as the output mapping. Please see the Output Mappings section for more details.

Input/Output Mappings are a special kind of mapping used for Stored Procedures that both require input (parameters) and generate output. Please see the Input/Output Mappings section for more details.

Connector Configuration

This section contains all of the configurable connector properties.

Settings Tab

Driver Configuration

Settings related to the database driver.

• Connector Id The static, unique identifier for the connector.
• Connector Type Displays the connector name and a description of what it does.
• Connector Description An optional field to provide a free-form description of the connector and its role in the flow.
• Connection Type The type of database driver used to communicate with the data source. The Windows edition supports ODBC and ADO.NET drivers, and the Java edition supports JDBC drivers.

Database Connection

Settings related to establishing the database connection. These fields are dynamically generated based on the type of database used.

• Driver Name (ADO.NET) The type of database to connect to.
• Data Source Name (ODBC, JDBC) The type of database to connect to.
• Connection String The database credentials in connection string format. Only applicable for ADO.NET connections, and is used in place of the other connection fields.
• Server The host name or IP address of the server hosting the database. Only applicable for some driver types.
• Port The port on which to connect to the database host. Only applicable for some driver types.
• Database The name of the database to connect to. Only applicable for some driver types.
• User The user credential that has permission to access the database.
• Password The password credential associated with the specified User.

Mappings

Establish mapping relationships between database tables and XML document structures. Once these mappings are created, XML data can be inserted into the database and data retrieved from the database can be pushed out as XML.

• Input Mappings Input mappings create a relationship between an XML document structure and a database insert (or update). When XML files that match the structure of the input mapping reach the Database Connector, the connector makes an insert/update call to the database with the values from the input file. For more information on generating Input mappings, see the Input Mappings section.
• Output Mappings Output mappings create a relationship between an XML document structure and a database select statement. When the connector receives data from the database, it populates an XML file with the same XML structure as the Output mapping. This XML file is then pushed on to the next connector in the Arc flow. For more information on generating Output mappings, see the Output Mappings section.
• Input/Output Mappings Input/Output mappings are used for Stored Procedures that both expect input (parameters) and produce output. These mappings establish two XML document structures, one for input files that provide the parameters of the Stored Procedure, and one for output files that return the results of the Stored Procedure.

Automation Tab

Automation Settings

Settings related to the automatic processing of files by the connector.

• Send Whether XML files that match an Input mapping will automatically be inserted into the database.
• Retry Interval The amount of time before a failed insert/update is retried.
• Max Attempts The maximum number of times a failed insert/update will be retried before an error is thrown by the connector.
• Receive Whether data should automatically be pulled from the target database and used to generate XML files that match the configured Output mappings.
• Receive Execution Interval The amount of time between automatic attempts to retrieve data from the database.
• Minutes Past the Hour The minutes offset for an hourly schedule. Only applicable when the interval setting above is set to Hourly. For example, if this value is set to 5, the automation service downloads at 1:05, 2:05, 3:05, etc.
• Time The time of day that the attempt should occur. Only applicable when the interval setting above is set to Daily, Weekly, or Monthly.
• Day The day on which the attempt should occur. Only applicable when the interval setting above is set to Weekly or Monthly.
• Minutes The number of minutes to wait before attempting the download. Only applicable when the interval setting above is set to Minute.
• Cron Expression A five-position string representing a cron expression that determines when the attempt should occur. Only applicable when the interval setting above is set to Advanced.

Performance

Settings related to the allocation of resources to the connector.

• Max Workers The maximum number of worker threads consumed from the threadpool to process files on this connector. If set, this overrides the default setting on the Settings > Automation page.
• Max Files The maximum number of files sent by each thread assigned to the connector. If set, this overrides the default setting on the Settings > Automation page.

Advanced Tab

Advanced Settings

The list of available advanced settings depends on the specific database type selected. Please see the documentation for the specific database for more information (e.g. if using a MySQL driver in the Database Connector, please see the MySQL Connector). The settings available for all databases are included below.

Miscellaneous

Miscellaneous settings are for specific use cases.

• Other Settings Enables you to configure hidden connector settings in a semicolon-separated list (for example, setting1=value1;setting2=value2). Normal connector use cases and functionality should not require the use of these settings.

Establishing a Connection

Establishing a connection with the Database Connector requires the appropriate database driver and database-specific connection properties. The connector UI will dynamically update with the expected connection properties based on the target database.

After filling in connection properties, the Test Connection button verifies that the connector can communicate with the target database.

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 ones listed at the top of this page; 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 CData 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 ones listed at the top of this page; 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.

Input Mappings

Input Mappings represent an INSERT/UPDATE to one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. Files processed by the connector that match the XML structure of an Input Mapping will automatically be converted into INSERT or UPDATE queries.

The process of converting XML to database queries is detailed in the Input Mappings as XML section; conceptually, the connector is simply reading values from XML elements and using them when making INSERT/UPDATE statements.

Creating an Input Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be included in the INSERT/UPDATE. For simple INSERTs, this is all that is required to generate a mapping.

After an Input Mapping is created, its XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

UPSERT

UPSERT settings govern how the connector decides whether to INSERT or UPDATE. The most common case is to set UPSERT by to the primary key of the table. With this configuration, the connector reads the primary key value from the input XML, queries the database to see if this key already exists in the table, updates the record if it does exist, and inserts a new record if it does not. UPSERT by can be set to columns other than the primary key, but this might result in multiple records being returned from the database. In this case, the connector updates the first record that is returned.

If Perform this query to select the UPSERT key is set to a SELECT query, the connector compares the results of this query to the input XML to determine whether to INSERT or UDPATE. This is useful when the primary key for the target table is not available in the input XML, but can be fetched by referencing another column.

For example, imagine that the primary key CustomerID needs to be used to perform UPSERT logic. The input XML does not have the CustomerID, but it does have a CustomerPhoneNumber that uniquely identifies each customer. The UPSERT query for CustomerID could be set to the following:

SELECT CustomerID FROM Customers WHERE CustomerPhoneNumber = @CustomerPhoneNumber

The ‘@’ syntax in the above query indicates that the value should be read from the CustomerPhoneNumber element in the input XML.

The connector runs this query against the database, and returns a CustomerID value if a record already exists with the same CustomerPhoneNumber. Then, this CustomerID is used to UPDATE a record (otherwise a new record is INSERTed).

INSERT

If UPSERT is disabled in the Input Mapping, the connector will automatically set the query action to INSERT. The connector will attempt to INSERT new records for each input XML file without querying the database to see if the record already exists.

LOOKUP

Some columns have a LOOKUP option. The connector has detected that these columns have a foreign key relationship with another table in the database. If LOOKUP is enabled, the connector queries this external table prior to INSERTing, and uses the value that is returned in the INSERT. The LOOKUP by field determines which column is used to query the external table.

For example, imagine INSERTing a Purchase Order (PO) into a database where one of the fields in the ‘PO’ table is an internal CustomerID. Imagine that the input XML has a CustomerName element, but not the customer’s internal ID. However, the ‘Customers’ table in the database has both the name and ID, so it can be used to lookup the ID value based on the name. The connector recognizes that the CustomerID column of the ‘PO’ table is a foreign key, and enables LOOKUP for that column. Setting LOOKUP by to CustomerName instructs the connector to query the ‘Customers’ table with a statement like this:

SELECT CustomerID FROM Customers WHERE CustomerName = @CustomerName

The ‘@’ syntax in the above query indicates that the value should be read from the CustomerName element in the input XML.

With this configuration, the connector first retrieves the CustomerID from ‘Customers’, then uses that value when INSERTing into the ‘PO’ table.

Input Mappings as XML

Input Mappings are represented in XML, with the following structure: a parent element identifies the table to insert into, and each child element corresponds to a column in the target table. The Input Mapping establishes the connection between XML elements and database columns, so that the connector can read values from incoming XML documents and use them in INSERT or UPDATE queries.

The following XML is an example Input Mapping for a ‘Students’ table:

<Items>
    <Students action="insert">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the connector processes an XML file that matches this structure, it will automatically insert the values from that XML into the ‘Students’ table. The following is an example XML input file for the above mapping:

<Items>
    <Students>
        <StudentID>12554</StudentID>
        <Name>Ferris Bueller</Name>
        <Year>11</Year>
        <GPA>1.8</GPA>
    </Students>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

When the connector processes the above input file, it will INSERT or UPDATE two records in the ‘Students’ table, one for each Students element. Note that the primary key of the table does not need to be included in the input XML if the database can automatically generate the primary key via AUTOINCREMENT.

Batching Input

The database connector supports batching to improve performance when inserting large sets of data. Batching is configured via two fields, TransactionSize and BatchInputSize. When these fields are set to positive integers, the connector will insert [BatchInputSize] records in a single operation (batch), and will insert [TransactionSize] records in a single connection (transaction).

An XML input file can contain more records than a single transaction size, in which case the connector will insert the data over multiple transactions.

If an error occurs during a batch insert, the connector will rollback the transaction and cache the index of the first record in the failed transaction. When the input file is retried, only records after the cached index will be inserted into the database.

INSERTing into Child Tables

Input Mappings support INSERTing into multiple tables with a single mapping. When creating an Input Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Input Mappings to insert into tables that are not related through a foreign key.

To INSERT into child tables, first create an Input Mapping that targets the parent table. Then add child tables from within the Input Mapping Editor using the +Add button in the upper-left. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Input Mapping.

REF

Some columns within child tables will be marked with a blue ‘REF’ tag. The connector has detected that these columns correspond to the primary key in the parent table. After making the insert into the parent table, and before making the insert into the child table, the connector will query the parent table for the primary key of the record it just inserted. The connector will then use this value when inserting into the child table.

This is useful when the primary key of the parent table is generated by the database, such as an AUTOINCREMENT key. In this case, this column is not passed in as input XML, so the connector does not know the value of the column until after it has INSERTed the record into the parent table.

In the Input Mapping XML, the ‘ref’ attribute indicates that a column can be referenced later or that the value for a column should be read from another column in the mapping. In order to reference the value of columns that are generated by the database (e.g. AUTOINCREMENT primary keys), the column element should include a ‘ref’ attribute set to the reserved value LAST_INSERT_ID:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />

This indicates that the film_id value will be retrieved after the insert and can be referenced later in the mapping (usually in a child table). To reference this value later, set the ‘ref’ element to the name of the AUTOINCREMENT column:

<film_id key="true" ref="@film_id" />

In the above example, film_id is the name of the primary key in the parent table, and also the name of a column in the child table. The ‘ref’ syntax instructs the connector to retrieve the film_id from the parent table after inserting, and using that retrieved value as the film_id column in the child table.

LOOKUP

LOOKUP logic for child tables is the same as parent tables. Please see the LOOKUP section in Creating an Input Mapping for more details.

Child Table XML

The Input Mapping XML structure will be multi-layered when inserting into parent and child tables. The following XML is an example Input Mapping for two tables related by a foreign key:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />
    <description />
    <language_id />
    <rating />
    <release_year type="date" />
    <title />
    <film_actor>
        <actor_id key="true" />
        <film_id key="true" ref="@film_id" />
        <last_update type="datetime" />
    </film_actor>
</film>

In the above example, ‘film’ is the parent table and ‘film_actor’ is the child table. The film_id column is the primary key to the ‘film’ table, and a foreign key in the ‘film_actor’ table. When the Database Connector processes an input XML file that matches this structure, it will update both the ‘film’ and ‘film_actor’ table and ensure that the keys linking the two records are the same.

Output Mappings

Output Mappings represent a SELECT query from one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. The results returned from the database are used to populate XML documents that match the structure of the Output Mapping. The process of converting database responses to XML is detailed in the Output Mappings as XML section.

Creating an Output Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be returned in the SELECT statement. For simple SELECTs, this is all that is required to generate a mapping.

After an Input Mapping is created, the query it represents can be executed by clicking the ‘Execute’ button (a black play button) next to the mapping name. Additionally, the mapping’s XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

Output Filters

The Mapping Editor includes a ‘Filters’ panel to define rules that restrict the records that are returned by the database. Each rule corresponds to a WHERE statement in the resulting SELECT query, and compares a specified column against a specified value; only records with a matching column value will be pulled.

Rules are combined with AND and OR logical operators, and rules can be grouped together to preserve order-of-operations when many rules are applied.

Output Mappings as XML

Output Mappings are represented in XML, with the following structure: a parent element identifies the table to SELECT from, and each child element corresponds to a column in the target table. The Output Mapping establishes the connection between database columns and XML elements, so that the connector can read values from database results and use them to populate XML documents.

The following XML is an example Output Mapping for a ‘Students’ table:

<Items>
    <Students selectQuery="SELECT * FROM `Students` WHERE `GPA` = 4.0">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the Output Mapping is executed, the connector will run the specified selectQuery and generate an XML document for each record that is returned. The following is an example XML Output file for the above mapping:

<Items>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

Batching Output

When querying for output, you can configure the connector to support batching records together into a single document. The Max Records setting governs how many records should be included in a single message, and the Batch Size setting indicates how many Batch Messages should be included in a single Batch Group.

Overwriting the SELECT Query

The Output Mapping XML displays the SELECT query that will be run against the target database. The connector builds this query based on the settings in the Output Mapping, but the query can be manually overwritten if fine-grain control over the query is required.

Simply edit the ‘selectQuery’ attribute of the element that shares a name with the database table and set it to any arbitrary SELECT query.

Only Process New or Changed Records

Output Mappings can be configured to only retrieve records that have been updated or added recently. The connector can use two approaches for detecting which records are new or modified.

The first approach requires a DateTime column that represents the last-modified time for records, and the second requires a column that explicitly stores a value representing whether a record should be processed or not.

Using a Last Modified Column

In the Mapping Editor, expand the Advanced settings at the bottom of the panel. If a DateTime column is detected, the option will be available to Use column for processing new or changed records. Set this to the appropriate DateTime column that represents the last-modified time for records.

Using a Custom Column

Output Mappings can be configured to update a column in the table for records that are successfully pulled from the database. This can be combined with a Filter rule that only SELECTs records with a column value indicating that it has not yet been pulled (or should be pulled again).

To enable column updating after processing, expand the Advanced settings at the bottom of the Mapping Editor. Enable Update column with value when rows are processed successfully, then specify which column should be updated and the value that should be set in this column.

For example, an Output Mapping might be configured to update the Processed column to ‘1’ after pulling a record. Then, a Filter rule could be applied that restricts records to only those where Processed not equal 1.

In this example, when the connector processes the output template, it creates a SELECT statement based on the template. The template would execute “SELECT Id, AccountId, Amount FROM INVOICE”, create an XML file for every record in the result set based on the template, and place those files in the Output folder for this connector.

SELECTing From Multiple Tables

Output Mappings support querying multiple tables with a single mapping. When creating an Output Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Output Mappings to SELECT from tables that are not related through a foreign key.

To query child tables, first create an Output Mapping that targets the parent table. Then add child tables from within the Output Mapping Editor using the +Add button in the upper-left corner. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Output Mapping.

Next, a WHERE filter should be applied to the child table mapping to reflect the foreign key relationship with the parent table.

Parent-Child Foreign Key Relationships

Typically, When SELECTing from a parent table and child table, records should only be pulled from the child table if a foreign key matches the primary key from the parent table.

As an example, imagine an Output Mapping that pulls purchase order data from a parent table, ‘PurchaseOrders’, and a child table, ‘PurchaseOrderLineItems’. Each record (order) pulled from ‘PurchaseOrders’ should also pull the line items associated with that order. The two tables are related by a shared column, for example a PONumber column that is the primary key of the ‘PurchaseOrders’ table and a foreign key in the ‘PurchaseOrdersLineItems’ table. Line item records should only be pulled from the child table if the line item’s PONumber matches the PONumber of the order being pulled from the parent table.

To establish this relationship in an Arc Output Mapping, add a new Filter to the child table mapping and configure it as follows:

• In the first (left-hand) dropdown, select the column of the child table that is the foreign key to the parent table. In the above example, this would be the PONumber column of the ‘PurchaseOrderLineItems’ table.
• In the middle (operator) dropdown, select ‘equal’
• In the last (right-hand) input box, first use the dropdown arrow to the right to select ‘REF’. Afterwards, the input box should have the $ prefix, indicating that it is referencing the parent table
• Use the last (right-hand) dropdown to select the parent table column that should match the child table’s foreign key. In the above example, this would be the PONumber column of the ‘PurchaseOrders’ table.

Parent-Child Output Mapping XML

The XML representation of an Output Mapping that includes a parent and a child table has a hierarchical structure to reflect the parent-child relationship. Both the query to SELECT from the parent table and the query to SELECT from the child table will be included in the XML:

<PurchaseOrders selectQuery="SELECT * FROM `PurchaseOrders` ">
  <PONumber key="true"/>
  <AccountId/>
  <Amount/>
  <AppliedAmount/>
  <Balance/>
  <BillingCity/>
  <BillingCountry/>
  <BillingLine1/>
  <BillingPostalCode/>
  <BillingState/>
  <InvoiceLineItem selectQuery="SELECT * FROM `PurchaseOrderLineItems` WHERE `PONumber` = ${PONumber}">
    <Id key="true"/>
    <PONumber/>
    <ItemAmount/>
    <ItemName/>
    <ItemQuantity/>
  </InvoiceLineItem>
</PurchaseOrders>

The ‘${PONumber}’ syntax in the child table’s query indicates that the value is referencing a column (the PONumber column) from the parent table.

If multiple records are pulled from the ‘PurchaseOrderLineItems’ table, the InvoiceLineItem element (and all of its children) will appear multiple times in the output XML.

Input/Output Mappings

Some database operations require input and produce output. Input/Output Mappings provide a way to set an XML schema for both input files and output files for the same database operation.

Creating an Input/Output Mapping

Input/Output Mappings are created using the same approach as Input Mappings and Output Mappings. Each mapping targets a specific Stored Procedure, and the Mapping Editor wizard lets you view the expected parameters for the Stored Procedure.

Input Parameters

The input parameters for a Stored Procedure are defined by the database system, so no Mapping Editor configuration is required to select the relevant input parameters. The parameters advertised by the system are displayed in the Mapping Editor, and these cannot be de-selected or otherwise configured.

Input/Output Mappings as XML

Once an Input/Output Mapping has been created, an XML template is generated to match the required input parameters, and an XML template is generated to handle the output from the Stored Procedure.

Input XML

The XML template for input files follows the same principles as XML templates for Input Mappings; files that match the template XML structure are interpreted as input to the database operation. See Input Mappings as XML for more details.

Output XML

Output from a Stored Procedure is not defined in advance by the database system. As a result, it might be necessary to provide a test set of input parameters so the connector can invoke the procedure and detect the structure of the output.

To provide a set of sample inputs, use the Show Sample Data button in the top right of the Mapping Editor. After you provide a test set of inputs in the resulting modal, the connector displays the XML structure of the output as it received it. Copy this XML structure or use the Save Sample Data button to use this XML structure in other areas of the application, such as an XML Map Connector template.