search

lurcher / unixODBC

The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms.
GNU Lesser General Public License v2.1
100 stars 52 forks source link

SQLSetStmtAttr failing when maybe it shoudn't #83

Open lurcher opened 3 years ago

lurcher commented 3 years ago

In the batch of changes added after some compatibility tests were run (2.3.5), the following was added

SQLSetStmtOption/W missing check for positive rowset sizes.

The code is checking for a positive non zero value and returning a error otherwise. Does anyone (specifically the MS guys) have a reference to the docs where that check is specified in the driver manager. I ask because I see SQL Server linked servers calling SQLSetStmrAttr( SQL_ROWSET_SIZE, 0, 8 ). The 8 worries me for a whole different reason as I wonder if it thinks it passing a SQLLEN in, but ignore that for now. Its the check for >0 that I am wondering about.

v-chojas commented 3 years ago

Yes, if you use either SQLSetStmtOption/W or SQLSetAtmtAttr/W to attempt to set SQL_ROWSET_SIZE to 0, the Microsoft DM on Windows returns S1009/HY024"[Microsoft][ODBC Driver Manager] Invalid argument value" error.

The doc does specify SQL_ATTR_ROW_ARRAY_SIZE (which is the ODBC 3.0 equivalent of SQL_ROWSET_SIZE) to be a SQLULEN. https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetstmtattr-function

lurcher commented 3 years ago

On 28/10/2021 16:11, v-chojas wrote:

Yes, if you use either SQLSetStmtOption/W or SQLSetAtmtAttr/W to attempt to set SQL_ROWSET_SIZE to 0, the Microsoft DM on Windows returns S1009/HY024"[Microsoft][ODBC Driver Manager] Invalid argument value" error.

Ok, thanks for that, so that is the behavior you see, but:

  1. Is it documented anywhere is is it just an observed behavioral thing?

  2. That doesn't help me with SQL Server linked servers making that call with a value of 0. We have a setup with a ODBC-ODBC Bridge is transferring the calls from Windows to Linux, The call is getting to the driver on Windows with a 0, so I guess that may be SQL Server not using the driver manager, if it was it would behavior as you describe. Once the call gets to Linux, it goes via unixODBC, which as it stands returns an error. This stops SQL Server from continuing. If I comment out the > 0 check in the driver manager code then SQL Server is happy and carries on. That gives me a bit of a circle to square.

--

Nick

kadler commented 3 years ago

The docs don't mention this behavior: https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetstmtattr-function?view=sql-server-ver15

HY024 The Driver Manager returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESSMODE or SQL ATTR_ASYNC_ENABLE. For all other connection and statement attributes, the driver must verify the value specified in ValuePtr.

I did test ODBCTest and confirmed the results that @v-chojas saw, though.

v-chojas commented 3 years ago

This check has probably been there since at least ODBC 2.x. I'm not surprised that the docs don't mention it. That said, it's strange that SQL Server linked server is doing that - does a Windows ODBC trace show any activity? I believe linked servers actually uses OLEDB and using an ODBC driver with it actually goes through an OLEDB-ODBC bridge first, so there's multiple layers interacting in this scenario.

TallTed commented 3 years ago

linked servers actually uses OLEDB and using an ODBC driver with it actually goes through an OLEDB-ODBC bridge first

Yes, that's accurate. Linked Servers that specify an ODBC connection invisibly use Microsoft's OLE DB Provider for ODBC Drivers. You can manually specify use of that bridge provider to control some of its features. You can also specify use of a different OLE DB Bridge Provider for ODBC Data Sources, such as those from OpenLink Software (my employer).

Past experience with Microsoft's OLE DB Provider for ODBC Drivers suggests it is the most likely culprit here.

lurcher commented 3 years ago

On 28/10/2021 19:33, Ted Thibodeau Jr wrote:

linked servers actually uses OLEDB and using an ODBC driver with
it actually goes through an OLEDB-ODBC bridge first

Yes, that's accurate. Linked Servers that specify an ODBC connection invisibly use Microsoft's OLE DB Provider for ODBC Drivers. You can manually specify use of that bridge provider to control some of its features. You can also specify use of a different OLE DB Bridge Provider for ODBC Data Sources, such as those from OpenLink Software (my employer).

Past experience with Microsoft's OLE DB Provider for ODBC Drivers suggests it is the most likely culprit here.

Yep, and from what we seem to have seen its not possible to get tracing out from the driver manager under SQL Server (now, it used to be possible with a bit of effort, but now not even that) our suspicion is that its not actually using the driver manager now after a MDAC upgrade, but we could be wrong, That's part of the reason we put the bridge in, to make it possible to see whats going on.

I am happy to leave unixODBC how it is, as its just a testing combination we have set up, so not likely to impact end users. But I was interested if there was any formal description of the behavior I was seeing.

-- Nick