6 Notes
This section discusses issues related to specific database systems.
6.1 Local Sockets for PostgreSQL and MySQL Servers
PostgreSQL and MySQL servers are sometimes configured by default to listen only on local sockets (also called “unix domain sockets”). This library provides support for communication over local sockets on Linux (x86 and x86-64) and Mac OS X. If local socket communication is not available, the server must be reconfigured to listen on a TCP port.
The socket file for a PostgreSQL server is located in the directory specified by the unix_socket_directory variable in the postgresql.conf server configuration file. For example, on Ubuntu 11.04 running PostgreSQL 8.4, the socket directory is /var/run/postgresql and the socket file is /var/run/postgresql/.s.PGSQL.5432. Common socket paths may be searched automatically using the postgresql-guess-socket-path function.
The socket file for a MySQL server is located at the path specified by the socket variable in the my.cnf configuration file. For example, on Ubuntu 11.04 running MySQL 5.1, the socket is located at /var/run/mysqld/mysqld.sock. Common socket paths for MySQL can be searched using the mysql-guess-socket-path function.
6.2 PostgreSQL Database Character Encoding
In most cases, a database’s character encoding is irrelevant, since the connect function always requests translation to Unicode (UTF-8) when creating a connection. If a PostgreSQL database’s character encoding is SQL_ASCII, however, PostgreSQL will not honor the connection encoding; it will instead send untranslated octets, which will cause corrupt data or internal errors in the client connection.
To convert a PostgreSQL database from SQL_ASCII to something sensible, pg_dump the database, recode the dump file (using a utility such as iconv), create a new database with the desired encoding, and pg_restore from the recoded dump file.
6.3 PostgreSQL Authentication
PostgreSQL supports a large variety of authentication mechanisms, controlled by the pg_hba.conf server configuration file. This library currently supports only cleartext and md5-hashed passwords, and it does not send cleartext passwords unless explicitly ordered to (see postgresql-connect). These correspond to the md5 and password authentication methods in the parlance of pg_hba.conf, respectively. On Linux and Mac OS X, ident authentication is automatically supported for local sockets (as of PostgreSQL 9.1, this authentication method has been renamed peer). The gss, sspi, krb5, pam, and ldap methods are not supported.
6.4 MySQL Authentication
As of version 5.5.7, MySQL supports authentication plugins. The only plugins currently supported by this library are mysql_native_password (the default) and mysql_old_password, which corresponds to the password authentication mechanisms used since version 4.1 and before 4.1, respectively.
6.5 SQLite Requirements
SQLite support requires the appropriate native library.
On Windows, the library is sqlite3.dll. It is included in the Racket distribution.
On Mac OS X, the library is libsqlite3.0.dylib, which is included (in /usr/lib) in Mac OS X version 10.4 onwards.
On Linux, the library is libsqlite3.so.0. It is included in the libsqlite3-0 package in Debian/Ubuntu and in the sqlite package in Red Hat.
6.6 FFI-Based Connections and Concurrency
Wire-based connections communicate using ports, which do not cause other Racket threads to block. In contrast, an FFI call causes all Racket threads to block until it completes, so FFI-based connections can degrade the interactivity of a Racket program, particularly if long-running queries are performed using the connection. This problem can be avoided by creating the FFI-based connection in a separate place using the #:use-place keyword argument. Such a connection will not block all Racket threads during queries; the disadvantage is the cost of creating and communicating with a separate place.
6.7 ODBC Requirements
ODBC requires the appropriate driver manager native library as well as driver native libraries for each database system you want use ODBC to connect to.
On Windows, the driver manager is odbc32.dll, which is included automatically with Windows.
On Mac OS X, the driver manager is libiodbc.2.dylib (iODBC), which is included (in /usr/lib) in Mac OS X version 10.2 onwards.
On Linux, the driver manager is libodbc.so.1 (unixODBC—
iODBC is not supported). It is available from the unixodbc package in Debian/Ubuntu and in the unixODBC package in Red Hat.
In addition, you must install the appropriate ODBC Drivers and configure Data Sources. Refer to the ODBC documentation for the specific database system for more information.
6.8 ODBC Status
ODBC support is experimental. The behavior of ODBC connections can vary widely depending on the driver in use and even the configuration of a particular data source.
The following sections describe the configurations that this library has been tested with.
Reports of success or failure on other platforms or with other drivers would be appreciated.
6.8.1 DB2 ODBC Driver
The driver from IBM DB2 Express-C v9.7 has been tested on Ubuntu 11.04 (32-bit only).
For a typical installation where the instance resides at /home/db2inst1, set the following option in the Driver configuration: Driver = /home/db2inst1/sqllib/lib32/libdb2.so. (The path would presumably be different for a 64-bit installation.)
The DB2 driver does not seem to accept a separate argument for the database to connect to; it must be the same as the Data Source name.
6.8.2 Oracle ODBC Driver
The driver from Oracle Database 10g Release 2 Express Edition has been tested on Ubuntu 11.04 (32-bit only).
It seems the ORACLE_HOME and LD_LIBRARY_PATH environment variables must be set according to the oracle_env.{csh,sh} script for the driver to work.
Columns of type TIME can cause a memory error (ie, Racket crashes). This seems to be due to a bug in Oracle’s ODBC driver, but I do not yet have a workaround.
6.8.3 SQL Server ODBC Driver
Basic SQL Server support has been verified on Windows (32-bit only), but the automated test suite has not yet been adapted and run.