6 Notes

This section describes miscellaneous issues.

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, but only 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 10.10 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 10.10 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 Database character encodings

In most cases, a PostgreSQL or MySQL 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 Prepared query parameter types

Different database systems vary in their handling of query parameter types. For example, consider the following parameterized SQL statement:

SELECT 1 + ?;

PostgreSQL reports an expected type of integer for the parameter and will not accept other types. MySQL and SQLite, in contrast, report no useful parameter type information, and ODBC connections vary in behavior based on the driver, the data source configuration, and the connection parameters (see ODBC support status for specific notes).

6.4 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, ident authentication is automatically supported for unix domain sockets (but not TCP). The gss, sspi, krb5, pam, and ldap methods are not supported.

6.5 SQLite and ODBC native libraries

SQLite support requires the appropriate native library, specifically libsqlite3.so.0 on Unix or sqlite3.dll on Windows.

ODBC support requires the appropriate native library, specifically libodbc.so.1 (from unixODBC; iODBC is not supported) on Unix or odbc32.dll on Windows. In addition, the appropriate ODBC Drivers must be installed and any Data Sources configured.

6.6 ODBC support status

ODBC support is experimental. This library is compatible only with ODBC 3.x Driver Managers. 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. The platform win32 means Windows Vista on a 32-bit processor and linux means Ubuntu 11.04 and unixODBC on both x86 (32-bit) and x86-64 processors, unless otherwise specified. The iODBC Driver Manager is not supported.

Reports of success or failure on other platforms or with other drivers would be appreciated.

6.6.1 PostgreSQL ODBC driver

The PostgreSQL ODBC driver version 09.00.0300 has been tested on win32 and linux.

To get specific parameter type information, set the following Data Source options: Protocol = 7.4 and UserServerSidePrepare = 1, and use the #:strict-parameter-types? connection option.

Older versions of the driver, including version 08.03.0200, provided by Ubuntu 11.04, seem to have a bug in the character mode this library uses by default; use the #:character-mode 'utf-8 connection option as a workaround.

6.6.2 MySQL ODBC driver

The MySQL ODBC driver version 5.1.6-1 has been tested on win32 and linux.

Avoid using the #:strict-parameter-types? connection option, as the driver assigns all parameters the type varchar.

6.6.3 SQLite3 ODBC driver

Avoid using the #:strict-parameter-types? connection option, as the driver assigns all parameters the type longvarchar. Furthermore, this driver interprets the declared types of columns strictly, replacing nonconforming values in query results with NULL. All computed columns, even those with explicit CASTs, seem to be returned as text.

6.6.4 DB2 ODBC driver

The driver from IBM DB2 Express-C v9.7 has been tested on linux (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 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.6.5 Oracle ODBC driver

The driver from Oracle Database 10g Release 2 Express Edition has been tested on linux (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.