ODBC – Open Database Connectivity and Mapping

ODBC mapping allows more flexibility in mapping because it allows update, delete and selection of information – not just adding to a file or table. This approach can also get closer to event-driven EDI than traditional flat files that are processed in batches and can be several more steps removed from the actual application tables. ODBC mapping’s primary drawback is that it takes more time to map (but less time to process) depending on the mapping tools being used and the stability of the network it is being run on.

Set Up for Mapping View

In order to use ODBC maps both the mapping client and the server must have drivers for the appropriate database and connectivity to that database. You will also need to set up the source (which can be database tables, text files or many other types of data) from the control panel ODBC or Data Sources (or administrative tools, depending on the operating system). In addition, you will need to know at least basic querying principles to do inbound mapping and more advanced querying to do outbound mapping. You should have a strong understanding of primary keys, index fields, null fields vs. space-filled fields, creating tables, etc.

A good relationship with the database administrator is also required as you gain access to the database and an understanding of how the application tables are designed and used. Once the connection to the data source is established, the development tool will be able to read the tables to identify the definition of the fields within each record or table to be used for mapping purposes.

Successes and Concerns

ODBC mapping can have some inherent problems, depending on the mapper chosen and how closely the tool ties the table name and owner to the database. Before the implementation of an ODBC map you will want to examine your systems architecture and determine testing and change management policies and procedures. While it may be very appealing to you to harness the prospect of real-time transactions, it may not be as appealing when you find out the mapper you have chosen cannot point to a new instance of the database and all development work will need to be recreated in production.

Also with server, even client workstations that are not doing mapping will need the correct drivers and ODBC sources installed. Although Process Control runs on the server, interactive processes run on the client and will need the same connectivity. You must exercise all due caution in understanding what to update in existing records, what tables you can add records to, etc.

ODBC mapping takes somewhat longer than flat file mapping for several reasons.

  1. Some trial and error on inbound mapping is required to identify all required fields and whether or not null values are allowed.
  2. A utility to view the data within the database is useful in this regard. You can use SQL, Toad (an Oracle utility) or any ODBC tool like Access or Excel for this.
  3. Outbound mapping can also require more time because the initial query design may be included in the mapping (rather than in a gateway extraction program).

DCS Consultants have several successes using ODBC mapping techniques. The biggest benefit derives from the ability to overcome deficiencies in the gateway (or the lack of a gateway). A couple of examples include querying the database to pick up information that is not included in the gateway’s extracted data and the ability to place data within a database on a different server (especially when FTP connectivity between servers may not exist). These tasks can be accomplished not only with ODBC mapping but also with user exits or extended rules using ActiveX calls, JavaScript and even OSQL batch files.

A Comparison to XML Mapping

EDI translators can write directly to tables or create an XML file. The XML file is an interface file, with the advantage of the table loading being automatic. Those with XML experience find XML integration is easier to write.

ODBC is preferred when there are many instances of a table. The loading can be automatic for all instances, which is not so for XML. An example of many instances is when a company has an ERP system with development, staging, and production versions (XML can be loaded into many instances with creation of appropriate expressions). Our DCS Grow™ development service can help you by setting up the first, comprehensive map for each Transaction Set and direction. You can then customize the map for each Trading Partner.