I tried and googled an tried again...but I can't get this to work. So if you can spare a moment, please help me out (or try to ;-) ).
Environment:
The Oracle Developer Days Virtual Box image, so that's 11gR2 EE on OEL. MySQL 5.1.51-community + mysql-connector-odbc-5.1.7-0.i386.rpm
1. Created a database called test in MySQL, with a user "oracle" and a password "123".
2. Created the file /home/oracle/.odbc.ini with as contents:
[ODBC Data Sources]
myodbc5 = MySQL ODBC 5.1.7 Driver DSN
[myodbc5]
DRIVER = /usr/lib/libmyodbc5.so
DESCRIPTION = MySQL ODBC 5.1.7 Driver DSN
SERVER = 127.0.0.1
PORT = 3306
USER = oracle
PASSWORD = 123
DATABASE = test
CHARSET = latin1
3. Created the file /home/oracle/app/oracle/product/11.2.0/dbhome_1/hs/admin/initmyodbc5.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=myodbc5
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc5.so
HS_LANGUAGE=AMERICAN.AMERICA.WE8ISO8859P15
HS_FDS_SUPPORT_STATISTICS=FALSE
# HS_NLS_NCHAR=AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
set ODBCINSTINI=/home/oracle/.odbc.ini
set LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/hs/lib:/usr/lib
#
# Environment variables required for the non-Oracle system
#
set HOME=/home/oracle
4. Added to tnsnames.ora :
dg4odbc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA = (SID=myodbc5))
(HS=OK)
)
5. Added to listener.ora (and restarted it) :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = myodbc5)
(PROGRAM = dg4odbc)
(ENVS ="LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/hs/lib:/usr/lib:/lib")
)
)
6. Ran some tests:
a. isql dg4odbc -> Connected!
b. tnsping dg4odbc -> OK
c. lsnrctl status -> Service "myodbc5" has 1 instance(s). Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
d. in SQLPLUS (as system): create public database link mysql connect to "oracle" identified by "123" using 'dg4odbc';
e. select * from test@mysql ("test" is a simple table I created in the MySQL DB) returns
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MYSQL
When I look in the trace file (in $ORACLE_HOME/hs/log) I see at the bottom of the file:
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib/libmyodbc5.so"
Entered hgolofns at 2010/11/02-14:10:54
hoaerr:28500
Exiting hgolofns at 2010/11/02-14:10:54
Failed to load ODBC library symbol: /usr/lib/libmyodbc5.so(SQLAllocHandle)
Exiting hgolofn, rc=28500 at 2010/11/02-14:10:54
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 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:108 FUNCTION:hgoexit() ID:Connection context
After Googling around I found out that all files are 32-bit (was on issue somewhere else)...and now I am stuck!
Any ideas, tips etc on how to proceed???
TIA
Roel