Access to Oracle database from Haskell

Henning Thielemann

19 Jun 2008 19 Jun '08

11:17 a.m.

Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error: $ isql USER -v [IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [ISQL]ERROR: Could not SQLConnect This error message is discussed in various web forums, but there seems to be no systematic way to track down the problem. So I wonder whether there is another way to access the Oracle data base from Haskell.

Show replies by date

Alistair Bayley

20 Jun 20 Jun

1:10 a.m.

...

Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error:

Takusen's Oracle backend uses the Oracle Call Interface (OCI) library directly. I think most Oracle client drivers (ODBC, ADO.Net, etc) use the OCI. I don't know what the Oracle Instant Client is; I'll assume that it includes an oci.so or liboci.so. On Windows, with which I am most familiar, the Oracle client software is all installed in $ORACLE_HOME, and the OCI library is $ORACLE_HOME/bin/oci.dll. If you can locate that, then you ought to be able to use Takusen with it. Alistair

Alistair Bayley

5:51 a.m.

2008/6/20 Alistair Bayley :

...

...
Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error:

Takusen's Oracle backend uses the Oracle Call Interface (OCI) library directly. I think most Oracle client drivers (ODBC, ADO.Net, etc) use the OCI. I don't know what the Oracle Instant Client is; I'll assume that it includes an oci.so or liboci.so. On Windows, with which I am most familiar, the Oracle client software is all installed in $ORACLE_HOME, and the OCI library is $ORACLE_HOME/bin/oci.dll. If you can locate that, then you ought to be able to use Takusen with it.

Having just taken a closer took at what Oracle Instant Client is, I suspect that you might have some trouble getting Takusen to compile against it. The Instant Client lacks header files, while Takusen's FFI imports specify oci.h. I don't know what happens if ghc can't find the header files. Oracle do state that the Instant Client is for deployment only; developers (that means you) will need the full client installation. Another problem is that the Oracle installation process assumes that, for all platforms, the library is called oci i.e. the linker option -loci is used. For Unix clients, the OCI library seems to be libclnstsh.so, so I guess it should pass -lclnstsh. This highlights the lack of testing on non-Windows platforms. If you'd like to help get this working better, perhaps we can discuss off-list. Alistair

Paul Moore

21 Jun 21 Jun

7:18 a.m.

2008/6/20 Alistair Bayley :

...

Having just taken a closer took at what Oracle Instant Client is, I suspect that you might have some trouble getting Takusen to compile against it. The Instant Client lacks header files, while Takusen's FFI imports specify oci.h. I don't know what happens if ghc can't find the header files. Oracle do state that the Instant Client is for deployment only; developers (that means you) will need the full client installation.

There's an additional download for the Instant Client which includes the OCI headers. Paul.

Steve Lihn

9:49 a.m.

Oracle OCI interface is quite different between 7/8 and 9/10. And 10 is different from 9 in some respect. I don't know much about 11. Oracle 10.2.0.3 is a stable release, but there are some major server bugs in it, that Oracle had to release 10.2.0.4. I'd recommend Haskell community to focus on 10.2.0.x release. It should be good for another 2-3 years. It is too risky to venture into 11. That said, there is a large enterprise user base that will find Haskell useful if it works with Oracle DB (particularly on linux). On 6/21/08, Paul Moore wrote:

...

2008/6/20 Alistair Bayley :

...
Having just taken a closer took at what Oracle Instant Client is, I suspect that you might have some trouble getting Takusen to compile against it. The Instant Client lacks header files, while Takusen's FFI imports specify oci.h. I don't know what happens if ghc can't find the header files. Oracle do state that the Instant Client is for deployment only; developers (that means you) will need the full client installation.

There's an additional download for the Instant Client which includes the OCI headers.

Paul. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Henning Thielemann

24 Jun 24 Jun

10:17 a.m.

On Fri, 20 Jun 2008, Alistair Bayley wrote:

...

Another problem is that the Oracle installation process assumes that, for all platforms, the library is called oci i.e. the linker option -loci is used. For Unix clients, the OCI library seems to be libclnstsh.so, so I guess it should pass -lclnstsh. This highlights the lack of testing on non-Windows platforms. If you'd like to help get this working better, perhaps we can discuss off-list.

(I remain on the list in order to fill the archive with information, others may need, too.) I have now installed the OCI SDK http://download.oracle.com/otn/linux/instantclient/10204/oracle-instantclien... and Cabal-1.4 in order to get Takusen from Darcs repository running. I configure the package with -f oracle and the package gets compiled (and emits several warnings from Haddock-2.0). (Btw. Takusen should be split into several packages for all database backends because Cabal flags must not influence the package interface.) But on installation Cabal complains about missing /usr/oci directory. I assume that this must be the include dir of OCI include files. For me this is /usr/include/oracle/10.2.0.4/client/ I can add this path with runhaskell Setup.hs configure --extra-include-dirs=/usr/include/oracle/10.2.0.4/client but I cannot replace /usr/oci. If I remove /usr/oci manually from dist/installed-pkg-config then the path /usr/oci is again added by runhaskell Setup.hs install . I'm lost. Am I on the right track? Once I will get Takusen running with OCI - how would I use it? Is there a minimal example which connects to an Oracle database, performing a very simple query?

Alistair Bayley

10:55 a.m.

2008/6/24 Henning Thielemann :

...

(I remain on the list in order to fill the archive with information, others may need, too.)

As you wish.

...

(Btw. Takusen should be split into several packages for all database backends because Cabal flags must not influence the package interface.)

I don't understand this (cabal flags must not influence package interface). I thought that was the point of cabal flags? I wanted to have a single package installation, and this seems like the best way.

...

But on installation Cabal complains about missing /usr/oci directory.

When you run configure, you should get output that says: Using Oracle: <path> What is <path>? The Setup.hs assumes the entire Oracle installation is under a single root folder ($ORACLE_HOME); is this not true on Linux? If not, then we will have to find some other way of locating the bin and include folders. Perhaps we can do something like the Postgres installation, where there is a program you can run which will print the include and lib folders. That may mean a little hacking on Setup.hs, but there's already a good example there for Postgres.

...

Once I will get Takusen running with OCI - how would I use it? Is there a minimal example which connects to an Oracle database, performing a very simple query?

There's an example in the README.txt, which I've tweaked here for Oracle: {-# OPTIONS -fglasgow-exts #-} {-# OPTIONS -fallow-overlapping-instances #-} module Main where import Database.Oracle.Enumerator import Control.Monad.Trans (liftIO) main = flip catchDB reportRethrow $ withSession (connect "user" "pswd" "dbname") (do let iter (s::String) (_::String) = result s result <- doQuery (sql "select 'Hello world.' from dual") iter "" liftIO (putStrLn result) ) Alistair

Henning Thielemann

4:38 p.m.

On Tue, 24 Jun 2008, Alistair Bayley wrote:

...

2008/6/24 Henning Thielemann :

...
(Btw. Takusen should be split into several packages for all database backends because Cabal flags must not influence the package interface.)

I don't understand this (cabal flags must not influence package interface). I thought that was the point of cabal flags? I wanted to have a single package installation, and this seems like the best way.

No, if a package imports Takusen - how can it be assert that it can import the Oracle back-end? It can't because it can reference the Takusen package only by its name and a version. Flags are there to make a package appear consistent under different circumstances (e.g. Windows and Unix, or GHC-6.4 and GHC-6.8). If the back-end modules would not be exported, then the API would remain the same for different back-ends and that would be ok.

...

...
But on installation Cabal complains about missing /usr/oci directory.

When you run configure, you should get output that says: Using Oracle: <path>

What is <path>?

I don't get these questions.

...

The Setup.hs assumes the entire Oracle installation is under a single root folder ($ORACLE_HOME); is this not true on Linux?

Ah, I must have set ORACLE_HOME for the Takusen installation ... should be documented. However in my case, this doesn't change the path used by Takusen's Setup/install. $ echo $ORACLE_HOME /usr/lib/oracle/10.2.0.4/client $ ls $ORACLE_HOME bin lib $ ll $ORACLE_HOME/lib -rw-r--r-- 1 root root 1525 17. Feb 09:58 glogin.sql lrwxrwxrwx 1 root root 17 24. Jun 15:36 libclntsh.so -> libclntsh.so.10.1 -rw-r--r-- 1 root root 19076649 17. Feb 09:57 libclntsh.so.10.1 -rw-r--r-- 1 root root 5656445 17. Feb 09:57 libnnz10.so lrwxrwxrwx 1 root root 15 24. Jun 15:36 libocci.so -> libocci.so.10.1 -rw-r--r-- 1 root root 1398244 17. Feb 09:57 libocci.so.10.1 -rw-r--r-- 1 root root 72698427 17. Feb 09:57 libociei.so -rw-r--r-- 1 root root 120443 17. Feb 09:57 libocijdbc10.so -rw-r--r-- 1 root root 1434263 17. Feb 09:58 libsqlplusic.so -rw-r--r-- 1 root root 1069221 17. Feb 09:58 libsqlplus.so -rw-r--r-- 1 root root 781311 17. Feb 09:58 libsqora.so.10.1 -rw-r--r-- 1 root root 1555682 17. Feb 09:57 ojdbc14.jar I assume that libociei.so is the library I need. $ runhaskell Setup.hs configure --user -f oracle Configuring Takusen-0.8.2... $ runhaskell Setup.hs build && runhaskell Setup.hs haddock Preprocessing library Takusen-0.8.2... Building Takusen-0.8.2... /usr/bin/ar: creating dist/build/libHSTakusen-0.8.2.a Preprocessing library Takusen-0.8.2... Running Haddock for Takusen-0.8.2... Preprocessing library Takusen-0.8.2... ... Documentation created: dist/doc/html/Takusen/index.html $ runhaskell Setup.hs install Installing: [...]/lib/Takusen-0.8.2/ghc-6.8.2 Registering Takusen-0.8.2... Reading package info from "dist/installed-pkg-config" ... done. ghc-pkg: /usr/oci doesn't exist or isn't a directory (use --force to override)

...

...
Once I will get Takusen running with OCI - how would I use it? Is there a minimal example which connects to an Oracle database, performing a very simple query?

There's an example in the README.txt, which I've tweaked here for Oracle:

{-# OPTIONS -fglasgow-exts #-} {-# OPTIONS -fallow-overlapping-instances #-} module Main where import Database.Oracle.Enumerator import Control.Monad.Trans (liftIO) main = flip catchDB reportRethrow $ withSession (connect "user" "pswd" "dbname") (do let iter (s::String) (_::String) = result s result <- doQuery (sql "select 'Hello world.' from dual") iter "" liftIO (putStrLn result) )

Thanks for the adapted example!

Alistair Bayley

5:35 p.m.

...

...
2008/6/24 Henning Thielemann :

...
When you run configure, you should get output that says: Using Oracle: <path>

What is <path>?

I don't get these questions.

Sorry. I was really asking (not very clearly): what is the output from "runhaskell Setup.hs configure -foracle" ? You've answered that below.

...

Ah, I must have set ORACLE_HOME for the Takusen installation ... should be documented. However in my case, this doesn't change the path used by Takusen's Setup/install.

Well, you don't actually need it set for the Takusen install (it doesn''t use it). But $ORACLE_HOME/bin should be in your path. And it needs to contain the executable sqlplus. Can you run sqlplus? It looks as though you don't have it. If sqlplus is not in my path then I get this error in the configure step, because I have specified sqlplus as a buildtool in Takusen.cabal:

...

setup configure -foracle Configuring Takusen-0.8.2... setup: sqlplus is required but it could not be found.

Note: sqlplus isn't really needed to build; this is just a validation step, to check you have $ORACLE_HOME/bin in your path.

...

$ echo $ORACLE_HOME /usr/lib/oracle/10.2.0.4/client $ ls $ORACLE_HOME bin lib $ ll $ORACLE_HOME/lib ... I assume that libociei.so is the library I need.

Actually it's libclntsh.so. You need to change the oracle section in Takusen.cabal to this: If flag(oracle) Exposed-modules: Database.Oracle.Enumerator , Database.Oracle.OCIConstants , Database.Oracle.OCIFunctions Build-Tools: sqlplus If os(windows) Extra-Libraries: oci Else Extra-Libraries: clntsh Your $ORACLE_HOME installation looks fairly normal, if somewhat minimal. I would normally expect to see sqlplus, and maybe some other executables, like tnsping. And a folder $ORACLE_HOME/oci/include, which contains the OCI header files. Perhaps you could also install the sqplus and SDK packages, as Lanny Ripple suggested, although I suspect they may not be strictly necessary, as you can apparently successfully compile, as evidenced below.

...

$ runhaskell Setup.hs configure --user -f oracle Configuring Takusen-0.8.2...

Here, I would expect to see "Using Oracle: /usr/lib/oracle/10.2.0.4/client" After setup configure you can say: $ runhaskell Setup.hs register --gen-pkg-config and then take a look at the generated Takusen-0.8.2.conf. Check the library-dirs and include-dirs fields. Alistair

Alistair Bayley

25 Jun 25 Jun

4:10 a.m.

...

...
$ ll $ORACLE_HOME/lib ... I assume that libociei.so is the library I need.

Actually it's libclntsh.so. You need to change the oracle section in Takusen.cabal to this:

<hand-slaps-forehead/> Another difference between Windows and Linux Oracle installations is that the client libs are in $ORACLE_HOME/lib on Linux, but in $ORACLE_HOME/bin on Windows. The Setup.hs script has the Windows case baked in. You should change the configOracle function like so: configOracle verbose buildtools = createConfigByFindingExe verbose buildtools "Oracle" sqlplusProgram parentFolder oracleLibDir "oci/include" -- location of OCI client library differs between Windows and Unix where oracleLibDir = if isWindows then "bin" else "lib" Alistair

Henning Thielemann

4:59 a.m.

On Tue, 24 Jun 2008, Alistair Bayley wrote:

...

...
...
2008/6/24 Henning Thielemann :

...
When you run configure, you should get output that says: Using Oracle: <path>

What is <path>?

I don't get these questions.

Sorry. I was really asking (not very clearly): what is the output from "runhaskell Setup.hs configure -foracle" ?

It doesn't output the Oracle path. :-(

...

...
Ah, I must have set ORACLE_HOME for the Takusen installation ... should be documented. However in my case, this doesn't change the path used by Takusen's Setup/install.

Well, you don't actually need it set for the Takusen install (it doesn''t use it). But $ORACLE_HOME/bin should be in your path. And it needs to contain the executable sqlplus. Can you run sqlplus?

Yes, I have successfully connected to a database with sqlplus, already.

...

If sqlplus is not in my path then I get this error in the configure step, because I have specified sqlplus as a buildtool in Takusen.cabal:

...
setup configure -foracle Configuring Takusen-0.8.2... setup: sqlplus is required but it could not be found.

Note: sqlplus isn't really needed to build; this is just a validation step, to check you have $ORACLE_HOME/bin in your path.

I don't get an error. sqlplus is in $ORACLE_HOME/bin, but the RPM package also sets a link from /usr/bin/sqlplus to $ORACLE_HOME/bin, thus 'sqlplus' is in the $PATH also without $ORACLE_HOME. (But sqlplus cannot be started until the LD_LIBRARY_PATH is extended to Oracles libraries.)

...

...
$ echo $ORACLE_HOME /usr/lib/oracle/10.2.0.4/client $ ls $ORACLE_HOME bin lib $ ll $ORACLE_HOME/lib ... I assume that libociei.so is the library I need.

Actually it's libclntsh.so. You need to change the oracle section in Takusen.cabal to this:

If flag(oracle) Exposed-modules: Database.Oracle.Enumerator , Database.Oracle.OCIConstants , Database.Oracle.OCIFunctions Build-Tools: sqlplus If os(windows) Extra-Libraries: oci Else Extra-Libraries: clntsh

Your $ORACLE_HOME installation looks fairly normal, if somewhat minimal. I would normally expect to see sqlplus, and maybe some other executables, like tnsping. And a folder $ORACLE_HOME/oci/include, which contains the OCI header files.

They got installed to: /usr/include/oracle/10.2.0.4/client/ That is, they are not in $ORACLE_HOME. :-(

...

...
$ runhaskell Setup.hs configure --user -f oracle Configuring Takusen-0.8.2...

Here, I would expect to see "Using Oracle: /usr/lib/oracle/10.2.0.4/client"

It does not tell me. :-(

...

After setup configure you can say: $ runhaskell Setup.hs register --gen-pkg-config

and then take a look at the generated Takusen-0.8.2.conf. Check the library-dirs and include-dirs fields.

Takusen-0.8.2.conf and dist/installed-pkg-config contain the same. They contain include-dirs: /usr/oci independent from my setting of ORACLE_HOME. As I said, I cannot manually fix dist/installed-pkg-config, because it gets overwritten by 'Setup install'.

Bayley, Alistair

5:24 a.m.

...

From: haskell-cafe-bounces@haskell.org [mailto:haskell-cafe-bounces@haskell.org] On Behalf Of Henning Thielemann

I don't get an error. sqlplus is in $ORACLE_HOME/bin, but the RPM package also sets a link from /usr/bin/sqlplus to $ORACLE_HOME/bin, thus 'sqlplus' is in the $PATH also without $ORACLE_HOME. (But sqlplus cannot be started until the LD_LIBRARY_PATH is extended to Oracles libraries.)

Ahhh... so Setup.hs thinks sqlplus is in /usr/bin, rather than $ORACLE_HOME/bin. That explains: include-dirs: /usr/oci I'll have to change the way that Setup.hs tries to find $ORACLE_HOME. Using getEnv would be a much better idea. I don't recall why I didn't use it before; perhaps it's not always set on Windows installations.

...

...
Your $ORACLE_HOME installation looks fairly normal, if somewhat minimal. I would normally expect to see sqlplus, and maybe some other executables, like tnsping. And a folder $ORACLE_HOME/oci/include, which contains the OCI header files.

They got installed to: /usr/include/oracle/10.2.0.4/client/ That is, they are not in $ORACLE_HOME. :-(

I'm not sure how Setup.hs would find out where these are installed. Any ideas? It may not matter, as I think you can compile without them. I'd expect problems if you compile -fvia-C, though.

...

...
...
$ runhaskell Setup.hs configure --user -f oracle Configuring Takusen-0.8.2...

Here, I would expect to see "Using Oracle: /usr/lib/oracle/10.2.0.4/client"

It does not tell me. :-(

That is odd. Alistair ***************************************************************** Confidentiality Note: The information contained in this message, and any attachments, may contain confidential and/or privileged material. It is intended solely for the person(s) or entity to which it is addressed. Any review, retransmission, dissemination, or taking of any action in reliance upon this information by persons or entities other than the intended recipient(s) is prohibited. If you received this in error, please contact the sender and delete the material from any computer. *****************************************************************

Alistair Bayley

6:07 a.m.

...

I'll have to change the way that Setup.hs tries to find $ORACLE_HOME. Using getEnv would be a much better idea. I don't recall why I didn't use it before; perhaps it's not always set on Windows installations.

Try this version of configOracle in Setup.hs: configOracle verbose buildtools = do if not (sqlplusProgram `isElem` buildtools) then return Nothing else do path <- getEnv "ORACLE_HOME" info verbose ("Using Oracle: " ++ path) makeConfig path libDir "oci/include" where libDir = if isWindows then "bin" else "lib" You'll also need to add this import: import System.Environment (getEnv) I always used to get the "Using..." messages, but now it seems I need to say: $ setup configure -v -foracle Alistair

John Goerzen

9:25 a.m.

Henning Thielemann wrote:

...

On Tue, 24 Jun 2008, Alistair Bayley wrote:

...
2008/6/24 Henning Thielemann :

...
(Btw. Takusen should be split into several packages for all database backends because Cabal flags must not influence the package interface.) I don't understand this (cabal flags must not influence package interface). I thought that was the point of cabal flags? I wanted to have a single package installation, and this seems like the best way.

No, if a package imports Takusen - how can it be assert that it can import

I haven't read this entire thread, but I might also just interject here that HDBC supports ODBC (on Windows, and on Linux/Posix platforms via unixODBC, iODBC, or similar), which may be another avenue for you to try. I'm sure there are ODBC Oracle drivers out there, and so if you have your ODBC layer working, you get Haskell support easily. -- John

Henning Thielemann

9:31 a.m.

On Wed, 25 Jun 2008, John Goerzen wrote:

...

I haven't read this entire thread, but I might also just interject here that HDBC supports ODBC (on Windows, and on Linux/Posix platforms via unixODBC, iODBC, or similar), which may be another avenue for you to try. I'm sure there are ODBC Oracle drivers out there, and so if you have your ODBC layer working, you get Haskell support easily.

This was actually how the thread started: I don't get ODBC for Oracle working on Linux. :-( Maybe this is an unixODBC issue - is iODBC an API-compatible replacement?

John Goerzen

2:09 p.m.

Henning Thielemann wrote:

...

On Wed, 25 Jun 2008, John Goerzen wrote:

...
I haven't read this entire thread, but I might also just interject here that HDBC supports ODBC (on Windows, and on Linux/Posix platforms via unixODBC, iODBC, or similar), which may be another avenue for you to try. I'm sure there are ODBC Oracle drivers out there, and so if you have your ODBC layer working, you get Haskell support easily.

This was actually how the thread started: I don't get ODBC for Oracle working on Linux. :-( Maybe this is an unixODBC issue - is iODBC an API-compatible replacement?

Yes, iODBC is. There are also proprietary ODBC implementations. At work, we have to deal with Progress databases. Their ODBC library, presumably due to being a rather ancient shared library, doesn't work with unixODBC -- but they ship their entire own ODBC framework, Merant ODBC. HDBC compiles against it (though must be modified to not support wide characters, which Merant ODBC doesn't support). -- John

Henning Thielemann

26 Jun 26 Jun

11:04 a.m.

On Wed, 25 Jun 2008, John Goerzen wrote:

...

Henning Thielemann wrote:

...
On Wed, 25 Jun 2008, John Goerzen wrote:

...
I haven't read this entire thread, but I might also just interject here that HDBC supports ODBC (on Windows, and on Linux/Posix platforms via unixODBC, iODBC, or similar), which may be another avenue for you to try. I'm sure there are ODBC Oracle drivers out there, and so if you have your ODBC layer working, you get Haskell support easily.

This was actually how the thread started: I don't get ODBC for Oracle working on Linux. :-( Maybe this is an unixODBC issue - is iODBC an API-compatible replacement?

Yes, iODBC is.

Ok, I gave also iODBC a try and installed libiodbc-3.52.6-1.i386.rpm libiodbc-admin-3.52.6-1.i386.rpm libiodbc-devel-3.52.6-1.i386.rpm After a bit of configuration I called: $ iodbctest NMR2 iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0607.1008 1: SQLDriverConnect = [iODBC][Driver Manager]/usr/lib/oracle/10.2.0.4/client/lib/libsqora.so.10.1: undefined symbol: _tcsnccmp (0) SQLSTATE=00000 2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003 What is _tcsnccmp and where can I get it from? I hope it is nothing Windows specific. Google suggests that its prototype is: int _tcsnccmp(const _TXCHAR* string1, const _TXCHAR* string2, size_t count);

John Goerzen

11:30 a.m.

Henning Thielemann wrote:

...

On Wed, 25 Jun 2008, John Goerzen wrote:

$ iodbctest NMR2 iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0607.1008 1: SQLDriverConnect = [iODBC][Driver Manager]/usr/lib/oracle/10.2.0.4/client/lib/libsqora.so.10.1: undefined symbol: _tcsnccmp (0) SQLSTATE=00000 2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003

What is _tcsnccmp and where can I get it from? I hope it is nothing Windows specific.

I don't know, but it could be that you are missing some Oracle libraries, or that your Oracle library location isn't on your LD_LIBRARY_PATH or /etc/ld.so.conf path. Since this isn't a Haskell-specific problem, I'd suggest you check it out on the Oracle, unixODBC, or iODBC groups. -- John

Steve Lihn

20 Jun 20 Jun

8:44 p.m.

You may want to check this out. http://www.orafaq.com/wiki/ODBC_FAQ#Where_can_one_get_ODBC_drivers_for_Oracl... As Oracle is a commercial company who is not interested in open source historically, it is little chance that you will get robust software for free -- from someone with many years of Oracle DBA experience :-) On 6/19/08, Henning Thielemann wrote:

...

Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error:

$ isql USER -v [IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [ISQL]ERROR: Could not SQLConnect

This error message is discussed in various web forums, but there seems to be no systematic way to track down the problem. So I wonder whether there is another way to access the Oracle data base from Haskell. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Lanny Ripple

21 Jun 21 Jun

1:42 a.m.

I had luck with this the other day using Database.HDBC.ODBC. For Ubuntu's Hardy I found that Oracle's 10.2.0.3 worked best. (10.2.0.4 and 11 seemed to have problems for me at least.) http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu... Grab the basic (not basic-lite), odbc, and sqlplus (to test) zips. The odbc_update_ini.sh should be run from inside the instantclient_10_2 directory as root sudo /bin/sh odbc_update_ini.sh / (assuming a standard unixODBC install.) You'll also need a tnsnames.ora file to describe your connection(s) to the DB(s). You'll also need to define some environment variables to run against all this. I use a small script: #!/bin/sh oracle_home=/opt/lib/oracle/instantclient_10_2 export TNS_ADMIN=$oracle_home export LD_LIBRARY_PATH=$oracle_home [ $# = 0 ] && exit 1 exec "$@" Best of luck, -ljr Steve Lihn wrote:

...

You may want to check this out.

http://www.orafaq.com/wiki/ODBC_FAQ#Where_can_one_get_ODBC_drivers_for_Oracl...

As Oracle is a commercial company who is not interested in open source historically, it is little chance that you will get robust software for free -- from someone with many years of Oracle DBA experience :-)

On 6/19/08, Henning Thielemann wrote:

...
Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error:

$ isql USER -v [IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [ISQL]ERROR: Could not SQLConnect

This error message is discussed in various web forums, but there seems to be no systematic way to track down the problem. So I wonder whether there is another way to access the Oracle data base from Haskell. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Lanny Ripple

5:06 p.m.

Lanny Ripple wrote:

...

I had luck with this the other day using Database.HDBC.ODBC. For Ubuntu's Hardy I found that Oracle's 10.2.0.3 worked best. (10.2.0.4 and 11 seemed to have problems for me at least.)

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...

Grab the basic (not basic-lite), odbc, and sqlplus (to test) zips.

Oops. And the sdk zip.

...

The odbc_update_ini.sh should be run from inside the instantclient_10_2 directory as root

sudo /bin/sh odbc_update_ini.sh /

(assuming a standard unixODBC install.) You'll also need a tnsnames.ora file to describe your connection(s) to the DB(s). You'll also need to define some environment variables to run against all this. I use a small script:

#!/bin/sh oracle_home=/opt/lib/oracle/instantclient_10_2 export TNS_ADMIN=$oracle_home export LD_LIBRARY_PATH=$oracle_home [ $# = 0 ] && exit 1 exec "$@"

Best of luck, -ljr

Steve Lihn wrote:

...
You may want to check this out.

http://www.orafaq.com/wiki/ODBC_FAQ#Where_can_one_get_ODBC_drivers_for_Oracl...

As Oracle is a commercial company who is not interested in open source historically, it is little chance that you will get robust software for free -- from someone with many years of Oracle DBA experience :-)

On 6/19/08, Henning Thielemann wrote:

...
Is there a way of accessing a remote Oracle database by one of the common Haskell database interfaces (HaskellDB, Takusen, etc.) ? I tried to get unixODBC and Oracle's Instant Client running on a Linux machine, but I'm trapped in the notorious error:

$ isql USER -v [IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [ISQL]ERROR: Could not SQLConnect

This error message is discussed in various web forums, but there seems to be no systematic way to track down the problem. So I wonder whether there is another way to access the Oracle data base from Haskell. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Henning Thielemann

24 Jun 24 Jun

4:54 p.m.

On Sat, 21 Jun 2008, Lanny Ripple wrote:

...

I had luck with this the other day using Database.HDBC.ODBC. For Ubuntu's Hardy I found that Oracle's 10.2.0.3 worked best. (10.2.0.4 and 11 seemed to have problems for me at least.)

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...

Grab the basic (not basic-lite), odbc, and sqlplus (to test) zips. The odbc_update_ini.sh should be run from inside the instantclient_10_2 directory as root

sudo /bin/sh odbc_update_ini.sh /

(assuming a standard unixODBC install.) You'll also need a tnsnames.ora file to describe your connection(s) to the DB(s). You'll also need to define some environment variables to run against all this. I use a small script:

#!/bin/sh oracle_home=/opt/lib/oracle/instantclient_10_2 export TNS_ADMIN=$oracle_home export LD_LIBRARY_PATH=$oracle_home [ $# = 0 ] && exit 1 exec "$@"

Thank you for the detailed hints! However, I tried a lot of this and that, but without a possibility to track down what went wrong, the ways to fix the probable problems are unbounded. Finally I get 'Driver's SQLAllocHandle on SQL_HANDLE_HENV failed' when I start 'isql' and that's where systematic error analysis stops and wild guessing starts. Since OCI and sqlplus work on my machine, I'll try to follow this way.

Daniil Elovkov

5:51 p.m.

Henning Thielemann wrote:

...

On Sat, 21 Jun 2008, Lanny Ripple wrote:

...
I had luck with this the other day using Database.HDBC.ODBC. For Ubuntu's Hardy I found that Oracle's 10.2.0.3 worked best. (10.2.0.4 and 11 seemed to have problems for me at least.)

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...

Grab the basic (not basic-lite), odbc, and sqlplus (to test) zips. The odbc_update_ini.sh should be run from inside the instantclient_10_2 directory as root

sudo /bin/sh odbc_update_ini.sh /

(assuming a standard unixODBC install.) You'll also need a tnsnames.ora file to describe your connection(s) to the DB(s). You'll also need to define some environment variables to run against all this. I use a small script:

#!/bin/sh oracle_home=/opt/lib/oracle/instantclient_10_2 export TNS_ADMIN=$oracle_home export LD_LIBRARY_PATH=$oracle_home [ $# = 0 ] && exit 1 exec "$@"

Thank you for the detailed hints! However, I tried a lot of this and that, but without a possibility to track down what went wrong, the ways to fix the probable problems are unbounded. Finally I get 'Driver's SQLAllocHandle on SQL_HANDLE_HENV failed' when I start 'isql' and that's where systematic error analysis stops and wild guessing starts. Since OCI and sqlplus work on my machine, I'll try to follow this way.

There are also Database.HSQL Database.HSQL.Oracle It works through OCI as well. It's not clear (to me at least) if it is/will be supported. It has back-ends for multiple DBs (is Takusen only for Oracle?) It doesn't take the typeful approach, that Takusen takes (may be a plus in some situations)

6175

Age (days ago)

6182

Last active (days ago)

List overview

Download

22 comments

8 participants

participants (8)

Alistair Bayley
Bayley, Alistair
Daniil Elovkov
Henning Thielemann
John Goerzen
Lanny Ripple
Paul Moore
Steve Lihn