23.12.1 Configuring unixODBC

On Unix, configure unixODBC in these files.

For the driver:

/etc/odbcinst.ini

For the datasource:

~/.odbc.ini /etc/odbc.ini

23.12.2 Loading unixODBC

At load time do:

(require "odbc")

At run time on Unix, Common SQL automatically loads the unixODBC module from the location in the variable sql::*odbc-foreign-modules*. In LispWorks for Linux this variable initially has the value ("/usr/lib/libodbc.so"). Therefore if, for example, the run time machine unixODBC installed in
/usr/local/, at run time do:

(setq sql::*odbc-foreign-modules* '("/usr/local/lib/libodbc.so"))

(sql:connect "mydatabase" :database-type :odbc)

23.12.3 External format for ODBC strings

On Unix, the default external format for ODBC strings is :ascii. On Microsoft Windows it is win32:*multibyte-code-page-ef*.

23.12.4 Using non-ASCII strings on Microsoft SQL Server

When passing a SQL expression containing string literals to Microsoft SQL Server (which you can do via ODBC), if a string literal contains characters that the server's code page cannot represent, then the string literal needs to be marked as "Native" by prefixing it with the character 'N' before the opening quote. For example:

N'Greek'

Code pages always can always represent ASCII characters, but differ in what other characters can represent. The functions string-needs-n-prefix and string-prefix-with-n-if-needed are provided to check if a string needs prefixing.

Other SQL backends work with all strings regardless of the N syntax, but the syntax is allowed by most of them as well (and is standard SQL). However, SQLite and Microsoft Access (via ODBC) do not recognize the N syntax, and give an error. This means that static SQL expressions, which are generated before knowing which SQL backend is going to be used, cannot reliably use the N syntax. In addition, knowing exactly which strings need the N syntax requires knowledge of the code page in the server, and hence requires the database to be opened already when string-needs-n-prefix or string-prefix-with-n-if-needed are called.

The syntax described in Symbolic SQL syntax generates static expressions when possible, and Lisp string values within them are processed independently of any database to produce string literals without the N syntax. This can be overridden by using the string pseudo-operator, which is described in SQL string literals, and can decide dynamically whether to use the N syntax or not. Thus you should use the string pseudo-operator in any symbolic SQL syntax that may be used with Microsoft SQL Server and contains SQL string literals (including Lisp expressions that evaluate to strings) to ensure that it works on Microsoft SQL Server for all strings and but is also portable.

If you want to work with Microsoft SQL Server and do not require portability to SQLite or Microsoft Access, then you can set *use-n-syntax-for-non-ascii-strings* to t to always use the N syntax. However, the N prefix changes the type of the string inside Microsoft SQL Server to "Unicode", which has a different collation to non-Unicode strings, so if you need the non-Unicode collation for strings that have codes in the server's code page then this may not be the right approach.

Another approach is to use prepare-statement with a bind-variable for the string, which works on all SQL backends without any additional code (because the string is not used as a literal in the SQL expression):

(setq *a-prepared-statement*

      (sql:prepare-statement [sql:select [name] 

                             :from [sometable] 

                             :where [= [nchar_column] [1]]]))

...

(sql:set-prepared-statement-variables *a-prepared-statement* 

                                      (list a-non-ascii-string))

(sql:query *a-prepared-statement*)

The functions update-records and insert-records also do not use the values that they get as literals in SQL expressions when modifying a Microsoft SQL Server database, and therefore do not require additional code for the values. However, the where expression in update-records and the query expression in insert-records are used directly, so if they contain non-ASCII strings as literals then they will need to be modified for Microsoft SQL Server.