| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.ODBC.SQLServer
Contents
Description
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 {}
- newtype Datetime2 = Datetime2 {}
- newtype Smalldatetime = Smalldatetime {}
- 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 [[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:
[[IntValue 1, TextValue "Chris", BoolValue False],[ IntValue 2, TextValue "Mary", BoolValue True]]
The output for rows2:
[(1,"Chris",False),(2,"Mary",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. |
| -> Query | SQL statement. |
| -> m () |
Execute a statement on the database.
Arguments
| :: (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.
Constructors
| 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. |
| NullValue | SQL null value. |
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.
Minimal complete definition
Instances
| 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. |
| TypeError Constraint ((:$$:) (Text "Instance for LocalTime is disabled:") (Text "Wrap your value in either (Datetime2 foo) or (Smalldatetime foo).")) => ToSql LocalTime Source # | You cannot use this instance. Wrap your value in either
|
| ToSql TimeOfDay Source # | Corresponds to TIME type of SQL Server.
|
| TypeError Constraint ((:$$:) ((:$$:) ((:$$:) (Text "Instance for UTCTime is not possible:") (Text "SQL Server does not support time zones. ")) (Text "You can use utcToLocalTime to make a LocalTime, and")) (Text "wrap your value in either (Datetime2 foo) or (Smalldatetime foo).")) => ToSql UTCTime Source # | You cannot use this instance. Wrap your value in either
|
| ToSql Day Source # | Corresponds to DATE type of SQL Server. |
| ToSql Binary Source # | |
| ToSql Value Source # | Converts whatever the |
| ToSql Smalldatetime Source # | Corresponds to SMALLDATETIME type of SQL Server. Precision up to minutes. Consider the seconds field always 0. |
| ToSql Datetime2 Source # | Corresponds to DATETIME/DATETIME2 type of SQL Server. The |
| ToSql a => ToSql (Maybe a) Source # | |
class FromValue a where Source #
Convert from a Value to a regular Haskell value.
Minimal complete definition
Methods
fromValue :: Value -> Either String a Source #
The String is used for a helpful error message.
Instances
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).
Minimal complete definition
Instances
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.
Constructors
| Binary | |
Fields | |
The LocalTime type has more accuracy than the datetime type and
the datetime2 types can hold; so you will lose precision when you
insert. Use this type to indicate that you are aware of the
precision loss and fine with it.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017
If you are using smalldatetime in SQL Server, use instead the
Smalldatetime type.
Constructors
| Datetime2 | |
Fields | |
Instances
| Eq Datetime2 Source # | |
| Data Datetime2 Source # | |
| Ord Datetime2 Source # | |
| Show Datetime2 Source # | |
| Generic Datetime2 Source # | |
| FromValue Datetime2 Source # | |
| ToSql Datetime2 Source # | Corresponds to DATETIME/DATETIME2 type of SQL Server. The |
| type Rep Datetime2 Source # | |
newtype Smalldatetime Source #
Use this type to discard higher precision than seconds in your
LocalTime values for a schema using smalldatetime.
Constructors
| Smalldatetime | |
Fields | |
Instances
| Eq Smalldatetime Source # | |
| Data Smalldatetime Source # | |
| Ord Smalldatetime Source # | |
| Show Smalldatetime Source # | |
| Generic Smalldatetime Source # | |
| FromValue Smalldatetime Source # | |
| ToSql Smalldatetime Source # | Corresponds to SMALLDATETIME type of SQL Server. Precision up to minutes. Consider the seconds field always 0. |
| type Rep Smalldatetime Source # | |
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 text ->
pure
(Continue
(if T.length text > T.length longest
then text
else longest)))
""
print longest)
Arguments
| :: (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.
Constructors
| 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. |
Instances
Debugging
renderQuery :: Query -> Text Source #
Render a query to a plain text string. Useful for debugging and testing.