ODBC table

Overview

Open Database Connectivity (ODBC) is a standard API for accessing SQL databases through a vendor-supplied driver. The ODBC Table destination writes to any database that has an ODBC driver, which makes it the destination to use for databases without a dedicated Cinchy destination: Db2 for i (AS/400), Informix, Teradata, SQLite, Microsoft Access, and others.

If a dedicated destination exists for your database (SQL Server, Oracle, Db2 for LUW, Snowflake), use it instead.

tip

The ODBC Table destination supports batch and real-time syncs, in both Full File and Delta mode.

Prerequisites

Install the ODBC driver where Connections runs

Cinchy does not ship ODBC drivers. The driver for your database has to be installed on every host that runs a Connections service:

• the Connections Worker, which executes the sync, and
• the Connections WebApi, which serves Test Connection and the column metadata used by the Connections UI.

On IIS, install the 64-bit driver from your database vendor on the Windows hosts running those services. The Windows ODBC Data Source Administrator (64-bit) lists the installed drivers under the Drivers tab, and the name shown there is the value to use for Driver= in the connection string.

On Kubernetes, the Connections images do not include unixODBC or any driver. Extend the Worker and WebApi images with a layer that installs unixodbc and the driver package, and registers the driver in /etc/odbcinst.ini. The name in square brackets in that file is the value to use for Driver=.

The IBM i Access ODBC Driver for Db2 for i is distributed by IBM as part of IBM i Access Client Solutions and is available for Windows and Linux.

Prepare the target table

The destination writes into an existing table. It does not create or alter tables.

• Every mapped target column must exist in the table.
• For a Full File sync whose Dropped Record Behaviour is Delete or Expire, the table needs an identity column, or a single column that is guaranteed unique and populated automatically for every new record, to use as the ID Column.
• For Expire, the expiration column named in the Sync Actions must accept a timestamp.

Naming the table and columns

• Column names must match the database catalog exactly, including case. On Db2 and Oracle, columns created without quotes are stored in upper case, so map to EMPNO, not empno. On Postgres they are stored in lower case unless the table was created with quoted names. Load Metadata in the Column Mappings section returns the exact names. Reserved words and names containing spaces are supported.
• Enter the table name as your database resolves an unquoted name, schema-qualified if needed, for example MYLIB.EMPLOYEES on Db2 for i or dbo.Employees on SQL Server. A table that can only be reached with a quoted, mixed-case name cannot be used.
• MySQL needs the ANSI_QUOTES SQL mode enabled, in the connection string or the server configuration.

Destination tab

The following table outlines the mandatory and optional parameters you will find on the Destination tab.

Destination details

The following parameters will help to define your data sync destination and how it functions.

Parameter	Description	Example
Destination	Mandatory. Select your destination from the drop-down menu.	ODBC Table
Connection String	Mandatory. The ODBC connection string. Either name an installed driver with Driver=, or reference a data source configured on the host with DSN=. The Connections UI will automatically encrypt this value for you. See Connection string examples.	Driver={IBM i Access ODBC Driver};System=as400.example.com;Uid=CINCHY;Pwd=secret;
Table	Mandatory. The name of the table to sync into, schema-qualified if your database needs it. Entered as an unquoted name.	MYLIB.EMPLOYEES
ID Column	Required for Delete and Expire. The name of the identity column in the target table, or a single column that is guaranteed unique and automatically populated for every new record. It must match the catalog exactly, including case.	EMPNO
ID Data Type	Required when ID Column is set. The data type of the ID Column: Text, Number, Date or Bool.	Number
Test Connection	Opens a connection with the connection string. If configured correctly, a "Connection Successful" pop-up will appear. If configured incorrectly, a "Connection Failed" pop-up will appear along with a link to the applicable error logs. A failure to load the driver itself, for example Can't open lib or Data source name not found, means the driver is not installed or not registered on the host serving the request.

Column Mapping

The Column Mapping section is where you define which source columns you want to sync to which destination columns. You can repeat the values for multiple columns.

Target column names are case-sensitive and must match the database catalog exactly. Use the Load Metadata button in this section to fill them from the table's catalog.

Parameter	Description	Example
Source Column	Mandatory. The name of your column as it appears in the source.	Name
Target Column	Mandatory. The name of your column as it appears in the destination table's catalog.	FULL_NAME

Filter

You have the option to add a destination filter to your data sync, written in the SQL dialect of the target database. Please review the documentation here for more information on destination filters.

Connection string examples

Enter the unencrypted string in the Connections UI; it is encrypted when the configuration is saved.

Database	Connection string
Db2 for i (AS/400)	Driver={IBM i Access ODBC Driver};System=as400.example.com;Uid=CINCHY;Pwd=secret;
Any database, through a DSN configured on the host	DSN=Payroll;Uid=cinchy;Pwd=secret;
SQL Server	Driver={ODBC Driver 18 for SQL Server};Server=sql.example.com,1433;Database=Payroll;Uid=cinchy;Pwd=secret;Encrypt=yes;
PostgreSQL	Driver={PostgreSQL Unicode};Server=pg.example.com;Port=5432;Database=payroll;Uid=cinchy;Pwd=secret;BoolsAsChar=0;

The Driver= value must be the driver's registered name on the host, exactly as it appears in the ODBC Data Source Administrator (Windows) or odbcinst.ini (Linux). Driver-specific options such as BoolsAsChar are documented by each driver vendor.

Considerations

• Type conversion is done by the driver. If a driver rejects a value for the target column type, the affected records are reported in the sync's error logs. Converting the value in a calculated column on the source is the usual remedy.
• Boolean columns on PostgreSQL. The PostgreSQL ODBC driver reports boolean columns as single-character text unless the connection string includes BoolsAsChar=0. Without it, Load Metadata shows the column as Text.
• High-volume real-time loads into a database that has a dedicated destination should use that destination.

Configuration XML

The Connections UI produces this configuration for you. If you maintain your configurations as XML, the destination element is:

<OdbcTableTarget
    connectionString="@ConnectionString"
    table="MYLIB.EMPLOYEES"
    idColumn="EMPNO"
    idDataType="Number">
  <ColumnMappings>
    <ColumnMapping sourceColumn="Employee Id" targetColumn="EMPNO" />
    <ColumnMapping sourceColumn="Full Name" targetColumn="FULL_NAME" />
    <ColumnMapping sourceColumn="Hired On" targetColumn="HIRE_DATE" />
  </ColumnMappings>
</OdbcTableTarget>

reconcileData="false" on the element selects a Delta sync; the default is Full File.

Next steps

• Define your Sync Actions.
• Add in your Post Sync Scripts, if required.
• Define your Permissions.
• If you are running a real-time sync, set up your Listener Config and enable it to begin your sync.
• If you are running a batch sync, click Jobs > Start a Job to begin your sync.