database.sql

CREATE TABLE `movies`.`movies_movie` (
  `id` INT  NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255)  NOT NULL,
  `year` SMALLINT  NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

db.hs

module DatabaseTest where
 
import Database.HDBC
import Database.HDBC.MySQL
-- cabal install MissingH
import qualified Data.List.Utils as T
import Control.Monad
 
startDb = connectMySQL defaultMySQLConnectInfo {
  mysqlHost = "localhost",
  mysqlDatabase = "movies",
  mysqlUser = "root",
  mysqlPassword = "passwd",
  mysqlUnixSocket = "/var/run/mysqld/mysqld.sock"
}

addMovie conn title year = do
  let params = T.join "," ["title", "year"]
  run conn ("INSERT INTO movies_movie (" ++ params ++ ") VALUES (?, ?)")
    [toSql title, nToSql year]
  commit conn


{-
[("id",SqlInt32 1),("title",SqlByteString "testing"),("year",SqlInt32 0)]
[("id",SqlInt32 2),("title",SqlByteString "testing 123"),("year",SqlInt32 0)]
[("id",SqlInt32 3),("title",SqlByteString "testing '123"),("year",SqlInt32 0)]
[("id",SqlInt32 4),("title",SqlByteString "testing '123"),("year",SqlInt32 0)]
[("id",SqlInt32 5),("title",SqlByteString "testing '123"),("year",SqlInt32 1999)]
-}
printMovie row = mapM_ printMovieData row

-- [("id",SqlInt32 1),("title",SqlByteString "testing"),("year",SqlInt32 0)]
printMovieData row = case row of
  (field, value) | field `elem` ["id"] -> print (movieId value)
  (field, value) | field `elem` ["title"] -> print (movieTitle value)
  _ -> return ()

movieId :: SqlValue -> Integer
movieId value = do fromSql value

movieTitle :: SqlValue -> String
movieTitle value = do fromSql value

main = do
  conn <- startDb
  
  -- This is awesome I don't have to type a bunch of (),; crap!
  -- addMovie conn "testing '123" 1999
  
  stmt <- prepare conn "SELECT * FROM movies_movie WHERE 1"
  execute stmt []
  results <- fetchAllRowsAL stmt
  mapM_ printMovie results
  
  disconnect conn
  
  print "Done!"