also used in the type of the exp argument, (τ′,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 => · · ·. 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. Constraint violation other than NOT NULL.
2. Overflow of integers or strings.
3. Division by zero.
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.
22.8 SQL Library: The SQL Structure
SML# provides the types and functions related to SQL as a library. All of them are included in the SQL structure provided by the sql.smi interface file. An SML# source file that uses the SQL feature must include the following lines in its interface file so that it refers to sql.smi:
_require "sql.smi"
The following is the signature of the SQL structure:
structure SQL : sig
val postgresql : string -> backend val mysql : string -> backend val odbc : string -> backend val sqlite3 : string -> backend structure SQLite3 : (see Section 22.8.1) val connect : ’a server -> ’a conn
val connectAndCreate : ’a server -> ’a conn val closeConn : ’a conn -> unit
val fetch : ’a cursor -> ’a option val fetchAll : ’a cursor -> ’a list val closeCursor : ’a cursor -> unit
val queryCommand : (’a list, ’b) query -> (’a cursor, ’b) command val toy : ((’a, ’c) db -> (’b, ’c) query) -> ’a -> ’b
val commandToString : ((’a,’c) db -> (’b,’c) command) -> string val queryToString : ((’a,’c) db -> (’b,’c) query) -> string val expToString : (’a,’c) exp -> string
Structure Op : (see Section 22.9) Structure Numeric : see Section 22.9 Structure Decimal = Numeric end
These definitions are categorized by their purposes. Each of the following subsections describes the definitions belonging to a category.
22.8.1 Connecting to a database server
• exception Connect of string
The exception of a database connection error. string is the error message.
• exception Link of string
The exception indicating the mismatch of database schema and SML# programs. string is the error message.
• type backend
The type of the untyped connection information to a database, which is a part of the _sqlserver expression. One of the following function can be used to write an expression of this type.
• val postgresql : string -> backend
22.8. SQL LIBRARY: THE SQL STRUCTURE 149
SQL.postgresql
param
returns an connection information to a PostgreSQL server. The stringparam
is the connection string of libpq, the PostgreSQL library. See the PostgreSQL manual for defails of the connection string. Ifparam
is not valid, the SQL.Connect exception is raised.The correspondence between the PostgreSQL and SML# types is the following:
PostgreSQL SML#
• val mysql : string -> backend
SQL.mysql
param
returns a connection information to a MySQL server. The stringparam
con-sists of the sequences of “key=value” separated by whitespaces. The keys avaliable and their meanings are the following:Key Description
host The hostname of a MySQL server port The port number of a MySQL server user The user name to log in a MySQL server password The password of the user
dbname The name of the target database unix_socket The filename of a UNIX socket
flags The flags of the communication protocol in decimal
dbname is mandatory. See the MySQL manual for details of these parameters. If
param
is not valid, the SQL.Connect exception is raised.The correspondence between the MySQL and SML# types are the following:
MySQL SML#
TINYINT, SMALLINT, MEDIUMINT, INT int TINYTEXT, TEXT, VARCHAR string
DOUBLE real
FLOAT real32
• val sqlite3 : string -> backend
val sqlite3’ : SQL.SQLite3.flags * string -> backend
SQL.sqlite3
filename
and SQL.sqlite3 (flags
,filename
) return a connection information to an SQLite3 database file. The stringfilename
indicates the filename. Note that SQLite3 interprets the filenames beginning with “:” in a special way.flags
is a record consisting of the following four fields:– mode: the open mode of the file, which is one of the following:
∗ SQL.SQLite3.SQLITE_OPEN_READONLY
∗ SQL.SQLite3.SQLITE_OPEN_READWRITE
∗ SQL.SQLite3.SQLITE_OPEN_READWRITE_CREATE
– threading: the threading mode, which is one of the following:
∗ SQL.SQLite3.SQLITE_OPEN_NOMUTEX
∗ SQL.SQLite3.SQLITE_OPEN_FULLMUTEX
– cache: the cache mode, which is one of the following:
∗ SQL.SQLite3.SQLITE_OPEN_SHAREDCACHE
∗ SQL.SQLite3.SQLITE_OPEN_PRIVATECACHE
– uri: the way to interpret the filename, which may be the following:
∗ SQL.SQLite3.SQLITE_OPEN_URI
See the SQLite3 C/C++ API manual for details of these flags. These constants are defined in the SQL.SQLite3 structure. In addition, SQL.SQLite3 provides the default flag SQL.SQLite3.flags, which is used if
flags
is omitted. A partially modified version of SQL.SQLite3.flags can be obatained by using the field update expression.The correspondence between SQLite3’s type affinities and SML#’s type is the following:
Type affinity SML#
Each type of columns specified in CREATE TABLE statement is interpreted to a type affinity as described in the SQLite3 manual.
• val odbc : string -> backen
SQL.odbc
param
returns a connection information to an ODBC server. The stringparam
consists of a DSN name, user name, and password in this order separated by whitespaces. Ifparam
is not valid, the SQL.Connect exception is raised.The correspondence between the ODBC and SML# types is the following
ODBC SML#
SQL.connect
server
establishes a connection to the server indicated by the connection descriptionserver
. If the connection is established and the schema of the connected database subsumes the schema represented byserver
, it returns a connection handle. If a connection error occurs, the SQL.Exec exception is raised. If the two schemas are not matched, the SQL.Link exception is raised.The type of the connection description
server
represts the type of the tables and views that the SML# program deals with through this connection. SQL.connect checks the system catalog of the database so that all tables and views in the type ofserver
exists in the database. The names of tables and views are case-insensitive during this check. The database may contain tables and views other than those specified in the type ofserver
. In contrast, for each table inserver
, its column set must exactly matches with the actual table definition.At the first time to connect to a database server, an external library is dynamically linked according to the kind of the database server. The default name of such libraries are hard-coded. If the library name is not appropriate, it can be changed by setting environment variables. The following table shows the default name and environment vairable name for each server kind:
Database Library name Environment variable PostgreSQL libpq.so.5 SMLSHARP_LIBPQ
MySQL libmysqlclient.16.so SMLSHARP_LIBMYSQLCLIENT
ODBC libodbc.so.2 SMLSHARP_LIBODBC
SQLite3 libsqlite3.so.0 SMLSHARP_LIBSQLITE3
• val connectAndCreate : ’a server -> ’a conn
Same as SQL.connect except for the following: SQL.connectAndCreate creates the tables that are indicated in the argument type but do not exist in the database by issueing the CREATE TABLE commands. If a table to be created includes an unsupported type, it raises the SQL.Link exception.
If a CREATE TABLE command fails, it raises the SQL.Exec exception.
• val closeConn : ’a conn -> unit
SQL.closeConn