Oracle Database Discussions

I am creating a DB Link from 10g 64bit SLES10 to MySQL I managed to prove the concept with XE using hsodbc on a 32bit machine. When I went to test in production I found I needed to patch the DB and install the 11g gateway to use dg4odbc which I have now done, I have also tried using unixODBC or directly from the gateway using the myodbc drivers and get the same problem.

It seems from looking at the trace I am connecting to the DB successfully although when it passes the command to MySQL the double quotes are not removed from the statement so the statement fails, here is my config:

LISTENER.ORA

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=WAD5QT)
(ORACLE_HOME=/usr/lib/oracle/xe/product/11.1.0/tg_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/lib/oracle/xe/product/11.1.0/tg_1/hs/lib:/usr/lib/oracle/xe/product/11.1.0/tg_1/lib:/usr/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 148.164.75.1)(PORT = 1522))
)
)

initWAD5QT.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = WAD5-QT
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#HS_NLS_NCHAR=AL32UTF8

#
# ODBC specific environment variables
#
set ODBCINI=/etc/unixODBC/odbc.ini
set LD_LIBRARY_PATH=/usr/lib/

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>


ODBCINI

[MySQL5.1]
Driver = /usr/lib/libmyodbc5.so
UsageCount = 1



ODBC

[WAD5-QT]
Description = Quote Portal Dev Connection
Driver = MySQL5.1
SERVER = webappsdev5.sanmina-sci.com
PORT = 3306
DATABASE = qtdb_upd
CHARSET = latin1
OPTION = 3
QuotedID=No


