101companies

Headline

Database programming in Language:Haskell with Technology:HDBC

Motivation

The implementation demonstrates database programming in Language:Haskell with Technology:HDBC. That is, Technology:HDBC is used to access a database with company data from within Haskell by means of embedded Language:SQL. In particular, simple (prepared) SQL statements for totaling and cutting salaries are implemented in this manner. SQL query results are mapped to HDBC's Haskell data types for results; query results are fetched in a lazy manner. The connection to the Technology:MySQL database is realized by means of an Technology:ODBC back-end. Hence, the Haskell code for cutting and totaling salaries is independent from the concrete DBMS. The implementation is only dynamically typed in the sense that the database schema is not leveraged in any way to statically typecheck the Haskell code. See Contribution:haskellDB for a schema-ware approach to database programming in Haskell.

Illustration

Connecting

In this!!Main.hs we connect to the MySQL database by using an ODBC driver and appropriate connection information: 

let connString = "Driver={MySQL ODBC 5.1 Driver};"
              ++ "Server=localhost;"
              ++ "Port=3306;"
              ++ "Database=101companies;"
              ++ "User=root;"
conn <- connectODBC connString

Totaling

The function 

total
defines a statement to total all salaries: 

total :: IConnection conn => conn -> String -> IO Double
total conn cName = do
     stmt <- prepare conn $ 
        "SELECT salary " ++ 
        "FROM employee, company " ++ 
        "WHERE company.name = ? and " ++ 
        "company.id = employee.cid"
     execute stmt [toSql cName]
     res <- fetchAllRows stmt
     return $ sum (map (fromSql.head) res)

In lines 3-7 we use a prepared statement in which the company name placeholder is then replaced by the given name 

cName
. The statement is executed and we use the lazy HDBC function 
fetchAllRows
in line 9 to get all salaries, which we then sum up lazy to a 
Double
value and return in line 10. That is, salaries are fetched one by one from the database. We can now use the open connection to total all salaries: 
let cName = "meganalysis" 
oldTotal <- total conn cName

Functionality to cut all salaries uses an UPDATE statement instead of SELECT (see this!!Cut.hs for details).

Architecture

this!!Company.sql and this!!Meganalysis.sql provide SQL-scripts to create and populate company tables. this!!Total.hs and this!!Cut.hs provide totaling and cutting functionality using SQL statements. this!!Main.hs collects test scenarios for totaling and cutting.

Usage

Setup

We need a local database server. In the following we explain the steps for XAMPP <cite>xampp</cite>. We also need an SQL tool to create and populate tables. In the following we explain the steps for the MySQL Workbench <cite>mysqlworkbench</cite>.

  • Download and install XAMPP.
  • Open the "XAMPP Control Panel" and start "Apache" and "Mysql".
  • A local MySQL Server is now running:
    • Server Host: localhost       
    • Port: 3306            
    • Username: root            
    • Password: (empty password)
  • Connect to the database in MySQL Workbench.
  • Select the "101companies" schema or create it.
  • Create company tables: Run the SQL script this!!Company.sql.
  • Populate company tables: Run the SQL script this!!Meganalysis.sql.

Testing

function has to be applied. One can also use the this!!Makefile with a target test for test automation.

(C) 2012-2024 softlang.org and contributors

There are no revisions for this page.

User contributions

    This user never has never made submissions.

    User edits

    Syntax for editing wiki

    For you are available next options:

    will make text bold.

    will make text italic.

    will make text underlined.

    will make text striked.

    will allow you to paste code headline into the page.

    will allow you to link into the page.

    will allow you to paste code with syntax highlight into the page. You will need to define used programming language.

    will allow you to paste image into the page.

    is list with bullets.

    is list with numbers.

    will allow your to insert slideshare presentation into the page. You need to copy link to presentation and insert it as parameter in this tag.

    will allow your to insert youtube video into the page. You need to copy link to youtube page with video and insert it as parameter in this tag.

    will allow your to insert code snippets from @worker.

    Syntax for editing wiki

    For you are available next options:

    will make text bold.

    will make text italic.

    will make text underlined.

    will make text striked.

    will allow you to paste code headline into the page.

    will allow you to link into the page.

    will allow you to paste code with syntax highlight into the page. You will need to define used programming language.

    will allow you to paste image into the page.

    is list with bullets.

    is list with numbers.

    will allow your to insert slideshare presentation into the page. You need to copy link to presentation and insert it as parameter in this tag.

    will allow your to insert youtube video into the page. You need to copy link to youtube page with video and insert it as parameter in this tag.

    will allow your to insert code snippets from @worker.