Re: [Caml-list] Type-safe interface to Postgres's SQL

[Richard Jones <rich@annexia.org>]

On Tue, Jan 31, 2006 at 02:07:33PM +0000, Richard Jones wrote:
> (2) PostgreSQL doesn't track NULL types properly so we have to do a
> bit of non-trivial guesswork to try to find out if a database field
> could contain a NULL or not (and therefore whether to map its type to
> 'a or 'a option).  For parameters this isn't possible at all, so all
> parameter variables must have an option type.  For result fields, it
> is usually possible to tell if the result corresponds to a known
> column in the database, which is the case in ninety percent of
> queries.

FWIW I have solved this problem with a small syntactic change.  You
now use $varname when you don't want NULLs and $?varname when you
do.[1] A modified example program is attached.

You can also download the full program from here:

http://merjis.com/tmp/pgocaml-0.2.tar.gz

Rich.

[1] It is still possible to insert a NULL into a NOT NULL field, as
was the case before, but I can't see any way to solve that without
rewriting Postgres.

----------------------------------------------------------------------
(* Example program using typesafe calls to PostgreSQL.
 * $Id: test.ml,v 1.4 2006/02/18 15:54:06 rich Exp $
 *)

open Printf

let () =
  let dbh = PGOCaml.connect () in

  PGSQL_EXECUTE_ON_COMPILE(dbh) "create temporary table employees (
     id serial not null primary key,
     name text not null,
     salary int4 not null,
     email text
  )";

  let insert name salary email =
    PGSQL(dbh) "insert into employees (name, salary, email)
                values ($name, $salary, $?email)"
  in
  insert "Ann" 10_000_l None;
  insert "Bob" 45_000_l None;
  insert "Jim" 20_000_l None;
  insert "Mary" 30_000_l (Some "mary@example.com");

  let rows = PGSQL(dbh) "select id, name, salary, email from employees" in
  List.iter (
    fun (id, name, salary, email) ->
      let email = match email with Some email -> email | None -> "-" in
      printf "%ld %S %ld %S\n" id name salary email
  ) rows;

  PGOCaml.close dbh


-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com