API Developers Guide

User Manuals > API Developers Guide

Introduction

CommitCRM provides various tools in order to read/add/update information in the CommitCRM database. These tools include: Programming API for retrieving and updating data, API by Email, and an option of using an ODBC Link for read-only purposes.

The Commit API provides developers with a set of APIs which allow them to perform database transactions in order to read, add and update records in the CommitCRM database. The API can be used as a Programming API by embedding code which uses the API functions, or as an API by Email (available only for adding or updating data) which allows you to send XML formatted emails which will be processed and executed.

• Programming API
  The Commit API provides developers with a set of API functions which can be used in VB, C++, Delphi and any other programming language that lets you use a standard Windows dll file. The Programming API includes two sets of functions: Data-Retrieval API and Data-Update API.

• API by Email
  The API by Email allows you to send XML formatted emails which contain database transactions. Commit Email Connector then pulls the incoming XML emails from your POP3 mail server, processes the email and performs the transactions in the XML transaction. XML formatted email gives you access to the Data-Update API, which allows you to perform many actions such as linking to external systems, receiving forms submitted from your web site, and more.

• ODBC Link
  In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). CommitCRM uses an open database structure and allows other applications to access the database using ODBC (Open Data Base Connectivity) for read-only purposes.
  
  You can use ODBC Link in order to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application).

Glossary

• Commit API (Application Programming Interface) - CommitCRM provides an API which allows you to add and update records in the CommitCRM database. The API includes a list of possible actions and parameters.

• ODBC Link (Open Data Base Connectivity) - ODBC is a function library which provides a common API for ODBC compliant databases. CommitCRM provides an open database and allows other applications to access the database using ODBC for read-only purposes.

• Record - Each entity in the CommitCRM database (such as Ticket, Account, Asset, Item, Appointment, Task, etc.) is considered a "record".

• Database fields - Each record contains a list of fields, each of which holds the record's data.

• Transaction – All additions or updates to record in the database are done by using a transaction. Each transaction includes the operation you wish to perform, the data type you wish to add/update and the additional parameters (database fields and their values).

• Transaction Parameters - Each transaction contains the parameters for performing the transaction: record type, list of database fields and a list of the values to be updated in these fields.

• External Application - This refers to the external program which integrates with CommitCRM in order to update the database. This name should be passed when performing updates to the database, and it will be saved in the CommitCRM record to indicate who performed the changes.

• Record ID (REC ID) - Each record in CommitCRM has a unique Record ID which is created when adding the record to the database. Every ID is 20 characters and should be transferred with the API transaction when you need to update an existing record.

Using Commit API

Commit API allows you to add and update records in the CommitCRM database, such as Accounts, Tickets, Assets, Items, Appointments, Tasks and more.

The updates are performed using transactions, where each transaction includes the operation you wish to perform, the data type you wish to add/update, and any additional parameters (i.e. record type, database fields and their values).

Every record added to the system is assigned a unique Record ID (REC ID) which is the database identifier for this record.

The Commit API allows you to perform the following operations.

• Add new records - if no Record ID is passed in the transaction, the API assumes that this is a new record, adds it to the system and creates a new Record ID for it.

• Update existing records - if the record ID is passed in the transaction, and the record exists, the system will update the record with the information in the transaction.

In both cases, the system will try to set default values for fields which do not exist in the incoming transaction. If it fails, the action will fail with an error.

Note that in order to read data from the CommitCRM database, you should use the ODBC Link, which provides a read-only link for the CommitCRM database. See more details in Retrieving data using ODBC.

Workflow

Following is a sample work-flow, which demonstrates the process of adding a new Account, and then adding a new Ticket to this Account, using the Commit API.

Note that the API can return two parameters to the External Application:

1. The Commit REC ID - when adding new records to the database, this will contains the new record ID.
2. The ExternalTransactionID (relevant for XML transaction only) - In case the external application passes a parameter to be returned with the response, the ExternalTransactionID will be returned as-is to in the response.

The External Application should keep the REC ID which is returned from the API response. This allows you to later update the existing records with new data or add records related to it (such as opening a Ticket under the Account).

Updating existing records

