8.7 The Common SQL interface

The Common SQL interface requires ODBC or one of the supported database types listed in section "Supported Databases" of the LispWorks® User Guide and Reference Manual.

8.7.1 Loading Common SQL

To load Common SQL enter, for example:

(require "odbc")

or:

(require "oracle")

Initialize the database type at run time, for example:

(sql:initialize-database-type :database-type :odbc)

or:

(sql:initialize-database-type :database-type :oracle)

See the LispWorks® User Guide and Reference Manual for further information.

8.7.2 Supported databases

Common SQL on macOS has been tested with DBMS Postgres 7.2.1, MySQL 5.0.18, Oracle Instant Client 10.2.0.4, ODBC driver PSQLODBC development code, and IODBC as supplied with macOS.

8.7.3 Special considerations when using Common SQL

8.7.3.1 Location of .odbc.ini

The current release of macOS comes with an ODBC driver manager from IODBC, including a GUI interface. IODBC attempts to put the file .odbc.ini file in a non-standard location. This causes problems at least with the PSQLODBC driver for PostgreSQL, because PSQLODBC expects to find .odbc.ini in either the users's home directory or the current directory. There may be similar problems with other drivers. Therefore the file .odbc.ini should be placed in its standard place ~/.odbc.ini. The IODBC driver manager looks there too, so it will work.

8.7.3.2 Errors using PSQLODBC

The PSQLODBC driver, when it does not find any of the Servername, Database or Username in .odbc.ini, returns the wrong error code. This tells the calling function that the user cancelled the login dialog.

Therefore, if Common SQL reports that the user cancelled when trying to connect, you need to check that you have got Servername, Database and Username, with the correct case, in the section for the datasource in the .odbc.ini file.

Note: Username may alternatively be given in the connect string.

8.7.3.3 psqlODBC version

Common SQL was tested with the development version of psqlODBC (that is downloaded from CVS), with the version changed to 3. Contact Lisp Support if you need help using Common SQL with psqlODBC.

8.7.3.4 Locating the Oracle, MySQL or PostgreSQL client libraries

For database-type :oracle, :mysql and :postgresql, if the client library is not installed in a standard place, its directory must be added to the environment variable DYLD_LIBRARY_PATH (see the OS manual entry for dyld).