[Haskell-cafe] Access to Oracle database from Haskell

[Henning Thielemann]

On Thu, 26 Jun 2008, Alistair Bayley wrote:

>> 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)
>
> Did you get a chance to try this? I'm quite keen to fix the
> non-Windows parts of the installation process, if I can.

I have replaced configOracle by

configOracle verbose buildtools = do
  if not (sqlplusProgram `isElem` buildtools)
    then return Nothing
    else do
      path <- getEnv "ORACLE_HOME"
      info verbose ("Using Oracle: " ++ path)
      let (libDir, includeDir) =
              if isWindows
                then ("bin", "oci/include")
                else ("lib", "/usr/include/oracle/10.2.0.4/client")
      makeConfig path libDir includeDir

This is obviously a hack. We should get the Oracle include path from the 
user, maybe via another environment variable or a custom Cabal option.

Now I can start GHCi with the example program you gave me. However I have 
to start with -lclntsh, otherwise symbol OCIEnvCreate cannot by found.
I thought I do not need this option, because the installed Takusen package 
contains the library name:

$ grep clntsh dist/installed-pkg-config
extra-libraries: clntsh clntsh

I wonder why it appears twice, because in dist/setup-config it exists
only once:
   extraLibs = ["clntsh"]


Nevertheless I can start GHCi with the example program and I can see 
'hello world'! Great - many thanks for the hints that led me to that 
state!


Next step is to fetch real data from the database. But now I encounter new 
problems. If I write a real 'select' statement I get at best:

Unexpected null in row 1, column 1.
*** Exception: (unknown)

and at worst:

50001752
*** glibc detected *** /usr/lib/ghc-6.8.2/ghc-6.8.2: free(): invalid 
pointer: 0x08d10065 ***
======= Backtrace: =========
/lib/libc.so.6[0xb7dba4b6]
/lib/libc.so.6(cfree+0x89)[0xb7dbc179]
/usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so(lfvclose+0x1c)[0xb5df9c1e]
/usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so(SlfMunmap+0x3f)[0xb5e70c3b]
/usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so(ldiutzd+0x37)[0xb5e49e7b]
/usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so(kputerm+0x51)[0xb54c96b9]
/usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so(OCITerminate+0x1c)[0xb5596888]
[0xb6a33801]
/usr/lib/ghc-6.8.2/ghc-6.8.2[0x8a600de]
======= Memory map: ========
...

then GHCi quits.


> BTW, the location of your header files is still a puzzle. Oracle's docs:
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/ociabdem.htm#i459676
>
> state that the header files shoulld be in $ORACLE_HOME/rdbms/public.
> But perhaps things are different for the Instant Client.

I don't know. I have just installed the RPMs and I expect that others will 
do so as well.