This discussion is archived
4 Replies Latest reply: Oct 16, 2012 8:00 AM by tx103108 RSS

ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere

tx103108 Newbie
Currently Being Moderated
OS: CentOS 5.8 64-bit
DB: Oracle XE 11gR1 64-bit
Gateway: Oracle Gateway for ODBC 64-bit
Database and gateway reside on same Linux Server.
Connecting to remote Sybase SQL Anywhere 10 server on WindowsXP.
Using SQL Anywhere 11 odbc driver and unixODBC driver manager on Linux server.
isql tool connects without any problems.
One LISTENER, service for db and gateway on same port.
===================================
Via SQL*Plus, the following error occurs....

SQL> select * from dual@dblink;
select * from dual@dblink
*
ERROR at line 1:
ORA-28513: internal error in heterogeneous remote agent
ORA-02063: preceding line from dblink

===================================
sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/xe/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH=(TNSNAMES)

ADR_BASE = /u01/app/oracle/product/11.2.0/xe
==================================
tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

dblink =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521)
)
(CONNECT_DATA =
(SID = dblink))
(HS=OK))

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

=======================
listener.ora
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = dblink)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/gtw)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/opt/sqlanywhere11/lib64:/u01/app/oracle/product/11.2.0/gtw/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

========================
initdblink.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=dblink
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL=255

HS_LANGUAGE=american_america.we8iso8859p1
HS_NLS_NCHAR=UTF-8

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

========================


Oracle Corporation --- MONDAY OCT 15 2012 17:23:39.915


Heterogeneous Agent Release
11.2.0.1.0




Oracle Corporation --- MONDAY OCT 15 2012 17:23:39.914

Version 11.2.0.1.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
Entered hgosdip
setting HS_OPEN_CURSORS to default of 50
setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
setting HS_FDS_RECOVERY_PWD to default value
setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
setting HS_IDLE_TIMEOUT to default of 0
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
HOSGIP returned value of "UTF-8" for HS_NLS_NCHAR
setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
setting HS_FDS_DATE_MAPPING to default of "DATE"
setting HS_RPC_FETCH_REBLOCKING to default of "ON"
setting HS_FDS_FETCH_ROWS to default of "100"
setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
setting HS_FDS_MAP_NCHAR to default of "TRUE"
setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
setting HS_FDS_QUERY_DRIVER to default of "TRUE"
setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
Parameter HS_FDS_QUOTE_IDENTIFIER is not set
setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "dblink"
Product-Info:
Port Rls/Upd:1/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:dblink
Exiting hgogprd, rc=0
hostmstr: 0: HOA After hoagprd
Invalid HS_NLS_NCHAR value UTF-8 specified
Setting HS_NLS_NCHAR to Server Default 2000
Invalid HS_NLS_NCHAR value UTF-8 specified
Setting HS_NLS_NCHAR to Server Default 2000
hostmstr: 0: HOA Before hoainit
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=2000
HOCXU_DB_CSET=873
HOCXU_SEM_VER=112000
Exiting hgoinit, rc=28513
hostmstr: 0: HOA After hoainit
RPC Calling nscontrol(0), rc=0
hostmstr: 0: RPC Before Exit Agent
hostmstr: 0: HOA Before hoaexit
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context
hostmstr: 0: HOA After hoaexit
hostmstr: 0: RPC After Exit Agent
=============================

Also, does anyone know how to determine the location (full path) of the unixODBC driver manager ?
Please assist if you can...thx.

Edited by: user601798 on Oct 15, 2012 3:37 PM
  • 1. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
    kgronau Guru
    Currently Being Moderated
    Let me start with the unixODBC driver:
    There's no generic location as it depends which package you've used to install the UnixODBC driver manager or how you compiled it from scratch. I commonly prefer to compile it from scratch as this offers me to install it to any location I like.

    If you're not sure where it is located, you could perform a search on your machine looking for libodbc.so. Then cross check it with "file <location>/libodbc.so" to see the lib is a 64bit library.
    So let's assume in the /usr/lib64 you found the libodbc.so library, then the setting
    HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
    is correct. Else you need to adopt the parameter.

    Next step is to check out which unixODBC version is installed. That's commonly done by calling "odbcinst -j".

    Looking now ta the gateway init file there's a parameter HS_NLS_NCHAR set to UTF-8. UnixODBC is commonly compiled with UCS2 support, so please change it to HS_NLS_NCHAR=UCS2


    Could you also please post the content of the odbc.ini file "more /etc/odbc.ini"?

    Thanks,
    Klaus
  • 2. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
    tx103108 Newbie
    Currently Being Moderated
    Changed the initdblink.ora file from UTF-8 to UCS2:

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO=txeis
    HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
    HS_FDS_TRACE_LEVEL=255

    HS_LANGUAGE=american_america.we8iso8859p1
    HS_NLS_NCHAR=UCS2

    and I get a connection. But now I only get the column headings back with no values except (it seems) to columns with a number value??

    odbc.ini file:
    =======
    [ODBC Data Sources]
    dblink = Sybase

    [dblink]
    Driver = SYBASE
    Description = Sybase
    CommLinks=tcpip(MyIP=10.10.1.100;IP=10.10.1.200:2638;)
    ServerName=dblink
    DBN=mydatabase

    [Default]
    Driver = /opt/sqlanywhere11/lib64/libdbodbc11.so

    Please advise...we are almost there ...

    Edited by: user601798 on Oct 16, 2012 7:06 AM
  • 3. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    What is the Sybase create table statement fo rthe table giving the problem ? We need to compare the datatypes with how they are handled by the ODBC driver and how those datatypes are handled by DG4ODBC.
    The following note available in My Oracle Supprot has some help on this -

    How to determine why certain foreign datatypes are not displayed within a select using DG4ODBC or HSODBC (Doc ID 252548.1)

    It would be useful to see -

    - Sybase create staement
    - an ODBC trace
    - a DG4ODBC trace

    from a select giving problems.

    Regards,
    Mike
  • 4. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
    tx103108 Newbie
    Currently Being Moderated
    Actually, even though the data does not always appear correctly via SQL*Plus, it does come up correctly in APEX via the gateway. (go figure?!).
    So that is really all we need it to do.
    So I'll go with this setup and address any further issues as they arise.
    Thanks for the help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points