Accessing ODBC and JDBC data dources from Oracle Heterogeneous Services (HSODBC)

Contents

• Introduction
• Install and configure the ODBC driver
• Configure Oracle
  • Create an init file
  • Edit listener.ora
  • Edit tnsnames.ora
  • Restart the listener
• Create a database link
• Known problems
  • unixODBC on AIX
  • No rows returned when connecting Oracle HSODBC to SQL Server
  • "Invalid user and or password specified"

Introduction

This tutorial shows you how to connect Oracle on UNIX and Linux to an Easysoft ODBC driver by using Oracle's Heterogeneous Services (HSODBC). Doing this allows you to exchange data between Oracle and other databases. For example, connecting HSODBC to the Easysoft SQL Server ODBC driver enables an Oracle database on a UNIX or Linux platform to read and write data in a remote Microsoft SQL Server database.

Heterogeneous Services is an integrated component within the Oracle database server. It allows transparent SQL access from an Oracle client to a non-Oracle system as if the non-Oracle system was an Oracle database. By using the Heterogeneous Services ODBC (HSODBC) agent with Easysoft products, this functionality can be extended on non-Windows platforms to include any ODBC or JDBC compliant database.

Important HSODBC is a normally a 32-bit application even when distributed with a 64-bit version of Oracle. You need to use the 32-bit HSODBC with a 32-bit Easysoft ODBC driver. For 64-bit platforms, choose the 32-bit version of the driver even when there's a 64-bit version available. 32-bit drivers function correctly on a 64-bit platform.

To check whether you have a 32-bit or 64-bit version of HSODBC, on the Oracle machine, run:

file $ORACLE_HOME/bin/hsodbc

If the command output contains ELF-Class64 (or something similar such as ELF-64 or ELF 64-bit), you have a 64-bit HSODBC. Otherwise, you have a 32-bit version.

If $ORACLE_HOME/bin/hsodbc is not present, contact your Oracle DBA.

If you have already downloaded, installed, and licensed a 64-bit Easysoft ODBC driver, but need to use a 32-bit version, make a backup copy of /usr/local/easysoft/license/licenses and then remove the Easysoft installation directory. For example:

cp /usr/local/easysoft/license/licenses /tmp
rm -rf /usr/local/easysoft

Download and install the 32-bit version of the driver, and then copy your backup licenses file to /usr/local/easysoft/license.

Note When using HSODBC to access SQL Server you may need to add DisguiseWide=1 to your DSN as HSODBC does not seem to recognise SQL_WCHAR columns. (Refer to No rows returned when connecting Oracle HSODBC to SQL Server.)

The article describes how to:

1. Install and configure the ODBC driver on the Oracle server.
2. Configure Oracle:
   • Heterogeneous Services (initSID.ora)
   • Database listener (listener.ora)
   • Network client (tnsnames.ora)
3. Create a database link with SQL*Plus.

Install and configure the ODBC driver

All Easysoft ODBC drivers come with the unixODBC Driver Manager. It's this that Oracle uses to load the ODBC driver. Essentially, we're linking HSODBC to unixODBC and the Driver Manager is then responsible for the ODBC access. The components are:

SQL*Plus > Oracle client > HSODBC instance > unixODBC > ODBC driver > database

Before you configure Oracle, you need to download, install, license, and test your Easysoft ODBC driver. For information about how to do this, refer to the product documentation.

Configure Oracle

To use HSODBC, you need to edit three files:

• initSID.ora
• tnsnames.ora
• listener.ora

All three files reference either entries in the other files or the data source in /etc/odbc.ini. Take care when configuring these files, as an incorrect entry in any of them can lead to a failure to connect.

After you've edited the files, restart the Oracle listener.

Create an init file

On the Oracle server, log in to the Oracle account and cd into $ORACLE_HOME/hs/admin. Each HSODBC instance needs a separate initSID.ora file. We created the file inithsconnect.ora. You can name this file to identify the non-Oracle system. For example, inithsmssql.ora. The init file specifies the ODBC data source to connect to and the ODBC Driver Manager shared object path. For example:

