Can't connect to Sybase (ASA) via Database Gateway for Sybase
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:
OracleODBC Sybase Wire Protocol driverSybase ASEASA 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:
...
ODBC13595http://SQLGetTypeInfo.c314
Exit:SQL_ERROR
DIAG 42000 FreeTDSSQL ServerASA 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