Subject: PostgreSQL via PolyBase (MS-SQL) - Numeric Data Type Issue

From: Matthew Reeves <[email-masked]>
Date: Sun, 20 Mar 2022 20:26:45 +0000 (UTC)

Hello, everyone,

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

Our company has a MS-SQL data-warehouse, and we have a requirement to incorporate data from a PostgreSQL ERP database into this environment.  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.  The master key, database scoped credential and external data source have all been created, and external table links work as expected, with the exception of any tables containing numeric or decimal fields (ex. numeric(18,2), decimal(18,2)).  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.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, 'Error calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)column->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info:  Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] Driver does not support this function | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 566, active connections: 7', Connection String: DSN=POSTGRESQLERP.

The only workaround that I have seen posted on the web thus far is to map the PostgreSQL numeric data type, on the MS-SQL side, as an INT type.  This works for any numeric fields where the scale is 0 (ex. numeric(10,0)).  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 solution.

I look forward to hearing from you,

Matthew

---

From: Matthew Reeves <[email-masked]>
Date: Sat, 26 Mar 2022 20:44:30 +0000 (UTC)

Hello, everyone,

As a follow-up, I downloaded the source code for the psqlODBC driver. The issue I described is cause by the call to function SQLSetDescRec, which, as of version 13.02.0000, has not yet been implemented. When the MS-SQL PolyBase component calls the function, the psqlODBC driver returns a SQL_ERROR with the message "Error not implemented", so MS-SQL responds to the client with error IM001: Driver does not support this function.

Does anyone know if/when this function is expected to be implemented in the psqlODBC driver?

Matthew
     On Sunday, March 20, 2022, 01:27:01 PM PDT, Matthew Reeves <[email-masked]> wrote:  
 
 Hello, everyone,

Matthew