HDBC-Example

HDBC Example

For this example I assume that you have a working installation of PostgreSQL and HDBC. Further I assume the existance of a database 'testdb' and a user 'testuser'.

We need some imports. Exception is optional, but since stuff can go wrong...

>import Control.Exception
>import Database.HDBC
>import Database.HDBC.PostgreSQL (connectPostgreSQL)

>main

We catch Exceptions with catchDyn and handle them with our handler function below

>  catchDyn (do

Step 1 is opening a connection to your Database server.

>    c <- connectPostgreSQL "host=localhost dbname=testdb user=testuser password=pass"

SQL statements are prepared first. Questionmarks are later replaced with parameters.

>    state <- prepare c "INSERT INTO testtable values (?,?);"
>    select <- prepare c "SELECT * FROM testtable;"

to actually execute a statement, use execute. The list of parameters replaces the ? in the statement

>    execute state [toSql "muhmuh", toSql (40::Int)]
>    execute select []

there are various ways to get results from an executed statement. We use the lazy fetchAllRows, which works like hGetContents. Refer to the API for other functions. Note that the statement magically remembers it's been executed and carries the results around

>    result <- fetchAllRows select
>    putStrLn $ show result

In HDBC everything runs inside a transaction. That means no changes are actually saved until you call commit. You can rollback to the last commit.

>    commit c

Finally, disconnect from the database

>    disconnect c
>    return ()
>  )

The handler merely prints the error message. In a real program you would to do some cleanup here.

>  handler where 
>        handler :: SqlError -> IO ()
>        handler err = print $ "Oh no: " ++ show err