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 CV11\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 |
Collect! Field Code |
ODBC Data Type |
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 CV11\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 CV11\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
CV11\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 CV11\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.
See Also
- ODBC Topics
|
Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.
Please click this link to send us your comments: helpinfo@collect.org