Consider the following Oracle table:
GAIUS@orcl 17-SEP-10> create table t1 ( 2 c1 number, 3 c2 number(1)); Table created. GAIUS@orcl 17-SEP-10> insert into t1 values (1, 1); 1 row created. GAIUS@orcl 17-SEP-10> commit; Commit complete. GAIUS@orcl 17-SEP-10> select count(1) from t1 where c1 = c2; COUNT(1) ---------- 1
Note that both the values inserted were identical. In Python:
>>> from cx_Oracle import connect >>> lda = connect("gaius/gaius") >>> sth = lda.cursor() >>> sth.execute("select c1, c2 from t1") [<cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>] >>> for row in sth: ... print ("OK" if row == row else "FAIL") ... OK
Same results in SQL and Python. But in OCaml:
open Occi let _ = let lda = occi_connect "guy" "abc123" "" in let sth = occi_create_statement lda "" in let (_, _, rs) = ora_select sth "select c1, c2 from t1" [||] in List.iter (fun r -> print_endline (match r.(0) = r.(1) with |true -> "OK" |false -> "FAIL")) rs (* End of file *)
gaius@debian:~$ ./test FAIL
What happened there? Well, OCaml is seeing the first column as
"1." and the second as
"1" (sans quotes), despite them both having obviously been identical integers when inserted. The question is really what didn’t happen – there was no invisible type coercion between float and int (they are actually both
Occi.ora_value in the code above).
It’s definitely doing the “right thing” according to the type system of course, unfortunately there has never been any sort of standard relating to this here; the DDL for columns of type
NUMBER seems to have been decided on a fairly whimsical basis as a cursory browse of the schemas of some of our main apps shows. And there is loads of pre-existing SQL that I would like to be able to reuse.
This is easy enough to deal with in the SQL:
SELECT TRUNC(C1), C2... will let OCaml make the comparison the same as Python. I think I will amend my
Occi_overlay module with an
ora_select that optionally parses the SQL (looking for a comma seperated list between the keywords
FROM, and between
WHERE), gets the type of each column and if it’s a number, wrap it in
TRUNC. Which is very much a hack, but if I know that the types are always really going to be integers (because I am familiar with the application), it will at least be seamless…
Note that I absolutely do not consider this to be a problem with OCaml – but is is something to be aware of when introducing OCaml (or any strongly-typed languge) into an environment with legacy code.