Date: Sun, 20 Mar 2022 20:26:45 +0000 (UTC)
From: Matthew Reeves <bytemyzer@yahoo.com>
To: pgsql-odbc@lists.postgresql.org
Message-ID: <1154651790.1143593.1647808005653@mail.yahoo.com>
Subject: PostgreSQL via PolyBase (MS-SQL) - Numeric Data Type Issue
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
References: <1154651790.1143593.1647808005653.ref@mail.yahoo.com>
Content-Length: 2064
Archived-At: 
 <https://www.postgresql.org/message-id/1154651790.1143593.1647808005653%40mail.yahoo.com>
Precedence: bulk

Hello, everyone,

I am hopeful that there is someone in the PG community who can help with th=
is one.

Our company has a MS-SQL data-warehouse, and we have a requirement to incor=
porate data from a PostgreSQL ERP database into this environment.=C2=A0 We =
have elected to do so via the MS-SQL PolyBase component.

We have installed the psqlODBC drivers (psqlodbc_13_02_0000) on the MS-SQL-=
Server.=C2=A0 The master key, database scoped credential and external data =
source have all been created, and external table links work as expected, wi=
th the exception of any tables containing numeric or decimal fields (ex. nu=
meric(18,2), decimal(18,2)).=C2=A0 When the MS-SQL-Server instance attempts=
 to execute a query on such a table, the following error is returned:


Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL=
" for linked server "(null)". 105082;Generic ODBC error: OdbcBufferReader.R=
eadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, 'Error=
 calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol=
, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)c=
olumn->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column=
->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | =
SQL Error Info:=C2=A0 Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager]=
 Driver does not support this function | Error calling: pReadConn->ReadBuff=
er(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFF=
F, number: 566, active connections: 7', Connection String: DSN=3DPOSTGRESQL=
ERP.


The only workaround that I have seen posted on the web thus far is to map t=
he PostgreSQL numeric data type, on the MS-SQL side, as an INT type.=C2=A0 =
This works for any numeric fields where the scale is 0 (ex. numeric(10,0)).=
=C2=A0 However, for any of the tables from the ERP financial module, where =
fields are defined as numeric(18,2), for example, this is not a viable solu=
tion.


I look forward to hearing from you,

Matthew