| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.ODBC
Description
ODBC database API.
- connect :: MonadIO m => Text -> m Connection
- close :: MonadIO m => Connection -> m ()
- data Connection
- exec :: MonadIO m => Connection -> Text -> m ()
- query :: MonadIO m => Connection -> Text -> m [[Maybe Value]]
- data Value
- = TextValue !Text
- | BytesValue !ByteString
- | BoolValue !Bool
- | DoubleValue !Double
- | IntValue !Int
- stream :: (MonadIO m, MonadUnliftIO m) => Connection -> Text -> (state -> [Maybe Value] -> m (Step state)) -> state -> m state
- data Step a
- data ODBCException
Building
You have to compile your projects using the -threaded flag to
GHC. In your .cabal file, this would look like:
ghc-options: -threaded
Basic library usage
An example program using this library:
{-# LANGUAGE OverloadedStrings #-}
import Database.ODBC
main :: IO ()
main = do
conn <-
connect
"DRIVER={ODBC Driver 13 for SQL Server};SERVER=192.168.99.100;Uid=SA;Pwd=Passw0rd"
exec conn "DROP TABLE IF EXISTS example"
exec conn "CREATE TABLE example (id int, name ntext, likes_tacos bit)"
exec conn "INSERT INTO example VALUES (1, 'Chris', 0), (2, 'Mary', 1)"
rows <- query conn "SELECT * FROM example"
print rows
close conn
You need the OverloadedStrings extension so that you can write
Text values for the queries and executions.
The output of this program is to print a list of rows of Value:
[[Just (IntValue 1),Just (TextValue "Chris"),Just (BoolValue False)],[Just (IntValue 2),Just (TextValue "Mary"),Just (BoolValue True)]]
Connect/disconnect
Arguments
| :: MonadIO m | |
| => Text | An ODBC connection string. |
| -> m Connection | A connection to the database. You should call |
Connect using the given connection string.
Arguments
| :: MonadIO m | |
| => Connection | A connection to the database. |
| -> m () |
Close the connection. Further use of the Connection will throw
an exception. Double closes also throw an exception to avoid
architectural mistakes.
data Connection Source #
Connection to a database. Use of this connection is thread-safe. When garbage collected, the connection will be closed if not done already.
Executing queries
Arguments
| :: MonadIO m | |
| => Connection | A connection to the database. |
| -> Text | SQL statement. |
| -> m () |
Execute a statement on the database.
Arguments
| :: MonadIO m | |
| => Connection | A connection to the database. |
| -> Text | SQL query. |
| -> m [[Maybe Value]] | A strict list of rows. This list is not lazy, so if you are retrieving a large data set, be aware that all of it will be loaded into memory. |
Query and return a list of rows.
A value used for input/output with the database.
Constructors
| TextValue !Text | A Unicode text value. |
| BytesValue !ByteString | A vector of bytes. It might be a string, but we don't know
the encoding. Use |
| BoolValue !Bool | A simple boolean. |
| DoubleValue !Double | Floating point values that fit in a |
| IntValue !Int | Integer values that fit in an |
Streaming results
Loading all rows of a query result can be expensive and use a lot of memory. Another way to load data is by fetching one row at a time, called streaming.
Here's an example of finding the longest string from a set of
rows. It outputs "Hello!". We only work on TextValue, we ignore
for example the NULL row.
{-# LANGUAGE OverloadedStrings, LambdaCase #-}
import qualified Data.Text as T
import Control.Exception
import Database.ODBC
main :: IO ()
main =
bracket
(connect
"DRIVER={ODBC Driver 13 for SQL Server};SERVER=192.168.99.100;Uid=SA;Pwd=Passw0rd")
close
(\conn -> do
exec conn "DROP TABLE IF EXISTS example"
exec conn "CREATE TABLE example (name ntext)"
exec conn "INSERT INTO example VALUES ('foo'),('bar'),(NULL),('mu'),('Hello!')"
longest <-
stream
conn
"SELECT * FROM example"
(\longest ->
\case
[Just (TextValue text)] ->
pure
(Continue
(if T.length text > T.length longest
then text
else longest))
_ -> pure (Continue longest))
""
print longest)
Arguments
| :: (MonadIO m, MonadUnliftIO m) | |
| => Connection | A connection to the database. |
| -> Text | SQL query. |
| -> (state -> [Maybe Value] -> m (Step state)) | A stepping function that gets as input the current |
| -> state | A state that you can use for the computation. Strictly evaluated each iteration. |
| -> m state | Final result, produced by the stepper function. |
Stream results like a fold with the option to stop at any time.
A step in the streaming process for the stream function.
Exceptions
Proper connection handling should guarantee that a close happens at the right time. Here is a better way to write it:
{-# LANGUAGE OverloadedStrings #-}
import Control.Exception
import Database.ODBC
main :: IO ()
main =
bracket
(connect
"DRIVER={ODBC Driver 13 for SQL Server};SERVER=192.168.99.100;Uid=SA;Pwd=Passw0rd")
close
(\conn -> do
rows <- query conn "SELECT N'Hello, World!'"
print rows)
If an exception occurs inside the lambda, bracket ensures that
close is called.
data ODBCException Source #
A database exception. Any of the functions in this library may throw this exception type.
Constructors
| UnsuccessfulReturnCode !String !Int16 | An ODBC operation failed with the given return code. |
| AllocationReturnedNull !String | Allocating an ODBC resource failed. |
| UnknownDataType !String !Int16 | An unsupported/unknown data type was returned from the ODBC driver. |
| DatabaseIsClosed !String | You tried to use the database connection after it was closed. |
| DatabaseAlreadyClosed | You attempted to |
| NoTotalInformation !Int | No total length information for column. |
Instances