Date: Sat, 26 Mar 2022 20:44:30 +0000 (UTC)
From: Matthew Reeves <bytemyzer@yahoo.com>
To: pgsql-odbc@lists.postgresql.org
Message-ID: <1796959239.619342.1648327470705@mail.yahoo.com>
In-Reply-To: <1154651790.1143593.1647808005653@mail.yahoo.com>
References: <1154651790.1143593.1647808005653.ref@mail.yahoo.com>
 <1154651790.1143593.1647808005653@mail.yahoo.com>
Subject: Re: PostgreSQL via PolyBase (MS-SQL) - Numeric Data Type Issue
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_619341_1752988392.1648327470704"
Content-Length: 6892
Archived-At: 
 <https://www.postgresql.org/message-id/1796959239.619342.1648327470705%40mail.yahoo.com>
Precedence: bulk

------=_Part_619341_1752988392.1648327470704
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 Hello, everyone,

As a follow-up, I downloaded the source code for the psqlODBC driver. The i=
ssue 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 PolyBa=
se component calls the function, the psqlODBC driver returns a SQL_ERROR wi=
th the message "Error not implemented", so MS-SQL responds to the client wi=
th 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 <bytemyzer@=
yahoo.com> wrote: =20
=20
 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


 =20
------=_Part_619341_1752988392.1648327470704
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>                Hello, everyone,<br><br>As a follow-up, I downloaded t=
he 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 no=
t yet been implemented.  When the MS-SQL PolyBase component calls the funct=
ion, the psqlODBC driver returns a SQL_ERROR with the message &quot;Error n=
ot implemented&quot;, so MS-SQL responds to the client with error IM001: Dr=
iver does not support this function.<br><br>Does anyone know if/when this f=
unction is expected to be implemented in the psqlODBC driver?<br><br><br>Ma=
tthew<br>            </div>            <div class=3D"yahoo_quoted" style=3D=
"margin:10px 0px 0px 0.8ex;border-left:1px solid #ccc;padding-left:1ex;">  =
                      <div style=3D"font-family:'Helvetica Neue', Helvetica=
, Arial, sans-serif;font-size:13px;color:#26282a;">                        =
        <div>                    On Sunday, March 20, 2022, 01:27:01 PM PDT=
, Matthew Reeves &lt;bytemyzer@yahoo.com&gt; wrote:                </div>  =
              <div><br></div>                <div><br></div>               =
 <div><div dir=3D"ltr">Hello, everyone,<br></div><div dir=3D"ltr"><br></div=
><div dir=3D"ltr">I am hopeful that there is someone in the PG community wh=
o can help with this one.<br></div><div dir=3D"ltr"><br></div><div dir=3D"l=
tr">Our company has a MS-SQL data-warehouse, and we have a requirement to i=
ncorporate data from a PostgreSQL ERP database into this environment.&nbsp;=
 We have elected to do so via the MS-SQL PolyBase component.<br></div><div =
dir=3D"ltr"><br></div><div dir=3D"ltr">We have installed the psqlODBC drive=
rs (psqlodbc_13_02_0000) on the MS-SQL-Server.&nbsp; The master key, databa=
se scoped credential and external data source have all been created, and ex=
ternal table links work as expected, with the exception of any tables conta=
ining numeric or decimal fields (ex. numeric(18,2), decimal(18,2)).&nbsp; W=
hen the MS-SQL-Server instance attempts to execute a query on such a table,=
 the following error is returned:<br></div><div dir=3D"ltr"><br></div><div =
dir=3D"ltr"><br></div><div dir=3D"ltr">Cannot execute the query "Remote Que=
ry" 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-&gt;Get=
Hdesc(), (SQLSMALLINT)column-&gt;idxServerCol, (SQLSMALLINT)column-&gt;odbc=
ReadType, 0, column-&gt;valueLength, (SQLSMALLINT)column-&gt;precision, (SQ=
LSMALLINT)column-&gt;scale, (SQLPOINTER)(pBuffer + column-&gt;valueOffset),=
 (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info:=
&nbsp; Error &lt;1&gt;: ErrorMsg: [Microsoft][ODBC Driver Manager] Driver d=
oes not support this function | Error calling: pReadConn-&gt;ReadBuffer(pBu=
ffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, num=
ber: 566, active connections: 7', Connection String: DSN=3DPOSTGRESQLERP.<b=
r></div><div dir=3D"ltr"><br></div><div dir=3D"ltr"><br></div><div dir=3D"l=
tr">The only workaround that I have seen posted on the web thus far is to m=
ap the PostgreSQL numeric data type, on the MS-SQL side, as an INT type.&nb=
sp; This works for any numeric fields where the scale is 0 (ex. numeric(10,=
0)).&nbsp; However, for any of the tables from the ERP financial module, wh=
ere fields are defined as numeric(18,2), for example, this is not a viable =
solution.<br></div><div dir=3D"ltr"><br></div><div dir=3D"ltr"><br></div><d=
iv dir=3D"ltr">I look forward to hearing from you,<br></div><div dir=3D"ltr=
"><br></div><div dir=3D"ltr">Matthew<br></div><div dir=3D"ltr"><br></div><d=
iv dir=3D"ltr"><br></div></div>            </div>                </div>
------=_Part_619341_1752988392.1648327470704--