To: pgsql-odbc@postgresql.org
From: Joris Wit <postgresql@joriswit.nl>
Subject: SQLSpecialColumns result set column names should change for ODBC 3
 compatibility
Message-ID: <a8e06c36-9d82-8e9a-c4b4-9b9c67285bb6@joriswit.nl>
Date: Fri, 13 Aug 2021 18:58:14 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.13.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/a8e06c36-9d82-8e9a-c4b4-9b9c67285bb6%40joriswit.nl>
Precedence: bulk

Hi,

I am using Microsoft SQL Server Management Studio (SSMS) to copy tables 
from a PostgreSQL database to SQL Server using the ODBC driver. However, 
this fails with an error that it cannot find the 'COLUMN_SIZE'. I think 
this is because the result set of the SQLSpecialColumns function uses 
the ODBC 2 column names, instead of the ODBC 3 column names.

According to Microsoft's docs, this is the difference between ODBC 2 and 
3 in the result set column names:

ODBC 2.0 column    ODBC 3.x column
PRECISION          COLUMN_SIZE
LENGTH             BUFFER_LENGTH
SCALE              DECIMAL_DIGITS

The documentation also says that "applications bind by column number", 
but evidently SSMS does not (in its ProviderDescriptors.xml file).

source:
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlspecialcolumns-function?view=sql-server-ver15

When looking in the psqlODBC source code, I think in the 
PGAPI_SpecialColumns function in info.c a check should be added to 
support ODBC 3 (possibly using EN_is_odbc3()). When the check confirms 
ODBC 3+ is used, the different column names should be used.

Greetings,
Joris Wit.