Retrieving values from SQLite3 Database for Scotty

Question

I'm trying to get information from a SQLite DB (HDBC.sqlite3) to feed to a web view using the Scotty framework. I'm currently trying to complete a "grab all" or rather select all the information from the table and then return it to display on my web page that is running via Scotty. I've encountered an error and I'm having some trouble figuring out how to fix it.

Here is my error:

Controllers/Home.hs:42:44:
Couldn't match expected type `Data.Text.Lazy.Internal.Text'
            with actual type `IO [[(String, SqlValue)]]'
In the expression: getUsersDB
In the first argument of `mconcat', namely
  `["<p>/users/all</p><p>", getUsersDB, "</p>"]'
In the second argument of `($)', namely
  `mconcat ["<p>/users/all</p><p>", getUsersDB, "</p>"]'

Here is my code:

import Control.Monad
import Web.Scotty (ScottyM, ActionM, get, html, param, text)
import Data.Monoid (mconcat)
import Controllers.CreateDb (createUserDB)
import Database.HDBC
import Database.HDBC.Sqlite3
import Control.Monad.Trans ( MonadIO(liftIO) )
import Data.Convertible

getAllUsers :: ScottyM()
getAllUsers = get "/users/all" $ do
  html $ mconcat ["<p>/users/all</p><p>", getUsersDB , "</p>"]

getUsersDB = do
  conn <- connectSqlite3 databaseFilePath
  stmt <- prepare conn "SELECT name FROM users VALUES"
  results <- fetchAllRowsAL stmt
  disconnect conn
  return (results)

score 1 · Accepted Answer · answered Oct 12 '14 at 19:05

1

run returns the number of rows modified : https://hackage.haskell.org/package/HDBC-2.4.0.0/docs/Database-HDBC.html#v:run

answered Oct 12 '14 at 19:05

Jean-Baptiste Potonnier

568
3
8

thanks, I probably wouldn't have realized that for a bit of time. So I guess I need to figure out the correct options for retrieving data. – Sleep Deprived Bulbasaur Oct 12 '14 at 19:13
@SleepDeprivedBulbasaur I never used HDBC (hence my short answer!). I basically read the compiler error message. Looking at the doc, you may want to look at `prepare` and `fetchAllRows`. Or maybe `quickQuery` could to it. – Jean-Baptiste Potonnier Oct 12 '14 at 19:18
It looks like you want `mconcat` to magically build your response, it will not. You have to find a way to transform your data into `Text`. For this kind of job, I found http://jaspervdj.be/blaze/ to be useful. But you may prefer a template tool, or just write a function yourself. – Jean-Baptiste Potonnier Oct 12 '14 at 21:11
I think you should write a type declaration for your `getUsersDB`, it is a good practice. Ensure that you understand monad, at least how to do IO, remember how to use `a <- doStuff` notation to be able to use the result of a IO action. Oh, and don't forget `liftIO`, it was good in the first place. – Jean-Baptiste Potonnier Oct 12 '14 at 21:22

Retrieving values from SQLite3 Database for Scotty

1 Answers1