[unixODBC-support] how to connect to oracle xe?

Ken Resander kresander at yahoo.com
Tue Dec 29 13:08:09 GMT 2009


Hi GianLuca,

I had difficulties getting Oracle Express to work with unixODBC in May this year (2009).
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.
  

+++++  email to Nick begins ++++++
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.


I am getting this error on SQLConnect when trying to connect to Oracle's HR sample database on my local PC:

Usage error: SQLConnect: dsn=TestDBDSN, user=hr, password=hr <<-- by my code
1: st=IM004, nerr=0, msg=
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

for:

/etc/odbcinst.ini
[OracleExpressdrv]
Driver = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1
Description = Oracle 10g express driver

/etc/odbc.ini
[TestDBDSN]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = OracleExpressdrv
DSN = TestDBDSN
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
# ServerName is TNS_alias_from_tnsnames.ora
ServerName = XE
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = hr



I have posted about this in Oracle Linux forum, Oracle Express forum and on DBforums forum, but no one seems to know.

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

#  Added by ken for oracle

ODBCINI=/etc/odbc.ini;export ODBCINI
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0;export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/server/network/admin;export TNS_ADMIN
LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib;export LD_LIBRARY_PATH

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

but still no luck.


Oracle® Database
Administrator’s Reference
10g Release 2 (10.2) for UNIX-Based Operating Systems
B15658-06
March 2009

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.

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?

I really don't want to give up on this, but...

Ken
      
P.S. when googling I found some posts containing unixODBC tracing code.
How do I turn tracing ON?
++++++  email to Nick ends  ++++


+++ last email to Nick about this problem begins ++++++

Nick,

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!

The problem was that Oracle had changed the install directory structure (undocumented?). I discovered by trial and error that ORACLE_HOME has to be:

ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server

not

ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0

which is indicated in dozens of pages on the Internet going back many years.
    
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.

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.

++++ post on codeblocks forum begin +++
For Ubuntu 8.10. using codeblocks rev 5607 (the latest).

The program connects ok via ODBC to Oracle Express on the Ubuntu commandline:   

ken at ken-desktop:~/projects/lsken/bin/Debug$ ./lsken2
ODBC connected OK << my tracemessage


but not from codeblocks Run in Build menu or Start on Debug menu. I receive error:

Can't open lib /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1

The so file is the Oracle ODBC driver and the unixODBC driver manager outputs the message.

I have written a minimal test program (THISAPP) in main.cpp that shows the problem:

#include <stdio.h>
#include <string.h>
#include "sql.h"
#include "sqlext.h"

static void dspmessage ( const char * title , const char * msg )
   {
   printf ( "%s: %s " , title , msg ) ;
   printf ( "Press Return/Enter to continue\n" ) ;
   getchar ( ) ;  //to wait
   }

static void sqlerrinfo ( int handletp , int handle , const char * logstring )
   {
   char txt [ 4002 ] ;
   int maxlen = 4000 ;
   int ofs = sprintf ( txt , "%s\n" , logstring ) ;
   int recnbr = 1;
   long nerr ;
   short lenerrmsg ;
   int ln ;
   unsigned char state [ 12 ] = { 0 } ;
   unsigned char msg [ SQL_MAX_MESSAGE_LENGTH+1 ] = { 0 } ;
   char line [ 802 ] = { 0 } ;
   int rc = SQL_SUCCESS ;
   while ( rc != SQL_NO_DATA_FOUND)
      {
      rc = SQLGetDiagRec ( handletp , (SQLHANDLE)handle , recnbr ,
                           state, &nerr , msg , SQL_MAX_MESSAGE_LENGTH ,
                           &lenerrmsg ) ;
      ln = sprintf ( line , "%d: st=%s, nerr=%d, msg=\n%s\n" ,
                     recnbr , state , (int)nerr , msg ) ;
      if ( ln+ofs < maxlen )
         {
         strcpy ( txt+ofs , line ) ;
         ofs += ln ;
         }
      else
         {
         break ;
         } ;
      recnbr++; // for next diagnostic record.
      }
   dspmessage ( "usage error" , txt ) ;
   }

static bool success ( SQLRETURN rc )
   {
   return (rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO) ;
   }

