2 Connections

This section describes functions for creating connections as well as administrative functions for managing connections.

2.1 Creating connections

Connections are made using the following functions.

By default, the connection is made via TCP to "localhost" at port 5432. To make a different TCP connection, provide one or both of the server and port arguments.

To connect via a local socket, specify the socket path as the socket argument. You must not supply the socket argument if you have also supplied either of the TCP arguments. See also Local sockets for PostgreSQL and MySQL servers for notes on socket paths. Supplying a socket argument of 'guess is the same as supplying (postgresql-guess-socket-path). Sockets are only available under Linux (x86) and Mac OS X.

If the server requests password authentication, the password argument must be present; otherwise an exception is raised. If the server does not request password authentication, the password argument is ignored and may be omitted. A connection normally only sends password hashes (using the md5 authentication method). If the server requests a password sent as cleartext (un-hashed), the connection is aborted unless allow-cleartext-password? is true.

If the ssl argument is either 'yes or 'optional, the connection attempts to negotiate an SSL connection. If the server refuses SSL, the connection raises an exception if ssl was set to 'yes or continues with an unencrypted connection if ssl was set to 'optional. By default, SSL provides encryption but does not verify the identity of the server (see this explanation). Host verification can be required via the ssl-context argument; see ssl-set-verify!. Some servers use SSL certificates to authenticate clients; see ssl-load-certificate-chain! and ssl-load-private-key!. SSL may only be used with TCP connections, not with local sockets.

The notice-handler is called on notice messages received asynchronously from the server. A common example is notice of an index created automatically for a table’s primary key. The notice-handler function takes two string arguments: the condition’s SQLSTATE and a message. The notification-handler is called in response to an event notification (see the LISTEN and NOTIFY statements); its argument is the name of the event as a string. An output port may be supplied instead of a procedure, in which case a message is printed to the given port. Finally, the symbol 'output causes the message to be printed to the current output port, and 'error causes the message to be printed to the current error port.

If the connection cannot be made, an exception is raised.

Examples:

> (postgresql-connect #:server "db.mysite.com"                       #:port 5432                       #:database "webappdb"                       #:user "webapp"                       #:password "ultra5ecret") (object:connection% ...) > (postgresql-connect #:user "me"                       #:database "me"                       #:password "icecream") (object:connection% ...) > (postgresql-connect ; Typical socket path                       #:socket "/var/run/postgresql/.s.PGSQL.5432"                       #:user "me"                       #:database "me") (object:connection% ...) > (postgresql-connect #:socket 'guess ; or (postgresql-guess-socket-path)                       #:user "me"                       #:database "me") (object:connection% ...)

If none of the attempted paths exist, an exception is raised.

If the connection cannot be made, an exception is raised.

Examples:

> (mysql-connect #:server "db.mysite.com"                  #:port 3306                  #:database "webappdb"                  #:user "webapp"                  #:password "ultra5ecret") (object:connection% ...) > (mysql-connect #:user "me"                  #:database "me"                  #:password "icecream") (object:connection% ...) > (mysql-connect ; Typical socket path                  #:socket "/var/run/mysqld/mysqld.sock"                  #:user "me"                  #:database "me") (object:connection% ...) > (mysql-connect #:socket (mysql-guess-socket-path)                  #:user "me"                  #:database "me") (object:connection% ...)

If none of the attempted paths exist, an exception is raised.

If mode is 'read-only, the database is opened in read-only mode. If mode is 'read/write (the default), the database is opened for reading and writing (if filesystem permissions permit). The 'create mode is like 'read/write, except that if the given file does not exist, it is created as a new database.

SQLite uses coarse-grained locking, and many internal operations fail with the SQLITE_BUSY condition when a lock cannot be acquired. When an internal operation fails because the database is busy, the connection sleeps for busy-retry-delay seconds and retries the operation, up to busy-retry-limit additional times. If busy-retry-limit is 0, the operation is only attempted once. If after busy-retry-limit retries the operation still does not succeed, an exception is raised.

If the connection cannot be made, an exception is raised.

Examples:

> (sqlite3-connect #:database "/path/to/my.db") (object:connection% ...) > (sqlite3-connect #:database "relpath/to/my.db"                    #:mode 'create) (object:connection% ...)

If strict-parameter-types? is true, then the connection attempts to determine and enforce specific types for query parameters. See ODBC types for more details.

By default, connections use ODBC’s SQL_C_WCHAR-based character encoding (as UTF-16) to send and receive Unicode character data. Unfortunately, some drivers’ support for this method is buggy. To use SQL_C_CHAR instead, set character-mode to 'utf-8 or 'latin-1.

See ODBC support status for notes on specific ODBC drivers and recommendations for connection options.

If the connection cannot be made, an exception is raised.

If the connection cannot be made, an exception is raised.

2.2 Mangaging connections

2.3 System-specific modules

The (planet ryanc/db:1:4) module exports all of the functions listed in this manual except those described in Utilities. The database system-specific connection modules are loaded lazily to avoid unnecessary dependencies on foreign libraries.

The following modules provide subsets of the bindings described in this manual.

(require (planet ryanc/db:1:4/base))

Provides all generic connection operations (those described in Mangaging connections and Queries) and SQL data support (SQL types and conversions).

(require (planet ryanc/db:1:4/postgresql))

Provides only postgresql-connect and postgresql-guess-socket-path.

(require (planet ryanc/db:1:4/mysql))

Provides only mysql-connect and mysql-guess-socket-path.

(require (planet ryanc/db:1:4/sqlite3))

Provides only sqlite3-connect. In contrast to (planet ryanc/db:1:4), this module immediately attempts to load the SQLite native library when required, and it raises an exception if it cannot be found.

(require (planet ryanc/db:1:4/odbc))

Provides only odbc-connect, odbc-driver-connect, odbc-data-sources, and odbc-drivers. In contrast to (planet ryanc/db:1:4), this module immediately attempts to load the ODBC native library when required, and it raises an exception if it cannot be found.