1

Since I am an absolute Haskell beginner, but determined to conquer it, I am asking for help again.

using:

fetchData2 = do
  conn <- connectSqlite3 "dBase.db"
  statement <- prepare conn "SELECT * FROM test WHERE id > 0"
  execute statement []
  results <- fetchAllRows statement
  print results

returns:

[[SqlInt64 3,SqlByteString "Newco"],[SqlInt64 4,SqlByteString "Oldco"],[SqlInt64 5,SqlByteString "Mycom"],[SqlInt64 4,SqlByteString "Oldco"],[SqlInt64 5,SqlByteString "Mycom"]]

Is there a clever way to clean this data into Int and [Char], in other words omitting types SqlInt64 and SqlByteString.

Madderote
  • 1,107
  • 10
  • 19
  • 2
    The [HDBC documentation](https://hackage.haskell.org/package/HDBC-2.4.0.1/docs/Database-HDBC-Types.html#v:fromSql) suggests that you're looking for `fromSql`. – Shawn Aug 29 '18 at 09:12
  • @Shawn: tried that. Can't find any example that works. To make matters worse: fetchRow (the one I actually need) wraps the list in a Maybe type, and returns eg. `Just [SqlInt64 3,SqlByteString "Newco"]` – Madderote Aug 29 '18 at 14:02
  • To add to that: drilling down to the value (let alone when wrapped in a Maybe) must be achievable simpler than this: `fetchData2 = do conn <- connectSqlite3 "dBase.db" statement <- prepare conn "SELECT * FROM test WHERE id > 0" execute statement [] results <- fetchAllRows statement let first = head (results) let col1 = head (first) let showInt = fromSql col1::Int print (showInt)` – Madderote Aug 29 '18 at 16:00

1 Answers1

2

You could define a helper:

fetchRowFromSql :: Convertible SqlValue a => Statement -> IO (Maybe [a])
fetchRowFromSql = fmap (fmap (fmap fromSql)) . fetchRow

The implementation looks a bit daunting, but this is just because we need to drill down under the layered functors as you already noted (first IO, then Maybe and lastly []). This returns something that is convertible from a SqlValue. There are a bunch of these defined already. See e.g. the docs. An example (using -XTypeApplications):

fetchRowFromSql @String :: Statement -> IO (Maybe [String])

I should perhaps add that the documentation mentions that fromSql is unsafe. Meaning that if you try to convert a sql value to an incompatible Haskell value the program will halt.

fredefox
  • 681
  • 3
  • 11
  • Hi Fredefox, thanks for the input. Going to give that a try. Figured that a Haskell beginner only learns by doing after having read the odd 4 books or so... – Madderote Aug 30 '18 at 05:25
  • I can get it from the Maybe by using `fromJust`. That works like a charm. `fromSql` however expects a type constraint per parsed value like `fromSql VALUE::Int`. Is there a quick way parsing several columns having different types, while Haskell prefers list of a single type. – Madderote Aug 30 '18 at 06:20
  • 1
    Ah yes, this is unfortunate. Perhaps this is why the authors of HDBC does not expose such a helper. Another library `sqlite-simple` solves this by defining the equivalent of your `Convertible` for tuples up to a certain length. Here's a quick-and-dirty solution for you: `three = \(a:b:c:[]) -> (fromSql a, fromSql b, fromSql c)` This unsafely converts a list to a triple with converted values. I'm sure you can come up with something more elegant for your use case. – fredefox Aug 31 '18 at 08:06