Integrate DataDirect Cloud ODBC with Oracle Database Gateway

Oracle Database Gateway for ODBC allows Oracle customers to access and integrate non-Oracle data sources, offering flexibility in multi-database environments.

We recently hosted a tutorial rumble in hopes of creating resources to help you solve all of your major connectivity issues. This tutorial comes courtesy of Libby Krause, Technical Support Engineer, Progress.

Architecture: Oracle Database Gateway for ODBC

The Oracle Database Gateway for ODBC is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is one of the gateways available with the Oracle Database Gateway software.

To access a non-Oracle data store using the Database Gateway for ODBC, the agent works with an ODBC driver. The ODBC driver must be on the same platform as the ODBC agent. The non-Oracle data stores can reside on the same machine as the Oracle database or a different machine.

Installation Steps and Code Examples

This example shows the configuration of the Database Gateway for ODBC on a Linux system using the Progress DataDirect Cloud Driver for ODBC to connect to Salesforce.com through a DataDirect Cloud data source. The DataDirect Cloud Driver for ODBC resides on the same machine as the Oracle Gateway for ODBC and connects to a Salesforce data source configured in DataDirect Cloud.

You will use two sets of credentials, the Salesforce credentials, which are configured in the DataDirect Cloud Salesforce data source, and the DataDirect Cloud credentials, which are used by the DataDirect Cloud Driver for ODBC to connect to DataDirect Cloud where the Salesforce data source is configured.

1. Install the Database Gateway for ODBC
   
   

   Oracle Database Gateway has its own installer similar to the one used to install the Oracle server. Run the installer and choose Database Gateway for ODBC and follow the prompts. If the Gateway and Oracle server are on the same machine, make sure to configure the Gateway with a different port number than the Oracle listener is using. For instructions on how to install the Oracle Database Gateway, refer to the Oracle documentation.

   Please check your Oracle documentation for specific Oracle Gateway version and platform support.  

2. Configure Your Salesforce Data Source in DataDirect Cloud
   
   

   Register for a DataDirect Cloud evaluation account. Log in to DataDirect Cloud at the sign-in page and create a Salesforce data source as shown. You will need to provide a data source Name, your Salesforce user ID and password, and your Salesforce security token.
    
   
   On the Mapping tab of the data source, open "set map options" and set Map System Column Names = 1.
   
   

3. Install the DataDirect Cloud Driver for ODBC
    

   Download the DataDirect Cloud Driver for ODBC. To download the driver, log in to the DataDirect Cloud website and click on the download link on the left side of the page. Under the DataDirect Cloud ODBC drivers section, download the driver for the platform you need with the bit type of your application.

   Once the driver is downloaded, install it on the machine where the Oracle Gateway for ODBC is installed. Installation instructions are in the DataDirect Cloud documentation.  

4. Configure the DataDirect Cloud Driver for ODBC
   
   

   The data source is configured in the odbc.ini file located in the DataDirect Cloud Driver for ODBC installation directory.

   Note: Annotations to the following example that begin with the // symbol should not be included in the actual file.

   [SF_Cloud]

   Driver=/opt/Progress/DataDirect/Cloud_for_ODBC_20/lib/ddd2c01.so

   Description=DataDirect Cloud 2.0

   Database=Salesforce // Data source name on DataDirect Cloud

   LogonID=            // DataDirect Cloud login ID

   ClientTimeZone=

   DataSourceUser=    // Salesforce UserID - Required if the DataDirect Cloud Salesforce data source does not contain the Salesforce userid.

   DataSourcePassword= // Salesforce Password - Required if the DataDirect Cloud Salesforce data source does not contain the Salesforce password.

   # The values below may need to be set in your environment. They are shown here with the default values.

   ProxyHost=

   ProxyPort=

   ProxyUser=

   ProxyPassword=

   TransactionMode=0

   WSRetryCount=3

   WSTimeout=120

   LogonDomain=

   LoginTimeout=30

   QueryTimeout=0

   ApplicationUsingThreads=1

   ReportCodepageConversionErrors=0

