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 Data.List.Utils
 
startDb = connectMySQL defaultMySQLConnectInfo {
  mysqlHost = "localhost",
  mysqlDatabase = "movies",
  mysqlUser = "root",
  mysqlPassword = "5texmex2go",
  mysqlUnixSocket = "/var/run/mysqld/mysqld.sock"
}

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


{-
  Why is this only hitting the first result?
  [("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 results = case results of
  row@_ -> mapM_ printMovieData row

-- [("id",SqlInt32 1),("title",SqlByteString "testing"),("year",SqlInt32 0)]
printMovieData row = case row of 
  id@("id", _) -> print (snd id)
  title@("title", _) -> print (snd title)
 
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!"

printMovie row = mapM_ printMovieData row

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