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

SQL.postgresql param returns an connection information to a PostgreSQL server. The string param is the connection string of libpq, the PostgreSQL library. See the PostgreSQL manual for defails of the connection string. If param is not valid, the SQL.Connect exception is raised.

The correspondence between the PostgreSQL and SML# types is the following:

val mysql : string -> backend

SQL.mysql param returns a connection information to a MySQL server. The string param consists of the sequences of “key=value” separated by whitespaces. The keys avaliable and their meanings are the following:

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:

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 string filename 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:

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 string param consists of a DSN name, user name, and password in this order separated by whitespaces. If param is not valid, the SQL.Connect exception is raised.

The correspondence between the ODBC and SML# types is the following

val connect : ’a server -> ’a conn

SQL.connect server establishes a connection to the server indicated by the connection description server. If the connection is established and the schema of the connected database subsumes the schema represented by server, 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 of server 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 of server. In contrast, for each table in server, 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:

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 conn closes a database connection. Any connection established by SQL.connect must be closed by SQL.closeConn.

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 cursor cursor 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 cursor cursor 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.

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 $\mathrm{⟨}$pat$\mathrm{⟩}$ => select...($\mathrm{⟨}$exp$\mathrm{⟩}$) 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 regards data as a database and evaluates the query query 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 function command 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 function query 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 expression exp.