In this post I am going to present a trivial application that nonetheless is representative of “real work” – parsing a command line, connecting to Oracle, running a query, processing the results and generating a report. I suppose it is also a summary of all the OCaml I have learnt so far.

Here is a list of the files in the project:

gaius@debian:~/Projects/QueryExample$ ls
data.sql        Makefile  occi.cmo  occi_overlay.ml  query.ml
log_message.ml  occi.cmi  occi.o    prompt.ml        report.ml

I am aware of ocamlbuild, but for now I am using a good, old-fashioned Makefile, based on the one in the Occi distribution since I am linking in that code.

# Makefile

TARGET=query

OCCIHOME=/opt/oracle/product/11.2.0_db
CAMLBASE=/usr
OBJS=prompt.cmo log_message.cmo report.cmo occi_overlay.cmo $(TARGET).cmo

$(TARGET): $(OBJS) 
	ocamlc -g -custom -o $(TARGET) unix.cma -cclib -L$(OCCIHOME)/lib -cclib -locci -cclib -lclntsh occi.o occi.cmo $(OBJS) -cclib -lunix

%.cmo: %.ml
	ocamlc -g -c $<

clean:
	rm -f $(OBJS) $(TARGET) $(TARGET).c* *~ 

# End of file

The occi.* files are symlinks to the ones we prepared earlier:

gaius@debian:~/Projects/QueryExample$ ls -lh occi.*
lrwxrwxrwx 1 gaius gaius 26 Sep  9 16:25 occi.cmi -> ../../oracaml-0.1/occi.cmi
lrwxrwxrwx 1 gaius gaius 26 Sep  9 16:25 occi.cmo -> ../../oracaml-0.1/occi.cmo
lrwxrwxrwx 1 gaius gaius 24 Sep  9 16:25 occi.o -> ../../oracaml-0.1/occi.o

Are you sitting comfortably? Then let’s begin with the main file, query.ml.

open Printf
open Occi
open Occi_overlay
open Report

The first step is to open the modules. Unlike in Python this does not load or execute any code; it is syntactic sugar to move the contents of those modules into the default namespace. They are read in order and symbols in later modules override those opened previously. I am exploiting this technique to overlay some of my own code on functions in the Occi module. This is similar to the object-oriented technique of code reuse via inheritance with one key difference. These are still there; they would simply need to be fully qualified in order to use (as well as being linked into the binary!). Specifically I wish to execute additional code on connection and disconnection from the database. The Report module was also prepared earlier.

(* Parse the command line arguments *)
let (username, password, database, outputfn) = ((ref ""), (ref ""), (ref ""), (ref ""))
let _ = Arg.parse 
	[("-u", Arg.Set_string username, "Database username");
	 ("-p", Arg.Set_string password, "Database password");
	 ("-d", Arg.Set_string database, (sprintf "Database connection (default %s)" (Unix.getenv "ORACLE_SID")));
	 ("-o", Arg.Set_string outputfn, "Output filename (default STDOUT)")]
	(fun x -> raise (Arg.Bad (sprintf "unexpected argument '%s'" x)))
	(sprintf "Usage: %s -u username [-p password] [-d database] [-o filename]" Sys.argv.(0))

Next the arguments on the command line are parsed using the Arg module that comes in the standard OCaml distribution. This is fully qualified as it is not opened; the function name parse is too generic to clutter up the main namespace. Firstly the variables that will be set are initialized as empty, mutable strings. The parser takes a list of options, what action to take with the value, and some help text, followed by a function to execute in case of an unexpected argument and a function to execute if the command is called with -help, as per most Unix™ utilities.

(* perform some computation on the row *)
let process_row a b = if a = b then "OK" else "FAIL"

This is the function that we will use to perform some computation in OCaml code on the results returned from the database. It will be included on a line with the query results as if it had been selected from the table. Almost anything can be done here, it could do a calculation, or look up another value in a file, or over the network (in those cases some caching could be used for performance, e.g. in a Hashtbl).

