The sql Library

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 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 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 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 1.4 Dylan DBMS classes.

Entity

Abstract Dylan class

SQL-ODBC class

DBMS

<dbms>

<odbc-dbms>

Database

<database>

<odbc-database>

User name and password

<user>

<odbc-user>

Active connection

<connection>

<odbc-connection>

You should create DBMS-specific instances of these classes to connect to a database.

See also with-database.

Connection protocol functions, methods, and macros

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 <connection>) in which SQL statements may be executed within an application. You can make connections by calling the connect function on a DBMS-specific instance of <database> and <user>.

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 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 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 <connection> object cannot be reused, and so references to it should be dropped.

Executing SQL statements

The SQL-ODBC library provides a way of processing SQL statements: the execute function, which you must apply to instances of the <sql-statement> class.

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.

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 <sql-statement> 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 <data-exception> 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 <sequence> 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 <sequence>, 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 <sql-statement> 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 <result-set> 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 <result-set>: <forward-only-result-set> and <scrollable-result-set>.

The <forward-only-result-set> 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 <scrollable-result-set> 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.

(result-set-policy: make(<scrollable-result-set-policy>))
    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(<sql-statement>,
                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 coercion-policy is supplied.

Record class

An instance of the <record> 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 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 type-for-copy on the <record> class is <simple-object-vector>.

Applications cannot instantiate the <record> 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:

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 coercion-policy slot of <sql-statement>.

If result-set-policy.scrollable? is #t, the result set will be an instance of <scrollable-result-set> otherwise it will be an instance of <forward-only-result-set>. If statement.coercion-policy ~= $no-coercion then the record will be an instance of <coercion-record>; otherwise, it will be an instance of <record>.

Table 1.5 Result set policies and classes.

Scrollable?

Coercion policy

Result set class

#f

#f

<forward-only-result-set>

#t

<scrollable-result-set>

See also:

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 <result-set>, <forward-only-result-set>, and <scrollable-result-set>.

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

define class <book> (<object>)
  slot title :: <string>, init-keyword: title:;
  slot publisher :: <string>, init-keyword: publisher:;
  slot isbn :: <string>, init-keyword: isbn:;
  slot author :: <string>, init-keyword: author:;
end class;

begin
  let booker =
    method (record :: <record>) => (book :: <book>)
      let (title, publisher, isbn, last_name, first_name) =
        apply(values, record);

      make(<book>, title: title, publisher: publisher,
           isbn: isbn, author: concatenate(last_name, ", ",
           first_name));
    end method;
let query = make(<sql-statement>,
                 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(<forward-only-result-set-policy>));
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 <sql-statement> class determines this behavior.

If the coercion-policy: init-keyword is $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 $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 <record> 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 1.6 Mapping from DBMS to Dylan datatypes

DBMS type

SQL type

Dylan type

sql_char

<sql-char>

<character>

sql_varchar

<sql-varchar>

<string>

sql_longvarchar

<sql-longvarchar>

<string>

sql_decimal

<sql-decimal>

<string>

sql_numeric

<sql-numeric>

<string>

sql_bit

<sql-bit>

<integer>

sql_tinyint

<sql-tinyint>

<integer>

sql_smallint

<sql-smallint>

<integer>

sql_integer

<sql-integer>

<integer>

sql_bigint

<sql-bigint>

<integer>

sql_real

<sql-real>

<single-float>

sql_float

<sql-float>

<single-float>, <double-float> or <extended-float>

sql_double

<sql-double>

<double-float>

sql_binary

<sql-binary>

<binary>

sql_varbinary

<sql-varbinary>

<binary>

sql_longvarbinary

<sql-longvarbinary>

<binary>

sql_date

<sql-date>

<date>

sql_time

<sql-time>

<time>

sql_timestamp

<sql-timestamp>

<timestamp>

To retrieve integer elements from databases that may contain more than 30-bit data, you must use the generic-arithmetic library or a run-time error will occur. The Dylan SQL-ODBC library must also be prepared.

Example library and module definition:

define library sql-example
  use common-dylan;
  use generic-arithmetic;
  use sql-odbc;

  export sql-example;
end library;

define module sql-example
  use generic-arithmetic-common-dylan;
  use sql-odbc;
end module;

Warning and error conditions

The SQL-ODBC library defines condition classes for each category of error and warning defined in SQL-92. (SQL-92 calls them classes rather than categories.)

When an error or warning occurs, SQL-ODBC detects it, creates a condition object, and signals it. You can then handle the condition using the Dylan condition system.

Some DBMSes can detect and report multiple errors or warnings during the execution of a single SQL statement. The DBMS reports these errors and warnings to the SQL-ODBC library using SQL-92’s concept of diagnostics; the first error or warning in the diagnostic area is the same error or warning indicated by the SQLSTATE status parameter. The SQL-ODBC library signals a condition which corresponds to the error or warning indicated by SQLSTATE.

While handling the first condition, your application can process any additional errors or warnings that may have occurred by signaling the next DBMS condition; to obtain the next DBMS condition, call next-dbms-condition on the condition being handled.

Diagnostics

SQL-92 defines a diagnostics area as a DBMS-managed data structure that captures specific information about the execution of a SQL statement, with the exception of the GET DIAGNOSTICS statement. A diagnostics area consists of two sections, a header and a collection of diagnostic details.

The header contains information about the last SQL statement executed, while the diagnostic details contain information about each error or warning that resulted from the execution of the SQL statement.

The size of the diagnostic details section is the default value for the DBMS implementation. This size is always greater than one, since the first diagnostic detail corresponds to sqlstate. A DBMS may only fill in one diagnostic detail regardless of the number of errors or warnings that occur. If multiple diagnostic details are filled in, there is no presumption of precedence or importance.

The SQL-ODBC library provides wrapper classes for these constructs and accessors for the information they represent.

See also:

Database introspection

The SQL-ODBC library offers introspection features to allow you to determine the structure of a database at run time. A database structure is a hierarchy comprising catalogs, schemas, tables and columns. A catalog is a named collection of schemas, a schema is a named collection of tables, and a table is a named collection of columns. For security reasons, the SQL-ODBC library does not provide any means of obtaining a list of databases available from a particular DBMS; your application must provide access to a particular database via a connection object.

For DBMSes which do not support catalogs or schemas, the SQL-ODBC library uses a default value that your application can use to perform introspection.

Database objects and integrity constraints

You can interrogate schema and table database objects for a collection of constraints defined against them. A constraint is a data integrity rule which the DBMS enforces at all times. These constraints are unique, primary key, referential and check.

The unique constraint specifies that one or more columns within a table must have a unique value or set of values for each record in the table (however, the set of columns are not necessarily a key). The primary key constraint is similar to the unique constraint, except the set of columns must uniquely identify records within the table.

The referential constraint specifies the relationship between a column or a group of columns in one table to another table; this constraint also specifies the action to take when records within the table are updated or deleted.

Finally, the check constraint is a general constraint on a table which must never be false and, due to three-valued logic, an unknown or null value will satisfy the constraint.

An assertion is a constraint on a schema. It is similar to the check constraint but it normally involves more than one table. The significant difference between an assertion and a check is that an assertion must always be true, whereas a check must never be false.

The nullability of a column is a column constraint which can be determined by introspection on the desired column.

Syntactically, SQL-92 supports table and column constraints; semantically, however, all constraints are enforced at the table level.

The SQL module

$default-coercion Constant
$default-result-set-policy Constant
$diagnostic-table Constant
$no-coercion Constant
$no-indicator Constant
$null-value Constant
Discussion:

References the canonical null value. It is an instance of <null-value>.

$read-committed Constant
$read-only Constant
$read-uncommitted Constant
$read-write Constant
$repeatable-read Constant
$scrollable-result-set-policy Constant
$serializable Constant
*all-connections* Constant
*all-connections-lock* Constant
<ambiguous-cursor-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<assertion-constraint> Abstract Class
Superclasses:

<constraint>

<cardinality-violation> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<catalog-not-found> Class
Superclasses:

<database-object-not-found>

Init-Keywords:

  • catalog-name

<catalog> Open Abstract Class
Superclasses:

<database-object>, <result-set>

Init-Keywords:

  • connection

<character-not-in-repertoire> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<check-constraint> Abstract Class
Superclasses:

<constraint>

<coercion-policy> Constant

Determines what data coercion is to be performed on a result set.

<coercion-record> Open Abstract Class
Superclasses:

<record>

Init-Keywords:

  • record-coercion-policy

<column> Open Abstract Class
Superclasses:

<database-object>

Init-Keywords:

  • default-value

  • domain

  • nullable?

<connection-does-not-exist> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<connection-exception> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<connection-failure> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<connection-name-in-use> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<connection-not-specified> Open Class
Superclasses:

<error>

<connection> Open Abstract Class
Superclasses:

<object>

Init-Keywords:

  • dbms

Discussion:

The <connection> class represents a database connection. More formally, we can say that it identifies a context in which a client application can execute SQL statements. The exact composition of a connection depends on the DBMS and the client platform. Implementation libraries like SQL-ODBC define a subclass of <connection> that implements the necessary requirements to identify the execution context to the client application.

<constraint> Abstract Class
Superclasses:

<database-object>

<cursor-operation-conflict> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<cursor-specification-cannot-be-executed> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<data-exception> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<data-not-available> Open Class
Superclasses:

<error>

<database-collection> Open Abstract Class
Superclasses:

<sequence>

<database-error> Open Abstract Class
Superclasses:

<error>

<database-object-not-found> Abstract Class
Superclasses:

<diagnostic>

<database-object> Abstract Class
Superclasses:

<object>

Init-Keywords:

  • name

<database-statement> Open Abstract Class
Superclasses:

<object>

Discussion:

This class represents statements which can be executed by a DBMS server.

<database> Open Abstract Class
Superclasses:

<object>

Discussion:

The <database> class identifies a database to a DBMS. Exactly what a database is depends on the DBMS in use. Implementation libraries like SQL-ODBC supply an instantiable subclass of <database> to provide whatever implementation is necessary for identifying a database to a specific DBMS.

<datetime-field-overflow> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<dbms-not-specified> Open Class
Superclasses:

<error>

<dbms> Open Abstract Class
Superclasses:

<object>

Discussion:

The <dbms> class identifies a database management system (DBMS) to a client application. Implementation libraries like SQL-ODBC supply an instantiable subclass of <dbms> to provide whatever implementation is necessary for identifying a DBMS to an application.

<dependent-privilege-descriptors-still-exist> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<diagnostic-table> Class
Superclasses:

<object>

Init-Keywords:

  • general-key

<diagnostic> Open Abstract Class
Superclasses:

<condition>

Init-Keywords:

  • class-code

  • condition-number

  • subclass-code

<disconnect-error> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<division-by-zero> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<dynamic-sql-error> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<empty-result-set> Open Class
Superclasses:

<result-set>

Init-Keywords:

  • liaison

<error-in-assignment> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<feature-not-supported> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<forward-only-result-set> Open Abstract Class

The class for result sets that support a one-shot forward iteration protocol.

Superclasses:

<result-set>

Discussion:

Instances of this class represent the results of an SQL SELECT statement, and support a one-shot forward-iteration-protocol. By one-shot, we mean each element of the collection can be visited only once, and no previously visited element can be revisited. A condition is signaled if the application tries to revisit a record. Thus, backward-iteration-protocol is not supported on this collection.

This collection class is useful when the result of a query is large and each element can be processed individually.

The function type-for-copy returns <simple-object-vector> when applied to objects of this class.

See also:

<implicit-zero-bit-padding> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<index> Open Abstract Class
Superclasses:

<database-object>

Init-Keywords:

  • indexed-table

  • unique-index?

<indicator-overflow> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<indicator-policy> Constant
<insufficient-item-descriptor-areas> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<integrity-constraint-violation> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<interval-field-overflow> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-argument> Open Class
Superclasses:

<error>

<invalid-authorization-specification> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-catalog-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-character-set-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-character-value-for-cast> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-condition-number> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-cursor-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-datatype-hint> Open Class
Superclasses:

<warning>

Init-Keywords:

  • datatype-hint

<invalid-datetime-format> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-descriptor-count> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<invalid-descriptor-index> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<invalid-escape-character> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-escape-sequence> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-fetch-sequence> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-parameter-value> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-schema-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-sql-descriptor-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-sql-statement-name> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-time-zone-displacement-value> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<invalid-transaction-state> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<invalid-transaction-termination> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<isolation-level> Constant
<multiple-server-transaction> Open Class
Superclasses:

<feature-not-supported>

Init-Keywords:

  • subclass-code

<no-data> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<null-value-eliminated-in-set-function> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<null-value-no-indicator-parameter> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<null-value> Open Class
Superclasses:

<object>

Discussion:

Instances of this class represent the canonical null value. This class is the root class for all null-value classes.

<numeric-value-out-of-range> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<prepared-statement-not-a-cursor-specification> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<privilege-not-granted> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<privilege-not-revoked> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<query-expression-too-long-for-information-schema> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<record> Open Abstract Class

The class of records retrieved from a DBMS table as the result of executing an SQL SELECT statement.

Superclasses:

<database-collection>

Init-Keywords:

  • indicator-policy

The class of records retrieved from a DBMS table as the result of executing an SQL SELECT statement.

Instances of this class represent a record that was retrieved from a DBMS table as the result of executing an SQL SELECT statement.

If the value passed to coercion-policy: is a sequence whose size is less than the degree of the record, the extra columns are converted to their equivalent Dylan type using the default coercion. If the size of the sequence is greater than the degree of the record, the extra elements of the sequence are ignored.

<referential-constraint> Abstract Class
Superclasses:

<constraint>

<remote-database-access> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<restricted-data-type-attribute-violation> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<result-set-mutation-error> Open Class
Superclasses:

<error>

<result-set-policy> Open Class

Specifies the behavior and performance characteristics of a result set.

Superclasses:

<object>

Init-Keywords:

  • asynchronous

  • rowset-size – An instance of type-union(<integer>, #"all").

  • scroll-window – An instance of <integer>. A cache size hint.

  • scrollable – An instance of <boolean>. Default value: #f.

Specifies the behavior and performance characteristics of a result set.

The rowset-size slot is the number of records to retrieve each time an internal fetch is performed. If rowset-size is #"all", all records are retrieved the first time a fetch is performed. Currently, rowset-size is ignored.

<result-set> Open Abstract Class
Superclasses:

<database-collection>

Init-Keywords:

  • liaison

Discussion:

Instances of this class represent the results of an SQL SELECT statement.

This class is the root class for all result-set classes. The type-for-copy function returns <simple-object-vector> for objects of this class.

See also:

<schema-not-found> Class
Superclasses:

<database-object-not-found>

Init-Keywords:

  • schema-name

<schema> Open Abstract Class
Superclasses:

<database-object>, <result-set>

<scrollable-result-set> Open Abstract Class

The class for result sets that support both forward and backward iteration.

Superclasses:

<result-set>

Discussion:

Instances of this class support both the forward- and backward-iteration-protocol.

The type-for-copy function returns <simple-object-vector> for objects of this class.

See also:

<search-condition-too-long-for-information-schema> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<sql-bigint> Open Class
Superclasses:

<sql-datatype>

<sql-binary> Open Class
Superclasses:

<sql-datatype>

<sql-bit-varying> Open Class
Superclasses:

<sql-datatype>

<sql-bit> Open Class
Superclasses:

<sql-datatype>

<sql-character-varying> Open Class
Superclasses:

<sql-datatype>

<sql-character> Open Class
Superclasses:

<sql-datatype>

<sql-client-unable-to-establish-connection> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<sql-datatype> Open Abstract Class
Superclasses:

<object>

<sql-date> Open Class
Superclasses:

<sql-datatype>

<sql-day-time-interval> Open Class
Superclasses:

<sql-datatype>

<sql-decimal> Open Class
Superclasses:

<sql-datatype>

<sql-double-precision> Open Class
Superclasses:

<sql-datatype>

<sql-double> Open Class
Superclasses:

<sql-datatype>

<sql-error> Open Abstract Class
Superclasses:

<database-error>

<sql-float> Open Class
Superclasses:

<sql-datatype>

<sql-integer> Open Class
Superclasses:

<sql-datatype>

<sql-longvarbinary> Open Class
Superclasses:

<sql-datatype>

<sql-longvarchar> Open Class
Superclasses:

<sql-datatype>

<sql-national-character-varying> Open Class
Superclasses:

<sql-character-varying>

<sql-national-character> Open Class
Superclasses:

<sql-character>

<sql-numeric> Open Class
Superclasses:

<sql-datatype>

<sql-real> Open Class
Superclasses:

<sql-datatype>

<sql-server-rejected-establishment-of-connection> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<sql-smallint> Open Class
Superclasses:

<sql-datatype>

<sql-statement> Open Abstract Class
Superclasses:

<database-statement>

Init-Keywords:

  • coercion-policy – An instance of false-or(<coercion-policy>). The coercion policy is a sequence of functions, or the value $default-coercion, or the value $no-coercion, used to perform data coercion when the SQL statement to be executed is a SELECT statement.

  • datatype-hints – An instance of false-or(<sequence>). This is a hint for parameter binding when the SQL statement to be executed is a SELECT statement.

  • input-indicator – An instance of <object>. The input indicator is a marker value used to identify null values in host variables.

  • output-indicator – An instance of <object>. The output indicator is a substitution value to be used whenever the column of a retrieved record contains the null value.

  • text – An instance of <string>. Required. Contains the text of the SQL statement. If you want to include host variables, place a question mark (?) at the point in the string at which you want a host variable to be substituted.

Discussion:

The <sql-statement> class represents SQL statements and their indicator values and coercion policy. You can use this class to represent any SQL statement, be it static or dynamic. You can send SQL statements to the DBMS for execution by calling the execute function on an instance of <sql-statement>. The execute function returns the results of executing the SQL statement, if there are any.

In the make method on <sql-statement>, you can specify that values should be substituted into the SQL statement when it is executed. You do not specify the values until calling execute on the statement, when you can pass the substitution values with the parameter: keyword.

The values are substituted wherever a question mark (?) occurs in the SQL statement string. We call the question marks anonymous host variables because there is no Dylan variable name. Substitution occurs positionally: the first value replaces the first anonymous host variable, the second value replaces the second anonymous host variable, and so on. If the number of values is greater than the number of anonymous host variables, the extra parameters are ignored. If the number of anonymous host variables is greater than the number of parameters, a condition is signaled.

When the SQL statement is SELECT, you can also specify a result-set policy and a liaison function in the call to execute. A result-set policy describes behavioral and performance characteristics of the result-set object that the execute function returns. A liaison function creates Dylan objects from the records retrieved from the database. These objects become the elements of the result set instead of the record object.

<sql-table> Open Abstract Class
Superclasses:

<database-object>, <result-set>

<sql-time-with-time-zone> Open Class
Superclasses:

<sql-datatype>

<sql-time> Open Class
Superclasses:

<sql-datatype>

<sql-timestamp-with-time-zone> Open Class
Superclasses:

<sql-datatype>

<sql-timestamp> Open Class
Superclasses:

<sql-datatype>

<sql-tinyint> Open Class
Superclasses:

<sql-datatype>

<sql-type-timestamp> Open Class
Superclasses:

<sql-datatype>

<sql-unknown-type> Open Class
Superclasses:

<sql-datatype>

<sql-unsupported-type> Open Class
Superclasses:

<sql-datatype>

<sql-varbinary> Open Class
Superclasses:

<sql-datatype>

<sql-warning> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<sql-year-month-interval> Open Class
Superclasses:

<sql-datatype>

<statement-completion-unknown> Open Class
Superclasses:

<transaction-rollback>

Init-Keywords:

  • subclass-code

<string-data-length-mismatch> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<string-data-right-truncation> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<substring-error> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<successful-completion> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<syntax-error-or-access-rule-violation-in-direct-sql-statement> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<syntax-error-or-access-rule-violation-in-dynamic-sql-statement> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<syntax-error-or-access-rule-violation> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<table-not-found> Class
Superclasses:

<database-object-not-found>

Init-Keywords:

  • table-name

<transaction-mode> Constant
<transaction-resolution-unknown> Open Class
Superclasses:

<connection-exception>

Init-Keywords:

  • subclass-code

<transaction-rollback-due-to-integrity-constraint-violation> Open Class
Superclasses:

<transaction-rollback>

Init-Keywords:

  • subclass-code

<transaction-rollback-due-to-serialization-failure> Open Class
Superclasses:

<transaction-rollback>

Init-Keywords:

  • subclass-code

<transaction-rollback> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<transaction> Open Class
Superclasses:

<object>

Init-Keywords:

  • diagnostics-size

  • isolation-level

  • transaction-mode

<triggered-data-change-violation> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

<trim-error> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<unhandled-diagnostic> Open Class
Superclasses:

<sql-error>

Init-Keywords:

  • diagnostic

<unique-constraint> Abstract Class
Superclasses:

<constraint>

<unknown-sqlstate> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

  • sqlstate

  • subclass-code

<unterminated-c-string> Open Class
Superclasses:

<data-exception>

Init-Keywords:

  • subclass-code

<user> Open Abstract Class
Superclasses:

<object>

Discussion:

The <user> class identifies a user to a DBMS. Exactly what a “user” means depends on the DBMS. Implementation libraries like SQL-ODBC supply an instantiable subclass of <user> to provide whatever implementation is necessary for identifying a user to a specific DBMS.

When connecting to a DBMS that did not have any users per se, instances of <user> would merely satisfy the API protocol, and would not identify a specific user – any instance of <user> would identify all users to the DBMS. However, most DBMSes do require a user name and password to identify a specific user. Indeed, some DBMSes require stringent authorization information in order to identify a user, such as multiple passwords.

<using-clause-does-not-match-dynamic-parameter-specification> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<using-clause-does-not-match-target-specification> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<using-clause-required-for-dynamic-parameters> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<using-clause-required-for-result-fields> Open Class
Superclasses:

<dynamic-sql-error>

Init-Keywords:

  • subclass-code

<warning-cursor-operation-conflict> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<warning-string-data-right-truncation> Open Class
Superclasses:

<sql-warning>

Init-Keywords:

  • subclass-code

<with-check-option-violation> Open Class
Superclasses:

<diagnostic>

Init-Keywords:

  • class-code

acquire-null-value Generic function
Signature:

acquire-null-value (indicator index) => (null-value)

Parameters:

  • indicator – An instance of <object>.

  • index – An instance of <integer>.

Values:

  • null-value – An instance of <object>.

asynchronous Generic function
Signature:

asynchronous (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

catalog-from-name Open Generic function
Signature:

catalog-from-name (connection name) => (catalog)

Parameters:

  • connection – An instance of <connection>.

  • name – An instance of <string>.

Values:

  • catalog – An instance of <catalog>.

catalog-name Open Generic function
Signature:

catalog-name (diag) => (catalog-name)

Parameters:
Values:

  • catalog-name – An instance of <string>.

catalogs Open Generic function
Signature:

catalogs (#key connection) => (result-set)

Parameters:
Values:
catalogs-assist Open Generic function
Signature:

catalogs-assist (connection) => (result-set)

Parameters:
Values:
class-code Generic function
Signature:

class-code (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

class-origin Open Generic function
Signature:

class-origin (diag) => (class-origin)

Parameters:
Values:

  • class-origin – An instance of <string>.

coercion-policy Generic function
Signature:

coercion-policy (sql-statement) => (coercion-policy)

Parameters:
Values:
Discussion:

Returns the coercion policy for sql-statement. This method is only relevant to SQL SELECT statements.

coercion-policy-setter Generic function
Signature:

coercion-policy-setter (new-coercion-policy sql-statement) => (new-coercion-policy)

Parameters:
Values:
Discussion:

Sets the coercion-policy slot of sql-statement to new-coercion-policy.

column-name Open Generic function
Signature:

column-name (diag) => (column-name)

Parameters:
Values:

  • column-name – An instance of <string>.

command-function Open Generic function
Signature:

command-function (diag) => (command-function)

Parameters:
Values:

  • command-function – An instance of <string>.

commit-transaction Open Generic function
Signature:

commit-transaction (transaction) => ()

Parameters:
condition-number Generic function
Signature:

condition-number (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

conditions-not-recorded? Open Generic function
Signature:

conditions-not-recorded? (diag) => (not-recorded-status)

Parameters:
Values:

  • not-recorded-status – An instance of <boolean>.

connect Open Generic function
Signature:

connect (database user) => (connection)

Parameters:
Values:
connect-with-prompt Open Generic function
Signature:

connect-with-prompt (dbms #key database user) => (connection)

Parameters:

  • dbms – An instance of <dbms>.

  • database (#key) – An instance of false-or(<database>).

  • user (#key) – An instance of false-or(<user>).

Values:
connect-with-prompt? Open Generic function
Signature:

connect-with-prompt? (dbms) => (connect-with-prompt-status)

Parameters:

  • dbms – An instance of <dbms>.

Values:

  • connect-with-prompt-status – An instance of <boolean>.

connection Open Generic function
Signature:

connection (o) => (result)

Parameters:
Values:
connection-name Open Generic function

Returns the name of the connection that was used to execute the SQL statement.

Signature:

connection-name (diag) => (connection-name)

Parameters:
Values:

  • connection-name – An instance of <string>.

connection-setter Open Generic function
Signature:

connection-setter (c o) => (result)

Parameters:
Values:
connections Open Generic function
Signature:

connections (#key dbms) => (connection-sequence)

Parameters:

  • dbms (#key) – An instance of false-or(<dbms>).

Values:

  • connection-sequence – An instance of <sequence>.

constraint-catalog Open Generic function
Signature:

constraint-catalog (diag) => (constraint-catalog)

Parameters:
Values:

  • constraint-catalog – An instance of <string>.

constraint-name Open Generic function
Signature:

constraint-name (diag) => (constraint-name)

Parameters:
Values:

  • constraint-name – An instance of <string>.

constraint-schema Open Generic function
Signature:

constraint-schema (diag) => (constraint-schema)

Parameters:
Values:

  • constraint-schema – An instance of <string>.

constraints Open Generic function
Signature:

constraints (db-object) => (result)

Parameters:

  • db-object – An instance of <database-object>.

Values:
convert-value Generic function
Signature:

convert-value (coercion-policy value key) => (converted-value)

Parameters:
Values:

  • converted-value – An instance of <object>.

cursor-name Open Generic function
Signature:

cursor-name (diag) => (cursor-name)

Parameters:
Values:

  • cursor-name – An instance of <string>.

database Open Generic function
Signature:

database (connection) => (database)

Parameters:
Values:
database-object-name Generic function
Signature:

database-object-name (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

database-object-name-setter Generic function
Signature:

database-object-name-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

datatype-hints Generic function
Signature:

datatype-hints (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

datatype-hints-setter Generic function
Signature:

datatype-hints-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

dbms Open Generic function
Signature:

dbms (connection) => (dbms)

Parameters:
Values:

  • dbms – An instance of <dbms>.

dbms-name Open Generic function
Signature:

dbms-name (dbms #key connection) => (dbms-name)

Parameters:
Values:

  • dbms-name – An instance of <string>.

dbms-version Open Generic function
Signature:

dbms-version (dbms #key connection) => (dbms-version)

Parameters:
Values:

  • dbms-version – An instance of <string>.

default-connection Generic function
Signature:

default-connection () => (connection)

Values:
default-conversion Open Generic function
Signature:

default-conversion (value) => (converted-value)

Parameters:
Values:

  • converted-value – An instance of <object>.

default-dbms Generic function
Signature:

default-dbms () => (dbms)

Values:

  • dbms – An instance of <dbms>.

default-diagnostics-size Open Generic function
Signature:

default-diagnostics-size (connection) => (diagnostics-size)

Parameters:
Values:

  • diagnostics-size – An instance of <integer>.

default-isolation-level Open Generic function
Signature:

default-isolation-level (connection) => (level)

Parameters:
Values:
default-transaction-mode Open Generic function
Signature:

default-transaction-mode (connection) => (mode)

Parameters:
Values:
default-value Open Generic function
Signature:

default-value (column) => (default)

Parameters:
Values:
diagnostic-to-string Open Generic function
Signature:

diagnostic-to-string (diag) => (string)

Parameters:
Values:

  • string – An instance of <string>.

diagnostics-size Generic function
Signature:

diagnostics-size (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

diagnostics-size-setter Generic function
Signature:

diagnostics-size-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

disconnect Open Generic function
Signature:

disconnect (connection #key terminate-statements) => ()

Parameters:

  • connection – An instance of <connection>.

  • terminate-statements (#key) – An instance of <boolean>.

disconnect-all Open Generic function
Signature:

disconnect-all (#key dbms) => ()

Parameters:

  • dbms (#key) – An instance of false-or(<dbms>).

domain Generic function
Signature:

domain (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

dynamic-function Open Generic function
Signature:

dynamic-function (diag) => (dynamic-function)

Parameters:
Values:

  • dynamic-function – An instance of <string>.

end-transaction Open Generic function
Signature:

end-transaction (transaction) => ()

Parameters:
environment-name Open Generic function
Signature:

environment-name (diag) => (env-name)

Parameters:
Values:

  • env-name – An instance of <string>.

execute Open Generic function

Prepares an SQL statement for execution on the specified connection and then executes the statement.

Signature:

execute (database-statement #key #all-keys) => (result-set)

Parameters:

  • database-statement – An instance of type-union(<database-statement>, <string>).

  • connection (#key) – An instance of <connection>.

  • parameters (#key) – An instance of false-or(<sequence>).

  • result-set-policy (#key) – An instance of false-or(<result-set-policy>).

  • liaison (#key) – An instance of false-or(<function>) whose signature is liaison(<record>) => <object>. Default value: default-liaison.

Values:

  • result-set – An instance of false-or(<result-set>).

Discussion:

Prepares the SQL statement represented by sql-statement for execution on the connection, then sends it to the DBMS for execution.

If connection is not supplied, execute uses the connection returned by default-connection instead.

The liaison function is invoked on each record as it is retrieved from the database. If a liaison function is not provided, a default value of default-liaison is used; each result-set class has its own default-liaison.

In the SQL-ODBC library, the database-statement will be an instance of <sql-statement>. If anonymous host variables–that is, question marks (?)–appear in database-statement, pass suitable substitution values in the call to this function.

Example:

This example executes two SQL statements against the database represented by the-connection. The first SQL statement inserts a new book record into the book table. The second SQL statement queries for the list of titles and their ISBN published by Addison Wesley.

with-connection(the-connection)
  let insert-stmt :: <sql-statement> =
  make(<sql-statement>,
    text: "insert into book (title, publisher, isbn)
               values (?, ?, ?)",
    input-indicator: $null-value);
  execute(insert-stmt,
          parameters: #("Large Databases", "Addison-Wesley",
                        $null-value));

let query-stmt :: <sql-statement> =
  make(<sql-statement>,
  text: "select title, isbn from book
             where publisher = ?",
             output-indicator: $null-value);
  execute(query-stmt, parameters: #("Addison-Wesley"));
end with-connection;

=> #(#("An Introduction to Database Systems", "0-201-14201-5"),
     #("Relational Database Writings, 1991-1994", "0-8053-1748-1), #("Large Databases", $null-value))
fields Generic function
Signature:

fields (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

fields-setter Generic function
Signature:

fields-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

find-diagnostic Function
Signature:

find-diagnostic (table diagnostic-set-key sqlstate) => (diagnostic-detail-class)

Parameters:

  • table – An instance of <diagnostic-table>.

  • diagnostic-set-key – An instance of <object>.

  • sqlstate – An instance of <string>.

Values:

  • diagnostic-detail-class – An instance of <object>.

indexed-table Generic function
Signature:

indexed-table (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

indexed-table-setter Generic function
Signature:

indexed-table-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

indexes Open Generic function
Signature:

indexes (table) => (index-collection)

Parameters:
Values:
indicator-policy Generic function
Signature:

indicator-policy (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

input-indicator Open Generic function
Signature:

input-indicator (sql-statement) => (input-indicator)

Parameters:
Values:
input-indicator-setter Open Generic function
Signature:

input-indicator-setter (new-input-indicator sql-statement) => (new-input-indicator)

Parameters:
Values:
install-diagnostic Function
Signature:

install-diagnostic (table class #key key) => ()

Parameters:

  • table – An instance of <diagnostic-table>.

  • class – An instance of subclass(<diagnostic>).

  • key (#key) – An instance of <symbol>.

install-diagnostic-key Function
Signature:

install-diagnostic-key (key) => ()

Parameters:

  • key – An instance of <symbol>.

installation-functions Generic function
Signature:

installation-functions (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

is-null? Generic function
Signature:

is-null? (record key) => (null-state)

Parameters:

  • record – An instance of <record>.

  • key – An instance of <integer>.

Values:

  • null-state – An instance of <boolean>.

isolation-level Generic function
Signature:

isolation-level (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

isolation-level-setter Generic function
Signature:

isolation-level-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

liaison Generic function
Signature:

liaison (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

liaison-setter Generic function
Signature:

liaison-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

make-dbms-specific Open Generic function
Signature:

make-dbms-specific (type dbms #rest more-args) => (instance)

Parameters:

  • type – An instance of <class>.

  • dbms – An instance of <dbms>.

  • more-args (#rest) – An instance of <object>.

Values:
message-text Open Generic function
Signature:

message-text (diag) => (message-text)

Parameters:
Values:

  • message-text – An instance of <string>.

multiple-connections? Open Generic function
Signature:

multiple-connections? (dbms) => (multiple-connections-status)

Parameters:

  • dbms – An instance of <dbms>.

Values:

  • multiple-connections-status – An instance of <boolean>.

next-dbms-diagnostic Open Generic function
Signature:

next-dbms-diagnostic (diag) => (next-diagnostic)

Parameters:
Values:

  • next-diagnostic – An instance of false-or(<diagnostic>).

nullable? Generic function
Signature:

nullable? (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

output-indicator Open Generic function
Signature:

output-indicator (sql-statement) => (output-indicator)

Parameters:
Values:
output-indicator-setter Open Generic function
Signature:

output-indicator-setter (new-output-indicator sql-statement) => (new-output-indicator)

Parameters:
Values:
possible-explanation Generic function
Signature:

possible-explanation (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

record-available? Open Generic function
Signature:

record-available? (result-set key) => (availability)

Parameters:

  • result-set – An instance of <result-set>.

  • key – An instance of <integer>.

Values:

  • availability – An instance of <boolean>.

record-coercion-policy Generic function
Signature:

record-coercion-policy (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

register-diagnostic-installer Function
Signature:

register-diagnostic-installer (function) => ()

Parameters:

  • function – An instance of <function>.

returned-sqlstate Open Generic function
Signature:

returned-sqlstate (diag) => (sqlstate)

Parameters:
Values:

  • sqlstate – An instance of <string>.

rollback-transaction Open Generic function
Signature:

rollback-transaction (transaction) => ()

Parameters:
row-count Open Generic function
Signature:

row-count (diag) => (count)

Parameters:
Values:

  • count – An instance of <integer>.

rowset-size Generic function
Signature:

rowset-size (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

schema-from-name Open Generic function
Signature:

schema-from-name (connection catalog-name schema-name) => (schema)

Parameters:

  • connection – An instance of <connection>.

  • catalog-name – An instance of <string>.

  • schema-name – An instance of <string>.

Values:
schema-name Open Generic function
Signature:

schema-name (diag) => (schema-name)

Parameters:
Values:

  • schema-name – An instance of <string>.

scroll-window Generic function
Signature:

scroll-window (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

scrollable? Generic function
Signature:

scrollable? (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

sql Macro
start-transaction Open Generic function
Signature:

start-transaction (connection transaction-mode isolation-level diagnostics-size) => (transaction)

Parameters:
Values:
statement-column-names Open Generic function
Signature:

statement-column-names (statement) => (column-names)

Parameters:
Values:

  • column-names – An instance of <sequence>.

subclass-code Generic function
Signature:

subclass-code (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

subclass-origin Open Generic function
Signature:

subclass-origin (diag) => (subclass-origin)

Parameters:
Values:

  • subclass-origin – An instance of <string>.

table-from-name Open Generic function
Signature:

table-from-name (connection catalog-name schema-name table-name) => (table)

Parameters:

  • connection – An instance of <connection>.

  • catalog-name – An instance of <string>.

  • schema-name – An instance of <string>.

  • table-name – An instance of <string>.

Values:
table-name Open Generic function
Signature:

table-name (diag) => (table-name)

Parameters:
Values:

  • table-name – An instance of <string>.

text Open Generic function

Returns a string containing the text of an SQL statement.

Signature:

text (sql-statement) => (sql-statement-text)

Parameters:
Values:

  • sql-statement-text – An instance of <string>.

Discussion:

Returns a string containing the text of sql-statement.

See also:

text-setter Open Generic function

Changes the text of an SQL statement.

Signature:

text-setter (new-text sql-statement) => (new-text)

Parameters:

  • new-text – An instance of <string>.

  • sql-statement – An instance of <sql-statement>.

Values:

  • new-text – An instance of <string>.

Discussion:

Changes the text of the SQL statement in sql-statement to new-text.

See also:

transaction-mode Generic function
Signature:

transaction-mode (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

transaction-mode-setter Generic function
Signature:

transaction-mode-setter (value object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

unique-index? Generic function
Signature:

unique-index? (object) => (#rest results)

Parameters:
Values:

  • #rest results – An instance of <object>.

user Open Generic function
Signature:

user (connection) => (user)

Parameters:
Values:

  • user – An instance of <user>.

with-connection Macro
with-database Macro
with-dbms Macro
with-transaction Macro