Read MS Sql Express from Haskell


First, we need to start our local MS Sql Express DB!

C:\dev\DB>SqlLocalDB start TestDB
Istanza del database locale "TestDB" avviata.

C:\dev\DB>SqlLocalDB info TestDB
Nome: "TestDB"
Versione: 11.0.3000.0
Nome condiviso:
Proprietario:              MyHome-PC\MyHome
Creazione automatica:        No
Stato:              in esecuzione
Ultima ora di inizio: 17/02/2019 22:25:28
Nome pipe istanza: np:\\.\pipe\LOCALDB#9A724B80\tsql\query

Then we have to set it in the DSN using the above pipe “\\.\pipe\LOCALDB#9A724B80\tsql\query” the server “(localdb)\TestDB
Now we have to install the haskell package for the ODBC: “stack install HDBC-odbc“.
Let’s write the code

import Database.HDBC
import Database.HDBC.ODBC

data Person = Person
    { id   :: Int
    , name :: [Char] }
    deriving Show

convRow :: [SqlValue] -> Person
convRow [sqlId, sqlDesc] = 
    Person intId desc
        where intId = (fromSql sqlId) :: Int
              desc = case fromSql sqlDesc of
                         Just x -> x 
                         Nothing -> "NULL" 

main =
    let connectionString =  "DSN=TestDB" in
    let ioconn = connectODBC connectionString in
            conn <- ioconn
            vals <- quickQuery conn "SELECT TOP 10 * FROM Person;" []
            let rows = map convRow vals
            mapM_ print rows

We compile the source with "stack exec -- ghc haskellDB.hs -o haskellDB
Here is my output:

Person {id = 1, name = "Alice"}
Person {id = 2, name = "Bob"}

Shared as a gist on github

That’s it for tonight!

Please note.

Related to possible issues due to the use of a temporary pipe and also about a problem with schemas different from dbo in SqlDbx, this is how I’ve sorted it out by myself: configuring the ODBC with the native sql client

Minor note, among other things on (not haskell), I was wondering why IIS Express, started from cmd, was crashing on SqlClient opening a local Sql Express db while VisualStudio was ok, well I found I had to run the 32 bit version of iis.
I had to switch to odbc to get an appropriate error message.

Real world tech!