Access to Oracle database from Haskell

[Henning Thielemann]

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@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[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.

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

[Alistair Bayley]

2008/6/20 Alistair Bayley <alistair@...>:

>> 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
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Steve Lihn]

You may want to check this out.

http://www.orafaq.com/wiki/ODBC_FAQ#Where_can_one_get_ODBC_drivers_for_Oracle_and_Rdb.3F

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 <lemming@...> 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@...
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>

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

[Lanny Ripple]

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/linuxsoft.html

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_Oracle_and_Rdb.3F
>
> 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 <lemming@...> 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@...
>> http://www.haskell.org/mailman/listinfo/haskell-cafe
>>
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe@...
> http://www.haskell.org/mailman/listinfo/haskell-cafe

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

[Paul Moore-2]

2008/6/20 Alistair Bayley <alistair@...>:
> 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@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Steve Lihn]

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 <p.f.moore@...> wrote:

> 2008/6/20 Alistair Bayley <alistair@...>:
> > 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@...
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>

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

[Lanny Ripple]

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/linuxsoft.html
>
> 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_Oracle_and_Rdb.3F
>>
>> 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 <lemming@...> 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@...
>>> http://www.haskell.org/mailman/listinfo/haskell-cafe
>>>
>> _______________________________________________
>> Haskell-Cafe mailing list
>> Haskell-Cafe@...
>> http://www.haskell.org/mailman/listinfo/haskell-cafe
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe@...
> http://www.haskell.org/mailman/listinfo/haskell-cafe

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

[Henning Thielemann]

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-instantclient-devel-10.2.0.4-1.i386.rpm
  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?
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Alistair Bayley]

2008/6/24 Henning Thielemann <lemming@...>:
>
> (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
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Henning Thielemann]

On Tue, 24 Jun 2008, Alistair Bayley wrote:

> 2008/6/24 Henning Thielemann <lemming@...>:
>>
>> (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!
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Henning Thielemann]

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/linuxsoft.html
>
> 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.
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Alistair Bayley]

>> 2008/6/24 Henning Thielemann <lemming@...>:
>>>
>> 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
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Daniil Elovkov]

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/linuxsoft.html 
>>
>>
>> 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)


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

[Alistair Bayley]

>> $ 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
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Henning Thielemann]

On Tue, 24 Jun 2008, Alistair Bayley wrote:

>>> 2008/6/24 Henning Thielemann <lemming@...>:
>>>>
>>> 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 cann