We have just install our test database to connect via ODBC to our backend informix database.
1) We have install windows 2008 R2
2) Install Informix 3.50 64 bit client + ODBC setup
3) Tested ODBC setup connection and was working fine. No error.
4) Install oracle ODBC gateway
5) Setup the connection initSID.ora at C:\Oracle\product\12.1.0\dbhome_1\hs\admin
6) Setup the listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = comets)
(ORACLE_HOME = c:\oracle\product\12.1.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
)
7) Setup tnsnames
COMETS =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521)))
(CONNECT_DATA = (SID = comets))
(HS = OK)
)
8) create db link
9) perform a test and encounter below error.
select * from dual@comets
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Informix][Informix ODBC Driver][Informix]A syntax error has occurred.
{42000,NativeErr = -201}
ORA-02063: preceding 2 lines from COMETS
Any ideas how to fix this error ?
Additional information from trace file:
| Oracle Corporation --- MONDAY | SEP 22 2014 13:41:59.119 |
Heterogeneous Agent Release
12.1.0.1.0
| Oracle Corporation --- MONDAY | SEP 22 2014 13:41:59.119 |
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "1"
HOCXU_VC2_MAX=4000
HOCXU_RAW_MAX=2000
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_CHARACTERS" 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"
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 wsm as default schema
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
hgoulcp, line 2138: calling SQLFetch got sqlstate HY004
SQL text from hgodtab, id=0, len=24 ...
| 00: 73656C65 6374202A 2066726F 6D202244 [select * from "D] |
| 10: 49564944 494F4E22 | [IVIDION"] |
SQL text from hgopars, id=1, len=24 ...
| 00: 53454C45 4354202A 2046524F 4D202244 [SELECT * FROM "D] |
| 10: 49564944 494F4E22 | [IVIDION"] |
hgopars, line 465: calling SQLPrepare got sqlstate 42000
SQL text from hgodtab, id=0, len=24 ...
| 00: 73656C65 6374202A 2066726F 6D202244 [select * from "D] |
| 10: 49564953 494F4E22 | [IVISION"] |
SQL text from hgopars, id=1, len=24 ...
| 00: 53454C45 4354202A 2046524F 4D202244 [SELECT * FROM "D] |
| 10: 49564953 494F4E22 | [IVISION"] |
hgopars, line 465: calling SQLPrepare got sqlstate 42000
SQL text from hgodtab, id=0, len=24 ...
| 00: 73656C65 6374202A 2066726F 6D202264 [select * from "d] |
| 10: 69766973 696F6E22 | [ivision"] |
SQL text from hgopars, id=1, len=24 ...
| 00: 53454C45 4354202A 2046524F 4D202264 [SELECT * FROM "d] |
| 10: 69766973 696F6E22 | [ivision"] |
hgopars, line 465: calling SQLPrepare got sqlstate 42000
SQL text from hgodtab, id=0, len=20 ...
| 00: 73656C65 6374202A 2066726F 6D202244 [select * from "D] |
| 10: 55414C22 | [UAL"] |
SQL text from hgopars, id=1, len=20 ...
| 00: 53454C45 4354202A 2046524F 4D202244 [SELECT * FROM "D] |
| 10: 55414C22 | [UAL"] |
hgopars, line 465: calling SQLPrepare got sqlstate 42000