5. Create the Oracle Gateway for ODBC Initialization File
   
   

   You must create and customize an initialization file for your Database Gateway for ODBC agent. Oracle supplies a sample initialization file named “initdg4odbc.ora,” which is stored in the $ORACLE_HOME/hs/admin directory. For additional details, refer to the Oracle documentation.

   To create an initialization file, copy the appropriate sample file and rename the file to init<SID>.ora. For example, if the SID that will be noted in the listener and tnsnames files is “Salesforce,” the newly created initialization file will be called “initSalesforce.ora.”

   Note: the SID name and the initialization file name are case sensitive.

   Configure the initSalesforce.ora file located in ORACLE_HOME/hs/admin.  

   initSalesforce.ora

   #

   # HS init parameters

   # HS_FDS_CONNECT_INFO is the ODBC data source name configured above

   HS_FDS_CONNECT_INFO = SF_Cloud

   HS_FDS_TRACE_LEVEL = OFF

   HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Cloud_for_ODBC_20/lib/libodbc.so

   #

   # ODBC specific environment variables

   #

   set ODBCINI=/opt/Progress/DataDirect/Cloud_for_ODBC_20/odbc.ini

   #

   # Environment variables required for the non-Oracle system

   #

   #set <envvar>=<value>

   Note: The driver manager library configured on AIX for HS_FDS_SHAREABLE_ NAME is called odbc.so.

6. Add the Following Entries to the Oracle Tnsnames.ora and Listener.ora Files
   
   

   If you are using a Gateway for ODBC installation that is separate from the Oracle database installation, the configuration will be done in the Gateway for ODBC listener.ora. If you are using the Gateway for ODBC that is installed with the Oracle database software, the configuration will be done in the Oracle database directory listener.ora.

   Note: The SID_NAME “Salesforce” below has to match the SID in the initialization file name configured above.  Unixhost below is the host where the Gateway for ODBC is installed.

   LISTENER.ORA

   listener =

   (description_list =

   (description =

   (address_list =

   (address = (protocol=tcp)(host=unixhost)(port = 1521))

   )

   )

   sid_list_listener=

   (sid_list=

         (SID_DESC=

         (SID_NAME=Salesforce)

         (ORACLE_HOME=/db/oracle/ora11db/11.2)

         (PROGRAM=dg4odbc)

    (ENV=LD_LIBRARY_PATH=/opt/Progress/DataDirect/Cloud_for_ODBC_20/
   lib:/db/oracle/ora11db/11.2/lib)

       )

   )

   Note: Shared library path Is LIBPATH on AIX, and SHLIB_PATH on HP-UX PA RISC.

   TNSNAMES.ORA

   SF_alias =

     (DESCRIPTION=

        (ADDRESS=(PROTOCOL=tcp)(HOST=unixhost)(PORT=1521))

        (CONNECT_DATA=(SID=Salesforce))

        (HS=OK)

     )

   Note: This configuration is done in the Oracle database tnsnames.ora file. The SID_NAME “Salesforce” below has to match the SID in the initialization file name configured above. 

7. Stop and Start the Oracle Net Listener for the Gateway
   

   $ lsnrctl stop

   $ lsnrctl start

8. Run “Lsnrctl Services” to Verify that You Now Have a Service Handler for the Salesforce SID
   

   LSNRCTL> status

   Services Summary...

   Service "Salesforce" has 1 instance(s).

     Instance "Salesforce", status UNKNOWN, has 1 handler(s) for this service...

   The command completed successfully

9. Create a Database Link to Access Salesforce
   

   Be sure to use the appropriate quotes as shown in the following example:

   SQL> create database link sf

     2  connect to "clouduser" identified by "cloudpassword"

     3  using 'SF_alias';

   Note: clouduser and cloudpassword are the DataDirect Cloud login and password.

10. To test, Run a Simple Query
    
    

    SQL> select count (*) from account@sf;

      COUNT(*)

    ----------

11. Common Errors and Solutions—Oracle Heterogeneous Services and Oracle Database Gateway for ODBC

Get Started Today

We value you and want you to achieve your data connectivity goals. If you want to give the DataDirect Cloud ODBC driver a try, we offer a 15-day free trial so you can experience it for yourself. Get started with high-performance connectivity and flexibility in multi-database environments today!

Try DataDirect Cloud ODBC.