22.8.2 executing SQL queries and retrieving their results
• exception Exec of string
The exception indicating that an error occur during a query execution on a database server. string is the error message.
• val fetch : ’a cursor -> ’a option
SQL.fetch
cursor
reads one row pointed by the cursorcursor
and move the cursor to the next row. If the cursor reaches to the end of a table, it returns NONE. If the cursor is already closed, the SQL.Exec exception is raised.• val fetchAll : ’a cursor -> ’a list
SQL.fetchAll
cursor
reads all rows between the cursorcursor
and the end of the table and closes the cursor. If the cursor is already closed, the SQL.Exec exception is raised.• closeCursor : ’a cursor -> unit
closeCursor
cursor
closes the given cursor. All cursors must be closed by this function or fetchAll.22.8.3 Utilities for SQL Queries
• val queryCommand : (’a list, ’b) query -> (’a cursor, ’b) command
SQL.queryCommand
query
converts the given SELECT query to a SQL command. This function performs the same thing as what an SQL execution function _sql ⟨pat⟩ => select...( ⟨exp⟩ ) does for a query (see Section 22.7) except for the query execution.• val toy : ((’a, ’c) db -> (’b, ’c) query) -> ’a -> ’b
SQL.toy
query data
regardsdata
as a database and evaluates the queryquery
on it. The evaluation is carried out in SML# without involving any server communication. This function executes a toy program that the SML# compiler generates for the typecheck of SQL queries. Note that the performance of the toy program is not considered and therefore this function may be seriously slow.• val commandToString : ((’a,’c) db -> (’b,’c) command) -> string
SQL.commandToString
command
returns the serialized string of the SQL command the functioncommand
returns. The string this function returns is identical to the string sent to the server when the command is executed.• val queryToString : ((’a,’c) db -> (’b,’c) query) -> string
SQL.queryToString
query
returns the serialized string of the SELECT query the functionquery
returns. The string this function returns is identical to the string sent to the server when the query is executed.
• val expToString : (’a,’c) exp -> string
SQL.expToString
exp
returns the serialized string of the SQL value expressionexp
.22.9 SQL Library: The SQL.Op structure
The SQL.Op structure provides SQL’s infix operators, aggregate functions, and other utilities, all of which are used for constructing SQL value expressions. In SML#’s SQL value expressions, the infixity of some identifiers are declared as follows:
infix 7 % infix 5 like ||
nonfix mod
In an SQL value expression that is not evaluated by SML#, SQL functions and operators defined in this structure can be used without any structure prefix.
Almost all of functions defined in SQL.Op is overloaded on multiple SQL basic types (see Section 22.1). In the description, the overloaded type variables and their ranges are indicated by their names as follows:
• ’sql is one of the SQL basic types or their option types.
• ’sqlopt is the option type of an SQL basic type.
• ’num is one of the SQL basic numeric types or their option types.
• ’str is either string or string option.
• Other type variables range over the set of all types.
The signature of the SQL.Op structure is the following:
structure SQL : sig
· · ·
structure Op : sig
val Some : ’a -> ’a option
val Part : ’a option list -> ’a list val Num : ’num -> numeric option val + : ’num * ’num -> ’num
val nullif : ’sqlopt * ’sqlopt -> ’sqlopt val coalesce : ’b option * ’b -> ’b
val coalesce’ : ’b option * ’b option -> ’b option val count : ’sql list -> int
val avg : ’num list -> numeric option val sum : ’num list -> ’num option
val sum’ : ’num option list -> ’num option val min : ’sql list -> ’sql option
val min’ : ’sql option list -> ’sql option val max : ’sql list -> ’sql option
val max’ : ’sql option list -> ’sql option end
end
Each of the following subsections describes the definitions for each category of their purposes.
22.9.1 Workarounds for type inconsistencies
SML# provides the following functions corresponding to implicit type cast. See Subsection 22.4.5 for details.
• val Some : ’a -> ’a option
22.9. SQL LIBRARY: THE SQL.OP STRUCTURE 153
• val Part : ’a option list -> ’a list
• val Num : ’num -> numeric option
22.9.2 SQL operators and functions
The SQL.Op structure provides the following operators. These operators return an SQL value expression that concatinates given expressions with the operator. The comparison is not performed until the constructed query is executed on a database server.
• Comparison operators: <, >, <=, >=, =, <> are provided for any SQL basic types. The type of these operators is
’sql * ’sql -> bool3
• Arithmetic operators: Five infix operators +, -, *, /, % and two unary operators ~, abs are provided for any SQL numeric types. In the SQL value expression, % is declared as a infix identifier. The type of these operators is either
’num * ’num -> ’num or
’num -> ’num
• Modulo operation: Following the standard SQL, the modulo operator mod is also provided as a function. Note that some database engines supports only one of mod and %. In the SQL value expression, mod is declared as a nonfix identifier. The type of mod is
’num * ’num -> ’num
• String operators: The pattern match operator like and string concatination operator || are available. Both identifiers are infix operators in SQL value expressions. Their types are the following:
val like : ’str * ’str -> bool3 val || : ’str * ’str -> ’str
• NULLIF: the nullif function of the following type is provided:
val nullif : ’sqlopt * ’sqlopt -> ’sqlopt
Note that the two arguments must be an option type. Use Some if needed.
• COALESCE: Two variants coalesce and coalesce’ are provided because of the option type.
val coalesce : ’b option * ’b -> ’b
val coalesce’ : ’b option * ’b option -> ’b option
In the SQL query sent to a server, both functions have the same name COALESCE. The type of coalesce is chosen for a paticular use of COALESCE that substitutes NULL value with non-NULL values. Different from the standard SQL, COALESCE with more than two arguments is not supported. Nest coalesce’ functions for more than two values.
22.9.3 SQL aggregation functions
count, avg, sum, min, and max are available. Because of the option type, sum, min, and max function has two variants such as sum and sum’. The name sent to a server is same regardless of the variant chosen.
The type of these functions are the following:
val count : ’sql list -> int
val avg : ’num list -> numeric option val sum : ’num list -> ’num option
val sum’ : ’num option list -> ’num option val min : ’sql list -> ’sql option
val min’ : ’sql option list -> ’sql option val max : ’sql list -> ’sql option
val max’ : ’sql option list -> ’sql option