In order to update an already existing record in the database (e.g. add a Ticket to an Account, update an Account's phone number, add a new Charge to an existing Ticket, etc.), you should pass the record's unique identifier to the API so it will update the existing record rather than create a new Account. The record's unique identifier is referred to as the Record ID (REC ID).

The Record ID can be obtained in the following ways:

1. API Response (as in the flow above) - You can extract the newly created Record ID from the Email Response when adding new records with the API. This requires the developer to process the API responses, and extract the returning REC ID for each newly added record.
2. ODBC Link - Use the ODBC Link to find relevant records (Accounts, Tickets, etc.), and read all information from the database, including the unique Record ID.
3. Manually - from the Account's Notes tab, at the bottom, right-click the REC ID field and copy it.

Once you have obtained the Record ID you can pass the Record ID as a parameter for the API and update this record.

Programming API

The Programming API allows you access the CommitCRM database and perform transactions from within a program (using various programming language). This means you can embed code which accesses the CommitCRM database from within your own application, and add your own functionality which updates the database.

The programming API executes database transactions. Each transaction uses API methods provides by the API dll file which is located in the CommitCRM installation. Both the Programming API and the API by Email use the same dll for performing the transactions.

Note that the API provides two separate dll programs, one for adding/updating data from the database, and one for retrieving data from the database. Retrieving data can be performed either by the API (recommended) or using ODBC . You can read more about it in the Data Retrieval section.

For the full list of API functions go to API Functions.
For samples go to API Code Samples.

API by Email (XML transaction)

The API by Email operates under the Commit Email Connector module, which automatically processes incoming emails arriving at a defined public email address. The Email may contain XML formatted messages which contain API transactions, and provides a full data update API to CommitCRM.

The Email Connector pulls incoming emails from your POP3 mail server, and processes the email. When an email containing XML formatted message is detected, the system analyzes the XML content of the email and performs the transactions written in the XML. You may set the Email Connector to send automatic replies in response to XML formatted email.

The API by Email supported only the Data Update API (Data Retrieval API is not supported via emails). The API by Email executes database the transactions using the functions provided by the API dll file which is located in the CommitCRM installation. Both the Programming API and the API by Email use the same dll for performing the transactions.

Note that the API by Mail supports only the Data update API functions, allowing you to add and update data. In order to retrieve data, you should use the Data Retrieval Programming API (recommended) or use ODBC, both methods do not support API by Email. You can read more about it in the Data Retrieval section.

• Learn more about using API by Email.
• Learn more about building the XML transactions for the API by Email in XML samples.

Data Retrieval

CommitCRM uses an open database and the CommitCRM API provides the ability to retrieve data using standard API functions which let you define queries and retrieve data.

In addition to the Data Retrieval API, there is an option of using ODBC to read data directly from the database. The ODBC link should not be used from programs you develop (you should use the data retrieval API for this purpose). Use the ODBC link only when you need to access the database from an external program, such as external report application (i.e. Crystal reports, or any other application).

• Learn more about Data Retrieval API.
• Learn more about Data Retrieval using ODBC Link.

Data Retrieval API

The Data Retrieval API provides API functions which help you execute database queries and receive the results. Each function uses API functions provided by the Retrieval API dll file which is located in the CommitCRM installation. The Data Retrieval API is available only via the Programming API; no API by Email is available for data retrieval.

The Data Retrieval API provides the following abilities:

• Execute a database query and receive a result records list.
• Read each record with its fields.
• Find out field properties.

See the Data Retrieval API Reference Manual for detailed usage description.

ODBC Data Retrieval

CommitCRM uses an open database and allows other applications to access it using ODBC (Open Data Base Connectivity) for read-only purposes. While the API provides the means for adding and updating information in the database, it is preferred to use ODBC to read information from the database.

You can use ODBC Link to read data from the database and integrate it with an external system (i.e. Crystal reports, or any other application). You may also use the ODBC Link in order to read the Database Record ID and update existing records (see Updating existing records).

Activation Notes:

• The ODBC drivers are well tested and work well, however, please note that the Commit Support team doesn't provide "pure ODBC" support.

• NEVER use ODBC to modify or delete data; use it for READ purposes only. Modifying data should be done using the Commit API.

• The ODBC Link can be used with your installed version of CommitCRM, and only requires installing an external driver. Before working with the API we recommend that you backup the database.

Below are the database table names and their meaning:

CommitCRM Record	Database Table Name
Accounts	Cards
Tickets	Tickets
Charges	Slips
Assets	Assets
Appointments	Events
Tasks	Events
History Notes	NoteBook
Opportunities	Opps
Documents	Docs
Knowledge Base	KBArticles

For the database fields list, please refer to API Reference Manual section. You can find detailed instructions for using the ODBC Link in the Installing ODBC Driver section.

Installing ODBC Driver

Product Overview
The Advantage ODBC Driver is an ODBC version 3 driver based on the Advantage Client Engine that provides SQL access to the Advantage Database Server. The driver provides full support for the "Minimum" ODBC SQL grammar specification, as well as many functions included in the "Core" and "Extended" grammar specifications. When used with the Advantage Database Server, ODBC users can have the application stability, performance and reduced network traffic benefits of client/server architecture.

Installation on Windows
Like other ODBC drivers, the Advantage ODBC Driver is installed and managed using the ODBC Administrator Utility. This utility works with the ODBC Driver Manager to configure ODBC data sources. At runtime, the ODBC Driver Manager works with available drivers and their configured data sources. If you have previously installed ODBC drivers, the ODBC Administrator may already be installed on your workstation. The icon for the Administrator is usually found in the Control Panel.

To install the Advantage ODBC Driver:

1. Download the ODBC driver version that corresponds with your Advantage SQL Database Server version from the following links: (The Advantage Version number can be found in the Advantage Configuration Utility located on the CommitCRM Server)
   ODBC Driver Version 9.0
   ODBC Driver Version 8.1 (**Also for systems that do not use the Commit SQL Database)
   
2. Run the Setup program.
3. Proceed through the setup windows to complete installation.

**DISCLAIMER: Using the ODBC connector without the Commit SQL Database is not recommended, since the chances for Database Corruption would be elevated.

Data Source Setup for Windows
Once the Advantage ODBC Driver is installed, a data source needs to be configured to use the Advantage ODBC Driver. The data source is an entry in the Windows Registry. When a data source is defined for the Advantage Driver, all information specific to the Advantage Driver and database files is stored under the Data Source entry in the Windows 95/98/ME/ NT/2000/2003/XP Registry.
The database files and indexes must be stored on your file server, and the Advantage Database Server must be loaded in order to access the files.

The Data Source settings may be modified at any time. Using the ODBC Administrator, you may modify the Data Source and Option settings. For specific information about the screen fields see the Data Source Setup Screen.

To Setup the Data Source:

1. From the ODBC Administrator, click Add.
2. Highlight the Advantage SQL ODBC line, and click OK.
3. Type a unique data source name. For example, type AdvData. This name is used by applications to reference the data source.
4. Specify the database or data dictionary path. Type a valid path name to a mapped drive on the server where the Advantage Database Server is installed or the path and file name of your Advantage Data Dictionary. Click Browse to select a database path or check the Data Dictionary check box and then browse to select a data dictionary file. Note Multiple Advantage data sources may need to be defined for your environment. If different settings are needed for ODBC connections in one application, separate data sources may be required.
5. Review the options and change them to your desired setup.
6. Once the Options are reviewed and/or altered, click OK to exit and save the settings. The new data source is displayed.
7. Click Close to exit the ODBC Administrator.

ODBC Data Source Entries for Windows
In Microsoft Windows, the entries are registry settings found on HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ (your unique data source name) or HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI (your unique data source name). Unless specified otherwise, all registry entries can be setup by the Advantage ODBC Driver setup utility. See Data Source Setup for Windows and Data Source Setup Screen for more information. ODBC Data Source Keys.

ODBC Data Source Keys
The following ODBC registry is required for the driver to load:

Registry Entry	Comments
DataDirectory=data path	'data path' should be a valid path name for files locations (e.g., x:\data). This path is used to automatically select all tables in the specified directory.
DefaultType=Advantage	Sets a certain type of database files to use Advantageproprietary ADT/ADI/ADM files.
ServerTypes=3	Allows the Driver to use the remote or local server

The following ODBC registry keys are optional:

Registry Entry	Comments
AdvantageLocking=ON \ OFF	The default is ON to use the Advantage proprietary locking.
CharSet=OEM \ ANSI	The default character collation setting is ANSI. If OEM is specified, Language must be indicated as well.
Language=USA	Used if CharSet=OEM.
Description=String	This is provided for easier administration.
Locking=RECORD \ FILE	Indicates whether updates lock the entire file or the individual records that are updated. The default is RECORD.
MaxTableCloseCache=n	N is the number of tables to hold in cache when cursors are opened and closed. The default is 25.
MemoBlockSize=n	N is the size of the Advantage memo blocks in tables that are created by the ODBC driver. The default value is 8 for Advantage proprietary table (ADT/ADM).
Rows=TRUE \ FALSE	Select whether deleted rows are displayed. If True, deleted rows are displayed. The default is False.
TrimTrailingSpaces=TRUE \ FALSE	The default is False. If True is specified, trailing spaces in character fields will be removed prior to returning the values to the application.

Troubleshooting ODBC in 64 Bit Operating Systems

The Commit ODBC drivers are mainly distributed for 32-bit operating systems; however, the same drivers are supported on the 64-bit operating systems (I.e. Windows 7 x64), and can be used to retrieve data from the CommitCRM database.

Configuring Data Source in 64-Bit Systems

When installing the ODBC drivers on 64-bit operating systems, the ODBC drivers may not be displayed in the default ODBC manager in windows control panel. In order to view the 32-bit ODBC manager in a 64-bit operating system, you’ll need to browse to %windir%\SysWOW64\odbcad32.exe and run the 32 bit ODBC Administrator manually. Once the data source has been configured there, you should be able to select the data source in your 3rd party ODBC programs.

Samples

For basic samples that provide an easy starting point see API Code Samples.

API Reference Manual

Find a detailed listing of the database fields in the API Reference Manual.

See Also

• API Code Samples
• API Reference Manual
• Commit Email Connector
• API by Email and MSP Integration
• MSP Integration Setup
• Commit SQL Database (CSD)
• Commit SQL Database (CSD) Installation Guide