Haskell で PosygreSQL(3)

オライリーから発売されるReal World Haskellを日本語に訳すページ

21. データベース操作

-- ghc --make -o sel -LC:\PostgreSQL\8.4\lib select.hs
import IO
import Database.HDBC
import Database.HDBC.PostgreSQL
 
main = do
    conn <- connectPostgreSQL "host=localhost port=5432 dbname=test" 
    run conn "INSERT INTO test VALUES (?, ?)" [toSql 0, toSql "zero"]
    commit conn
    disconnect conn

$ ghc -package HDBC -package HDBC-postgresql hdbc.hs -o hdbc
hdbc.hs:10:47:
    No instance for (Data.Convertible.Base.Convertible t SqlValue)
      arising from a use of `toSql' at hdbc.hs:10:47-53
    Possible fix:
      add an instance declaration for
      (Data.Convertible.Base.Convertible t SqlValue)
    In the expression: toSql 0
    In the third argument of `run', namely `[toSql 0, toSql "zero"]'
    In a stmt of a 'do' expression:
        run conn "INSERT INTO test VALUES (?, ?)" [toSql 0, toSql "zero"]

【エラー】本には Int が記載されているがエラーになる。

run conn "INSERT INTO test VALUES (?, ?)" [toSql 0, toSql "zero"]

【OK】文字列ならおk。（20110512:追記これは誤り）

run conn "INSERT INTO test VALUES (?, ?)" [toSql "0", toSql "zero"]

• toSql は型を指定する。（20110512:追記）

run conn "INSERT INTO test VALUES (?, ?)" [toSql (0::Int), toSql "zero"]

-- ghc --make -o sel -LC:\PostgreSQL\8.4\lib select.hs
import IO
import Database.HDBC
import Database.HDBC.PostgreSQL
 
main = do
    conn <- connectPostgreSQL "host=localhost port=5432 dbname=test" 
    run conn "INSERT INTO test (id) VALUES (0)" []
    run conn "INSERT INTO test (id,name) VALUES (256,'test')" []
    run conn "INSERT INTO test VALUES (?, ?)" [toSql "0", toSql "zero"]
    -----
    stmt <- prepare conn "INSERT INTO test VALUES (?, ?)"
    execute stmt [toSql "1", toSql "one"]
    execute stmt [toSql "2", toSql "two"]
    execute stmt [toSql "3", toSql "three"]
    execute stmt [toSql "4", SqlNull]
    commit conn
    disconnect conn

テーブルを確認。

$ echo "select * from test"|psql test
 id  | name  
-----+-------
   0 | 
 123 | hoge
   0 | zero
   1 | one
   2 | two
   3 | three
   4 | 
(7 rows)

読み込んでみる。

-- ghc --make -o sel -LC:\PostgreSQL\8.4\lib dbread.hs

import IO
import Database.HDBC
import Database.HDBC.PostgreSQL
 
main = do
    conn <- connectPostgreSQL "host=localhost port=5432 dbname=test" 
    q <- quickQuery' conn "SELECT * from test WHERE id=2" []
    print q
    disconnect conn

$ runghc dbread.hs
[[SqlInteger 2,SqlByteString "two"?]]