#
# This is a sample agent init file containing the HS parameters that 
# are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = ODBC_DSN 
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so

where:

• HS_FDS_CONNECT_INFO is the data source name specifed in /etc/odbc.ini. Note that as HSODBC uses the SQLDriverConnect ODBC API, you can actually put any valid ODBC connection string here. Here are some examples:
  • mydsn The name of a data source in the odbc.ini file. HSODBC calls SQLDriverConnect with DSN=mydsn;UID=xxx;PWD=yyy; where xxx and yyy are the user name and password specified when you create the database link.
  • DSN=mydsn;UID=aaa;PWD=bbb; As above, except the user name and password in the created link is overridden with aaa and bbb.
  • DRIVER={Easysoft ODBC-SQL Server};Server=myhost\\myinstance;UID=mydomain\\myuser; PWD=mypassword;Port=1500; A DSN-less connection where all the ODBC driver attributes are specified in the connection string. This example is for the Easysoft SQL Server ODBC driver.
  • FILEDSN=/tmp/mydsn.dsn Uses the ODBC FILEDSN attribute to instruct the ODBC Driver Manager to read the details for this data source from /tmp/mydsn.dsn.
• HS_FDS_SHAREABLE_NAME The location of libodbc.so on your machine. This is the Driver Manager that HSODBC loads to access your ODBC data source.
• HS_FDS_TRACE_LEVEL The tracing level. You should generally leave this commented out (a # character at the start of the line), as tracing impacts performance. However, if you're having problems, HS_FDS_TRACE_LEVEL may be set to a number from 1 to 4 (where 4 is the most verbose) and trace output is written to the file specified with HS_FDS_TRACE_FILE_NAME.
• HS_FDS_TRACE_FILE_NAME the trace file name. This is only relevant if HS_FDS_TRACE_LEVEL is set to a number between 1 and 4. If HS_FDS_TRACE_LEVEL is set but HS_FDS_TRACE_FILE_NAME is not, the trace file location is in $ORACLE_HOME/hs/trace.

Save you init file in $ORACLE_HOME/hs/admin.

Edit listener.ora

This creates the HSODBC instance that you will reference in tnsnames.ora. It creates a SID_NAME and specifies the Oracle executable to run on connection. listener.ora is in $ORACLE_HOME/network/admin. You need to create SID_DESC within SID_LIST in the SID_LIST_LISTENER block. For example:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /space/oracle/OraHome10db)
      (PROGRAM = extproc)
    )
    (SID_DESC=
      (SID_NAME=hsconnect)
      (ORACLE_HOME=/space/oracle/OraHome10db)
      (PROGRAM=hsodbc)
      (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
             /usr/local/easysoft/lib)
    ))

where:

• SID_NAME The name of the init file without the init prefix or ora extension. For example, inithsconnect.ora becomes the SID_NAME hsconnect.
• ORACLE_HOME The Oracle home directory path.
• PROGRAM The Oracle program used by this SID (hsodbc).
• ENVS=LD_LIBRARY_PATH The environment variables that unixODBC and the ODBC driver require. For example, LD_LIBRARY_PATH. You may have to set other environment variables (colon separated).

Edit tnsnames.ora

The final Oracle file to edit is $ORACLE_HOME/network/admin/tnsnames.ora. This entry identifies the Oracle server to attach to and the SID_NAME to use. Create a new Oracle definition. For example:

hsconnectid=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
    (CONNECT_DATA= (SID=hsconnect))
    (HS=OK)
  )

where:

• hsconnectid The name given to the server, which is used when connecting with SQL*Plus. Use a name that matches your initSID.ora file. Do this to tie the entries in the three Oracle configuration files together.
• ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521) The details for your Oracle server.
• CONNECT_DATA=(SID=hsconnect) This must match the entry created as SID_NAME in listener.ora.
• HS=OK Any tnsnames.ora entry that connects to a SID that runs HSODBC must have this entry.

