OCaml and SQLite Quick Introduction

In attempting to actually attempt to dive a little more deeply into one of the languages that supports one of my most-favoured language features (implicit return value is the value of the last expression of a function), I decided to check out OCaml and write a minimal "todo list" script which stores and retrieves some data from SQLite.

Debian Etch:

$ sudo aptitude install ocaml libdbi-ocaml
$ ocaml

#use "topfind";;
#require "dbi.sqlite";;
module DB = Dbi_sqlite;;

(* connect *)
let dbh = new DB.connection "todo.db";;

(* create a table *)
let sth = dbh#prepare "create table items (name varchar(80), description text)" in
sth#execute [];;

(* insert some rows *)
let items_list = [("feed fish", "not too much, not too little"); ("pick up dry cleaning", "don't forget to use the coupon")] in
let sth = dbh#prepare "insert into items(name,description) values(?,?)" in
List.iter(fun (name,description) ->
  sth#execute [`String name; `String description];
  ) items_list;;

(* commit! *)
dbh#commit ();;

(* get our list of items and print them *)
open Printf;;
let sth = dbh#prepare "select name, description from items" in 
sth#execute [];
sth#iter(function
  | [`String name; `String description] -> 
    printf "name: %s, description: %s\n" name description
  | _ -> assert false);;

So that is running in the ocaml interpreter -- how about compiling? Simply remove those first two lines (#use and #require) and save the listing to a text file, e.g. ocamlsqlite.ml, and compile:

ocamlfind ocamlc -o ocamlsqlite -package dbi.sqlite -linkpkg ocamlsqlite.ml

And now you've got a standalone executable:

./ocamlsqlite

Hopefully that's enough of a teaser to get you started. There is a bug in /usr/lib/ocaml/3.09.2/dbi/dbi.mli as its example for "Simple usage, returning multiple rows" used a comma where it should have used a semi-colon in the match clause for the iteration function. I'll have to figure out how I "contribute" a fix later, as I am already late for about 4 things.

changed January 31, 2008