An SQL execution function _sql $\mathrm{⟨}$pat$\mathrm{⟩}$ => $\mathrm{⟨}$sqlfn$\mathrm{⟩}$ generates a function that executes the SQL command $⟨$sqlfn$\mathrm{⟩}$ on a database server. By applying this function to a connection handle to a database, the $⟨$sqlfn$\mathrm{⟩}$ expression is evaluated and the resulting SQL command is sent to the server. This function returns the execution result if the execusion is successfully done, or raises the SQL.Exec exception otherwise. This function behaves as the following:

1. 1.

   It receives a connection handle of the $\tau$ SQL.conn type as its argument.

2. 2.

   It obtains an database instance of the ($\tau$, $w$) SQL.db type from the connection handle and binds the pattern $⟨$pat$\mathrm{⟩}$ to it.

3. 3.

   It evaluates the expression $⟨$sqlfn$\mathrm{⟩}$ and obtains the command of type ($\tau {}^{\mathrm{\prime }}$, $w$) SQL.command. Exceptionally, if $⟨$sql$\mathrm{⟩}$ is of the form $⟨$sqlselect$\mathrm{⟩}$, it interprets an SELECT query of type ($\tau$, $w$) SQL.query as an SQL command of type ($\tau$ SQL.cursor, $w$) SQL.command.

4. 4.

   It serializes the command and sends it to the server.

5. 5.

   If succeeded, it returns the result of type $\tau {}^{\prime }$.

As seen in these steps, the type of the execution function is $\tau$ SQL.conn -> $\tau {}^{\mathrm{\prime }}$ when the type of the command is ($\tau$, $w$) SQL.db -> ($\tau {}^{\mathrm{\prime }}$, $w$) SQL.command. Typically, $\tau {}^{\prime }$ is either SQL.cursor if $⟨$sqlfn$\mathrm{⟩}$ is $⟨$sqlselect$\mathrm{⟩}$, or unit otherwise.

The most simple way to execute an SQL query or command is to write it in an SQL execution function directly. For example,

val q = _sql db => select #t.name as name, #t.age as age
                   from #db.employee as t
                   where #t.salary >= 300
                   order by #.age

is the function q that execute the query. To execute the query whose clauses are independently constructed, use the select...($\mathrm{⟨}$exp$\mathrm{⟩}$) notation in the body of an SQL execution function, as seen in the following:

val s = _sql select #t.name as name, #t.age as age
val f = fn db => _sql from #db.employee as t
val g = fn db => select...(s) from...(f db)
val q = _sql db => select...(q db)

For SQL commands, write it directly in an SQL execution function. For example,

val w = fn () => _sql where #employee.name = "Taro"
val c = fn db => _sql update #db.employee
                      set āge = #employee.age + 1,
                      salary = #employee.salary + 100
                      where...(w ())
val q = _sql db => ...(c db)

The typing rules of the SQL execution functions are the following:

• •

  In the type of $⟨$sqlfn$\mathrm{⟩}$, which is either ($\tau$,$w$) SQL.command or ($\tau {}^{\mathrm{\prime }}$,$w$) SQL.query, the $w$ must be the type variable occuring only in the type of $⟨$sqlfn$\mathrm{⟩}$.

This restriction is introduced to avoid queries across multiple databases. For example, the following causes a type error:

# fun f exp = _sql db => select #e.name, (...exp) from #db.employee as e;
(interactive):1.12-1.65 Error:
  (type inference 067) User type variable cannot be generalized: ’$h

The formal reason of this error is that the $w$ of ($\tau$,$w$) SQL.query, which is the type of select $\mathrm{\cdots }$, is also used in the type of the exp argument, ($\tau {}^{\mathrm{\prime }}$,$w$) SQL.exp, since exp of the argument of f is included in select, and therefore the above restriction is violated. From the practical perspective, this is an error because the exp argument may be an SQL value expression related to the database different from the db of _sql db => $\mathrm{\cdots }$. To understand the situation, consider another following function that calls f:

fun badExample conn1 conn2 =
    (_sql db2 =>
          select...((f _sql((select #t1.c1 from #db2.t1)) conn1;
                     _sql(select #t2.c2 from #db2.t2))))
      conn2

If f is a polymorphic function, the badExample function is also typed regardless of the above type restriction. The badExample function receives two connection handle conn1 and conn2, which point to possibly different databases, and executes the query of the function f and another query on conn1 and conn2, respectively. Its strange behavior is that it executes the query of f in an SQL execution function. In addition, it applies f to a subquery on db2 of conn2, not conn1. Concequently, the query executed in f refers to the two databases, one of which is referred to as db in f, and another of which is referred to as db2 in badExample. Such a query cannot be executed.

In practice, the place in which an SQL execution function can place is limited due to this restriction. In particular, as seen in the above f, a function that executes a given query as an argument is not allowed. To avoid this limitation, you should distinguish between query construction functions and query execution functions. For example, if the above f is rewritten so that it constructs a query rather than executes it as follow, then the type error is avoided:

fun f exp = fn db => _sql select #e.name, (...exp) from #db.employee as e
val q = _sql db => select...(f _sql(#e.saraly) db)

The SQL.Exec exception is raised in the following situations:

1. 1.

   Constraint violation other than NOT NULL.

2. 2.

   Overflow of integers or strings.

3. 3.

   Division by zero.

4. 4.

   The use of an SQL syntax that SML# supports but the database server cannot interpret.

Notes on 4.: the SQL syntax of SML# is designed based on the SQL99 standard extended with popular extensions among major database engines and natural extension in the sence of functional programming langauges. The compliance with the standard SQL and the detail of interpretation rules of SQL queries may depend on the implementation of the database servers. Therefore, the programmer should not use SML#’s SQL syntax with no limitation but choose its subset in accordance with the manual of the database server to be used. The following are such incompatibilities known at the time when this manual is authored:

• •

  PostgreSQL ignores AS in a FROM clause if the AS occur in a join expression labeled with AS. For example, the following query causes an error since x is undefined:

  SELECT x.col FROM (a AS x NATURAL JOIN b AS y) AS z

• •

  SQLite3 does not support the group by () notation, which is introduced in SQL99. Use group by "" instead.