static bool connectSrv ( const char * dsn , const char * uid , const char * pwd )
   {
   SQLHENV henv;
   SQLRETURN rc = SQLAllocHandle ( SQL_HANDLE_ENV , SQL_NULL_HANDLE , &henv ) ;
   if ( !success ( rc ) )
      {
      dspmessage ( "sqlapi" , "SQLAllocHandle Env failed" ) ;
      return false ;
      }

   rc = SQLSetEnvAttr ( henv, SQL_ATTR_ODBC_VERSION , (void*)SQL_OV_ODBC3 , 0 ) ;
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_ENV , (int)henv , "Set ODBC version fail" ) ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;

   SQLHDBC hdbc;
   rc = SQLAllocHandle ( SQL_HANDLE_DBC , henv , &hdbc ) ;
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_ENV , (int)hdbc , "Alloc connection handle error" ) ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;

   SQLSetConnectAttr ( hdbc , SQL_LOGIN_TIMEOUT , (void*)5 , 0 ) ;

   char msg [ 100 ] ;
   rc = SQLConnect ( hdbc , (SQLCHAR*)dsn , SQL_NTS,
                      (SQLCHAR*) uid , SQL_NTS,
                      (SQLCHAR*) pwd , SQL_NTS);
   if ( !success ( rc ) )
      {
      sprintf ( msg , "SQLConn: dsn=%s user=%s pwd=%s\n" , dsn , uid , pwd ) ;
      sqlerrinfo ( SQL_HANDLE_DBC , (int)hdbc , msg ) ;
      SQLFreeConnect ( hdbc ) ;
      SQLFreeEnv ( henv ) ;
      dspmessage ( "sqlapi" , "SQLConnect failed" ) ;
      return false ;
      } ;

   SQLHSTMT hstmt;
   rc = SQLAllocHandle ( SQL_HANDLE_STMT , hdbc , &hstmt);
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_DBC , (int)hdbc , "Alloc Stmt error" ) ;
      SQLDisconnect ( hdbc ) ;
      SQLFreeConnect ( hdbc ) ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;
   return true ;
   }

int main ( int argc , char * argv[] )
   {
   if ( connectSrv ( "TestDBDSN"  , "hr" , "hr" ) )
      {
      dspmessage ( "sqlapi" , "ODBC Connect OK\n" ) ;
      }
   return 0 ;
   }
+++ post to codeblocks forum ends ++++

+++++ last email to Nick ends +++++


So after many days I managed to get Oracle XE to work, but I have actually not used it yet!

Hope this may be of some use.

Ken






--- On Tue, 29/12/09, GianLuca Sarto <glsarto at tiscali.it> wrote:

From: GianLuca Sarto <glsarto at tiscali.it>
Subject: Re: [unixODBC-support] how to connect to oracle xe?
To: "Support for the unixODBC project" <unixodbc-support at mailman.unixodbc.org>
Date: Tuesday, 29 December, 2009, 6:58 PM

Nick,

thanks for your kind reply.

I am not sure I fully understood the Oracle ReadME, but I managed to install the driver.

Well, almost...

ODBCConfig crashes when I try to configure the driver:
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ODBCConfig
ODBCConfig: libltdl/ltdl.c:1178: try_dlopen: Assertion `filename && *filename' failed.
Aborted

This is what I've done:

1. ensured that instant client was setup:
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ ./sqlplus user/pwd@//192.168.1.126:1521/XE

2. make script executable:
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo chmod a+x odbc_update_ini.sh

3. run script:
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ./odbc_update_ini.sh /

4. content of odbc.ini
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ cat /etc/odbc.ini
[oracle]
Driver        = /usr/lib/oracle/instantclient_10_2/libsqora.so.10.1
Database    = //192.168.1.126:1521/XE
User        = user
Password    = pwd

5. content of odbcinst.ini
gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ cat /etc/odbcinst.ini
[Oracle 10g ODBC driver]
Description     = Oracle ODBC driver for Oracle 10g
Driver          = /usr/lib/oracle/instantclient_10_2/libsqora.so.10.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

I must have missed something...
-Gian

Nick Gorham wrote:
> GianLuca Sarto wrote:
>> Hello All,
>> 
>> 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.
>> 
>> I installed the Oracle Instant Client Basic and Odbc, and it works, i.e. I can connect to our Oracle XE database with SqlPlus.
>> 
>> However, no matter how I googled, I wasn't able to find a tutorial or how-to setup the Oracle driver with UnixOdbc. gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ODBCConfig
>> ODBCConfig: libltdl/ltdl.c:1178: try_dlopen: Assertion `filename && *filename' failed.
>> Aborted
>> 
>> 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... :-(
>> 
>> thanks for your time,
>> -GianLuca
> I think the XE driver itself contains docs about setting up with unixODBC
> 

_______________________________________________
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support



      Get your new Email address!
Grab the Email name you&#39;ve always wanted before someone else does!
http://mail.promotions.yahoo.com/newdomains/aa/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20091229/39ba3a6f/attachment-0001.html>


More information about the unixODBC-support mailing list