Access to Oracle database from Haskell

[John Goerzen-3]

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

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

[Alistair Bayley]

> 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"]

Yes, puzzling. I don't know why you need to say -lclntsh, because
that's the point of all of this Setup shenanigans: to get things set
up so that ghci works nicely.

That's why we have flags that expose/hide modules in the API: ghci has
a custom linker, and this tries to link everything in the library, and
of course this fails if you don't have the .ddl/.so installed for a
particular backend. ghc uses gnu ld to link, and this does not try to
link unused modules, so this works fine with the entire library API
compiled.

Do you need to say -lclntsh when you use ghc to compile?


> 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)

This looks like you are fetching a null value back with an iteratee
that does not expect nulls. We use Maybe to specify that a column in
the result-set can be null e.g.

  iter :: Int -> Maybe Int -> Maybe String -> IterAct [(Int, Maybe
Int, Maybe String)]

The first column must never contain null. If it does then you get the
"Unexpected null" exception.


> and at worst:
>
> 50001752
> *** glibc detected *** /usr/lib/ghc-6.8.2/ghc-6.8.2: free(): invalid
> pointer: 0x08d10065 ***
> ======= Backtrace: =========

If this continues, can you provide a test case for me?

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

[Henning Thielemann]

On Thu, 26 Jun 2008, Alistair Bayley wrote:

> Yes, puzzling. I don't know why you need to say -lclntsh, because
> that's the point of all of this Setup shenanigans: to get things set
> up so that ghci works nicely.
>
> That's why we have flags that expose/hide modules in the API: ghci has
> a custom linker, and this tries to link everything in the library, and
> of course this fails if you don't have the .ddl/.so installed for a
> particular backend. ghc uses gnu ld to link, and this does not try to
> link unused modules, so this works fine with the entire library API
> compiled.
>
> Do you need to say -lclntsh when you use ghc to compile?

Ah, I see, I must run both GHCi and GHC with -package Takusen and
everything is fine.

>> 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)
>
> This looks like you are fetching a null value back with an iteratee
> that does not expect nulls. We use Maybe to specify that a column in
> the result-set can be null e.g.
>
>  iter :: Int -> Maybe Int -> Maybe String -> IterAct [(Int, Maybe Int, Maybe String)]

Indeed, using Maybe as type solves that problem.

>> and at worst:
>>
>> 50001752
>> *** glibc detected *** /usr/lib/ghc-6.8.2/ghc-6.8.2: free(): invalid
>> pointer: 0x08d10065 ***
>> ======= Backtrace: =========
>
> If this continues, can you provide a test case for me?

I cannot reliably reproduce this. Sometimes it seems that the first run of
'main' succeeds, and the second one crashes in GHCi.


So, many thanks for the help!
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Henning Thielemann]

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

[John Goerzen-3]

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

[Alistair Bayley]

2008/6/26 Henning Thielemann <lemming@...>:
>
>> Do you need to say -lclntsh when you use ghc to compile?
>
> Ah, I see, I must run both GHCi and GHC with -package Takusen and everything
> is fine.

This still doesn't seem right. Both ghci and ghc --make should
automatically link the package. The only time you should need to use
-package is in ghc "batch" mode i.e. ghc sans --make.

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

Yes... I've had a quick look at the instant client packages. The SDK
zip just puts the headers under instantclient_10_2/sdk/include (which
is still not $ORACLE_HOME/rdbms/public, but c'est la vie). So it looks
like the .rpm puts them somewhere completely different:
/usr/include/oracle/10.2.0.4/client. Do you also have
$ORACLE_HOME/sdk/include, with headers in?

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

[Henning Thielemann]

On Fri, 27 Jun 2008, Alistair Bayley wrote:

> 2008/6/26 Henning Thielemann <lemming@...>:
>>
>>> Do you need to say -lclntsh when you use ghc to compile?
>>
>> Ah, I see, I must run both GHCi and GHC with -package Takusen and everything
>> is fine.
>
> This still doesn't seem right. Both ghci and ghc --make should
> automatically link the package. The only time you should need to use
> -package is in ghc "batch" mode i.e. ghc sans --make.

I actually called GHC without --make because the example program consists
of only one (the main) module.

> Yes... I've had a quick look at the instant client packages. The SDK
> zip just puts the headers under instantclient_10_2/sdk/include (which
> is still not $ORACLE_HOME/rdbms/public, but c'est la vie). So it looks
> like the .rpm puts them somewhere completely different:
> /usr/include/oracle/10.2.0.4/client. Do you also have
> $ORACLE_HOME/sdk/include, with headers in?

No, in ORACLE_HOME there is only 'bin' and 'lib'.

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

[Alistair Bayley]

>> Yes... I've had a quick look at the instant client packages. The SDK
>> zip just puts the headers under instantclient_10_2/sdk/include (which
>> is still not $ORACLE_HOME/rdbms/public, but c'est la vie). So it looks
>> like the .rpm puts them somewhere completely different:
>> /usr/include/oracle/10.2.0.4/client. Do you also have
>> $ORACLE_HOME/sdk/include, with headers in?
>
> No, in ORACLE_HOME there is only 'bin' and 'lib'.

I haven't found a way to detect where headers are installed, so I
propose that the Setup.hs assumes $ORACLE_HOME/rdbms/public (for
Unix), and you can add more with --extra-include-dirs=... . What do
you think?

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

[Henning Thielemann]

On Mon, 30 Jun 2008, Alistair Bayley wrote:

>> No, in ORACLE_HOME there is only 'bin' and 'lib'.
>
> I haven't found a way to detect where headers are installed, so I
> propose that the Setup.hs assumes $ORACLE_HOME/rdbms/public (for
> Unix), and you can add more with --extra-include-dirs=... . What do
> you think?

It's ok, but it should be documented in the Oracle section of README.txt.
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Henning Thielemann]

On Mon, 30 Jun 2008, Alistair Bayley wrote:

> I haven't found a way to detect where headers are installed, so I
> propose that the Setup.hs assumes $ORACLE_HOME/rdbms/public (for
> Unix), and you can add more with --extra-include-dirs=... . What do
> you think?

Many thanks for including the necessary changes for Oracle on Linux!
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe