*************** The sql Library *************** .. current-library:: sql .. current-module:: sql Open Dylan's SQL-ODBC library provides a generic Dylan protocol for interfacing applications to any database management system (DBMS) supporting Microsoft's Open Database ConnectivityTM (ODBC) interface and the SQL database query language. The SQL-OBDC library supports the full SQL language defined in the ANSI SQL-89 and ANSI SQL-92 specifications, as well as any extensions defined by a DBMS. A low-level interface to the Microsoft ODBC API is also available in the ODBC-FFI library. The ODBC-FFI library uses the C-FFI library and the same C-to-Dylan name mapping scheme as described in the Win32 API FFI library documentation. See the C FFI and Win32 library reference for details of that scheme. The ODBC-FFI library is otherwise undocumented. Implementation -------------- The SQL-ODBC library is built on top of a generic SQL library. This SQL library does not include the low-level code necessary to communicate with any particular DBMS. In itself, the SQL library simply provides a convenient high-level mechanism for integrating database operations into Dylan applications. It is designed to form the high-level part of "implementation libraries" that contain lower-level code to supporting a particular DBMS protocol, such as ODBC. The SQL-ODBC library is, then, one such implementation library. Our intention is that the SQL library will provide a common high-level Dylan interface to any DBMS. Applications written using the SQL-ODBC library will therefore be simple to port to any future DBMSes for which implementation libraries are written. Using the SQL-ODBC library in applications ------------------------------------------ The SQL-ODBC library is available to applications as the SQL-ODBC library, which exports the modules SQL-ODBC and SQL. (You should not need to use the SQL module, but it will be visible during debugging sessions.) Object-oriented languages and relational databases -------------------------------------------------- The SQL-ODBC library does not provide the means to "objectify" a relational database or an SQL statement. That is, it does not treat a databases or statements in an object-oriented fashion or provide support for doing so. This is because the object-oriented programming model is very different from the relational database model. The two most significant differences follow. First, the relational database model has only simple datatypes (string, integer, floating point number, and so on) and does not provide a means of defining new types, as object-oriented programming languages do. Second, objects in an object-oriented program have unique identities that allow two objects of the same value to be distinguished from one another. By contrast, SQL rows (the SQL notion nearest to the notion of an object) do not have unique identities: if two rows in a given table have identical values, they are indistinguishable. Result-retrieval protocol ------------------------- The SQL-ODBC library provides an abstract Dylan protocol for handling SQL result sets, the means by which SQL SELECT statement results are retrieved. The library allows result sets to be processed as Dylan collections. The various Dylan collection protocols and functions work as you would expect on a result set. Processing results ------------------ SQL SELECT statements return database records. You process the results of an SQL SELECT statement using a result set. Result sets are the focal point of the SQL-ODBC library's encapsulation of the protocol for retrieving database records. Using result sets allows you to concentrate on the logic of your application instead of the logic of record retrieval. Result sets retrieve their records from the database synchronously. As result sets retrieve their records, you can specify conversion of records to application-specific objects which are added to the result set in place of the record. Result sets retrieve their records one at a time. Bridging the object-relational gap ---------------------------------- Relational DBMSes do not in general deal with objects or classes. Since Dylan is an object-oriented language, this creates a gap between Dylan and the DBMS. The SQL-ODBC library bridges this gap by allowing you to specify a liaison function for results. A liaison function acts as an interpreter for results, taking the records retrieved from the relational DBMS and converting each into suitable Dylan objects. A default liaison method exists for use in situations where your application does not know the appropriate conversion, for example when processing SQL SELECT statements typed in by the application user. The default method transforms each record retrieved into a Dylan collection, where each element of the collection corresponds to a column of the record. See Section 1.5.4 on page 36 for more on liaison functions. Error handling -------------- As in any application, errors at run time can occur when applications talk to databases. The SQL-ODBC library captures the errors and warnings that a DBMS generates and signals a corresponding Dylan error or warning condition. Your application can then process the condition using the Dylan condition system. Examples used in this document ------------------------------ The following tables depict example database tables to which this document's code examples refer. .. table:: Table 1.1 Table "Book" used in this document's code examples. +-------------------------------------------------+-------------------+---------------+ | Title | Publisher | ISBN | +=================================================+===================+===============+ | An Introduction to Database Systems | Addison Wesley | 0-201-14201-5 | +-------------------------------------------------+-------------------+---------------+ | Transaction Processing: Concepts and Techniques | Morgan Kaufmann | 1-55860-190-2 | +-------------------------------------------------+-------------------+---------------+ | Fundamentals of Database Systems | Benjamin/Cummings | 0-8053-1748-1 | +-------------------------------------------------+-------------------+---------------+ | Relational Database Writings, 1991-1994 | Addison-Wesley | 0-201-82459-0 | +-------------------------------------------------+-------------------+---------------+ .. table:: Table 1.2 Table "Author" used in this document's code examples. +-----------+-----------+------------+ | Author ID | Last Name | First Name | +===========+===========+============+ | 1 | Date | Chris | +-----------+-----------+------------+ | 2 | Gray | Jim | +-----------+-----------+------------+ | 3 | Reuter | Andreas | +-----------+-----------+------------+ | 4 | Elmasri | Ramez | +-----------+-----------+------------+ | 5 | Navathe | Shamkant | +-----------+-----------+------------+ .. table:: Table 1.3 Table "Book_author" used in this document's code examples. +-----------+---------------+ | Author_ID | ISBN | +===========+===============+ | 1 | 0-201-14201-5 | +-----------+---------------+ | 2 | 1-55860-190-2 | +-----------+---------------+ | 3 | 1-55860-190-2 | +-----------+---------------+ | 4 | 0-8053-1748-1 | +-----------+---------------+ | 5 | 0-8053-1748-1 | +-----------+---------------+ | 1 | 0-201-82459-0 | +-----------+---------------+ Connecting to a database ======================== Before it can query a database, your application must connect to it. Most DBMSes operate a form of login procedure to verify connections, using a user name and password for the purpose. The popular DBMSes each have different protocols for identifying themselves, their users, their databases, and connections to those databases. The SQL-ODBC library provides a general-purpose connection protocol that is not specific to any DBMS, and represents DBMSes, databases, database connections, user names and passwords with generic Dylan classes, thereby hiding the idiosyncrasies of the various DBMSes from Dylan applications. The classes that the SQL-ODBC library defines are shown in Table 1.4. .. table:: Table 1.4 Dylan DBMS classes. +------------------------+-----------------------+-----------------------+ | Entity | Abstract Dylan class | SQL-ODBC class | +========================+=======================+=======================+ | DBMS | :class:`` | ```` | +------------------------+-----------------------+-----------------------+ | Database | :class:`` | ```` | +------------------------+-----------------------+-----------------------+ | User name and password | :class:`` | ```` | +------------------------+-----------------------+-----------------------+ | Active connection | :class:`` | ```` | +------------------------+-----------------------+-----------------------+ You should create DBMS-specific instances of these classes to connect to a database. See also :macro:`with-database`. Connection protocol functions, methods, and macros -------------------------------------------------- * :macro:`with-dbms` * :gf:`dbms` * :gf:`database` * :gf:`user` Connecting and disconnecting ---------------------------- The SQL-ODBC library provides DBMS-independent functions to connect to and disconnect from databases. Connecting to a database establishes a context (an instance of :class:``) in which SQL statements may be executed within an application. You can make connections by calling the :gf:`connect` function on a DBMS-specific instance of :class:`` and :class:``. An application can connect to multiple databases served by a DBMS if the DBMS supports the feature. Multiple-connection support can be determined by calling the :gf:`multiple-connections?` function on the DBMS object. Keeping connections open requires system resources. An application can disconnect from connections that it no longer needs in order to reduce its use of system resources. When the application terminates, the SQL-ODBC library disconnects all open connections. If a connection is not explicitly terminated using the :gf:`disconnect` generic function, and a client application has no references to it, the connection is terminated when the garbage collector notices that the object can be reclaimed. After a connection has been disconnected, the :class:`` object cannot be reused, and so references to it should be dropped. * :gf:`connect` * :gf:`connections` * :gf:`default-connection` * :gf:`disconnect` * :gf:`disconnect-all` * :macro:`with-connection` Executing SQL statements ======================== The SQL-ODBC library provides a way of processing SQL statements: the :gf:`execute` function, which you must apply to instances of the :class:`` class. * :class:`` * :gf:`execute` * :class:`` * :gf:`coercion-policy` * :gf:`coercion-policy-setter` * :gf:`datatype-hints` * :gf:`datatype-hints-setter` * :gf:`execute` * :gf:`input-indicator` * :gf:`input-indicator-setter` * :gf:`output-indicator` * :gf:`output-indicator-setter` * :gf:`text` * :gf:`text-setter` The null value -------------- SQL offers the null value to represent missing information, or information that is not applicable in a particular context. All columns of a table can accept the null value -- unless prohibited by integrity constraints -- regardless of the domain of the column. Hence, the null value is included in all domains of a relational database and can be viewed as an out-of-band value. Relational database theory adopted a three-valued logic system -- "true", "false", and "null" (or "unknown") -- in order to process expressions involving the null value. This system has interesting (and sometimes frustrating) consequences when evaluating arithmetic and comparison expressions. If an operand of an arithmetic expression is the null value, the expression evaluates to the null value. If a comparand of a comparison expression is the null value, the expression may evaluate to the null/unknown truth-value. For example: * ``a + b``, where a contains the null value or b contains the null value, evaluates to the null value * ``a + b``, where a contains the null value and b contains the null value, evaluates to the null value * ``a = b``, where a contains the null value or b contains the null value, evaluates to unknown * ``a = b``, where a contains the null value and b contains the null value, evaluates to unknown * ``a | b``, where a is true and b contains the null value, evaluates to true * ``a & b``, where a is false and b contains the null value, evaluates to false The SQL ``SELECT`` statements return records for which the ``WHERE`` clause (or ``WHERE`` predicate) evaluates to true (not to false and not to the null value). In order to test for the presence or absence of the null value, SQL provides a special predicate of the form:: column-name is [not] null The null value is effectively a universal value that is difficult to use efficiently in Dylan. To identify when null values are returned from or need to be sent to a DBMS server, the SQL-ODBC library supports indicator objects. Indicator objects indicate when a column of a record retrieved from a database contains the null value, or when a client application wishes to set a column to the null value. * :class:`` * :const:`$null-value` Input indicators and output indicators -------------------------------------- It is difficult for database applications written in traditional programming languages to represent the semantics of the null value, because it is a universal value which is in the domain of all types, and the three-valued logic system which accompanies null values does not easily translate to the two-value logic system in traditional programming languages. In Dylan, a universal value can be achieved if we ignore type specialization, but this inhibits optimization and method dispatching. Even if we were to forgo type specialization, the evaluation of arithmetic and comparison expressions is a problem since Dylan's logic system is boolean and not three-valued. Therefore, the SQL-ODBC library has a goal of identifying null values and translating them into Dylan values that can be recognized as representing null values. In order to identify null values during SQL statement processing, the :class:`` class supports an input indicator and output indicator. An input indicator is a marker value or values which identifies an input host variable as containing the null value. An output indicator is a substitution value which semantically identifies columns of a retrieved record as containing the null value. If the SQL-ODBC library encounters a null value when retrieving records from a database, and there is no appropriate indicator object, it signals a :class:`` condition. The condition is signaled from result-set functions (including the collection protocol) and not the execute function. During the execution of an SQL statement to which an input indicator value was supplied, each input host variable is compared (with the function ``\==``) to the input indicator and, if it holds the input indicator value, the null value is substituted for it. The input indicator may be a single value or a sequence of values. A single value is useful when it is in the domain of all input host variables; if the host variables have not been specialized, any newly created value will do. Otherwise, a sequence of values must be used. Input indicators that are general instances of :drm:`` use their positional occurrence within the SQL statement as the key for the sequence. The SQL SELECT statement is the only SQL statement that returns non- status results back to the client application. During the retrieval of these results, the SQL-ODBC library substitutes the output indicator, if supplied, for null values found in the columns of each record. The output indicator may be a single value or a sequence of values. If the output indicator is a general instance of :drm:``, the element of the sequence whose key corresponds to the column index is used as the substitution value. Otherwise, the output indicator value itself is used as the substitution value. Data retrieval using result-set collection ========================================== Executing an SQL SELECT statement by invoking the execute function on the instance of :class:`` that represents the statement yields a result set. A result set is a Dylan collection which encapsulates the protocol necessary to retrieve data from a database. The SQL-ODBC library defines two subclasses of :class:`` that provide different behaviors and performance characteristics. The type of the result set returned by the execute function is determined by the result-set policy supplied to the function or macro. There are two subclasses of :class:``: :class:`` and :class:``. The :class:`` class provides an efficient means of accessing the elements of a result set. Efficiency is achieved by performing minimal processing on each record retrieved and by maintaining in memory only the current record. Implicit in this behavior is that records you have accessed previously are no longer available to your application; if you maintain references to previous records behavior is unpredictable. The key for each access must always be greater than or equal to the previous access's key; otherwise, a condition is signaled. The :class:`` class allows your application to access elements of the result-set collection in any order, meaning that records you have accessed previously can be revisited. Scrollable result sets retrieve records synchronously. Example: This example returns a list of authors who have published two or more books. .. code-block:: dylan (result-set-policy: make()) select last_name, first_name, count(*) from author, book_author where book_author.author_id = author.author_id group by last_name, first_name having count(*) > 2 end; => #(#("Date", "Chris", 2)) let query = make(, text: "select last_name, first_name, count(*)" "from author, book_author" "where book_author.author_id" "= author.author_id" "group by last_name, first_name having" "count(*) >= 2"); execute(query, result-set-policy: $scrollable-result-set-policy); Result-set collections ====================== A result-set collection, in spirit, contains the result of an SQL ``SELECT`` statement. To provide these results, result-set collections and their methods control the retrieval of elements from the database. Each element of a result set is a record and each element of a record is a value. The SQL-ODBC library does not provide any classes to represent columns; the elements of a record are just Dylan objects. Result-set classes, in conjunction with the methods defined on them, provide a protocol to retrieve data from a database. Result-sets do not necessarily contain the records from the database. A result set could cache a small subset of the records retrieved for performance reasons. The logic for retrieving a record from a result set (from the database) is as follows: 1. Perform an internal fetch: values are stored into bindings established during SQL statement preparation. A record object is created during the preparation of the SQL statement which represents the values of the record (collection of values). 2. Invoke the liaison method on the record object. The result of the liaison function is the result of the collection access. The columns of a record are processed when the columns are retrieved from the record object. This includes checking for null values and performing data coercion if a :gf:`coercion-policy` is supplied. Record class ------------ An instance of the :class:`` class is a placeholder for records retrieved from the database. The record class is a collection whose elements are the columns of the records retrieved from the database. If the record object has a coercion policy (obtained through the :gf:`result-set-policy`), datatype coercion is performed on the elements of the record object as they are retrieved from the collection. The elements of a record collection are ephemeral under the result-set retrieval protocol: the values for the elements of the collection can change when the next record of the result set is accessed. A result set may maintain more than one record object to improve performance. Record collections support the forward- and backward-iteration protocols. The result of calling :drm:`type-for-copy` on the :class:`` class is :class:``. Applications cannot instantiate the :class:`` class. However, the functions returned by the forward- and backward-iteration protocol methods on the result-set classes return instances of this class. The values in a record object have a short lifespan: they are only valid until the next fetch is performed. See also: * :class:`` * :class:`` Result-set policy class ----------------------- Applications use result-set policy classes to specify the behavior and performance characteristics of a result set, as well as its type. The type of the result set is determined by the result-set policy object. The type of the record object is determined by the :gf:`coercion-policy` slot of :class:``. If ``result-set-policy.scrollable?`` is :drm:`#t`, the result set will be an instance of :class:`` otherwise it will be an instance of :class:``. If ``statement.coercion-policy ~= $no-coercion`` then the record will be an instance of :class:``; otherwise, it will be an instance of :class:``. .. table:: Table 1.5 Result set policies and classes. +-------------+-----------------+------------------------------------+ | Scrollable? | Coercion policy | Result set class | +=============+=================+====================================+ | :drm:`#f` | :drm:`#f` | :class:`` | +-------------+-----------------+------------------------------------+ | :drm:`#t` | - | :class:`` | +-------------+-----------------+------------------------------------+ See also: * :class:`` Result-set classes ------------------ Result-sets are the focal point for the encapsulation of the protocol required to retrieve records from a database. The SQL-ODBC library provides three result-set classes with different performance and behavioral characteristics. These classes are :class:``, :class:``, and :class:``. Liaison functions ----------------- Liaison functions convert records retrieved from a database query to Dylan objects. These functions bridge the conceptual gap between relational databases and Dylan's object-orientation. To create a Dylan object from a retrieved record, the liaison function must understand the form of the records coming from the database and the mappings of records to Dylan objects. These Dylan objects make up the elements of the result set: the results of the liaison function are added to the result set each time it is called. As your application iterates over a result set, the liaison function provides the objects that the application processes. If you do not provide a liaison function for a result set, the SQL- ODBC library supplies a ``default-liaison`` function to perform the conversion. If a coercion policy is provided, the ``default-liaison`` function is :drm:`copy-sequence`. The new sequence is safe in that it is a normal Dylan collection with no relationship to databases, SQL statements, or result sets. If a coercion policy is not provided, the ``default-liaison`` is the identity function. You can specify the identity function as the liaison function to process the actual record objects. If no type coercion is performed by the functions on the record class, this function will have the lowest overhead, but there are some restrictions: the values retrieved from the record may become invalid when the state of the iteration protocol changes. The liaison function can, potentially, cause the greatest number of problems for an application using SQL-ODBC since there is no type safety between the liaison function, the record class and the SQL ``SELECT`` statement. You must ensure that the liaison function is in sync with the SQL ``SELECT`` statement since there is no support in SQL-ODBC for this. Example: .. code-block:: dylan define class () slot title :: , init-keyword: title:; slot publisher :: , init-keyword: publisher:; slot isbn :: , init-keyword: isbn:; slot author :: , init-keyword: author:; end class; begin let booker = method (record :: ) => (book :: ) let (title, publisher, isbn, last_name, first_name) = apply(values, record); make(, title: title, publisher: publisher, isbn: isbn, author: concatenate(last_name, ", ", first_name)); end method; let query = make(, statement: "select title, publisher, isbn, last_name, first_name from book, author, book_author where book.isbn = book_author.isbn and book_author.author_id = author.author_id order by author.last_name, author.first_name"); execute(query, liaison: booker result-set-policy: make()); end; Coercion policies ----------------- In the SQL-ODBC library, the element method on the record class encapsulates all coercions of data retrieved from a database. This method can return columns with or without coercion: as low-level SQL data-types (no conversion), as Dylan data-types, or as user-defined types. The ``coercion-policy:`` init-keyword of the :class:`` class determines this behavior. If the ``coercion-policy:`` init-keyword is :const:`$no-coercion`, coercions are not performed. Hence, your application will be processing objects with low-level SQL datatypes. This option has the lowest overhead but the most restrictions: the values returned from the element method may not be valid (values may change as storage may be reused) after the next call to the ``next-state`` method returned by ``forward-iteration-protocol``. The value of :const:`$default-coercion` for the ``coercion-policy:`` init-keyword (the default value) indicates that default coercion should be performed: the data retrieved from the database is coerced to the corresponding Dylan objects. A sequence for the ``coercion-policy:`` init-keyword instructs the SQL library to perform specific data coercion on the data retrieved from the database. Essentially, each element of the limited sequence is a data coercion function which will be invoked using the objects returned from the database as the argument. When there is a one-to-one correspondence between an SQL datatype and a built-in or user-defined Dylan datatype, use the :class:`` class to perform the conversion. When multiple columns define a Dylan object or one column defines multiple Dylan objects, use the liaison function to perform the conversion. Data types and conversions ========================== The datatypes that relational DBMSes use are different from those Dylan uses. The SQL-ODBC library provides classes that represent these low-level relational datatypes, along with a table that defines the mapping from these datatypes to Dylan datatypes (Table 1.6). The methods on the record class consult this mapping when performing data coercion. The datatypes of host variables are limited to the Dylan datatypes that appear in Table 1.6. Host variables come in two flavors: read and write. Host variables appearing in an into clause of an SQL ``SELECT`` statement are write parameters, and all other host variables are read parameters. .. table:: Table 1.6 Mapping from DBMS to Dylan datatypes +-------------------+------------------------------+--------------------------+ | DBMS type | SQL type | Dylan type | +===================+==============================+==========================+ | sql_char | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_varchar | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_longvarchar | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_decimal | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_numeric | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_bit | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_tinyint | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_smallint | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_integer | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_bigint | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_real | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_float | :class:`` | :drm:``, | | | | :drm:`` or | | | | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_double | :class:`` | :drm:`` | +-------------------+------------------------------+--------------------------+ | sql_binary | :class:`` | :class:`` | +-------------------+------------------------------+--------------------------+ | sql_varbinary | :class:`` | :class:`` | +-------------------+------------------------------+--------------------------+ | sql_longvarbinary | :class:`` | :class:`` | +-------------------+------------------------------+--------------------------+ | sql_date | :class:`` | :class:`` | +-------------------+------------------------------+--------------------------+ | sql_time | :class:`` | :class:`