13 Replies Latest reply: Oct 3, 2012 2:41 AM by Mkirtley-Oracle RSS

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

    tx103108
      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-Oracle
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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