Basic MySQL Transactions

The following example shows how to access a MySQL database with F# on Mono. To try it out, please install the MySQL NET/Connector in advance.

Firstly, lets create an example database and a mysql user account.

~> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.46-log SUSE MySQL RPM

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> create user "2sharp4u"@"localhost" identified by "fsharp";
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on test to 2sharp4u;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

Secondly, we may apply our F# code to open, edit and retrieve the data out of this database.

open MySql.Data.MySqlClient

let conn = new MySqlConnection("Server=localhost;"+
 "Uid=2sharp4u;"+
 "Pwd=fsharp;"+
 "Database=test")
conn.Open()

let cmd1 = new MySqlCommand("CREATE TABLE presidents (
 ID INT NOT NULL,
 FirstName VARCHAR(30) NOT NULL,
 LastName VARCHAR(30) NOT NULL,
 TookOffice datetime,
 LeftOffice datetime,
 PRIMARY KEY (ID))", conn)
cmd1.ExecuteNonQuery() |> ignore

let cmd2 = new MySqlCommand("INSERT INTO presidents
 (ID, FirstName, LastName, TookOffice, LeftOffice)
 VALUES (1, 'George', 'Washington', '30.04.1789', '04.03.1797')", conn)
cmd2.ExecuteNonQuery() |> ignore

let cmd3 = new MySqlCommand("INSERT INTO presidents
 (ID, FirstName, LastName, TookOffice, LeftOffice)
 VALUES (2, 'John', 'Adams', '04.03.1797', '04.03.1801')", conn)
cmd3.ExecuteNonQuery() |> ignore

let cmd4 = new MySqlCommand("INSERT INTO presidents
 (ID, FirstName, LastName, TookOffice, LeftOffice)
 VALUES (3, 'Thomas', 'Jefferson', '04.03.1801', '04.03.1809')", conn)
cmd4.ExecuteNonQuery() |> ignore

let qcmd = new MySqlCommand("SELECT FirstName,
LastName FROM presidents", conn)
let reader = qcmd.ExecuteReader()

let s = seq {
 while reader.Read() do
 yield (reader.GetString 0, reader.GetString 1)
 }

s |> Seq.iter (fun (firstname, lastname) ->
 printfn "%s %s" firstname lastname)

conn.Close()

Compile and execute the source file.

~> fsc -r MySql.Data.dll mysql1.fs
Microsoft (R) F# 2.0 Compiler build 2.0.0.0
Copyright (c) Microsoft Corporation. All Rights Reserved.
~> mono mysql1.exe
George Washington
John Adams
Thomas Jefferson
~>