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
