Home Services Partners Company
Integrate DataDirect Cloud ODBC with Oracle Database Gateway

Integrate DataDirect Cloud ODBC with Oracle Database Gateway

June 06, 2016 0 Comments

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.

Oracle Gateway

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.
     Configure your Salesforce data source in DataDirect Cloud

    On the Mapping tab of the data source, open "set map options" and set Map System Column Names = 1.

    Configure your Salesforce data source in DataDirect Cloud

  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

ERROR

CAUSE

ACTION

ORA-28509: unable to establish a con­nection to non-Oracle system

ORA-02063: preceding line from HS

Problem with the Oracle configuration files.

Make sure:

• HOST parameter in the tnsnames.ora file is correct

• PORT number is correct

• SID name is correct in both tnsnames.ora and listener.ora

ORA-02068: following severe error from HS

ORA-03114: not connected to ORACLE

The required syntax for the TNSNAMES. ORA file is not present.

Add (HS=OK) in the description section of the tnsnames.ora file.

Note: the listener is unable to start if an invalid PROGRAM name is provided.

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[DataDirect][ODBC lib] Data source name not found and no default driver specified

ORA-02063: preceding 2 lines from SF

Incorrect parameter settings in the HS init.ora file.

Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name located in the odbc.ini file.

Example: HS_FDS_CONNECT_INFO = Salesforce

Make sure the HS init.ora file exists in the $ORACLE_HOME/hs/admin directory and has the same name as the SID in the listener.ora.

Example: If SID=HSodbc in the listener.ora file, then the HS init.ora file would be named $ORACLE_HOME/hs/admin/initHSodbc. ora

ERROR at line 1:

ORA-28500: connection from Oracle to a non-Oracle system returned this message:

ORA-02063: preceding line from SF

Incorrect parameter settings in the HS init.ora file.

Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc. so file.

Example: HS_FDS_SHAREABLE_NAME=/opt/odbc/ lib/libodbc .so

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[DataDirect][ODBC lib] System information file not found. Please check the

ODBCINI environment variable. {IM002}

ORA-02063: preceding 2 lines from SF

The HS agent cannot find the odbc.ini file.

Set the ODBCINI variable in the HS init. ora file.

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

 

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

[DataDirect][ODBC Cloud driver][Service]Invalid data source - User Id: clouduser

Data Source: wrongds {28000,NativeErr = 222206001}

ORA-02063: preceding 2 lines from SF

The Database setting in the odbc.ini file does not have a valid DataDirect Cloud data source name.

In the odbc.ini file, set the Database option to use the data source name configured at login.datadirectcloud.com.

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

[DataDirect][ODBC Cloud driver][Service]Invalid user ID or password.

{28000,NativeErr = 222206007}ORA-02063: preceding 2 lines from SF

Invalid DataDirect Cloud User ID or password was used in the Oracle database link.

Recreate the Oracle database link using the DataDirect Cloud login and password. Note that the user name and password must be in double quotes.

Example: Create database link ODBC connect to “clouduser” identified by “cloudpassword” using ‘SF_alias.'

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[DataDirect][ODBC Cloud driver][Salesforce]A value was not specified for a required property: password {08001,NativeErr = 60}

Salesforce password is missing from the DataDirect Cloud data source configuration.

Enter the Salesforce password in the DataDirect Cloud data source password field.

SQL> select * from account@sf;

select * from account@sf

*

ERROR at line 1:

ORA-02070: database SF does not support ROWIDs in this context

The Oracle Gateway for ODBC does not support ROWID.

On the DataDirect Cloud data source mapping tab, expand "mapping options" and set "map system column names" to 0.

This will cause the Salesforce column ROWID name to be returned as ID.

Refer to the DataDirect Cloud documentation under "using data sources" for details.

 

ERROR at line 1:

ORA-00942: table or view does not exist

[DataDirect][ODBC Cloud driver][Salesforce]Table not found in statement [SELECT

* FROM "BADTABLE"] {42S02,NativeErr = -22}

ORA-02063: preceding 2 lines from SF

The table name in the query does not exist in Salesforce.

Check that the table name is correct.

 

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.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next How Hybrid Data Pipeline Transforms How Clouds Access Data
Comments
Comments are disabled in preview mode.