From: Adam Chlipala Date: Fri, 15 Apr 2005 16:35:29 +0000 (+0000) Subject: Add proper handling of null column values X-Git-Url: https://git.hcoop.net/hcoop/smlsql.git/commitdiff_plain/d08450a7d0f596da46ddf96eeabdeae70549ed0e Add proper handling of null column values --- diff --git a/libpq/pg.sml b/libpq/pg.sml index 63d2720..de1a9e8 100644 --- a/libpq/pg.sml +++ b/libpq/pg.sml @@ -25,6 +25,8 @@ struct exception Sql of string + type value = string option + fun cerrmsg con = Int32.toString (F_PQstatus.f' (C.Ptr.ro' con)) ^ ": " ^ ZString.toML' (F_PQerrorMessage.f' (C.Ptr.ro' con)) @@ -73,6 +75,12 @@ struct end end + fun makeValue v = + if C.Ptr.isNull' v then + NONE + else + SOME (ZString.toML' v) + fun fold c f b q = let val q = ZString.dupML' q @@ -96,7 +104,7 @@ struct let fun build (~1, acc) = acc | build (j, acc) = - build (j-1, ZString.toML' (F_PQgetvalue.f' (roRes, i, j)) :: acc) + build (j-1, makeValue (F_PQgetvalue.f' (roRes, i, j)) :: acc) in builder (i+1, f (build (nf-1, []), acc)) end @@ -117,18 +125,27 @@ struct type timestamp = Time.time exception Format of string - fun isNull s = s = "" + fun valueOf v = + case v of + NONE => raise Sql "Trying to read NULL value" + | SOME v => v + + fun isNull s = + case s of + NONE => true + | _ => false fun intToSql n = if n < 0 then "-" ^ Int.toString(~n) else Int.toString n - fun intFromSql "" = 0 - | intFromSql s = + fun intFromSql' "" = 0 + | intFromSql' s = (case Int.fromString s of NONE => raise Format ("Bad integer: " ^ s) | SOME n => n) + fun intFromSql v = intFromSql' (valueOf v) fun stringToSql s = let @@ -139,18 +156,19 @@ struct in foldl (fn (c, s) => s ^ xch c) "'" (String.explode s) ^ "'" end - fun stringFromSql s = s + val stringFromSql = valueOf fun realToSql s = if s < 0.0 then "-" ^ Real.toString(~s) else Real.toString s - fun realFromSql "" = 0.0 - | realFromSql s = + fun realFromSql' "" = 0.0 + | realFromSql' s = (case Real.fromString s of NONE => raise Format ("Bad real: " ^ s) | SOME r => r) + fun realFromSql v = realFromSql' (valueOf v) fun realToString s = realToSql s fun toMonth m = @@ -221,42 +239,45 @@ struct ".000000" ^ offsetStr (Date.offset d) end fun timestampToSql t = "'" ^ timestampToSqlUnquoted t ^ "'" - fun timestampFromSql s = + fun timestampFromSql' s = let val tokens = String.tokens (fn ch => ch = #"-" orelse ch = #" " orelse ch = #":" orelse ch = #"." orelse ch = #"+") s in case tokens of [year, mon, day, hour, minute, second, _, offset] => - Date.toTime (Date.date {day = intFromSql day, hour = intFromSql hour, minute = intFromSql minute, - month = toMonth (intFromSql mon), - offset = SOME (Time.fromSeconds (LargeInt.fromInt (intFromSql offset * 3600))), - second = intFromSql second div 1000, year = intFromSql year}) + Date.toTime (Date.date {day = valOf (Int.fromString day), hour = valOf (Int.fromString hour), minute = valOf (Int.fromString minute), + month = toMonth (valOf (Int.fromString mon)), + offset = SOME (Time.fromSeconds (LargeInt.fromInt (valOf (Int.fromString offset) * 3600))), + second = valOf (Int.fromString second) div 1000, year = valOf (Int.fromString year)}) | [year, mon, day, hour, minute, second, _] => - Date.toTime (Date.date {day = intFromSql day, hour = intFromSql hour, minute = intFromSql minute, - month = toMonth (intFromSql mon), + Date.toTime (Date.date {day = valOf (Int.fromString day), hour = valOf (Int.fromString hour), minute = valOf (Int.fromString minute), + month = toMonth (valOf (Int.fromString mon)), offset = NONE, - second = intFromSql second, year = intFromSql year}) + second = valOf (Int.fromString second), year = valOf (Int.fromString year)}) | [year, mon, day, hour, minute, second] => - Date.toTime (Date.date {day = intFromSql day, hour = intFromSql hour, minute = intFromSql minute, - month = toMonth (intFromSql mon), + Date.toTime (Date.date {day = valOf (Int.fromString day), hour = valOf (Int.fromString hour), minute = valOf (Int.fromString minute), + month = toMonth (valOf (Int.fromString mon)), offset = NONE, - second = intFromSql second div 1000, year = intFromSql year}) + second = valOf (Int.fromString second) div 1000, year = valOf (Int.fromString year)}) | _ => raise Format ("Invalid timestamp " ^ s) end + fun timestampFromSql v = timestampFromSql' (valueOf v) fun boolToSql true = "TRUE" | boolToSql false = "FALSE" - fun boolFromSql "FALSE" = false - | boolFromSql "f" = false - | boolFromSql "false" = false - | boolFromSql "n" = false - | boolFromSql "no" = false - | boolFromSql "0" = false - | boolFromSql "" = false - | boolFromSql _ = true + fun boolFromSql' "FALSE" = false + | boolFromSql' "f" = false + | boolFromSql' "false" = false + | boolFromSql' "n" = false + | boolFromSql' "no" = false + | boolFromSql' "0" = false + | boolFromSql' "" = false + | boolFromSql' _ = true + + fun boolFromSql v = boolFromSql' (valueOf v) end structure PgClient = SqlClient(PgDriver) \ No newline at end of file diff --git a/sql_client.sig b/sql_client.sig index 9109f49..aa076f7 100644 --- a/sql_client.sig +++ b/sql_client.sig @@ -23,16 +23,16 @@ signature SQL_CLIENT = sig include SQL_DRIVER - val query : conn -> string -> string list list + val query : conn -> string -> value list list (* Get thr row results of an SQL query over a connection *) - val oneRow : conn -> string -> string list + val oneRow : conn -> string -> value list (* Make a query that must return exactly one row *) - val oneOrNoRows : conn -> string -> string list option + val oneOrNoRows : conn -> string -> value list option (* Make a query that may return zero or one row *) - val app : conn -> (string list -> unit) -> string -> unit + val app : conn -> (value list -> unit) -> string -> unit (* Behaves like List.app over the results of a query *) - val map : conn -> (string list -> 'a) -> string -> 'a list + val map : conn -> (value list -> 'a) -> string -> 'a list (* Behaves like List.map over the results of a query *) end diff --git a/sql_driver.sig b/sql_driver.sig index 350b47e..5a6bc4e 100644 --- a/sql_driver.sig +++ b/sql_driver.sig @@ -27,29 +27,32 @@ sig exception Sql of string (* All-purpose exception *) + type value + (* One value in a query result *) + val conn : string -> conn (* Connect to a server based on a string of information *) val close : conn -> unit (* Close a connection *) val dml : conn -> string -> string (* Execute a DML command over a connection, returning a result message *) - val fold : conn -> (string list * 'a -> 'a) -> 'a -> string -> 'a + val fold : conn -> (value list * 'a -> 'a) -> 'a -> string -> 'a (* Behaves like List.foldl, applied over the result rows of a query *) type timestamp = Time.time exception Format of string (* Conversions between SML values and their string representations from SQL queries *) - val isNull : string -> bool + val isNull : value -> bool val intToSql : int -> string - val intFromSql : string -> int + val intFromSql : value -> int val stringToSql : string -> string - val stringFromSql : string -> string + val stringFromSql : value -> string val timestampToSql : timestamp -> string val timestampToSqlUnquoted : timestamp -> string - val timestampFromSql : string -> timestamp + val timestampFromSql : value -> timestamp val realToSql : real -> string - val realFromSql : string -> real + val realFromSql : value -> real val boolToSql : bool -> string - val boolFromSql : string -> bool + val boolFromSql : value -> bool end