Poco:Data::ODBC - MSSQL (n)varchar(max) length issue

[Spaky]

Describe the bug
The data binding of (n)varchar(max) field in Poco:Data::ODBC seems just to allow max 8000 bytes, but MSSQL Server allow allows up to 2^31-1 bytes.

To Reproduce
Create a table with a varchar(max) or nvarchar(max) field, insert data with more then 8000 byte.

Expected behavior
I should be possible to insert data with more then 8000 bytes when SQL datatype is (n)varchar(max).

Logs
INSERT failed: exception: 'class Poco::Data::LengthExceededException' what: 'Data too long' message: 'Error binding column 6 size=67150, max size=8000)'

Please add relevant environment information:

• Windows 10, Windows Server 2019
• POCO Version: 1.12.4
• MS SQLServer 2019

Additional context
I think the retrieving of the type info of the DB needs a special handling for (n)varchar(max).

[aleks-f]

From SQLBindParameter:

If the SQL data type passed in is a character type, ColumnSize is the size in characters (not bytes). If the length of the data string in bytes is greater than 8000, ColumnSize should be set to SQL_SS_LENGTH_UNLIMITED, indicating that there is no limit to the size of the SQL type.

For instance, if the SQL data type is SQL_WVARCHAR, ColumnSize should not be greater than 4000. If the actual data length is greater than 4000, then ColumnSize should be set to SQL_SS_LENGTH_UNLIMITED so that nvarchar(max) will be used by driver.

The SQL_SS_LENGTH_UNLIMITED is not standard ODBC. If you want this feature, add the SQLServer-specific session feature like eg. multiActiveResultset:

poco/Data/ODBC/src/SessionImpl.cpp

Lines 134 to 138 in e868ecb

	// SQL Server supports multiple active resultsets
	// currently has no effect on other back ends
	addFeature("multiActiveResultset",
	&SessionImpl::setMultiActiveResultset,
	&SessionImpl::getMultiActiveResultset);

and send pull request.

[aleks-f]

See also DataFormat:

SQL Server Native Client uses SQL_SS_LENGTH_UNLIMITED to denote that the size of a column is greater than 8,000 bytes for large object (LOB) types. Beginning with SQL Server 2008, the same value is used for CLR UDTs when their size is greater than 8,000 bytes.

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development.