ODBC errors occur when using VARCHAR(MAX) type field in WHERE clause of query run via DataServer for MS SQL Server

ODBC errors occur when using VARCHAR(MAX) type field in WHERE clause of query run via DataServer for MS SQL Server.
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types varchar(max) and text are incompatible in the equal to operator.
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Code used is similar in format to the following:

FOR EACH <Table> NO-LOCK WHERE <nvarchar(max) field> = "<value>":

For example:

FOR EACH myTable NO-LOCK WHERE bigfield = "test":
...

END.

Schema holder has been generated using either the SQL Server ODBC driver or OpenEdge 10.2x Wire Protocol Driver for MS SQL Server
Query succeeds when using a schema holder that has been generated using the SQL Native Client or SQL Server Native Client 10.0
Errors do not occur when using RUN STORED PROCEDURE send-sql-statement to pass the equivalent SQL statement to the DataServer

DEFINE VAR handle1 AS INTEGER.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE ("SELECT id, name FROM myTable WHERE bigfield = 'test'").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
     DISPLAY proc-text.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

Executing the equivalent SQL statement via a third-party ODBC utility via the same ODBC DSN succeeds.

This is expected behavior resulting from a driver limitation. Per release note# OE00196260:

[DataDirect][ODBC SQL Server Driver] behavior to 'MAX' datatype in WHERE clause MSS DataServer provides capability of mapping MS SQL Server Large value data types, for instance VARBINARY (MAX), VARCHAR (MAX) and NVARCHAR (MAX), to OpenEdge CHARACTER data types with a data limit of 30K in size.

Hence mapped as above, MSS DataServer also supports using OpenEdge CHARACTER datatype in a WHERE clause.

However, with the DataDirect Driver, it is not possible to use a parameter of the type VARCHAR(MAX) in a WHERE clause when using an equal operator. The following ODBC error will occur "DataDirect][ODBC SQL Server Driver][SQL Server]The data types varchar(max) and text are incompatible in the equal to operator."

For more information, please refer to the following knowledge base link from DataDirect:

The data types varchar(max) and text are incompatible in the equal to operator"


Functionality-wise, when processing the query, the DataServer calls ODBC functions to prepare the query (SQLPrepare), bind the parameter data to the query (SQLBindParameter) and then execute it (SQLExecute). The problem occurs when the bound parameter has type SQL_LONGVARCHAR; SQLBindParameter succeeds but SQLExecute fails.
When using a third party tool or RUN STORED PROCEDURE send-sql-statement, the complete query is passed so there is no need to bind parameters; ODBC function SQLExecuteDirect is called instead.

Rebuild the schema holder against an ODBC DSN that uses the SQL Native Client or SQL Server Native Client 10.0 driver.

References to Written Documentation:

DataDirect article(s):
 The data types varchar(max) and text are incompatible in the equal to operator