› KCM Database Interface Definition Guide › Configure connection types › ODBC connection

ODBC connection

With the ODBC connection, you can retrieve data from an Open Database Connectivity data source using SQL statements. The SQL statements, input parameters, and output columns are all part of the entry definition. The SQL statements and functions to use depend on the data source type and the ODBC driver.

When you run a Master Template, the Data Manager connects to an ODBC data source. Specify the data source to connect to in the connection configuration in KCM Core Administrator.

To connect to multiple data sources in a single DID, you can specify the ODBC settings per DID module. If you omit settings for a certain DID module, KCM uses the settings specified at the DID level for this DID module.

For information about ODBC connection strings, see the documentation for your ODBC driver.

Requirements

The ODBC driver must be an ODBC 2.0 driver or higher, and it must implement all core functions and the following level one functions:

  • SQLBindParameter
  • SQLDriverConnect

The KCM DID Development Kit comes with a wizard to generate an ODBC entry. This wizard requires additional level one and level two functions.

Also, consider the following limitations when using this connection type:

  • You cannot use stored functions.
  • Use the SQL SELECT and CALL statements in the data and key retrieval statements.
  • To update the database, use the UPDATE statement to create DID defined functions.

Usage

To use this connection type, follow these steps:

  1. Open the required DID document and locate a DID module that contains an ODBC connection entry.

    A DID can contain multiple ODBC connection modules. However, KCM can connect to only one data source at a given time. You can use different data sources for different DIDs and Master Templates, but not within a single DID.

  2. Set the CONNECTION attribute to ODBC.

  3. To construct the SELECT part of an SQL statement, specify a column name in the DATABASE_FIELD attribute. You can rename a database column name to a KCM Field name.

    In this example, a database column named ORDNR is renamed to a Field name Order_number. 

    Order_number NUMERICAL(8 0) DATABASE_FIELD "ORDNR"

    The following data types are supported for Fields in the ODBC connection: C_CHAR, TEXT, NUMERICAL, and DOUBLEW_CHA.