Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV

Because we have customers who think Excel is an RDBMS, our product LISTSERV religiously queries ODBC capabilities at startup and prints warnings as appropriate. A customer tried to use PostgreSQL on Windows and got the following (these are our messages not yours, I am pasting them to show all the version numbers):

> Driver manager version: 03.80.7601.0000
> ODBC driver: PSQLODBC35W.DLL (09.01.0100)
> DBMS: PostgreSQL (9.1.4)
> [FATAL] LIKE operator has no ESCAPE clause, errors will occur!
> [SEVERE] FOR UPDATE clause not supported, no locking will occur

Due to the popularity of underscores in e-mail addresses, we require support for LIKE ... ESCAPE and issue a fatal compatibility warning if the functionality is not present in the DBMS. A fatal warning means that we do not support the DBMS and will not accept incidents involving it. In short, the customer must choose another database, in practice MySQL, the most problematic database we have ever had to support :(

But from what I understand, PostgreSQL does support both the LIKE ... ESCAPE clause and the SELECT ... FOR UPDATE clause. What's more, the '{escape}' sequence seems to be implemented by the ODBC driver, but the driver claims otherwise for some reason. Here is the relevant code from info.c:

case SQL_LIKE_ESCAPE_CLAUSE: /* ODBC 2.0 */

/*
* is there a character that escapes '%' and '_' in a LIKE
* clause? not as far as I can tell
*/
p = "N";
break;

I assume that this was true many years ago and someone forgot to change it when implementing the '{escape}' sequence :) For the second issue, the code is in info30.c:

case SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1:
len = 4;
value = SQL_CA1_NEXT; /* others aren't allowed in ODBC spec */
break;

Here I am puzzled as the Microsoft documentation says no such thing, at least not today, but even in an earlier version of ODBC it would have made no sense. Allowing only the SQL_CA1_NEXT flag would defeat the whole purpose of this information call - which database would not support SQL_FETCH_NEXT? :) Anyway, LISTSERV is looking for the flag SQL_CA1_SELECT_FOR_UPDATE. Note that the same problem exists for other types of cursors, although LISTSERV does not use dynamic cursors and only queries the capabilities forward-only cursors.

This is not a cosmetic issue. LISTSERV will not use any ODBC features reported as unsupported, so we cannot tell customers that "it complains at startup but in practice it works fine" - it doesn't. E-mail addresses containing underscores or percent signs will cause unwanted side effects because LISTSERV thinks that using '{escape}' would result in a syntax error.

For reference, MySQL used to have the same two problems, and showed no interest in fixing them because "You should not use ODBC." Well how exactly am I supposed to support SQL Server and DB2? Wait until Microsoft and IBM switch to the mysql_xxx() API? :) But Oracle provided a solution for the LIKE escape in the ODBC driver. The driver still reports lack of support for the FOR UPDATE clause though, probably because it is only supported by some of their umpteen back-end engines so it varies from one table to another :D

Eric