Connecting with an ODBC

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 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 be used similarly to other integration methods.

 The following document details how to establish a connection with an ODBC 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 > Databases.
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 drop-down. 
   Additional Information on Connection Name

   Note 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

Custom ODBC Metadata Flows is 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 contain the credentials for Database access while also collecting the necessary data types 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.

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 appropriately 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].