Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV - Mailing list pgsql-bugs

Because we have customers who think Excel is an RDBMS, our product LISTSERV=
 religiously queries ODBC capabilities at startup and prints warnings as ap=
propriate. A customer tried to use PostgreSQL on Windows and got the follow=
ing (these are our messages not yours, I am pasting them to show all the ve=
rsion 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 suppor=
t for LIKE ... ESCAPE and issue a fatal compatibility warning if the functi=
onality is not present in the DBMS. A fatal warning means that we do not su=
pport the DBMS and will not accept incidents involving it. In short, the cu=
stomer must choose another database, in practice MySQL, the most problemati=
c database we have ever had to support :(

But from what I understand, PostgreSQL does support both the LIKE ... ESCAP=
E 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 =3D "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 =3D 4;
                  value =3D SQL_CA1_NEXT; /* others aren't allowed in ODBC =
spec */
                  break;

Here I am puzzled as the Microsoft documentation says no such thing, at lea=
st 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 o=
f 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-onl=
y cursors.

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

For reference, MySQL used to have the same two problems, and showed no inte=
rest 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 swit=
ch to the mysql_xxx() API? :) But Oracle provided a solution for the LIKE e=
scape 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