OCaml client interface for Oracle Code

(*-------------------------------------------------------------------------*)
(** File: $Id: occi_test.ml 452 2006-11-02 21:49:32Z serge $
  {[2] Module ORA}
  This module implements an Oracle client interface

  @author  Serge Aleynikov
  @version $Rev: 452 $ $Date: 2006-11-02 16:49:32 -0500 (Thu, 02 Nov 2006) $
  Created: 1-Oct-2006                                                      *)
(*-------------------------------------------------------------------------*)

open Occi;

(*
 * Process command line options
 *)
value (user, pwd, db) = (ref "", ref "", ref "");

value options = 
    [ ("-u", Arg.Set_string user, "\"user\" - User name");
      ("-p", Arg.Set_string pwd,  "\"password\" - Password");
      ("-d", Arg.Set_string db,   "\"database\" - Database name")
    ];
value usage_msg = ("usage: " ^ Sys.executable_name ^ " [options]");

if   (Array.length Sys.argv) < 3 
then do { Arg.usage options usage_msg; exit 1}
else Arg.parse options (fun s -> () ) usage_msg;

(* 
 *  T E S T   C A S E S
 *  ===================
 *)

print_endline "===Testing Functional features===";

value con  = occi_connect user.val pwd.val db.val;
value stmt = occi_create_statement con "";

