This discussion is archived
13 Replies Latest reply: Oct 3, 2012 12:41 AM by mkirtley-Oracle RSS

Oracle XE and ODBC Gateway will not return rows from remote Sybase

tx103108 Newbie
Currently Being Moderated
OS: Red Hat Linux 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 Anywhere 10 server on WindowsXP.
Using FreeTDS odbc driver and unixODBC driver manager.
tsql and isql tools connect w/o issues.
Using 2 LISTENERs started from Oracle XE $ORACLE_HOME listener.ora file.
Noticed dg4odbc was available for use with Oracle XE installation -- that is the dg4odbc that I am using.
===================================
Via SQL*Plus, the following error occurs....

SQL> select * from mytable@dblink;
select * from mytable@dblink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
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 = reveal01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

dblink =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = mysystem)(PORT = 1522)
)
(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_GTW =
(SID_LIST =
(SID_DESC =
(SID_NAME = dblink)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/local/lib/libtdsodbc.so:/u01/app/oracle/product/11.2.0/xe/lib)
)
)

LISTENER_GTW =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = mysystem) (PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
)

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

DEFAULT_SERVICE_LISTENER = (XE)
~
~
========================
initdblink.ora

HS_FDS_CONNECT_INFO=dblink
HS_FDS_SHAREABLE_NAME=/etc/unixODBC-2.3.1/DriverManager/.libs/libodbc.so
#HS_FDS_TRACE_LEVEL=255

HS_LANGUAGE=american_america.we8iso8859p1
HS_NLS_NCHAR=UCS2

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
========================
HS trace file
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
HOSGIP returned value of "UCS2" 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_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:2/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.2.0_0008, Instance:dblink
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=1000
HOCXU_DB_CSET=873
HS_LANGUAGE is american_america.we8iso8859p1
LANG=en_US.UTF-8
HOCXU_SEM_VER=112000
Entered hgolofn at 2012/10/01-16:49:50
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/etc/unixODBC-2.3.1/DriverManager/.libs/libodbc.so"
Entered hgolofns at 2012/10/01-16:49:50
hoaerr:28500
Exiting hgolofns at 2012/10/01-16:49:50
Failed to load ODBC library symbol: /etc/unixODBC-2.3.1/DriverManager/.libs/libodbc.so(SQLAllocHandle)
Exiting hgolofn, rc=28500 at 2012/10/01-16:49:50
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:417 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:110 FUNCTION:hgoexit() ID:Connection context
===============================

Appears to be an issue with the unixODBC driver manager on Linux. As mentioned earlier, tsql and isql work fine.

Any help will be greatly appreciated.
Thank you.

Edited by: user601798 on Oct 1, 2012 2:52 PM

