How To Use ODBC Sync

Collect!'s ODBC Sync extension module empowers you to interface with any third party ODBC compliant application. Commit on-the-spot Updates, Inserts and Deletes to your other application from within Collect!. All the functions of Collect!'s contact plan management system may be used to trigger events. You can synchronize with one or several third party applications. Collect!'s ODBC Sync requires Open Database Connectivity capability with Version 3.x ODBC drivers.

Some examples of using this feature are updates to a predictive dialer campaign, inserts and deletes from an SQL database table, updates to your ODBC compliant accounting system software.

Requirements

ODBC Sync can interface with any third party application that supports Open Database Connectivity with Version 3.x compliant ODBC drivers.

*** ODBC Sync is a licensed add-on component. ***

ODBC Sync Setup

To use ODBC Sync, you must be familiar with creating and configuring a data source for your application's ODBC driver. Collect! relies on your successful installation of the third party's ODBC driver by your technician. This is out of our realm.

Once you have successfully created your Data Source for your third party application, you can proceed to configure Collect! to talk to this database. This process involves configuring two files, a CSV and an INI.

Odbcsync CSV File

The CSV file coordinated with the ODBC Sync feature must map each Collect! field to the corresponding field in your third party database table. The first field must be an identifier that is unique. In Collect!, this is typically the Debtor File Number or the Debtor Acct Number. This field must be mapped to the unique identifier or primary key in your third party application table.

The CSV file has three columns:

• Third party application field name
• Collect! field code
• Third party application field data type

A sample odbcsync.csv is included in the Demo installation. You can find it in the Collect\bin\odbcsync folder.

View sample ODBC CSV file

Please note that this file must have column headers.

You must have three fields for each line:

ODBC Field Name

To specify a field from your third party application, use the exact name assigned to the field in that application. This column is CASE SENSITIVE so please be exact!

Collect! Field Code

To specify a field from Collect!, use the printable information code for the field.

If needed, while setting your configurations, you can make modifications to the CSV file without having to restart Collect!. ODBC Sync will evaluate the modified time of the odbcsync.csv whenever you execute an ODBC event in Collect!. It is not cached, so it is always current.

ODBC Data Type

The field data type must be the data type of each field in your third party application. Currently, the only data types supported are CHAR, TYPE_DATE, DECIMAL, and NUMERIC.

These are the ODBC standard names for these data types. Collect! uses them to correctly format the SQL queries sent via ODBC Sync to your third party application. If your application uses different names, you must still refer to the field in the CSV using the ODBC standard names. For instance, a STRING field in your application should be mapped to CHAR in the data type column of your ODBC CSV file.

Odbcsync Ini File

The INI file coordinated with the ODBC Sync feature contains configuration settings for your ODBC Data Source. The ID and Password for signing into the third party application are stored here. Also, the name of the database table to be updated is stored in this file.

A sample odbcsync.ini is included in the Demo installation. You can find it in the Collect\bin\odbcsync folder.

View sample ODBC INI file

Please note that this file must have exact information taken from your third party application ODBC driver installation.

If you have an invalid table name in your INI file and you run a contact plan, the contact plan will fail, generating the default contact plan error message for failure.

Odbcsync Folder

The CSV and INI files for ODBC Sync go together. They must have the same name. In our samples, we used "odbcsync" but you can use a different name, as long as both files have the same file name with CSV and INI extensions. The files must reside in the Collect\bin\odbcsync folder. You cannot change the name of this odbcsync folder.

You can have one or several "pairs" of odbcsync files depending on the number of third party applications you will be writing to through ODBC Sync. Each pair of files must reference the correct data source and the correct data source table as per the installation your technician performed for your ODBC drivers. Each pair of files must reside in the Collect\bin\odbcsync folder.

ODBC Sync Contact Management

Three events may be triggered through ODBC Sync. These are: Updates, Inserts and Deletes. Each of these event types is triggered through Collect!'s native contact management system. A contact type has been created especially for the ODBC Sync feature. It is the ODBC contact. Every aspect of the contact plan management system may be utilized to manage accounts for ODBC Sync.

The Update, Insert and Delete events would be triggered from running one or several contact plans. You can set up the events using any Schedule If criteria you need, just as you would for any other contact plans. The name of the CSV/INI configuration file "pair" is entered in the ODBC contact's Attachment field with NO file extension.

ODBC Event Type with ODBC Attachment

You can trigger events in a different third party application or table by entering a different name for a different pair of configuration files.

You can create schedule ODBC events through a contact plan, using the normal steps for scheduling contacts and running contact plans.

Each ODBC event must be scheduled to execute "immediately."

Schedule Immediately

As soon as the plan is run, the Update, Delete or Insert event occurs on-the-spot. You can see your changes in your third party application immediately.

Executing an ODBC event using a contact plan enables you to engage the Schedule If conditioning functionality.

Set Plans To Run On Changes

You can trigger ODBC events when the Operator or Phones are changed on an account. Select the contact plans you want to run when the Operator or Phones change. These options are available in the Screen and Messages form, accessed through System -> Preferences -> Options, Sounds and Colors.

Run Plans on Changes

Updating Via ODBC Sync

When you schedule an ODBC event in a contact plan and you set it to occur immediately, Collect! submits the change on-the-spot to your third party application.

Updates and Deletes must be able to find the exact record in your third party database table that matches the account in Collect!. To do this, ODBC Sync keys off the information in the first row of data in the CSV config table, much the same as a primary key. So this must be the unique identifier for the record. In Collect!, this is typically the Debtor File Number or the Debtor Acct Number. You must be careful to configure this correctly when creating your odbcsync CSV.

Troubleshooting ODBC Sync

Several tools have been included for monitoring and troubleshooting ODBC Sync operations.

When ODBC Sync fails to commit a change when a plan is executed, Collect! displays the same error message it uses whenever a contact plan fails to run.

ODBC Contact Plan Did Not Execute

To view details, you can examine the Application Log for entries referring to your ODBC Sync calls. You can also switch ON a debugging utility in Screen and Messages to log more in-depth details.

ODBC Debug Logging Enabled

When this switch is ON, Collect! will generate an odbcsync.log in your Collect\bin folder. This log gives very specific information regarding Collect!'s attempts to connect to the data source you specified and to commit the changes you requested. It also includes the exact SQL command with details for each account.

This file will grow very large very fast! So it is recommended that you only enable the switch for short periods while debugging a particular issue or when requested to do so by Collect! Member Services.

Summary

Collect!'s ODBC Sync extension module expands the functionality of Collect! to write to any third party application with Open Database Connectivity and Version 3.x compliant ODBC drivers. You can commit on-the-spot Updates, Inserts and Deletes to your other application from within Collect!. All the functions of Collect!'s contact plan management system may be used to trigger events. You can synchronize with one or several third party applications.

ODBC Sync can interface with any third party application that supports Open Database Connectivity with Version 3.x compliant ODBC drivers.