leptonyu / odpic-raw

Haskell raw bindings to Oracle ODPI-C Library

odpic-raw

Haskell raw bindings to Oracle ODPI-C Library.

Requirements:

• odpic-raw 5.* contains the ODPI-C source, so you don't need to install it manually.
• Oracle Instant Client Basic or Basic Light package
• GHC 8.* or greater

Installation:

stack build odpic-raw

Use environment DPI_DEBUG_LEVEL to set DPI debug level.

For non-english user, please set environment NLS_LANG to specify the oracle db encodings. use following sql to get the value.

SELECT USERENV ('language') FROM DUAL

Or use setupLanguage to set NLS_LANG automatically.

Test:

export DB_USER=username
export DB_PASS=password
export DB_URL=localhost/dbname
stack test

More Documents:

• Hackage
• ODPI-C

odpic-raw's Issues

README.md install dependency clarification

How about this small install clarification in README.md?

<  * [Oracle Instant Client](http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)
>  * [Oracle Instant Client Basic or Basic Light package](http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)

Definition of Data_Bytes

This looks very suspicious. Shouldn't we be using ByteString here?

data Data_Bytes = Data_Bytes
  { bytes    :: L.Text
  , encoding :: Text
  } deriving Show

Text is by definition for Unicode strings.

I can prepare a PR if that's ok.

support odpic-3.1.x

I tried building odpic-raw with odpic-3.1.0, but the build process is failing currently:

Preprocessing library for odpic-raw-0.3.0..
c2hs: Error limit of 20 errors has been reached.
src/Database/Dpi/Internal.chs:46: (column 8) [ERROR]  >>> Expected enum!
  Expected `SubscrNamespace' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:45: (column 8) [ERROR]  >>> Expected enum!
  Expected `StatementType' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:44: (column 8) [ERROR]  >>> Expected enum!
  Expected `StartupMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:43: (column 8) [ERROR]  >>> Expected enum!
  Expected `ShutdownMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:42: (column 8) [ERROR]  >>> Expected enum!
  Expected `Purity' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:41: (column 8) [ERROR]  >>> Expected enum!
  Expected `PoolGetMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:40: (column 8) [ERROR]  >>> Expected enum!
  Expected `PoolCloseMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:39: (column 8) [ERROR]  >>> Expected enum!
  Expected `OracleTypeNum' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:38: (column 8) [ERROR]  >>> Expected enum!
  Expected `OpCode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:37: (column 8) [ERROR]  >>> Expected enum!
  Expected `NativeTypeNum' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:36: (column 8) [ERROR]  >>> Expected enum!
  Expected `MessageState' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:35: (column 8) [ERROR]  >>> Expected enum!
  Expected `MessageDeliveryMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:34: (column 8) [ERROR]  >>> Expected enum!
  Expected `FetchMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:33: (column 8) [ERROR]  >>> Expected enum!
  Expected `ExecMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:32: (column 8) [ERROR]  >>> Expected enum!
  Expected `EventType' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:31: (column 8) [ERROR]  >>> Expected enum!
  Expected `DeqNavigation' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:30: (column 8) [ERROR]  >>> Expected enum!
  Expected `DeqMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:29: (column 8) [ERROR]  >>> Expected enum!
  Expected `CreateMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:28: (column 8) [ERROR]  >>> Expected enum!
  Expected `ConnCloseMode' to denote an enum; instead found
  a struct, union, or object.
src/Database/Dpi/Internal.chs:27: (column 8) [ERROR]  >>> Expected enum!
  Expected `AuthMode' to denote an enum; instead found
  a struct, union, or object.

Building with odpic-2.4.2 did work, but as the latest version of the python binding (cx_oracle) already doesn't work with 2.4.2 anymore, it would be nice if odpic-raw could support 3.1.x too.

FromDataField ByteString miss BLOB/CLOB

the FromDataField ByteString instance miss BLOB/CLOB type convert.

