Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

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

FACT(s) (Environment):

SQL Native Client Driver pulls VARCHAR(MAX) fields into schema holder as type SQL_VARCHAR
Schema holder generated with SQL Native Client Driver shows field with foreign type VARCHAR(MAX) as VARCHAR
SQL Server driver or OpenEdge 10.2x Wire Protocol Driver for MS SQL Server pulls VARCHAR(MAX) fields into schema holder as type SQL_LONGVARCHAR
Schema holder generated with SQL Server driver or OpenEdge 10.2x Wire Protocol Driver for MS SQL Server shows field with foreign type VARCHAR(MAX) as LONGVARCHAR
OpenEdge 10.2x
All Supported Operating Systems
MS SQL Server

CAUSE:

This is expected behaviour 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 knowledegbase link from DataDirect:
http://knowledgebase.datadirect.com/Connect-for-ODBC/article-3291-p-4.html


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.

FIX:

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