Fwd: [BUGS] Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV

ODBC issue report from -bugs

-------- Original Message --------
Subject: [BUGS] Small bug in psqlodbc-09.01 prevents interoperability
with LISTSERV
Date: Sat, 4 Aug 2012 15:43:58 +0000
From: Eric Thomas <ERIC(at)LSOFT(dot)COM>
To: pgsql-bugs(at)postgresql(dot)org <pgsql-bugs(at)postgresql(dot)org>

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 L

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 J 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? J 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? J 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