(* Drop test table *)
try ignore (occi_execute stmt "drop table test" [])
with [ORA_EXCEPTION (942, _) -> ()];  (* Ignoring table or view doesn't exist error *)

print_endline "Test table dropped.";

(* Create test table *)
ignore (occi_execute stmt ("create table test (" ^
                           "  id   Integer," ^
                           "  name Varchar(25) NOT NULL," ^
                           "  dt   Date," ^
                           "  num  Number(10,5))") []);

print_endline "Test table created.";

(* Test occi_describe and print column details *)
Array.iter (fun {name=a; ctype=s; len=c; scale=d; not_null=e} -> 
               Printf.printf "  %-25s %-10s %5d %5d %5b\n" a s c d e)
           (ora_describe con "test");
print_newline ();

(* Test simple SQL insert *)
occi_prepare_sql stmt "insert into test (id, name, dt) values (0, 'XXX', null)";
let i = occi_execute_update stmt [| |] in
    Printf.printf "01. Inserted %d record using simple SQL\n" i;

occi_commit con;

let (rset, names) = occi_execute_query stmt "select * from test" in
do {
    (* Print column headers *)
    Array.iteri (fun i fn -> Printf.printf "  %d: %-30s\n" (i+1) fn) names;
    
    let r1 = ([| Int 0; Str "XXX"; Null; Null |], [|"ID"; "NAME"; "DT"; "NUM"|])
    and r2 = occi_fetch rset;

    assert (r1 = (r2, names));
};

(* Test parameterized SQL insert *)
occi_prepare_sql stmt "insert into test (id, name, dt, num) values (:1, :2, :3, :4)";

let now  = encode_date (2006, 1, 2, 3, 4, 5) in
let recs = [(10, "AAA", Null_date,             1.0); 
            (20, "BBB", Var_date now,          1.23); 
            (30, "CCC", Var_date (sysdate ()), 1.0)] in
let f = fun (i, s, d, n) -> 
            ignore (occi_execute_update stmt 
                        [| Var_int i; Var_str s; d; Var_float n |]
        ) in
do {
    (* Insert records *)
    List.iter f recs;

    (* Verify *)
    let (rset, _) = occi_execute_query stmt "select * from test where id = 20";

    assert ([| Int 20; Str "BBB"; Date now; Float 1.23 |] = (occi_fetch rset));

    Printf.printf "02. Inserted %d records using parameterized insert\n" (List.length recs)
};

occi_commit con;

(* Now let's get all records *)
print_endline "03. Testing select all records";

let (cursor, _) = occi_execute_query stmt "select * from test" in
try  ora_fetch_foreach cursor print_record
with [ End_of_file -> print_endline "Finished!"];
    

(* Test delete statement *)
occi_prepare_sql stmt "delete from test";
let i = occi_execute_update stmt [| |] in 
    Printf.printf "04. Deleted %d records\n" i;

(* Test drop statement *)
try  ignore (occi_execute stmt "drop function test_fun" [])
with [ ORA_EXCEPTION (4043, _) -> ()    (* Object doesn't exist *)
     | ORA_EXCEPTION err -> raise (ORA_EXCEPTION err)];

print_endline "05. Dropped test function";

(* Test PL/SQL block statement *)
ignore (
    occi_execute stmt ("create function test_fun (N IN Integer, D OUT Date) return Integer is\n" ^
                      "begin\n" ^
                      "  D := to_date('01/02/2003 10:20:30', 'mm/dd/yyyy hh24:mi:ss');\n" ^
                      "  return N * 100;\n" ^
                      "end;") []
);
print_endline "06. Created PL/SQL test function";

let res = occi_execute stmt 
              ("begin\n" ^
               "  :1 := test_fun(:2, :3);\n" ^
               "end;") 
              [(1, Var_out_int); (2, Var_int 100); (3, Var_out_date)] in
do {
    print_endline "07. Executed PL/SQL block. OUT parameters:";
    List.iter (fun (i, t) -> print_field (i-1) t) res;
    print_newline ()
};

ignore (occi_execute stmt "delete from test" []);

(* Test Array DML insert *)
let d1 = encode_date (2006,1,2,3,4,5) 
and d2 = encode_date (2006,2,3,4,5,6) in 
let n  = occi_execute_array stmt 
            "insert into test (id, name, dt, num) values (:1, :2, :3, :4)"
            [
              [| Var_int 1; Var_str "AAA"; Null_date;   Var_float 1.0 |]; 
              [| Var_int 2; Var_str "BBB"; Var_date d1; Var_float 1.23|]; 
              [| Var_int 3; Var_str "CCC"; Var_date d2; Var_float 1.0 |]
            ] in
    Printf.printf "08. Inserted %d records using array DML\n" n;

let (_, _, l) = ora_select stmt "select * from test" [| |] in 
    List.iter print_record l;

(* Test ORA_BULK_EXCEPTION with Array DML insert *)
print_endline "09. Testing exceptions with bulk array DML insert. Two records should fail.";

try 
    let n = occi_execute_array stmt 
            "insert into test (id, name, dt, num) values (:1, :2, :3, :4)"
            [
              [| Var_int 1; Var_str "AAA"; Null_date; Null_float |]; 
              [| Var_int 2; Null_str;      Null_date; Null_float |]; 
              [| Var_int 3; Null_str;      Null_date; Null_float |]
            ] in
    Printf.printf "  Inserted %d records using array DML\n" n
with [ORA_BULK_EXCEPTION (m, a) -> do {
          print_endline m;
          Array.iter (fun (i, e, m) -> Printf.printf "  Row[%d]: %d - %s\n" i e m) a
      }];

occi_commit con;

(* Drop test function *)
ignore (occi_execute stmt "drop function test_fun" []);

occi_free_statement stmt ;
print_endline "  Statement explicitely freed";

Gc.full_major ();  (* Test automatic freeing of resources *)

occi_disconnect con;

print_endline "  Session disconnected\n";

(*------------------*)
(* Test OO features *)
(*------------------*)

print_endline "===Testing Object-Oriented features===";
value con  = new connection user.val pwd.val db.val;
value stmt = con#stmt ();

print_endline "10. Parameterized select test";

let (_, _, l) = stmt#select ~sql:"select * from test where id = :1" [| Var_int 2 |] in
    List.iter print_record l;

print_endline "11. Parameterized select using foreach";
stmt#foreach print_record ~sql:"select * from test";

print_endline "12. Execute DDL test";

(* Drop test table *)
ignore (stmt#execute ~sql:"drop table test" []);
print_endline "  Table dropped";
con#disconnect;
print_endline "  Session disconnected";