Edited by: user601798 on Oct 1, 2012 2:53 PM
  • 1. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    kgronau Guru
    Currently Being Moderated
    Please provide the output of the Unix command:
    file /etc/unixODBC-2.3.1/DriverManager/.libs/libodbc.so
    and
    file /u01/app/oracle/product/11.2.0/xe/bin/dg4odbc
    as well as
    file /usr/local/lib/libtdsodbc.so


    BTW, in your listener.ora the ENVS=LD_LIBRARY_PATH is not correct. It commonly requires the location to the unixODBC driver libs as well as to the FreetDS libs like:
    (ENVS=LD_LIBRARY_PATH=/usr/local/lib/::/etc/unixODBC-2.3.1/DriverManager/.libs:/u01/app/oracle/product/11.2.0/xe/lib)
  • 2. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    unixODBC (strange that this lib does not exist in any unixiODBC directory, however it is version 2.2.11 and isql works fine):
    --------------
    -bash-3.2$ file /usr/lib/libodbc.so
    /usr/lib/libodbc.so: symbolic link to `libodbc.so.1.0.0'
    -bash-3.2$ file libodbc.so.1.0.0
    libodbc.so.1.0.0: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), stripped

    dg4odbc: (hmmmm, why is this an AMDx x86-64 version? I am running on Intel. May not be an issue but...):
    -------------
    -bash-3.2$ file $ORACLE_HOME/bin/dg4odbc
    /u01/app/oracle/product/11.2.0/xe/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped

    FreeTDS: (hmmmm, why is this an AMDx x86-64 version? I am running on Intel. May not be an issue but...):
    ------------
    -bash-3.2$ file /usr/local/lib/libtdsodbc.so
    /usr/local/lib/libtdsodbc.so: symbolic link to `libtdsodbc.so.0.0.0'
    -bash-3.2$ file libtdsodbc.so.0.0.0
    libtdsodbc.so.0.0.0: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), not stripped

    ==========================================================
    I changed the listener.ora file and restarted the listner (LISTENER_GTW). Still same issue.

    =============
    listener.ora (new)
    # listener.ora Network Configuration File:

    SID_LIST_LISTENER_GTW =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = dblink)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
    (PROGRAM = dg4odbc)
    (ENVS=LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/u01/app/oracle/product/11.2.0/xe/lib)
    )
    )

    LISTENER_GTW =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = mysystem) (PORT = 1522))
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
    (PROGRAM = extproc)
    )
    )

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

    DEFAULT_SERVICE_LISTENER = (XE)
    =======================

    Any suggestions? Thanks.

    Edited by: user601798 on Oct 2, 2012 7:33 AM
  • 3. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Thanks for that information.
    What do you see for -

    file libodbc.so.1.0.0

    and

    file libtdsodbc.so.0.0.0

    these should both be 64-bit files.

    If the driver manager file libodbc.so is not in the UnixODBC directory then you need to change the parameter -

    HS_FDS_SHAREABLE_NAME

    in the initdblink.ora to the actual location of the file.
    Currently, you have -

    HS_FDS_SHAREABLE_NAME=/etc/unixODBC-2.3.1/DriverManager/.libs/libodbc.so

    but if the file is not in that directory then you will have errors.

    Regards,
    Mike
  • 4. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    Good catch on the initdblink.ora file HS parameter. Changed that, restarted the listener but no difference.
    Previous reply has 'file' command results. Noticed the unixODBC lib is 32-bit. Should that not be ok in a 64-bit env?

    We are getting close. Any suggestions?
    Thanks.
  • 5. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    On 64-bit platforms DG4ODBC runs as a 64-bit application and it needs a 64-bit ODBC driver and driver manager. It will not work with 32-bit files.
    You will need to download and install the 64-bit versions of the files and use those instead of the 32-bit versions.

    Regards,
    Mike
  • 6. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    I removed the 32-bit unixODBC libs and installed the unixODBC64 libs.

    [root@reveal01 ~]# file /usr/lib64/libodbc.so
    /usr/lib64/libodbc.so: symbolic link to `libodbc.so.1.0.0'
    [root@reveal01 ~]# file /usr/lib64/libodbc.so.1.0.0
    /usr/lib64/libodbc.so.1.0.0: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped

    isql and tsql still working fine. Ok, we are getting somewhere. New error from SQL*Plus session:

    ERROR at line 1:
    ORA-28511: lost RPC connection to heterogeneous remote agent using
    SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
    SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=reveal01)(PORT=1522))(CONNECT_DATA
    =(SID=dblink)))
    ORA-02063: preceding line from dblink
    Process ID: 21197
    Session ID: 50 Serial number: 2899

    trace file:
    ----------------------
    Oracle Corporation --- TUESDAY OCT 02 2012 11:25:08.090
    Heterogeneous Agent Release
    11.2.0.2.0
    Oracle Corporation --- TUESDAY OCT 02 2012 11:25:08.089
    Version 11.2.0.2.0
    Entered hgogprd
    HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
    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 "UCS2" 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_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:2/0 PrdStat:0

    Agent:Oracle Database Gateway for ODBC
    Facility:hsa
    Class:ODBC, ClassVsn:11.2.0.2.0_0008, Instance:dblink
    Exiting hgogprd, rc=0
    Entered hgoinit
    HOCXU_COMP_CSET=1
    HOCXU_DRV_CSET=31
    HOCXU_DRV_NCHAR=1000
    HOCXU_DB_CSET=873
    HS_LANGUAGE is american_america.we8iso8859p1
    LANG=en_US.UTF-8
    HOCXU_SEM_VER=112000
    Entered hgolofn at 2012/10/02-11:25:08
    HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab28300
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab28390
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab28b50
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab29dc0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab31890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab31d30
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab34580
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab35a20
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab36220
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08

    symbol_peflctx=0xaab37c40
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab37c60
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab39370
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3c4f0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3c890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3e330
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3ef80
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3f280
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab410b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab41480
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab42fc0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab42e10
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab2a3e0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab2bb80
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab2f4f0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab31150
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab32890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab354b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab36b40
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab37fb0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab399d0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab39e90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3b060
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3bc90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3cad0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3d4d0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3df90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3f9b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab3fec0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab40470
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab40b00
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab41770
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab44320
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab42b30
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab458c0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Entered hgolofns at 2012/10/02-11:25:08
    symbol_peflctx=0xaab46550
    hoaerr:0
    Exiting hgolofns at 2012/10/02-11:25:08
    Exiting hgolofn, rc=0 at 2012/10/02-11:25:08
    HOSGIP for "HS_OPEN_CURSORS" returned "50"
    HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
    HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
    HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
    HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
    HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
    HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
    HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
    HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
    Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
    treat_SQLLEN_as_compiled = 1
    Exiting hgoinit, rc=0 at 2012/10/02-11:25:08
    Entered hgolgon at 2012/10/02-11:25:08
    reco:0, name:dba, tflag:0
    Entered hgosuec at 2012/10/02-11:25:08
    Exiting hgosuec, rc=0 at 2012/10/02-11:25:08
    HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
    HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
    HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
    HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
    HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
    HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
    HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
    using dba as default value for "HS_FDS_DEFAULT_OWNER"
    HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
    Entered hgocont at 2012/10/02-11:25:08
    HS_FDS_CONNECT_INFO = "dblink"
    RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
    Entered hgogenconstr at 2012/10/02-11:25:08
    dsn:dblink, name:dba
    optn:
    Entered hgocip at 2012/10/02-11:25:08
    dsn:dblink
    Exiting hgocip, rc=0 at 2012/10/02-11:25:08
    Exiting hgogenconstr, rc=0 at 2012/10/02-11:25:08
    Entered hgolosf at 2012/10/02-11:25:08
    Exiting hgolosf, rc=0 at 2012/10/02-11:25:08
    DriverName:libtdsodbc.so, DriverVer:0.92.dev.20120922
    DBMS Name:SQL Anywhere, DBMS Version:10.00.0000
    Exiting hgocont, rc=0 at 2012/10/02-11:25:08
    SQLGetInfo returns Y for SQL_CATALOG_NAME
    SQLGetInfo returns 30 for SQL_MAX_CATALOG_NAME_LEN
    Exiting hgolgon, rc=0 at 2012/10/02-11:25:08
    Entered hgoulcp at 2012/10/02-11:25:08
    Entered hgowlst at 2012/10/02-11:25:08
    Exiting hgowlst, rc=0 at 2012/10/02-11:25:08
    SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
    TXN Capable:2, Isolation Option:0xf
    SQLGetInfo returns 30 for SQL_MAX_SCHEMA_NAME_LEN
    SQLGetInfo returns 30 for SQL_MAX_TABLE_NAME_LEN
    SQLGetInfo returns 36 for SQL_MAX_PROCEDURE_NAME_LEN
    SQLGetInfo returns for SQL_IDENTIFIER_QUOTE_CHAR
    SQLGetInfo returns Y for SQL_COLUMN_ALIAS
    Entered hgopoer at 2012/10/02-11:25:08
    hgopoer, line 231: got native error 504 and sqlstate 52W09; message follows...
    [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'sp_datatype_info' not found {52W09,NativeErr = 504}
    Exiting hgopoer, rc=0 at 2012/10/02-11:25:08
    hgoulcp, line 1246: calling SQLGetTypeInfo got sqlstate 52W09
    Exiting hgoulcp, rc=28500 at 2012/10/02-11:25:08 with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp() ID:GetTypeInfo: varchar

    Edited by: user601798 on Oct 2, 2012 9:30 AM

    Edited by: user601798 on Oct 2, 2012 9:38 AM
  • 7. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Looks like you are now making a connection using the gateway.
    An ora-28511 can have many different causes.
    Does the following work -

    select * from dual@dblink ;

    If so, what is the full select giving the error nd can you post a 255 level trace from running it somewhere we can look at it ?
    To so this in the iinitdblink.ora set -
    HS_FDS_TRACE_LEVEL=255
    and run the select from a new SQLPLUS session.
    Also, what is the chartacter set used by the Oracle server and by the non-Oracle database ?

    Regards,
    Mike
  • 8. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    select * from dual@dblink

    produces same error.

    Trace file output ( level=DEBUG) now part of previous message on this thread (sorry about the delay).

    Oracle char set:
    NLS_CHARACTERSET
    AL32UTF8

    NLS_NCHAR_CHARACTERSET
    AL16UTF16

    (remote) Sybase char set:
    CHAR
    windows-1252

    NCHAR
    UTF-8

    Character set in initdblink file:
    ========================
    initdblink.ora

    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=UCS2

    set ODBCINI=/etc/odbc.ini

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

    Trace output with debug level = 255: Using SQL 'select * from dual@dblink;'
    ----------------------------------------------------

    Oracle Corporation --- TUESDAY OCT 02 2012 12:03:44.334
    Heterogeneous Agent Release
    11.2.0.2.0
    Oracle Corporation --- TUESDAY OCT 02 2012 12:03:44.334
    Version 11.2.0.2.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 "UCS2" 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_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:2/0 PrdStat:0
    Agent:Oracle Database Gateway for ODBC
    Facility:hsa
    Class:ODBC, ClassVsn:11.2.0.2.0_0008, Instance:dblink
    Exiting hgogprd, rc=0
    hostmstr: 0: HOA After hoagprd
    hostmstr: 0: HOA Before hoainit
    Entered hgoinit
    HOCXU_COMP_CSET=1
    HOCXU_DRV_CSET=31
    HOCXU_DRV_NCHAR=1000
    HOCXU_DB_CSET=873
    HS_LANGUAGE is american_america.we8iso8859p1
    LANG=en_US.UTF-8
    HOCXU_SEM_VER=112000
    Entered hgolofn at 2012/10/02-12:03:44
    HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLAllocHandle
    symbol_peflctx=0xaab28300
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLBindCol
    symbol_peflctx=0xaab28390
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLBindParameter
    symbol_peflctx=0xaab28b50
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLCancel
    symbol_peflctx=0xaab29dc0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLDescribeParam
    symbol_peflctx=0xaab31890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLDisconnect
    symbol_peflctx=0xaab31d30
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLEndTran
    symbol_peflctx=0xaab34580
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLExecute
    symbol_peflctx=0xaab35a20
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLFetch
    symbol_peflctx=0xaab36220
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLFreeHandle
    symbol_peflctx=0xaab37c40
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLFreeStmt
    symbol_peflctx=0xaab37c60
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetData
    symbol_peflctx=0xaab39370
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetEnvAttr
    symbol_peflctx=0xaab3c4f0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetFunctions
    symbol_peflctx=0xaab3c890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLMoreResults
    symbol_peflctx=0xaab3e330
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLNumResultCols
    symbol_peflctx=0xaab3ef80
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLParamData
    symbol_peflctx=0xaab3f280
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLPutData
    symbol_peflctx=0xaab410b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLRowCount
    symbol_peflctx=0xaab41480
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLSetEnvAttr
    symbol_peflctx=0xaab42fc0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLSetDescRec
    symbol_peflctx=0xaab42e10
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLColAttribute
    symbol_peflctx=0xaab2a3e0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLColumns
    symbol_peflctx=0xaab2bb80
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLConnect
    symbol_peflctx=0xaab2f4f0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLDescribeCol
    symbol_peflctx=0xaab31150
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLDriverConnect
    symbol_peflctx=0xaab32890
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLExecDirect
    symbol_peflctx=0xaab354b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLForeignKeys
    symbol_peflctx=0xaab36b40
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetConnectAttr
    symbol_peflctx=0xaab37fb0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetDescField
    symbol_peflctx=0xaab399d0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetDescRec
    symbol_peflctx=0xaab39e90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetDiagField
    symbol_peflctx=0xaab3b060
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetDiagRec
    symbol_peflctx=0xaab3bc90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetInfo
    symbol_peflctx=0xaab3cad0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetStmtAttr
    symbol_peflctx=0xaab3d4d0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLGetTypeInfo
    symbol_peflctx=0xaab3df90
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLPrepare
    symbol_peflctx=0xaab3f9b0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLPrimaryKeys
    symbol_peflctx=0xaab3fec0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLProcedureColumns
    symbol_peflctx=0xaab40470
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLProcedures
    symbol_peflctx=0xaab40b00
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLSetConnectAttr
    symbol_peflctx=0xaab41770
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLSetStmtAttr
    symbol_peflctx=0xaab44320
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLSetDescField
    symbol_peflctx=0xaab42b30
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLStatistics
    symbol_peflctx=0xaab458c0
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Entered hgolofns at 2012/10/02-12:03:44
    libname=/usr/lib64/libodbc.so, funcname=SQLTables
    symbol_peflctx=0xaab46550
    hoaerr:0
    Exiting hgolofns at 2012/10/02-12:03:44
    Exiting hgolofn, rc=0 at 2012/10/02-12:03:44
    HOSGIP for "HS_OPEN_CURSORS" returned "50"
    HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
    HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
    HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
    HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
    HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
    HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
    HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
    HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
    Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
    treat_SQLLEN_as_compiled = 1
    Exiting hgoinit, rc=0 at 2012/10/02-12:03:44
    hostmstr: 0: HOA After hoainit
    hostmstr: 0: HOA Before hoalgon
    Entered hgolgon at 2012/10/02-12:03:44
    reco:0, name:dba, tflag:0
    Entered hgosuec at 2012/10/02-12:03:44
    Exiting hgosuec, rc=0 at 2012/10/02-12:03:44
    HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
    HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
    HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
    HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
    HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
    HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
    HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
    using dba as default value for "HS_FDS_DEFAULT_OWNER"
    HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
    Entered hgocont at 2012/10/02-12:03:44
    HS_FDS_CONNECT_INFO = "dblink"
    RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
    Entered hgogenconstr at 2012/10/02-12:03:44
    dsn:dblink, name:dba
    optn:
    Entered hgocip at 2012/10/02-12:03:44
    dsn:dblink
    Exiting hgocip, rc=0 at 2012/10/02-12:03:44
    ##>Connect Parameters (len=25)<##
    ## DSN=dblink;
    #! UID=dba;
    #! PWD=*
    Exiting hgogenconstr, rc=0 at 2012/10/02-12:03:44
    Entered hgolosf at 2012/10/02-12:03:44
    ODBC Function-Available-Array 0xFFFE 0x00FF 0xFF00 0xFB7F 0x03FF 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0xFE00 0x3F5F
    Exiting hgolosf, rc=0 at 2012/10/02-12:03:44
    DriverName:libtdsodbc.so, DriverVer:0.92.dev.20120922
    DBMS Name:SQL Anywhere, DBMS Version:10.00.0000
    Exiting hgocont, rc=0 at 2012/10/02-12:03:44
    SQLGetInfo returns Y for SQL_CATALOG_NAME
    SQLGetInfo returns 30 for SQL_MAX_CATALOG_NAME_LEN
    Exiting hgolgon, rc=0 at 2012/10/02-12:03:44
    hostmstr: 0: HOA After hoalgon
    RPC Calling nscontrol(0), rc=0
    hostmstr: 0: RPC Before Upload Caps
    hostmstr: 0: HOA Before hoaulcp
    Entered hgoulcp at 2012/10/02-12:03:44
    Entered hgowlst at 2012/10/02-12:03:44
    Exiting hgowlst, rc=0 at 2012/10/02-12:03:44
    SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
    TXN Capable:2, Isolation Option:0xf
    SQLGetInfo returns 30 for SQL_MAX_SCHEMA_NAME_LEN
    SQLGetInfo returns 30 for SQL_MAX_TABLE_NAME_LEN
    SQLGetInfo returns 36 for SQL_MAX_PROCEDURE_NAME_LEN
    SQLGetInfo returns for SQL_IDENTIFIER_QUOTE_CHAR
    SQLGetInfo returns Y for SQL_COLUMN_ALIAS
    Entered hgopoer at 2012/10/02-12:03:44
    hgopoer, line 231: got native error 504 and sqlstate 52W09; message follows...
    [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'sp_datatype_info' not found {52W09,NativeErr = 504}
    Exiting hgopoer, rc=0 at 2012/10/02-12:03:44
    hgoulcp, line 1246: calling SQLGetTypeInfo got sqlstate 52W09
    Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44 with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp() ID:GetTypeInfo: varchar
    hostmstr: 0: HOA After hoaulcp

    Edited by: user601798 on Oct 2, 2012 10:12 AM

    Edited by: user601798 on Oct 2, 2012 10:15 AM
  • 9. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    I wanted to add one thing that may be important and I did not want it to get lost in the previous (long) reply sent by yours truly.

    The Oracle ODBC Gateway IS WORKING on the Sybase / WindowsXP server. That is, I have installed and configured the Oracle ODBC Gateway on the same server as the Sybase database and it works fine. However, the environment we need to deploy to is Linux (i.e., the ODBC gateway on the same server as the Sybase database is not an option going forward and is just a 'band-aid' until we can get the Gateway on Linux up and running).

    Thx.

    Edited by: user601798 on Oct 2, 2012 11:04 AM
  • 10. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    This issue is almost certainly character-set related.

    When I use (in the file initdblink.ora)

    HS_NLS_NCHAR=UCS2

    I get the error

    SQL> select * from dual@dblink;
    select * from dual@dblink
    *
    ERROR at line 1:
    ORA-28511: lost RPC connection to heterogeneous remote agent using
    SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
    SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=reveal01)(PORT=1522))(CONNECT_DATA
    =(SID=dblink)))
    ORA-02063: preceding line from dblink
    Process ID: 22006
    Session ID: 95 Serial number: 3423

    .... but when I change the HS character setting to

    HS_NLS_NCHAR=UTF-8

    I get the error

    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

    Any ideas on the character set windows-1252 and its equivalent on Linux? (windows-1252 is what the Sybase database character set is and I believe it is a Latin character set)?

    Any suggestions?
  • 11. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    Just found out that the stored procedure sp_datatype_info is no longer available in Sybase SQL Anywhere database.
    Looking at the trace file, the Oracle ODBC Gateway for Linux needs that stored procedure to be available.

    ....
    Entered hgopoer at 2012/10/02-12:03:44
    hgopoer, line 231: got native error 504 and sqlstate 52W09; message follows...
    [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'sp_datatype_info' not found {52W09,NativeErr = 504}
    Exiting hgopoer, rc=0 at 2012/10/02-12:03:44
    hgoulcp, line 1246: calling SQLGetTypeInfo got sqlstate 52W09
    Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44 with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp() ID:GetTypeInfo: varchar
    hostmstr: 0: HOA After hoaulcp
    ....

    Apparently it is not required on the Windows version of the Oracle ODBC Gateway.

    Bottom line is that this appears to be a bug as the Oracle ODBC Gateway should connect to any ODBC 3 compliant database. And Sybase SQL Anywhere is such a database.
    By the way, this gateway does work successfully under Linux with Sybase Adaptive Enterprise database.

    Can we confirm this?

    Edited by: user601798 on Oct 2, 2012 2:23 PM
  • 12. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    tx103108 Newbie
    Currently Being Moderated
    Oracle documentation says ODBC Gateway supports any ODBC 3 compliant database, which Sybase SQL Anywhere is. I think this is a bug...
  • 13. Re: Oracle XE and ODBC Gateway will not return rows from remote Sybase
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    I think this is more likely a problem with the FreeTDS ODBC driver as discussed in this thread -

    Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase

    To check this could you get an ODBC trace and see if there is an entry similar to this -

    [ODBC][14632][SQLGetTypeInfo.c][164]
    Entry:
    Statement = 0x9b86518
    Data Type = SQL_VARCHAR
    [ODBC][14632][SQLGetTypeInfo.c][314]
    Exit:[SQL_ERROR]
    DIAG [42000] [FreeTDS][SQL Server]ASA Error -611: Transact-SQL feature not supported

    The FreeTDS driver translates the ODBC call SQLGetTypeInfo to the Sybase procedure sp_datatype_info which is not supported by Sybase Adaptive Server Anywhere. This is why it works with Sybase Adaptive Enterprise database which does support the procedure.
    You could check that also in DG4ODBC and ODBC traces using DG4ODBC and FreeTDS against the Enterprise version and see if it makes the same calls and conversion - SQLGetTypeInfo to sp_datatype_info.

    You could try a trial version of the another ODBC driver - for example DataDirect - and see if that works against Sybase ASA.

    Regards,
    Mike

Legend

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