Is MS ODBC driver 18.5.1.1 compatible with SQL Server 2025 for new JSON and VECTOR types?

Thank you Davide for the info.

Davide,

I did some test using the new VECTOR data type, and face an issue with the MS ODBC 18.5.1.1 driver...

When performing SQLFetchScroll(), I get this error:

SQL State: 42000
SQL code : 16926
Message : [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]sp_cursoroption: The column ID (2) does not correspond to a text, ntext, or image column.

Similar to the JSON case, when I use a CAST( colname AS VARCHAR(MAX)), I can fetch the vector data without problem.

I know sp_cursoroption() is on of the stored procedures used by MS ODBC driver(s).

We are using various ODBC cursor options (like SQL_CO_FFO), so I need to investigate what is done exactly. Maybe using the SQL Server Profiler.

I will also try to reproduce with a simple example, but wanted to warn you that it does not work out of the box in my case.

BR,

Seb

Davide,

After creating my table as follows:

create table tab1 ( pk int, ve vector(4))

insert into tab1 values ( 101, '[0.5,0.6,0.7,0.8]')

Then executing my ODBC program.

In SQL Server Profiler extension for Azure Data Studio, I see the following TSQL commands executions:

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'select pk, ve from tab1 order by pk',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

exec sp_cursoroption 180150003,1,2

exec sp_cursorfetch 180150003,2,0,1

I could reproduce the error by executing these sp_* calls directly in SQL Query tool so you should be able to reproduce directly on your side.

Trying to understand what sp_cursoropen does from https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursoroption-transact-sql?view=sql-server-ver17

... I continue to investigate ...

Seb

Davide,

Attached a test sample fetching VECTOR data reproducing the error:

$ gcc -o odbctest8.bin -I/opt3/dbs/uxo/2.3/include -I/opt3/dbs/snc/18/include -D HAVE_LONG_LONG odbctest8.c -L/opt3/dbs/snc/18/lib64 -lmsodbcsql-18

Seb

The system stored procedures "sp_cursor*" expose the API cursors for the drivers. The proc sp_cursoropen prepares a cursor based on the DML statement and takes several optional parameters (cursor options, named parameters for parameter markers in the DML statement etc.) This stored procedure will ultimately prepare the cursor on the server based on the input statement, open the cursor (i.e., start the DML execution and wait for fetch from the client) and returns the handle.

The proc sp_cursoroption failure is expected. You are passing the option 0x0001 to return text pointer only for the 2nd column. Since VECTOR is not a LOB type that supports text pointer (i.e., it is not text, ntext or image column), the error is expected. So, you need to skip the call to request a text pointer for the vector column. This also means that the server cannot stream the VECTOR data (since it is not a LOB type). You would fetch the value like any other non-LOB type.

Try below batch which works. I removed the sp_cursoroption call.

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'select pk, ve from tab1 order by pk',@p3 output,@p4 output,@p5 output
exec sp_cursorfetch @p1,2,0,1;
exec sp_cursorclose @p1;

Hello Umachandar!

Context

We (FOURJS) are a software dev tool company focused on the Informix 4GL marked, with our solution called Genero BDL : www.4js.com
To make it short, our solution is a 4GL language compiler and VM executing PCode (similar to Java), and we implement database client modules (like Python SQL client modules), to connect to DB Servers like SQL Server and execute SQL.
All of this is written in pure good old C language, thus the use of MS ODBC client.
MS ODBC driver is mission critical for us and our customers.

Code details

We are using SQLExecDirect[W]/SQLDescribeCol[W]/SQLBindCol/SQLFetchScroll ...

When the column is a VARCHAR(MAX) or NVARCHAR(MAX), we consider this as a CLOB type and use piecewise fetching with SQLGetData()

The issue

When fetching data from a JSON column, it will go through the same code as for regular [N]VARCHAR(MAX) typed columns.

So I suspect that the MS ODBC driver (18.5.1.1) is not yet prepared to handle the JSON type when SQLFetchScroll() occurs.

Repro

With that info, I assume that you can easily reproduce, but I will also try to write a little sample if I have time.

Thanks a lot!

BR,

Seb

And for the JSON manipulation, this is not our current concern:

We let programmers fetch JSON data into a CLOB-like container (TEXT type in 4GL), and then we provide our own APIs to modify the JSON data.

So in our case we do not need any ODBC API to manipulate JSON data.

Here is a sample to reproduce: odbctest7.c.txt

$ gcc -o odbctest7.bin -I/opt3/dbs/uxo/2.3/include -I/opt3/dbs/snc/18/include -D HAVE_LONG_LONG odbctest7.c -L/opt3/dbs/snc/18/lib64 -lmsodbcsql-18

sf@toro:~/dbvendors/sqlserver/problems$ ./odbctest7.bin

Connecting to DB server...

Preparing the SQL table...

SQLExecDirectW rcode = 0

col 1 type=12

Error -1 at: SQLFetch() failed

Diagnostic info:

SQL State: HY000

SQL code : 0

Message : [Microsoft][ODBC Driver 18 for SQL Server]Unknown token received from SQL Server

Thanks for the info. This is very helpful.