Safe Haskell | None |
---|---|
Language | Haskell2010 |
SQL Server database API.
- connect :: MonadIO m => Text -> m Connection
- close :: MonadIO m => Connection -> m ()
- data Connection
- exec :: MonadIO m => Connection -> Query -> m ()
- query :: (MonadIO m, FromRow row) => Connection -> Query -> m [row]
- data Value
- data Query
- class ToSql a where
- class FromValue a where
- class FromRow r where
- newtype Binary = Binary {}
- stream :: (MonadUnliftIO m, FromRow row) => Connection -> Query -> (state -> row -> m (Step state)) -> state -> m state
- data Step a
- data ODBCException
- renderQuery :: Query -> Text
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" :: IO [[Maybe Value]] print rows rows2 <- query conn "SELECT * FROM example" :: IO [(Int,Text,Bool)] print rows2 close conn
The rows
list contains rows of some value that could be
anything. The rows2
list contains tuples of exactly Int
,
Text
and Bool
. This is achieved via the FromRow
class.
You need the OverloadedStrings
extension so that you can write
Text
values for the queries and executions.
The output of this program for rows
:
[[Just (IntValue 1),Just (TextValue "Chris"),Just (BoolValue False)],[Just (IntValue 2),Just (TextValue "Mary"),Just (BoolValue True)]]
The output for rows2
:
[(1,"Chris",False),(2,"Mary",True)]
Connect/disconnect
:: MonadIO m | |
=> Text | An ODBC connection string. |
-> m Connection | A connection to the database. You should call |
Connect using the given connection string.
:: 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
:: MonadIO m | |
=> Connection | A connection to the database. |
-> Query | SQL statement. |
-> m () |
Execute a statement on the database.
:: (MonadIO m, FromRow row) | |
=> Connection | A connection to the database. |
-> Query | SQL query. |
-> m [row] |
Query and return a list of rows.
The row
type is inferred based on use or type-signature. Examples
might be (Int, Text, Bool)
for concrete types, or [Maybe Value]
if you don't know ahead of time how many columns you have and their
type. See the top section for example use.
A value used for input/output with the database.
TextValue !Text | A Unicode text value. |
ByteStringValue !ByteString | A vector of bytes. It might be binary, or a string, but we
don't know the encoding. Use |
BinaryValue !Binary | Only a vector of bytes. Intended for binary data, not for ASCII text. |
BoolValue !Bool | A simple boolean. |
DoubleValue !Double | Floating point values that fit in a |
FloatValue !Float | Floating point values that fit in a |
IntValue !Int | Integer values that fit in an |
ByteValue !Word8 | Values that fit in one byte. |
DayValue !Day | Date (year, month, day) values. |
TimeOfDayValue !TimeOfDay | Time of day (hh, mm, ss + fractional) values. |
LocalTimeValue !LocalTime | Local date and time. |
A query builder. Use toSql
to convert Haskell values to this
type safely.
It's an instance of IsString
, so you can use OverloadedStrings
to produce plain text values e.g. "SELECT 123"
.
It's an instance of Monoid
, so you can append fragments together
with <>
e.g. "SELECT * FROM x WHERE id = " <> toSql 123
.
This is meant as a bare-minimum of safety and convenience.
Handy class for converting values to a query safely.
For example: query c ("SELECT * FROM demo WHERE id > " <> toSql 123)
WARNING: Note that if you insert a value like an Int
(64-bit)
into a column that is int
(32-bit), then be sure that your number
fits inside an int
. Try using an Int32
instead to be
sure.
ToSql Bool Source # | Corresponds to BIT type of SQL Server. |
ToSql Double Source # | Corresponds to FLOAT type of SQL Server. |
ToSql Float Source # | Corresponds to REAL type of SQL Server. |
ToSql Int Source # | Corresponds to BIGINT type of SQL Server. |
ToSql Int16 Source # | Corresponds to SMALLINT type of SQL Server. |
ToSql Int32 Source # | Corresponds to INT type of SQL Server. |
ToSql Word8 Source # | Corresponds to TINYINT type of SQL Server. |
ToSql ByteString Source # | Corresponds to TEXT (non-Unicode) of SQL Server. For Unicode, use
the |
ToSql ByteString Source # | Corresponds to TEXT (non-Unicode) of SQL Server. For proper
BINARY, see the |
ToSql Text Source # | Corresponds to NTEXT (Unicode) of SQL Server. Note that if your character exceeds the range supported by a wide-char (16-bit), that cannot be sent to the server. |
ToSql Text Source # | Corresponds to NTEXT (Unicode) of SQL Server. Note that if your character exceeds the range supported by a wide-char (16-bit), that cannot be sent to the server. |
ToSql LocalTime Source # | Corresponds to DATETIME/DATETIME2 type of SQL Server. The |
ToSql TimeOfDay Source # | Corresponds to TIME type of SQL Server.
|
ToSql Day Source # | Corresponds to DATE type of SQL Server. |
ToSql Binary Source # | |
ToSql Value Source # | Converts whatever the |
class FromValue a where Source #
Convert from a Value
to a regular Haskell value.
class FromRow r where Source #
For producing rows from a list of column values.
You can get a row of a single type like Text
or a list
e.g. [Maybe Value]
if you don't know what you're dealing with, or
a tuple e.g. (Text, Int, Bool)
.
A simple newtype wrapper around the ByteString
type to use when
you want to mean the binary
type of SQL, and render to binary
literals e.g. 0xFFEF01
.
The ByteString
type is already mapped to the non-Unicode text
type.
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 Text
, we ignore
for example the NULL
row.
{-# LANGUAGE OverloadedStrings, LambdaCase #-} import qualified Data.Text as T import Control.Exception import Database.ODBC.SQLServer main :: IO () main = bracket (connect "DRIVER={ODBC Driver 13 for SQL Server};SERVER=192.168.99.101;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 mtext -> pure (Continue (maybe longest (\text -> if T.length text > T.length longest then text else longest) mtext))) "" print longest)
:: (MonadUnliftIO m, FromRow row) | |
=> Connection | A connection to the database. |
-> Query | SQL query. |
-> (state -> row -> 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.SQLServer 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.
UnsuccessfulReturnCode !String !Int16 !String | 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. |
DataRetrievalError !String | There was a general error retrieving data. String will contain the reason why. |
Debugging
renderQuery :: Query -> Text Source #
Render a query to a plain text string. Useful for debugging and testing.