On this page:
5.1 Connecting to a server
5.2 Character encodings
5.3 Prepared query parameter types
5.4 Postgre SQL authentication
5.5 SQLite and ODBC native libraries
5.6 ODBC

5 System-specific notes

This section describes miscellaneous issues specific to particular database systems.

5.1 Connecting to a server

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

5.2 Character encodings

Changing a connection’s encoding via SQL statements such as SET NAMES is not allowed; when possible, the connection will observe the change and automatically disconnect with an error.

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 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, create a new database with the desired encoding (eg, with iconv), and pg_restore from the recoded dump file.

5.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 int4 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 and even the connection parameters.

5.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.

5.5 SQLite and ODBC native libraries

SQLite support requires the libsqlite3 native library, specifically libsqlite3.so.0.

ODBC support requires the libodbc native library, specifically libodbc.so.1. This library is provided by packages such as unixODBC or iODBC. In addition, the appropriate ODBC Drivers must be installed and any Data Sources configured.

5.6 ODBC

ODBC support is experimental. This library is compatible only with ODBC 3.x drivers. The behavior of ODBC connections can vary widely depending on the driver in use and even the configuration of a particular data source.

This library has been tested with unixODBC on Linux/x86 (32-bit) running Ubuntu 10.10 with the following drivers: PostgreSQL Unicode (from the odbc-postgresql package), MySQL (from the libmyodbc package), and SQLite3 (from the libsqliteodbc package).

PostgreSQL Unicode I have not been able to configure the driver to support SQLDescribeParam, so all parameter types are set to 'unknown. One test fails: no error is reported for multiple SQL statements in a string.

MySQL All tests pass.

SQLite3 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. Several tests fail because of this behavior.