29 October 2010

It gets better. Using this nifty trick from Mauricio Scheffer we can enhance our previous SQL computations with typed parameters and protect our code against SQL injections.

open Suave.Data
open System.Data.SQLite

type Person  = { FirstName: string; LastName: string; Age: int }

let cn = new SQLiteConnection("Data Source = database")

let tx = sql cn

let queries = 
        ("Albert", "Einstein", 32);
        ("Leonard", "Euler", 33);
        ("Benoit", "Mandelbrot", 34)
    |> List.iter (fun (a,b,c)  
                    -> tx.Query @"insert into family (first_name,last_name,age) 
                                                    values (%s,%s,%d)" a b c 
                    |> executeNonQuery)
    tx { 
        // binding to records
        for each in tx.Query "SELECT * FROM family WHERE age>%d" 30 do
                printfn "%s, %s, %d" each.FirstName each.LastName each.Age

        // binding to tuples       
        for (a,b,c) in tx.Query "SELECT * FROM family WHERE last_name like %s" "%brot" do
                printfn "%s, %s, %d" a b c
        // let! binds to option types, fails the evaluation by returning None 
        //if the query does not have any results
        let!  age = tx.Query "SELECT age FROM family WHERE first_name = %s" "Leonard"
        let query = tx.Query "SELECT * FROM family WHERE age = %d and first_name = %s"
        let! person =   query 33 "Leonard" 

        return person

match queries with 
|Some(first_name,last_name,age) -> printfn "%s, %s, %d" first_name last_name age
|None -> printfn "query failed."

tx.Query "delete from family" |> executeNonQuery

System.Console.ReadLine() |> ignore 

blog comments powered by Disqus