<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi GianLuca,<br><br>I had difficulties getting Oracle Express to work with unixODBC in May this year (2009).<br>Here are the last two emails to Nick about it. The last explains what the problem was. I am not sure though if you are having the same problem.<br> <br><br>+++++ email to Nick begins ++++++<br>I have DB2, MySQL, Mimer SQL and SQL Anywhere connect successfully via unixODBC on Ubuntu 8.10, but Oracle Express 10g is giving me a hard time.<br><br><br>I am getting this error on SQLConnect when trying to connect to Oracle's HR sample database on my local PC:<br><br>Usage error: SQLConnect: dsn=TestDBDSN, user=hr, password=hr <<-- by my code<br>1: st=IM004, nerr=0, msg=<br>[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed<br><br>for:<br><br>/etc/odbcinst.ini<br>[OracleExpressdrv]<br>Driver =
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1<br>Description = Oracle 10g express driver<br><br>/etc/odbc.ini<br>[TestDBDSN]<br>Application Attributes = T<br>Attributes = W<br>BatchAutocommitMode = IfAllSuccessful<br>CloseCursor = F<br>DisableDPM = F<br>DisableMTS = T<br>Driver = OracleExpressdrv<br>DSN = TestDBDSN<br>EXECSchemaOpt =<br>EXECSyntax = T<br>Failover = T<br>FailoverDelay = 10<br>FailoverRetryCount = 10<br>FetchBufferSize = 64000<br>ForceWCHAR = F<br>Lobs = T<br>Longs = T<br>MetadataIdDefault = F<br>QueryTimeout = T<br>ResultSets = T<br># ServerName is TNS_alias_from_tnsnames.ora<br>ServerName = XE<br>SQLGetData extensions = F<br>Translation DLL =<br>Translation Option = 0<br>UserID = hr<br><br><br><br>I have posted about this in Oracle Linux forum, Oracle Express forum and on DBforums forum, but no one seems to know.<br><br>I have googled for days on 'IM004' and 'SQLAllocHandle on SQL_HANDLE_HENV failed' etc and
received matches, but most of them old, for Windows or other db products. Some posts have suggested 'set environment variable so-and-so', which I have done by adding to the bash login script. These<br><br># Added by ken for oracle<br><br>ODBCINI=/etc/odbc.ini;export ODBCINI<br>ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0;export ORACLE_HOME<br>TNS_ADMIN=$ORACLE_HOME/server/network/admin;export TNS_ADMIN<br>LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib;export LD_LIBRARY_PATH<br><br>PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/home/db2inst1/sqllib/bin:/home/db2inst1/sqllib/adm:/home/db2inst1/sqllib/misc:/home/db2inst1/sqllib/db2tss/bin:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin<br><br>but still no luck.<br><br><br>Oracle® Database<br>Administrator’s Reference<br>10g Release 2 (10.2) for UNIX-Based Operating Systems<br>B15658-06<br>March 2009<br><br>contains
information about ODBC using unixODBC in appendix G, but none of it helped. I have not found any other Oracle documentation about ODBC for Linux. The Oracle Admin Reference says that error messages formatted [unixODBC][Driver Manager]message-text are output by the unixODBC driver manager, so I checked and found that the message text 'Driver's SQLAllocHandle on SQL_HANDLE_HENV failed' is set in _info.c, post_internal_error for case ERROR_IM004.<br><br>It is hard for me to understand the unixodbc driver code. Is there any error information coming from the Oracle driver that may hint at a solution? Do you know if anyone has managed to connect to Oracle Express on Linux?<br><br>I really don't want to give up on this, but...<br><br>Ken<br> <br>P.S. when googling I found some posts containing unixODBC tracing code.<br>How do I turn tracing ON?<br>++++++ email to Nick ends ++++<br><br><br>+++ last email to Nick about
this problem begins ++++++<br><br>Nick,<br><br>I asked for your assistance to connect to Oracle Express and you suggested I run strace. That eventually solved the problem. Many thanks for that!<br><br>The problem was that Oracle had changed the install directory structure (undocumented?). I discovered by trial and error that ORACLE_HOME has to be:<br><br>ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server<br><br>not<br><br>ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0<br><br>which is indicated in dozens of pages on the Internet going back many years.<br> <br>I am using code::blocks IDE for programming on Ubuntu/Linux. It is a good IDE and it has served me well. I have ported several 100+ file projects from Windows and my current project developed on codeblocks connects to DB2, MySQL, Mimer SQL and SQL Anywhere via unixODBC, but Oracle is still giving me a problem. The Oracle connect works from the command
line but not from codeblocks.<br><br>I have posted about this on the codeblocks General forum, but not received any responses that have helped. The section below is taken from that forum.<br><br>++++ post on codeblocks forum begin +++<br>For Ubuntu 8.10. using codeblocks rev 5607 (the latest).<br><br>The program connects ok via ODBC to Oracle Express on the Ubuntu commandline: <br><br>ken@ken-desktop:~/projects/lsken/bin/Debug$ ./lsken2<br>ODBC connected OK << my tracemessage<br><br><br>but not from codeblocks Run in Build menu or Start on Debug menu. I receive error:<br><br>Can't open lib /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1<br><br>The so file is the Oracle ODBC driver and the unixODBC driver manager outputs the message.<br><br>I have written a minimal test program (THISAPP) in main.cpp that shows the problem:<br><br>#include <stdio.h><br>#include <string.h><br>#include
"sql.h"<br>#include "sqlext.h"<br><br>static void dspmessage ( const char * title , const char * msg )<br> {<br> printf ( "%s: %s " , title , msg ) ;<br> printf ( "Press Return/Enter to continue\n" ) ;<br> getchar ( ) ; //to wait<br> }<br><br>static void sqlerrinfo ( int handletp , int handle , const char * logstring )<br> {<br> char txt [ 4002 ] ;<br> int maxlen = 4000 ;<br> int ofs = sprintf ( txt , "%s\n" , logstring ) ;<br> int recnbr = 1;<br> long nerr ;<br> short lenerrmsg ;<br> int ln ;<br> unsigned char state [ 12 ] = { 0 } ;<br> unsigned char msg [ SQL_MAX_MESSAGE_LENGTH+1 ] = { 0 } ;<br> char line [ 802 ] = { 0 } ;<br> int rc = SQL_SUCCESS ;<br> while ( rc != SQL_NO_DATA_FOUND)<br>
{<br> rc = SQLGetDiagRec ( handletp , (SQLHANDLE)handle , recnbr ,<br> state, &nerr , msg , SQL_MAX_MESSAGE_LENGTH ,<br> &lenerrmsg ) ;<br> ln = sprintf ( line , "%d: st=%s, nerr=%d, msg=\n%s\n" ,<br> recnbr , state , (int)nerr , msg ) ;<br> if ( ln+ofs < maxlen )<br> {<br> strcpy ( txt+ofs , line )
;<br> ofs += ln ;<br> }<br> else<br> {<br> break ;<br> } ;<br> recnbr++; // for next diagnostic record.<br> }<br> dspmessage ( "usage error" , txt ) ;<br> }<br><br>static bool success ( SQLRETURN rc )<br> {<br> return (rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO) ;<br> }<br><br>static bool connectSrv ( const char * dsn , const char * uid , const char * pwd )<br> {<br> SQLHENV henv;<br> SQLRETURN rc = SQLAllocHandle ( SQL_HANDLE_ENV , SQL_NULL_HANDLE , &henv ) ;<br> if ( !success ( rc )
)<br> {<br> dspmessage ( "sqlapi" , "SQLAllocHandle Env failed" ) ;<br> return false ;<br> }<br><br> rc = SQLSetEnvAttr ( henv, SQL_ATTR_ODBC_VERSION , (void*)SQL_OV_ODBC3 , 0 ) ;<br> if ( !success ( rc ) )<br> {<br> sqlerrinfo ( SQL_HANDLE_ENV , (int)henv , "Set ODBC version fail" ) ;<br> SQLFreeEnv ( henv ) ;<br> return false ;<br> } ;<br><br> SQLHDBC hdbc;<br> rc = SQLAllocHandle ( SQL_HANDLE_DBC , henv , &hdbc ) ;<br> if ( !success ( rc ) )<br> {<br> sqlerrinfo ( SQL_HANDLE_ENV , (int)hdbc , "Alloc connection handle error" ) ;<br>
SQLFreeEnv ( henv ) ;<br> return false ;<br> } ;<br><br> SQLSetConnectAttr ( hdbc , SQL_LOGIN_TIMEOUT , (void*)5 , 0 ) ;<br><br> char msg [ 100 ] ;<br> rc = SQLConnect ( hdbc , (SQLCHAR*)dsn , SQL_NTS,<br> (SQLCHAR*) uid , SQL_NTS,<br> (SQLCHAR*) pwd , SQL_NTS);<br> if ( !success ( rc ) )<br> {<br> sprintf ( msg , "SQLConn: dsn=%s user=%s pwd=%s\n" , dsn , uid , pwd ) ;<br> sqlerrinfo ( SQL_HANDLE_DBC , (int)hdbc , msg ) ;<br> SQLFreeConnect ( hdbc )
;<br> SQLFreeEnv ( henv ) ;<br> dspmessage ( "sqlapi" , "SQLConnect failed" ) ;<br> return false ;<br> } ;<br><br> SQLHSTMT hstmt;<br> rc = SQLAllocHandle ( SQL_HANDLE_STMT , hdbc , &hstmt);<br> if ( !success ( rc ) )<br> {<br> sqlerrinfo ( SQL_HANDLE_DBC , (int)hdbc , "Alloc Stmt error" ) ;<br> SQLDisconnect ( hdbc ) ;<br> SQLFreeConnect ( hdbc ) ;<br> SQLFreeEnv ( henv ) ;<br> return false ;<br> } ;<br> return true ;<br> }<br><br>int main ( int argc , char * argv[] )<br> {<br> if ( connectSrv ( "TestDBDSN" , "hr" , "hr" )
)<br> {<br> dspmessage ( "sqlapi" , "ODBC Connect OK\n" ) ;<br> }<br> return 0 ;<br> }<br>+++ post to codeblocks forum ends ++++<br><br>+++++ last email to Nick ends +++++<br><br><br>So after many days I managed to get Oracle XE to work, but I have actually not used it yet!<br><br>Hope this may be of some use.<br><br>Ken<br><br><br><br><br><br><br>--- On <b>Tue, 29/12/09, GianLuca Sarto <i><glsarto@tiscali.it></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: GianLuca Sarto <glsarto@tiscali.it><br>Subject: Re: [unixODBC-support] how to connect to oracle xe?<br>To: "Support for the unixODBC project" <unixodbc-support@mailman.unixodbc.org><br>Date: Tuesday, 29 December, 2009, 6:58 PM<br><br><div class="plainMail">Nick,<br><br>thanks for your kind
reply.<br><br>I am not sure I fully understood the Oracle ReadME, but I managed to install the driver.<br><br>Well, almost...<br><br>ODBCConfig crashes when I try to configure the driver:<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ODBCConfig<br>ODBCConfig: libltdl/ltdl.c:1178: try_dlopen: Assertion `filename && *filename' failed.<br>Aborted<br><br>This is what I've done:<br><br>1. ensured that instant client was setup:<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ ./sqlplus user/pwd@//192.168.1.126:1521/XE<br><br>2. make script executable:<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo chmod a+x odbc_update_ini.sh<br><br>3. run script:<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ./odbc_update_ini.sh /<br><br>4. content of odbc.ini<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ cat /etc/odbc.ini<br>[oracle]<br>Driver =
/usr/lib/oracle/instantclient_10_2/libsqora.so.10.1<br>Database = //192.168.1.126:1521/XE<br>User = user<br>Password = pwd<br><br>5. content of odbcinst.ini<br>gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ cat /etc/odbcinst.ini<br>[Oracle 10g ODBC driver]<br>Description = Oracle ODBC driver for Oracle 10g<br>Driver = /usr/lib/oracle/instantclient_10_2/libsqora.so.10.1<br>Setup =<br>FileUsage =<br>CPTimeout =<br>CPReuse =<br><br>I must have missed something...<br>-Gian<br><br>Nick Gorham wrote:<br>> GianLuca Sarto wrote:<br>>> Hello All,<br>>> <br>>> I was ready to leave my XP Desktop and move it to Ubuntu Karmic, when I realized that I never tried my OpenOffice Base queries on
Linux.<br>>> <br>>> I installed the Oracle Instant Client Basic and Odbc, and it works, i.e. I can connect to our Oracle XE database with SqlPlus.<br>>> <br>>> However, no matter how I googled, I wasn't able to find a tutorial or how-to setup the Oracle driver with UnixOdbc. gian@hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ODBCConfig<br>>> ODBCConfig: libltdl/ltdl.c:1178: try_dlopen: Assertion `filename && *filename' failed.<br>>> Aborted<br>>> <br>>> I did find that Easysoft has commercial solutions for this issue, but I can't afford 1500 euro: I'd rather keep my XP desktop... :-(<br>>> <br>>> thanks for your time,<br>>> -GianLuca<br>> I think the XE driver itself contains docs about setting up with unixODBC<br>> <br><br>_______________________________________________<br>unixODBC-support mailing list<br><a ymailto="mailto:unixODBC-support@mailman.unixodbc.org"
href="/mc/compose?to=unixODBC-support@mailman.unixodbc.org">unixODBC-support@mailman.unixodbc.org</a><br><a href="http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support" target="_blank">http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support</a><br></div></blockquote></td></tr></table><br>
<hr size=1> <a href="http://sg.rd.yahoo.com/aa/mail/domainchoice/mail/signature/*http://mail.promotions.yahoo.com/newdomains/aa/"> New Email addresses available on Yahoo! </a> <br>
Get the Email name you've always wanted on the new @ymail and @rocketmail.<br>
Hurry before someone else does!