Restart the listener

Restart the Oracle listener so that the new HSODBC entries take effect:

cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start

Note You may not have been running the Oracle listener service previously, but you need it to use HSODBC:

When you restart the listener, you may get information messages similar to:

Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
 service...
Service "hsconnect" has 1 instance(s).
  Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this
  service...

Heterogeneous Services returns the status UNKNOWN message, as the service is not created until it's used.

You can also run lsnrctl and enter services to get a list and the status of the services.

You can use tnsping alias to check connectivity to the tnsnames.ora alias:

# tnsping hsconnectid
Used parameter files:
/space/oracle/OraHome10db/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = hermod)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
 (SERVICE_NAME = hsconnectid)))
OK (10 msec)

which should come back with a successful message (as shown above).

Once you've checked the alias, you can create a database link and attempt to connect to it.

Create a database link

In SQL*Plus, create a new database link:

CREATE PUBLIC DATABASE LINK hslink CONNECT TO "dbuser"
IDENTIFIED BY "dbpassword" using 'hsconnectid';

In this command, hslink is a database link that uses the hsconnect entry in tnsnames.ora. Note that when creating the database link, you supply the database user name and password. These are not the operating system user and passwords. If the tnsnames.ora entry is not enclosed with single quotes ('hsconnectid'), Oracle returns errors similar to:

ERROR at line 1:
ORA-02010: missing host connect string

Once the database link has been created, try passing in an SQL statement. For example:

select * from mytable@hslink;

Use this SQL syntax:

table@linkname

You can also join non-Oracle tables to those in your Oracle database. For example:

select * from table@hslink,dual

If you have any problems configuring HSODBC with an Easysoft driver, contact the Easysoft Support team ().

Known problems

unixODBC on AIX

When you configure and build unixODBC on AIX, you end up with the Driver Manager shared object inserted into libodbc.a as libodbc.so.1. For example:

$ ar -X32_64 -tv libodbc.a
rwxr-xr-x   201/1     636475 Aug 20 09:11 2004 libodbc.so.1

libtool creates the Driver Manager like this on AIX. Unfortunately, SQL*Plus is built and linked against libodbc.so. You can work around this as follows:

1. cd to the directory where unixODBC's libraries are installed. For example, /usr/local/easysoft/unixODBC/lib.
2. Extract the shared object from the archive:

   ar -X32_64 -xv libodbc.a
   x - libodbc.so.1

3. Rename libodbc.so.1 to libodbc.so.

   mv libodbc.so.1 libodbc.so

4. Point HSODBC directly at the new shared object by editing HS_FDS_SHAREABLE_NAME in your initSID.ora file:

   HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so

5. Add this line to your .profile file:

   LIBPATH=$LIBPATH:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib

6. Restart your Oracle listener. Your link should now work.

No rows returned when connecting Oracle HSODBC to SQL Server

Some versions of HSODBC don't seem to recognise SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR columns. If a table contains these column types, HSODBC errors and returns no rows. A workaround for this is to add DisguiseWide=1 to your DSN in odbc.ini.

"Invalid user and or password specified"

If you get an error similar to:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this 
message:

[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2052): ;
[unixODBC][][unixODBC][Easysoft SQI-ISAM Driver]
Invalid user and or password specified.
(SQL State: 28000; SQL Code: 702)
ORA-02063: preceding 2 lines from MYLINK

or any error indicating the database user name and password are incorrect, you probably specified them incorrectly when creating the database link. Although you can display the links in the dba_db_links table, you can't find out the user name and password.

If you set HS_FDS_TRACE_LEVEL to 4 in initSID.ora and try again, you'll get a trace output file. In the trace file, search for the SQLDriverConnect call to find out the user name and password being used. If this confirms that the database user name or password are incorrect, you need to delete your link and recreate it. Alternatively, change HS_FDS_CONNECT_INFO in initSID.ora to specify UID and PWD.

You can delete a database link using:

drop public database link MYLINK