[Haskell-cafe] 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
Haskel...@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Alistair Bayley
> 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
>> 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.
leonelfl
Three points you may want to check.
1) Oracle post-installation configuration (adapt to your unix
environment where needed):
o Add the directory to the PATH system environment variable.
+ Otherwise ODBC manager will be not able to load/find the driver.
o Define the TNS_ADMIN to point the .ora files directory for OCI.
+ This needs to be set for OCI to resolve the TNS name.
+ If TNS_ADMIN is not set, OCI will examine an operating system
dependent set of directories to find tnsnames.ora. This search path
includes looking in the directory %ORACLE_HOME%/network/admin/
tnsnames.ora. This is the only reason to set the ORACLE_HOME
environment variable for Oracle Instant Client. If ORACLE_HOME is set
when running Instant Client applications, it must be set to a
directory where Instant Client exists (in this case C:\Oracle
\instantclient).
o Set any Oracle globalization variables required for your locale. See
the Oracle Database 10g Globalization Support Guide for more
information.
+ For example on Windows to set NLS_LANG:
# set NLS_LANG=JAPANESE_JAPAN.JA16EUC
2)
If you perform a "heavy" client installation in your machine you can
use Takusen without problem.
This kind of installation will copy the required OCI files Alistair
talks about.
3)
If you wonder the about the compability of Instant Client (I didn't
know), this info is on Metalink (Note:565000.1):
Oracle Instant Client supports following API's, Drivers and Providers
API's
* OCI Oracle Call Interface
o Generic OS
o Oracle Instant Client 10.1.0.2 and higher
* OCCI Oracle C++ Class Interface,
o Generic OS
o Oracle Instant Client 10.1.0.2 and higher
* OO4O Oracle Objects for OLE
o Windows 32bit
o Oracle Instant Client 11.1.0.6 and higher,
+ Contained in Oracle Data Access Components 11.1.0.6 and
higher
Drivers
* ODBC Open Database Connectivity
o Windows 32bit
o Oracle Instant Client 10.1.0.2 and higher
* JDBC Java Database Connectivity
o Generic OS
o Oracle Instant Client 10.1.0.2 and higher
Providers
* OLEDB Oracle Provider for OLEDB
o Windows 32bit
o Oracle Instant Client 11.1.0.6 and higher
+ Contained in Oracle Data Access Components 11.1.0.6 and
higher
* ODP.NET Oracle Data Provider for .NET
o Windows 32bit
o .NET 1.x / 2.x
o Oracle Instant Client 11.1.0.6 and higher
+ Contained in Oracle Data Access Components 11.1.0.6 and
higher
Services
* ORAMTS (Oracle Services for Microsoft Transaction Server)
o Windows 32bit
o Oracle Instant Client 11.1.0.6 and higher
+ Contained in Oracle Data Access Components 11.1.0.6 and
higher
Steve Lihn
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 :-)
Lanny Ripple
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
Paul Moore
> 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
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).
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.
Oops. And the sdk zip.
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?
Alistair Bayley
>
> (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
On Tue, 24 Jun 2008, Alistair Bayley wrote:
> 2008/6/24 Henning Thielemann <lem...@henning-thielemann.de>:
>>
>> (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!
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.
Alistair Bayley
>>>
>> 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
Daniil Elovkov
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)
Alistair Bayley
>> ...
>> 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"
Henning Thielemann
On Tue, 24 Jun 2008, Alistair Bayley wrote:
>>> 2008/6/24 Henning Thielemann <lem...@henning-thielemann.de>:
>>>>
>>> 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
> [mailto:haskell-ca...@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
> 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
> On Tue, 24 Jun 2008, Alistair Bayley wrote:
>
>> 2008/6/24 Henning Thielemann <lem...@henning-thielemann.de>:
>>> (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
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
> 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
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.
Alistair Bayley
> 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
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!
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);
John Goerzen
> 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
Alistair Bayley
>
>> 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
Henning Thielemann
On Fri, 27 Jun 2008, Alistair Bayley wrote:
> 2008/6/26 Henning Thielemann <lem...@henning-thielemann.de>:
>>
>>> 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'.
Alistair Bayley
>> 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
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.
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!