Skip site navigation (1) Skip section navigation (2)

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

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Fwd: [BUGS] Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV
Date: 2012-08-08 01:16:18
Message-ID: 5021BDE2.7000402@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-odbc
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




In response to

Responses

pgsql-odbc by date

Next:From: Craig RingerDate: 2012-08-08 01:22:02
Subject: Re: Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV
Previous:From: John SlatteryDate: 2012-08-07 20:03:05
Subject: Re: GSSAPI Authentication Problem

pgsql-bugs by date

Next:From: Craig RingerDate: 2012-08-08 01:22:02
Subject: Re: Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV
Previous:From: Todd KoverDate: 2012-08-08 00:10:25
Subject: renaming+recreating table w/default sequence causes dependency seq issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group