it report DpiException $ "type mismatch "

Cabal file

Hi again,

first of thank you for the latest changes! However, if I can nitpick a bit more, please keep the cabal file in the project. Among other things it's used by Nix tools to automatically create expressions. Removing it means odpic-raw will be broken on NixOS.

BTW. since odpic is now in nixpkgs, on master you can get a working shell with odpic-raw using:

nix run '(import <nixpkgs> {}).haskellPackages.ghcWithPackages(ps: [ ps.odpic-raw ])'

Support TWO_TASK, TNS_ADMIN and Kerberos env setup

I need to connect to an instance that performs authentication via Kerberos. I have a env setup where environment variables ORACLE_SID, TNS_ADMIN and TWO_TASK (and the Kerberos config) are properly setup so that a simple invocation of sqlplus / (i.e. no user, password nor conn string specified) can connect/authenticate to the Oracle DB. I have not been able to connect with odpic-raw and wonder if this is supported by odpic-raw/odpi-c. Here is the simple test I've tried (basically the Hackage example with some minor changes):

{-# LANGUAGE DuplicateRecordFields #-}
{-# LANGUAGE OverloadedStrings #-}
module Main where

import Data.ByteString (ByteString)
import Database.Dpi

connStr :: ByteString
connStr = ...  -- actual conn string as present in 
               -- $TNS_ADMIN/tnsnames.ora,
               -- identified by $TWO_TASK

conf :: OracleConfig
conf = defaultOracle "" "" ""

conf2 :: OracleConfig
conf2 = defaultOracle "" "" connStr

fp :: Data_PoolCreateParams -> IO Data_PoolCreateParams
fp c = pure (c { externalAuth = 1 } :: Data_PoolCreateParams)

main :: IO ()
main = do
  withContext $ \cxt ->
    withPool cxt conf fp $ \pool ->
      withPoolConnection pool $ \conn ->
        withStatement conn False "SELECT SYSDATE FROM DUAL" $ \st -> do
          r <- executeStatement st ModeExecDefault
          f <- fetch st
          mapM (getQueryValue st) [1..r] >>= print

If I use conf, I get error:

ErrorInfoException (Data_ErrorInfo {code = 12545, offset = 0, message = "ORA-12545: Connect failed because target host or object does not exist", encoding = "ASCII", fnName = "dpiPool_create", action = "create pool", sqlState = "HY000", isRecoverable = False})

i.e., it's unable to resolve the connection string using $TWO_TASK/$TNS_ADMIN/tnsnams.ora. If I explicitly specify the conn string using conf2, I get error:

ErrorInfoException (Data_ErrorInfo {code = 12638, offset = 0, message = "ORA-12638: Credential retrieval failed", encoding = "ASCII", fnName = "dpiPool_create", action = "create pool", sqlState = "HY000", isRecoverable = False})

i.e. it can connect but is unable to retrieve de Kerberos credentials as configured in $TNS_ADMIN/sqlnet.ora (and other env setup).

So this makes me think the env variables are completely ignored.

Issue with number precision

It seems that odpic-raw looses precision when reading NUMBERs. Test query on Chinook db:

select avg(milliseconds) from track;

Results:

• 393599.2121039109334855837853268626891236 from SQLDeveloper
• 393599.2121039109188131988048553466796875 from Haskell as Decimal

Still looking into it but:

• it looks like the FromDataField instance for Decimal is missing a case for reading a full precision NUMBER from DataNumBytes which I would expect to happen here
• there may be some more issues with the Storable Data instance because FromDataField Decimal instance is always getting DataDouble while here I would expect DataNumBytes

Btw. any particular reasons to use Decimal over Scientific? They seem to be very similar but checking reverse dependencies Scientific is vastly more popular.

Missing Char type

Hello!

I see Char is missing from DataValue. The other types seem to be there, more or less.

I would somewhat expect odpic-raw to fairly closely follow upstream so I'm confused. Should Char be represented there?