5 Replies Latest reply: Sep 8, 2009 4:06 AM by ebangma-Oracle RSS

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

    299073
      Hi,
      I'm trying to connect from Oracle 11g (11.2.0.1.0) to Sybase Adaptive Server Anywhere (9.0.2.3527). My Oracle environment is running on Linux Centos 5.3, the sybase database runs on a Windows Server.

      All my attempts have failed so far - both through Oracle Database Gateway for Sybase and via Oracle Database Gateway for ODBC in combination with freeTDS. Using either way I'm ending up with apparently the same error:

      ASA Error -611: Transact-SQL feature not supported

      When I use db link created via dg4sybs (Oracle Database Gateway for Sybase) I get this:
      SQL> SELECT * FROM aaa@hvx;
      SELECT * FROM aaa@hvx
      *
      ERROR at line 1:
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      [Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]ASA Error -611:
      Transact-SQL feature not supported {HY000,NativeErr = 176}
      ORA-02063: preceding 2 lines from HVX

      _My agent init file {$ORACLE_HOME/dg4sybs/admin/initdg4sybs.ora} contains:_
      # This is a customized agent init file that contains the HS parameters
      # that are needed for the Database Gateway for Sybase

      #
      # HS init parameters
      #
      #havex_dms is the network alias defined in /etc/hosts pointing to 10.219.11.249
      #havex_dms is, at the same time, also a Sybase database name
      #using just IP as the server name resulted in "database not found" type of errors
      HS_FDS_CONNECT_INFO=havex_dms:2638/havex_dms
      #HS_FDS_CONNECT_INFO=10.219.11.249:2638/havex_dms
      #HS_FDS_CONNECT_INFO=Havex
      HS_FDS_TRACE_LEVEL=DEBUG
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER


      When I use db link created via dg4odbc (Oracle Database Gateway for ODBC) and freeTDS driver I get this:

      SQL> SELECT * FROM aaa@hvx1;
      SELECT * FROM aaa@hvx1
      *
      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=localhost.localdomain)(PORT=1521))
      (CONNECT_DATA=(SID=dg4odbc)))
      ORA-02063: preceding line from HVX1
      Process ID: 12664
      Session ID: 49 Serial number: 21020

      In the ODBC trace file I can see the same error:
      ...
      [ODBC][13595][SQLGetTypeInfo.c][314]
      Exit:[SQL_ERROR]
      DIAG [42000] [FreeTDS][SQL Server]ASA Error -611: Transact-SQL feature not supported
      ...

      My unixODBC & freeTDS configuration should be OK - I can sucessfully connect via isql:

      [root@localhost ~]# isql -v havex intranet 1234
      ---------------------------------------
      | Connected! |
      | |
      | sql-statement |
      | help [tablename] |
      | quit |
      | |
      ---------------------------------------
      SQL> select @@version;
      ---------------------------------------------------------------------------------------------------------------------------------
      | @@version |
      ---------------------------------------------------------------------------------------------------------------------------------
      | 9.0.2.3527 |
      ---------------------------------------------------------------------------------------------------------------------------------
      SQLRowCount returns 1
      1 rows fetched
      SQL> select * from aaa;
      -----------------------+
      | a | b |
      -----------------------+
      | test | 1 |
      | pokus | 2 |
      -----------------------+
      SQLRowCount returns 2
      2 rows fetched
      SQL>



      My Oracle Net configuration:

      {$ORACLE_HOME/network/admin/listener.ora}
      # listener.ora Network Configuration File: /oracle/db_11.2/network/admin/listener.ora
      # Generated by Oracle configuration tools.

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      )
      )

      SID_LIST_LISTENER=
      (SID_LIST=
      (SID_DESC=
      (SID_NAME=dg4sybs)
      (ORACLE_HOME=/oracle/db_11.2)
      (PROGRAM=dg4sybs)
      )
      (SID_DESC=
      (SID_NAME=dg4odbc)
      (ORACLE_HOME=/oracle/db_11.2)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib:/oracle/db_11.2/lib)
      )
      )

      ADR_BASE_LISTENER = /oracle

      {$ORACLE_HOME/network/admin/tnsnames.ora}
      # tnsnames.ora Network Configuration File: /oracle/db_11.2/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.

      VISION1 =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vision1.havex.cz)
      )
      )

      HAVEX_DMS =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (CONNECT_DATA =
      (SID = dg4sybs)
      )
      (HS=OK)
      )

      dg4odbc =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (CONNECT_DATA =
      (SID = dg4odbc)
      )
      (HS=OK)
      )

      It seems both drivers use Transact-SQL instructions which are then denied by ASA. I'm not familiar with Sybase products at all, but as far as what my googling revealed the Transact-SQL is only supported in Adaptive Server Enterprise (enterprise-class version of Sybase's database). I couldn't figure out if there's a way how to disable Transact-SQL in the driver configuration. Has anybody faced (solved) this problem? Is there a proved way how to connect from Oracle (10g or 11g) to Sybase Adaptive Server Anywhere?

      Thanks a lot in advance.

      Zdenek

      Edited by: zfajfr on 6.9.2009 11:55
        • 1. Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase
          ebangma-Oracle
          Hi Zdanek,

          Oracle Database Gateway for Sybase cannot be used with an Sybase Anywhere server, However Oracle Database Gateway for ODBC (DG4ODBC) should be able to function with Sybase Anywhere.

          Could you check the ODBC trace on what kind of SQL construct this error is generated? What happens if you disable AutoCommit in the odbc.ini?

          Regads,
          Ed
          • 2. Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase
            MMEGE-Oracle
            Hi,

            ASA is not officially supported.Only Sybase Server is supported by the gateways.

            Oracle Database Gateway for Sybase supports only Sybase 12.5 and 15 versions.

            To be used with gateway 11g, ODBC driver must be of course compliant level 3 ODBC.

            Furthermore, Transact-SQL is the dialect of SQL supported by Sybase Adaptive Server Enterprise, not necessarly by Adaptive Server Anywhere .

            So you have to check your database has been created to be compatible to support for Transact-SQL language .

            To get more information, please read the doc:
            http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/index.html

            regards

            Mireille
            • 3. Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase
              299073
              Hi Ed,
              I tried setting
              Autocommit=Off
              in /etc/odbc.ini
              but it is all the same.

              After a simple SQL select (SELECT * FROM aaa@hvx1;) the trace file contains this:

              [ODBC][14632][__handles.c][444]
              Exit:[SQL_SUCCESS]
              Environment = 0x9b73708
              [ODBC][14632][SQLSetEnvAttr.c][182]
              Entry:
              Environment = 0x9b73708
              Attribute = SQL_ATTR_ODBC_VERSION
              Value = 0x3
              StrLen = -6
              [ODBC][14632][SQLSetEnvAttr.c][349]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLAllocHandle.c][345]
              Entry:
              Handle Type = 2
              Input Handle = 0x9b73708
              [ODBC][14632][SQLAllocHandle.c][463]
              Exit:[SQL_SUCCESS]
              Output Handle = 0x9b851a0
              [ODBC][14632][SQLSetConnectAttr.c][318]
              Entry:
              Connection = 0x9b851a0
              Attribute = SQL_ATTR_AUTOCOMMIT
              Value = (nil)
              StrLen = -5
              [ODBC][14632][SQLSetConnectAttr.c][500]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLDriverConnect.c][678]
              Entry:
              Connection = 0x9b851a0
              Window Hdl = (nil)
              Str In = [DSN=Havex;UID=intranet;PWD=****][length = 31]
              Str Out = 0x9b844b8
              Str Out Max = 1024
              Str Out Ptr = 0xbfb15d10
              Completion = 0
              UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

              [ODBC][14632][SQLDriverConnect.c][1487]
              Exit:[SQL_SUCCESS]
              Connection Out [[DSN=Havex;UID=intranet;PWD=****][length = 31 (SQL_NTS)]]
              [ODBC][14632][SQLGetFunctions.c][147]
              Entry:
              Connection = 0x9b851a0
              Id = 999
              Supported = 0x9b82834
              [ODBC][14632][SQLGetFunctions.c][182]
              Exit:[SQL_SUCCESS]
              Supported = 0x9b82834 -> -2
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_DRIVER_NAME (6)
              Info Value = 0xbfb15a64
              Buffer Length = 512
              StrLen = 0xbfb15d14
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_DRIVER_VER (7)
              Info Value = 0xbfb15a64
              Buffer Length = 512
              StrLen = 0xbfb15d14
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_DBMS_NAME (17)
              Info Value = 0xbfb15a64
              Buffer Length = 512
              StrLen = 0xbfb15d14
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_DBMS_VER (18)
              Info Value = 0xbfb15a64
              Buffer Length = 512
              StrLen = 0xbfb15d14
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_CATALOG_NAME (10003)
              Info Value = 0xbfb15d44
              Buffer Length = 512
              StrLen = 0xbfb15fa0
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_MAX_CATALOG_NAME_LEN (34)
              Info Value = 0xbfb15f9c
              Buffer Length = 2
              StrLen = (nil)
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetConnectAttr.c][279]
              Entry:
              Connection = 0x9b851a0
              Attribute = SQL_ATTR_CURRENT_CATALOG
              Value = 0x9b84898
              Buffer Length = 30
              StrLen = 0x9b82b28
              [ODBC][14632][SQLGetConnectAttr.c][751]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_SCHEMA_USAGE (91)
              Info Value = 0xbfb1603c
              Buffer Length = 4
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_TXN_CAPABLE (46)
              Info Value = 0x9b8300c
              Buffer Length = 2
              StrLen = 0xbfb1607c
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_TXN_ISOLATION_OPTION (72)
              Info Value = 0x9b83010
              Buffer Length = 4
              StrLen = 0xbfb1607c
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_MAX_SCHEMA_NAME_LEN (32)
              Info Value = 0xbfb16078
              Buffer Length = 2
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_MAX_TABLE_NAME_LEN (35)
              Info Value = 0xbfb16078
              Buffer Length = 2
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_MAX_PROCEDURE_NAME_LEN (33)
              Info Value = 0xbfb16078
              Buffer Length = 2
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_IDENTIFIER_QUOTE_CHAR (29)
              Info Value = 0xbfb15df8
              Buffer Length = 512
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLGetInfo.c][214]
              Entry:
              Connection = 0x9b851a0
              Info Type = SQL_COLUMN_ALIAS (87)
              Info Value = 0xbfb15df8
              Buffer Length = 512
              StrLen = 0xbfb16074
              [ODBC][14632][SQLGetInfo.c][528]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLAllocHandle.c][510]
              Entry:
              Handle Type = 3
              Input Handle = 0x9b851a0
              [ODBC][14632][SQLAllocHandle.c][872]
              Exit:[SQL_SUCCESS]
              Output Handle = 0x9b86518
              [ODBC][14632][SQLBindCol.c][165]
              Entry:
              Statement = 0x9b86518
              Column Number = 1
              Target Type = 1 SQL_CHAR
              Target Value = 0xbfb15d80
              Buffer Length = 120
              StrLen Or Ind = 0xbfb16050
              [ODBC][14632][SQLBindCol.c][251]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLBindCol.c][165]
              Entry:
              Statement = 0x9b86518
              Column Number = 3
              Target Type = -16 SQL_C_SLONG
              Target Value = 0xbfb16038
              Buffer Length = 0
              StrLen Or Ind = 0xbfb16040
              [ODBC][14632][SQLBindCol.c][251]
              Exit:[SQL_SUCCESS]
              [ODBC][14632][SQLBindCol.c][165]
              Entry:
              Target Type = -15 SQL_C_SSHORT
              Target Value = 0xbfb16070
              Buffer Length = 0
              StrLen Or Ind = 0xbfb16048
              [ODBC][14632][SQLBindCol.c][251]
              Exit:[SQL_SUCCESS]
              [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

              [ODBC][14632][SQLGetDiagRec.c][710]
              Entry:
              Statement = 0x9b86518
              Rec Number = 1
              SQLState = 0xbfb15cfc
              Native = 0xbfb15d1c
              Message Text = 0xbfb15ac4
              Buffer Length = 510
              Text Len Ptr = 0xbfb15d34
              [ODBC][14632][SQLGetDiagRec.c][747]
              Exit:[SQL_SUCCESS]
              SQLState = 42000
              Native = 0xbfb15d1c -> 176
              Message Text = [[FreeTDS][SQL Server]ASA Error -611: Transact-SQL feature not supported]
              [ODBC][14632][SQLGetDiagRec.c][710]
              Entry:
              Statement = 0x9b86518
              Rec Number = 2
              SQLState = 0xbfb15cfc
              Native = 0xbfb15d1c
              Message Text = 0xbfb15ac4
              Buffer Length = 510
              Text Len Ptr = 0xbfb15d34
              [ODBC][14632][SQLGetDiagRec.c][747]
              Exit:[SQL_NO_DATA]


              Thank you,

              Zdenek
              • 4. Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase
                299073
                Hi Mireille,
                thanks for pointing me to the Sybase ASA documentation.

                Obviously, according to this page http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dberen9/00000578.htm
                our ASA database does not support Transact-SQL.

                I wonder if there is a way how to configure an existing Adaptive Server Anywhere database to be Transact-SQL compliant. Any idea?

                Thanks in advance,

                Zdenek
                • 5. Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase
                  ebangma-Oracle
                  The error is returned after following ODBC Call is executed:
                  Entry:
                  Statement = 0x9b86518
                  Data Type = SQL_VARCHAR
                  ODBC14632http://SQLGetTypeInfo.c314
                  Exit:SQL_ERROR
                  DIAG 42000 FreeTDSSQL ServerASA Error -611: Transact-SQL feature not supported
                  The ODBC Call SQLGetTypeInfo is translated by FreeTDS into calling a stored procedure called sp_datatype_info, which is not supported by Sybase Anywhere (like Mireille explained), therefor the error message. I think you should try another ODBC-Driver.

                  Regards,
                  Ed