I also tried adding QuotedID=No and QuotedID=Yes to the odbc.ini and HS_IDENTIFIER_QUOTE_CHAR=` to the initWAD5QT.ora although this seems to make no difference.

I also get this problem with 11g installed on both a 32bit machine which has proved to previously work with hsodbc.

Here is the entire trace when I run "select * from bom@QP_TO_QT_TEST;" :

Oracle Corporation --- WEDNESDAY JUL 29 2009 11:27:25.633

Heterogeneous Agent Release
11.1.0.6.0

Oracle Corporation --- WEDNESDAY JUL 29 2009 11:27:25.600

Version 11.1.0.6.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_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
setting HS_NLS_NCHAR to default of "AL32UTF8"
setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
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_PROC_IS_FUNC to default of "FALSE"
setting HS_FDS_CHARACTER_SEMANTICS 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"
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
Exiting hgosdip, rc=0
ORACLE_SID is "WAD5QT"
Product-Info:
Port Rls/Upd:6/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.1.0.6.0_0006, Instance:WAD5QT
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=46
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=873
HOCXU_SEM_VER=102000
Entered hgolofn at 2009/07/29-11:27:25
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib/libodbc.so"
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5220e60
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5221050
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5221b70
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52235f0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb522d3b0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb522d9a0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5231040
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5232be0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5233610
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52355f0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5235620
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52375c0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523b610
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523ba40
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523ded0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523eec0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523f2a0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5241aa0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5241f70
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5244450
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5244250
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5223d80
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5225e30
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb522a530
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb522ca10
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb522e630
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52324b0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5234130
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5235a80
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52399e0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523aa00
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523bd10
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523cbe0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523d9f0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb523fc10
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb52402d0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5240a80
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5241300
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5242330
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5245f00
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5243d50
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5247fd0
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Entered hgolofns at 2009/07/29-11:27:25
symbol_peflctx=0xb5249050
hoaerr:0
Exiting hgolofns at 2009/07/29-11:27:25
Exiting hgolofn, rc=0 at 2009/07/29-11:27:25
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 ".,"
Exiting hgoinit, rc=0 at 2009/07/29-11:27:25
Entered hgolgon at 2009/07/29-11:27:25
reco:0, name:Appsuser, tflag:0
Entered hgosuec at 2009/07/29-11:27:25
Exiting hgosuec, rc=0 at 2009/07/29-11:27:25
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using Appsuser as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2009/07/29-11:27:25
HS_FDS_CONNECT_INFO = "WAD5-QT"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2009/07/29-11:27:25
dsn:WAD5-QT, name:Appsuser
optn:
##>Connect Parameters (len=38)<##
## DSN=WAD5-QT;
#! UID=Appsuser;
#! PWD=*
Exiting hgogenconstr, rc=0 at 2009/07/29-11:27:25
DriverName:libmyodbc5.so, DriverVer:05.01.0005
DBMS Name:MySQL, DBMS Version:5.0.51b-community-nt
Exiting hgocont, rc=0 at 2009/07/29-11:27:31
SQLGetInfo returns Y for SQL_CATALOG_NAME
SQLGetInfo returns 64 for SQL_MAX_CATALOG_NAME_LEN
Exiting hgolgon, rc=0 at 2009/07/29-11:27:32
Entered hgoulcp at 2009/07/29-11:27:32
Entered hgowlst at 2009/07/29-11:27:32
Exiting hgowlst, rc=0 at 2009/07/29-11:27:32
SQLGetInfo returns ` for SQL_IDENTIFIER_QUOTE_CHAR
SQLGetInfo returns Y for SQL_COLUMN_ALIAS
Exiting hgoulcp, rc=0 at 2009/07/29-11:27:32
Entered hgouldt at 2009/07/29-11:27:32
Exiting hgouldt, rc=0 at 2009/07/29-11:27:32
Entered hgobegn at 2009/07/29-11:27:32
tflag:0 , initial:1
hoi:0xbfa65ea0, ttid (len 21) is ...
00: 382E4558 61376132 2E393839 362E3031 [XE.82a7a989.10.6]
10: 3836322E 32 [.2682]
tbid (len 10) is ...
0: 0006000A 00000A7A 0104 [....z.....]
Exiting hgobegn, rc=0 at 2009/07/29-11:27:32
Entered hgodtab at 2009/07/29-11:27:32
count:1
table: BOM
Entered hgopcda at 2009/07/29-11:27:33
Column:1(Id): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
Entered hgopcda at 2009/07/29-11:27:33
Column:2(BomFolder_Id): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:0, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
Entered hgopcda at 2009/07/29-11:27:33
Column:3(Region_Id): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
Entered hgopcda at 2009/07/29-11:27:33
Column:4(LastChange): dtype:93 (TIMESTAMP), prc/scl:19/0, nullbl:0, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
Entered hgopcda at 2009/07/29-11:27:33
Column:5(LastUser): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:0, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
Entered hgopcda at 2009/07/29-11:27:33
Column:6(IsDeactivated): dtype:-6 (TINYINT), prc/scl:3/0, nullbl:0, octet:0, sign:1, radix:10
Exiting hgopcda, rc=0 at 2009/07/29-11:27:33
The hoada for table BOM follows...
hgodtab, line 577: Printing hoada @ 0x81b5e7c
MAX:6, ACTUAL:6, BRC:1, WHT=6
DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
-5 BIGINT Y 8 8 0/ 0 0 0 20 Id
-5 BIGINT N 8 8 0/ 0 0 0 20 BomFolder_Id
4 INTEGER N 4 4 0/ 0 0 0 0 Region_Id
91 DATE N 16 16 0/ 0 0 0 0 LastChange
-5 BIGINT N 8 8 0/ 0 0 0 20 LastUser
-6 TINYINT N 1 1 0/ 0 0 0 20 IsDeactivated
Exiting hgodtab, rc=0 at 2009/07/29-11:27:33
Entered hgodafr, cursor id 0 at 2009/07/29-11:27:33
Exiting hgodafr, rc=0 at 2009/07/29-11:27:33
Entered hgotcis at 2009/07/29-11:27:33
Calling SQLStatistics for BOM
New Index:PRIMARY, type=3, ASCENDING, UNIQUE, cardinality=6
ordinal position = 1
New Index:BomFolder_Id, type=3, ASCENDING, NON-UNIQUE, cardinality=6
ordinal position = 1
New Index:LastUser, type=3, ASCENDING, NON-UNIQUE, cardinality=2
ordinal position = 1
New Index:Region_Id, type=3, ASCENDING, NON-UNIQUE, cardinality=2
ordinal position = 1
Calling SQLColumns for Appsuser.BOM
Column "Id": dtype=-5, colsize=19, decdig=0, char_octet_length=0, cumulative avg row len=4
Column "BomFolder_Id": dtype=-5, colsize=19, decdig=0, char_octet_length=0, cumulative avg row len=8
Column "Region_Id": dtype=4, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=12
Column "LastChange": dtype=93, colsize=19, decdig=0, char_octet_length=0, cumulative avg row len=28
Column "LastUser": dtype=-5, colsize=19, decdig=0, char_octet_length=0, cumulative avg row len=32
Column "IsDeactivated": dtype=-6, colsize=3, decdig=0, char_octet_length=0, cumulative avg row len=36
Exiting hgotcis, rc=0 at 2009/07/29-11:27:33
Entered hgopars, cursor id 1 at 2009/07/29-11:27:33
type:0
SQL text from hgopars, id=1, len=110 ...
00: 454C4553 41205443 49222E31 412C2264 [SELECT A1."Id",A]
10: 42222E31 6F466D6F 7265646C 2264495F [1."BomFolder_Id"]
20: 2E31412C 67655222 5F6E6F69 2C226449 [,A1."Region_Id",]
30: 222E3141 7473614C 6E616843 2C226567 [A1."LastChange",]
40: 222E3141 7473614C 72657355 31412C22 [A1."LastUser",A1]
50: 7349222E 63616544 61766974 22646574 [."IsDeactivated"]
60: 4F524620 4222204D 20224D4F 4131 [ FROM "BOM" A1]
Entered hgopoer at 2009/07/29-11:27:34
hgopoer, line 159: got native error 1064 and sqlstate 42000; message follows...
[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near '"Id",A1."BomFolder_Id",A1."Region_Id",A1."LastChange",A1."LastUser",A1."IsDeacti' at line 1[MySQL][ODBC 5.1 Driver][mysqld-5.0
.51b-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Id"
,A1."BomFolder_Id",A1."Region_Id",A1."LastChange",A1."LastUser",A1."IsDeacti' at line 1
Exiting hgopoer, rc=0 at 2009/07/29-11:27:34
hgopars, line 347: calling SQLNumResultCols got sqlstate 42000
Exiting hgopars, rc=28500 at 2009/07/29-11:27:34 with error ptr FILE:hgopars.c LINE:377 FUNCTION:hgopars() ID:Preprocess number of columns