Connecting with an ODBC
  • 03 Dec 2021
  • 3 Minutes to read
  • Dark
    Light

Connecting with an ODBC

  • Dark
    Light

Overview 

To further the integration abilities with External Databases, Decisions provides the ability to connect to ODBC Databases. ODBC's (Open Database Connectivity) are APIs that allow an application to utilize information from different Database Management Systems

Designers integrate with any Database supporting ODBC not already built into Decisions through the use of the ODBC Metadata Flow. Designers may specify the unique characteristics of the Database's type to connect and collect the Database's Metadata for creating Tables, Reports, etc. within Decisions. Once successfully connected, the new Database automatically appears as an option in future Decisions Integration wizards on the device. 

Once a Database is introduced, components to Query, Insert, or Update data to or from the Database within the Flow Designer can then be used similar to other methods of integration.

 The following document details how to establish a connection with an ODBC via Decisions integration.


Example

The following example demonstrates how to Integrate with a Sybase ODBC Database. To do so: 

  1. From the Decisions Studio, navigate to System > Integrations > Database
  2. From the Global Action Bar, click the CREATE CONNECTION button. 
  3. From the Database Integration window, provide a Connection Name. Then, select ODBC from the Database Type dropdown. 
    Additional Information on Connection Name
    Note that that the Connection Name cannot contain any spaces.


  4. Provide a valid connection string following the: Driver={ODBC Driver's Name Here};server=nameOfTheServer;port=portNumber;db=databaseName;uid=login;pwd=password;database=databaseName;schema=schemaName; format. Then click PICK OR CREATE ODBC DB TYPE. 


  5. PICK the Flow that corresponds with the Database (in this case Sybase ASE), and click TEST to test the connection. Then, click OK to save the Integration and exit. 



Pre-built ODBC Metadata Flows

Decisions installations come with pre-built OBDC Metadata Flows for the following Databases:

  • Access
  • Netezza IBM
  • Redshift AWS
  • SAP SQL Anywhere
  • Sybase ASE



Note on Building a Custom ODBC Metadata Flow

Each Database and its ODBC Metadata Flow is Inherently Unique
When constructing a custom ODBC Metadata Flow, it is important to keep in mind that not all Databases require nor provide the same information to connect; some Databases may only use a certain combination of Procedures, Tables, or Views Data Types.

Unused Data Types should be set to the Ignore mapping type to proceed with the connection.

The example zip folder provided demonstrates an ODBC Metadata Flow integration to Amazon Athena which also features an unused Data Type: Procedures. Please download the file and import it to your Decisions instance for reference of where the Ignore mapping type occurs.


Custom ODBC Metadata Flows are a flexible solution for connecting to a Database that does not have built-in ODBC support in Decisions. To accomplish this goal, ODBC Metadata Flows both contain the credentials for Database access while also collecting the necessary types of data and Metadata for use in the platform. 

Given the elasticity of ODBC Metadata Flows, each is highly unique due to the target Database for integration in addition to considering the Designer's goals.

Additional Info on ODBC Metadata Flow Configuration
The most essential steps when building a custom ODBC Metadata Flow include Create Data, Raw SQL, Add Item to List, List Mapping Step, and ForEach steps. However, additional Elements such as Rules, Truth Tables, etc may be applicable depending on the Database and/or the Designer's goals. Some built-in ODBC Metadata Flows contain these so it is recommended to view them as a reference.

An ODBC Metadata Flow starts two Create Data steps: 

  1. The first Create Data step creates lists to be populated by the Database with the DBFullTableInfo Data Type. Note that a list was not constructed for Procedures since Athena does not utilize it.
  2. The second must declare the connectionString to the Database.
  3. After these, a series of Raw SQL steps gather specific Metadata through a loop that then adds the desired data to a list. This command is specific to the language of the Database. Once complete, the next Raw SQL command loop begins.
  4. After all loops are completed, the End Step outputs the data lists for the Database's Procedures, Tables, and Views Metadata. Again, since Athena does not produce Procedures, its input mapping type is set to Ignore

After Connection

  1. Once integration with an ODBC has been properly established, users may then Integrate with its respective Database Tables or utilize its respective Flow steps in the Flow Designer by navigating to Toolbox > INTEGRATION > [NAMEOFINTEGRATION] > [TABLE]. 
Additional Information and Resources 
For more information on how to integrate with Database Tables, see Integrate with Database Table. 


Was this article helpful?