[MonetDB-users] calc.date in SQL to MAL.
Hi. I have resumed work on the OCaml binding for MonetDB. I have been playing a bit with it and with MonetDB, and noticed something rather weird when trying to explain a SQL statement to MAL. Compilation, and launching a small test in an OCaml toplevel: yziquel@seldon:~/data$ make ocamlbuild -use-ocamlfind import.cma Finished, 3 targets (0 cached) in 00:00:00. yziquel@seldon:~/data$ make test cp ocamlinit _build/ cd _build; ledit ocaml -init ocamlinit Objective Caml version 3.12.0 Findlib has been successfully loaded. Additional directives: #require "package";; to load a package #list;; to list the available packages #camlp4o;; to load camlp4 (standard syntax) #camlp4r;; to load camlp4 (revised syntax) #predicates "p,q,...";; to set these predicates Topfind.reset();; to force that packages will be reloaded #thread;; to enable threads /usr/lib/ocaml/csv: added to search path /usr/lib/ocaml/csv/csv.cma: loaded /usr/lib/ocaml/unix.cma: loaded /usr/lib/ocaml/monetdb5: added to search path /usr/lib/ocaml/monetdb5/monetDB5.cma: loaded /usr/lib/ocaml/dynlink.cma: loaded /usr/lib/ocaml/camlp4: added to search path /usr/lib/ocaml/pcre: added to search path /usr/lib/ocaml/pcre/pcre.cma: loaded /usr/local/lib/ocaml/3.12.0/tophide: added to search path /usr/local/lib/ocaml/3.12.0/tophide/tophide.cmo: loaded /usr/local/lib/ocaml/3.12.0/mikmatch_pcre: added to search path /usr/local/lib/ocaml/3.12.0/mikmatch_pcre/run_mikmatch_pcre.cma: loaded So much for the prologue, and dynamic loading. So the test starts here. Everytime you see "Asking", it is the statement that is fed to the connection handler in order to retrieve the query handler. (With mapilib). Asking: select name from tables; Asking: create table indu (Date date, Open decimal(15,2), High decimal(15,2), Low decimal(15,2), Close decimal(15,2), Volume decimal(15,2), OI decimal(15,2)); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-02-01', 108.80, 108.80, 108.80, 108.80, 1156, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-05-01', 108.90, 108.90, 108.90, 108.90, 1305, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-06-01', 107.40, 107.40, 107.40, 107.40, 1251, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-08-01', 107.20, 107.20, 107.20, 107.20, 843, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-09-01', 106.60, 106.60, 106.60, 106.60, 736, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0); And on this last statement, the server doesn't seem happy at all: Exception: MonetDB5.Mapi.Error (Some (MonetDB5.Mapi.MSERVER, None)). So I've been trying to 'explain' the SQL to MAL. The first two work fine and output a very similar MAL statement. The third SQL statements, however, is getting translated to something somewhat different: You see a call to 'calc.date' on the 4th MAL line: # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0); - : string option list list = [[Some "function user.s0_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _32 := language.dataflow();"]; [Some " _3 := sql.append(_2,\"sys\",\"indu\",\"date\",1920-07-01);"]; [Some " _7 := sql.append(_3,\"sys\",\"indu\",\"open\",10760:lng);"]; [Some " _9 := sql.append(_7,\"sys\",\"indu\",\"high\",10760:lng);"]; [Some " _11 := sql.append(_9,\"sys\",\"indu\",\"low\",10760:lng);"]; [Some " _13 := sql.append(_11,\"sys\",\"indu\",\"close\",10760:lng);"]; [Some " _15 := sql.append(_13,\"sys\",\"indu\",\"volume\",77100:lng);"]; [Some " sql.append(_15,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _32;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s0_1;"]] # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); - : string option list list = [[Some "function user.s1_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _32 := language.dataflow();"]; [Some " _3 := sql.append(_2,\"sys\",\"indu\",\"date\",1920-12-01);"]; [Some " _7 := sql.append(_3,\"sys\",\"indu\",\"open\",10420:lng);"]; [Some " _9 := sql.append(_7,\"sys\",\"indu\",\"high\",10420:lng);"]; [Some " _11 := sql.append(_9,\"sys\",\"indu\",\"low\",10420:lng);"]; [Some " _13 := sql.append(_11,\"sys\",\"indu\",\"close\",10420:lng);"]; [Some " _15 := sql.append(_13,\"sys\",\"indu\",\"volume\",104900:lng);"]; [Some " sql.append(_15,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _32;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s1_1;"]] # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0); - : string option list list = [[Some "function user.s2_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _33 := language.dataflow();"]; [Some " _3 := calc.date(\"1920-13-01\");"]; [Some " _4 := sql.append(_2,\"sys\",\"indu\",\"date\",_3);"]; [Some " _8 := sql.append(_4,\"sys\",\"indu\",\"open\",10450:lng);"]; [Some " _10 := sql.append(_8,\"sys\",\"indu\",\"high\",10450:lng);"]; [Some " _12 := sql.append(_10,\"sys\",\"indu\",\"low\",10450:lng);"]; [Some " _14 := sql.append(_12,\"sys\",\"indu\",\"close\",10450:lng);"]; [Some " _16 := sql.append(_14,\"sys\",\"indu\",\"volume\",90800:lng);"]; [Some " sql.append(_16,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _33;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s2_1;"]] # Why is that happening? The SQL statements seem so similar that I do not see why it gets compiled differently to MAL. Best regards, -- Guillaume Yziquel http://yziquel.homelinux.org
Le Friday 22 Oct 2010 à 01:57:01 (+0200), Guillaume Yziquel a écrit :
Hi.
I have resumed work on the OCaml binding for MonetDB. I have been playing a bit with it and with MonetDB, and noticed something rather weird when trying to explain a SQL statement to MAL.
values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);
Stupid me: There's no such thing as a 13th month... Sorry for the bother. -- Guillaume Yziquel http://yziquel.homelinux.org
On 22-10-2010 02:26:32 +0200, Guillaume Yziquel wrote:
Le Friday 22 Oct 2010 à 01:57:01 (+0200), Guillaume Yziquel a écrit :
values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);
Stupid me: There's no such thing as a 13th month... Sorry for the bother.
I would have expected the server just to tell you this in the first case. Did the error not come up somehow, or does the OCaml binding currently not support the string exceptions from the server?
Le Friday 22 Oct 2010 à 08:31:01 (+0200), Fabian Groffen a écrit :
On 22-10-2010 02:26:32 +0200, Guillaume Yziquel wrote:
Le Friday 22 Oct 2010 à 01:57:01 (+0200), Guillaume Yziquel a écrit :
values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);
Stupid me: There's no such thing as a 13th month... Sorry for the bother.
I would have expected the server just to tell you this in the first case. Did the error not come up somehow, or does the OCaml binding currently not support the string exceptions from the server?
Well, it should: exception Error of (msg * string option) option let check_no_error ?null (dbh : mapi) x = let nn = match null with | None -> (dbh :> pointer) | Some n -> n in match error dbh nn with | Some MOK -> x | z -> raise (Error (match z with | None -> None | Some y -> Some (y, (error_str dbh)))) in the file: http://yziquel.homelinux.org/gitweb/?p=ocaml-monetdb5.git;a=blob;f=mapi.ml;h... However, I'm rereading that code, which I've been leaving aside for quite a few months. Will come back to that. Thanks. -- Guillaume Yziquel http://yziquel.homelinux.org
Le Friday 22 Oct 2010 à 17:18:00 (+0200), Guillaume Yziquel a écrit :
Le Friday 22 Oct 2010 à 08:31:01 (+0200), Fabian Groffen a écrit :
On 22-10-2010 02:26:32 +0200, Guillaume Yziquel wrote:
Le Friday 22 Oct 2010 à 01:57:01 (+0200), Guillaume Yziquel a écrit :
values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);
Stupid me: There's no such thing as a 13th month... Sorry for the bother.
I would have expected the server just to tell you this in the first case. Did the error not come up somehow, or does the OCaml binding currently not support the string exceptions from the server?
Short answer: The OCaml binding supports string exceptions from the server. Long answer: Concerning queries, I was reading the error message in the connection handle rather than in the query handle. So I was not seeing it. yziquel@seldon:~$ ocaml Objective Caml version 3.12.0 # #use "topfind";; - : unit = () Findlib has been successfully loaded. Additional directives: #require "package";; to load a package #list;; to list the available packages #camlp4o;; to load camlp4 (standard syntax) #camlp4r;; to load camlp4 (revised syntax) #predicates "p,q,...";; to set these predicates Topfind.reset();; to force that packages will be reloaded #thread;; to enable threads - : unit = () # #require "monetdb5";; /usr/lib/ocaml/monetdb5: added to search path /usr/lib/ocaml/monetdb5/monetDB5.cma: loaded # open MonetDB5;; # module DB = struct include DB.SQL let name = "my_db" end;; module DB : sig val embedded : bool val farm : string val backend : MonetDB5.language val name : string end # include Embedded (DB);; val mapi : MonetDB5.Mapi.mapi = <abstr> val query : string -> MonetDB5.Mapi.Handle.t = <fun> val ask : int list -> string -> string option list list = <fun> # let dbh = mapi;; val dbh : MonetDB5.Mapi.mapi = <abstr> # open Mapi;; # type pointer;; type pointer # type handle = private pointer;; type handle = private pointer # external raw_query : mapi -> string -> handle = "ocamlmonetdb5_mapi_query";; external raw_query : MonetDB5.Mapi.mapi -> string -> handle = "ocamlmonetdb5_mapi_query" # let h = raw_query dbh "insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0)";; val h : handle = <abstr> # external raw_result_error : handle -> 'a = "ocamlmonetdb5_mapi_result_error";; external raw_result_error : handle -> 'a = "ocamlmonetdb5_mapi_result_error" # let e = raw_result_error h;; val e : 'a = <poly> # external string_of_pointer : pointer -> string = "String_valptr";; external string_of_pointer : pointer -> string = "String_valptr" # string_of_pointer e;; - : string = "SQLException:date:conversion of string '1920-13-01' failed\n" Will correct that ASAP. Best regards, -- Guillaume Yziquel http://yziquel.homelinux.org
participants (2)
-
Fabian Groffen
-
Guillaume Yziquel