(* main *)
let _ =
	try
		(* Connect to the database *)	
		let lda = occi_connect !username !password !database () in  
		
		(* Create a new report object *)
		let r = new report [|"Fruit ordered"; "Fruit received"; "Fruitbowl Status"|] in
		
		(* Execute the query and process the rows returned *)
		let sth = occi_create_statement lda "" in 
		let (_, _, rs) = ora_select sth "SELECT * FROM QUERY_DATA" [| |] in
		List.iter (fun row ->	let a = field_to_string row.(0) in
								let b = field_to_string row.(1) in
								let c = process_row a b in
								r#add_row[|String.capitalize a; String.capitalize b; c|]) rs;

This is the main body of the program. Firstly a connection is made to Oracle, dereferencing the mutable strings initialized earlier that may have been set by the command line argument parser. A new Report object is also created with the headings for the report. In this case we will be comparing the fruit we ordered to the fruit in the bowl (for some reason, this is being tracked in Oracle). The terminology I am using (lda for a database connection and sth for a “statement handle” are traditional OCI I remember from back in the day). A query is executed and the result set rows mapped to a λ function that unpacks them, passes them to the process_row function, then adds a line to the report. Note the explicit casting between datatypes; OCaml requires this whereas Python would do it implicitly (with the attendant risk that it blows up at runtime!). Also note the start of a try block.

				
		(* Output the results to STDOUT if no file name is specified *)
		let chan = match !outputfn with |"" -> stdout |_ -> open_out !outputfn in
			r#print_report ~chan:chan ();
			occi_disconnect lda
	with
		Sys_error e 	  	  -> eprintf "%s: %s\n" Sys.argv.(0) e
		|ORA_EXCEPTION (_, e) -> eprintf "%s: %s" Sys.argv.(0) e

(* End of file *)

Finally the results are output – either to standard output if no filename was specified on the command line, or to a file if it was. Should writing the file fail (e.g. a read-only filesystem) then Sys_error will be thrown. Similarly if anything goes wrong with the database connection (e.g. an incorrect password) then ORA_EXCEPTION will be thrown. When we are finished we disconnect from Oracle, even tho’ as the program now terminates this would happen when the garbage collector cleans up.

Here is the Occi_overlay module:

open Printf
open Unix
open Occi
open Prompt
open Log_message
  
(** Wrap Occi.occi_connect prompting for a password if pass = "" and doing any default setup *)
let occi_connect ?(log = true) ?(envvar="") user pass _db () =
	let db = (match _db with
				| "" -> getenv "ORACLE_SID" 
				| _  -> _db) in
	let desc = sprintf "%s@%s" user db in
	let lda =
		Occi.occi_connect user
		(match pass with
			| "" -> get_env_prompt ~envvar: (if (String.length envvar) > 0 then envvar else (String.uppercase user) ^ "_PASSWORD") (sprintf "Enter password for %s: " desc)
			| _  -> pass) db in
	let sth = occi_create_statement lda "" in
		(ignore (occi_execute sth "alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'" []);
		if log then log_message ("Connected to " ^ desc) else ();
		lda)
  
(** Wraps Occi.occi_disconnect for logging and any default actions we might want to do on session close *)
let occi_disconnect ?(log = true) lda =
	(if log then log_message "Disconnected." else (); Occi.occi_disconnect lda)
  
(** Wrap Occi.field_to_string to get strings without quotes, passing other types to the underlying function *)
let field_to_string fld =
	match fld with 
	| Str s -> s 
	| _     -> Occi.field_to_string fld
  
(* End of file *)

It demonstrates calling the original functions by fully qualifying their module name, and also the technique of parameters with default values (note for example that the logging mode was not explicitly set in the main body of the code). If the password is not set, then I check for it in an environment variable, e.g. if my username is gaius then the password could be set in GAIUS_PASSWORD. This is a technique I use to simplify deployment in mass-provisioned environments where any specific configuration is held in a single ENV file. If that does not work, then an interactive prompt is displayed. It also logs the fact that a connection is made to Oracle (and again on disconnecting) and can execute some SQL to set up the session as preferred.

This is the code used for logging in an identical format to the Python code I write. In some cases the log output from one program is the input for another, so the same functions get used everywhere. OCaml unfortunately has no equivalent of Python’s very useful Time module which provides strptime and strftime for easily converting between human-readable and Epoch formats, so I have used the example from PLEAC.

(** Functions related to logging and parsing a logfile *)
open Unix
open Printf
  
let days = [| "Sun"; "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat" |]
let months = [| "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec" |]
  
(** List all the indexes of element el in array arr *)
let indexesOf el arr =
  let stk = Stack.create ()
  in
    (Array.iteri (fun i x -> if x = el then Stack.push i stk else ()) arr;
     stk)
  
(** Return the first index of an element in an array e.g. indexOf "b" ["a"; "b"; "c"|] returns 1 *)
let indexOf el arr =
  try Stack.top (indexesOf el arr) with | Stack.Empty -> raise Not_found
  
(** Convert the timestamp applied by the log_message function back into Epoch seconds *)
let epoch_of_log_timestamp ts =
  Scanf.sscanf ts "%3s %3s %2d %2d:%2d:%2d %4d"
    (fun day mon dd hh24 mm ss yyyy ->
       fst (Unix.mktime { Unix.tm_sec = ss; tm_min = mm; tm_hour = hh24;
              tm_mday = dd; tm_mon = indexOf mon months;
              tm_year = yyyy - 1900; tm_wday = indexOf day days; tm_yday = 0; tm_isdst = false; }))
  
(** Log a message to STDOUT in the same format as the Python equivalent *)
let log_message msg =
  let lt = localtime (time ()) in
  let ts =
    sprintf "%3s %3s %2d %02d:%02d:%02d %4d" days.(lt.tm_wday)
      months.(lt.tm_mon) lt.tm_mday lt.tm_hour lt.tm_min lt.tm_sec
      (lt.tm_year + 1900)
  in print_endline (ts ^ (": " ^ msg))
  
(* End of file *)

And here is the data:

-- data for query.ml
drop table query_data;

create table query_data (
	ordered 	varchar2(32),
	received 	varchar2(32));
	
insert into query_data values ('apples', 'oranges');
insert into query_data values ('bananas', 'lemons');
insert into query_data values ('pears', 'pears');

commit;
-- end of file

And finally the output:

gaius@debian:~/Projects/QueryExample$ ./query -u gaius -p gaius -o report.txt
Thu Sep  9 17:32:36 2010: Connected to gaius@orcl
Thu Sep  9 17:32:36 2010: Disconnected.
gaius@debian:~/Projects/QueryExample$ cat report.txt 
Fruit ordered Fruit received Fruitbowl Status 
------------- -------------- ---------------- 
       Apples        Oranges             FAIL 
      Bananas         Lemons             FAIL 
        Pears          Pears               OK 

Actually there is one more thing: does it run on Redhat 64? Well it’s funny you should ask…

redhat64$ ./query -u gaius 
./query: error while loading shared libraries: libocci.so.11.1: wrong ELF class: ELFCLASS64
redhat64$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib32
redhat64$ ./query -u gaius
./query: /lib/libc.so.6: version `GLIBC_2.7' not found (required by ./query)

You can see why I might find OCaml to be a hard sell in my organization. Here I am using an unmaintained library that would prefer obsolete and not readily available versions of OCaml itself and GCC (3.8 and 3.3 respectively) that creates binaries that fail to run on seemingly similar machines with inexplicable errors (this is the same box on which occi_test ran correctly!). But I am optimistic; it’s a great language, clean and expressive, and hopefully before long I’ll be at the level where I can simply volunteer to be the Oracaml maintainer myself…