SQL Server 2025 support

[sebflaesch]

Hello,

SQL Server 2025 CTP2 is available as public preview.

Is there any work on FreeTDS to support for new features like JSON and VECTOR data types?

Thanks
Seb

[freddy77]

I was expecting patches from you 😄

[sebflaesch]

Sorry Frediano,
I can't work on FreeTDS sources (contract).
I can however do a lot of testing when available.

[freddy77]

Usually contractors allows exceptions for coding on related projects. Anyway...
I added 0xd feature and server correctly replied acking it but with these queries

create table #tmp1(id int, js json null)
go
insert into #tmp1 values(1, '{"test":123}')
go
select * from #tmp1
go

I'm getting

packet.c:404:Received packet
0000 04 01 00 5a 00 36 01 00-81 02 00 00 00 00 00 09 |...Z.6.. ........|
0010 00 26 04 02 69 00 64 00-00 00 00 00 09 00 a7 ff |.&..i.d. ........|
0020 ff 09 04 00 26 00 02 6a-00 73 00 d1 04 01 00 00 |....&..j .s......|
0030 00 fe ff ff ff ff ff ff-ff 0c 00 00 00 7b 22 74 |........ .....{"t|
0040 65 73 74 22 3a 31 32 33-7d 00 00 00 00 fd 10 00 |est":123 }.......|
0050 c1 00 01 00 00 00 00 00-00 00                   |........ ..|

token.c:573:processing result tokens.  marker is  81(TDS7_RESULT)
token.c:1581:processing TDS7 result metadata.
mem.c:665:tds_free_all_results()
token.c:1606:set current_results (2 columns) to tds->res_info
token.c:1613:setting up 2 columns
token.c:1550:tds7_get_data_info: 
	colname = id
	type = 38 (integer-null)
	server's type = 38 (integer-null)
	column_varint_size = 1
	column_size = 4 (4 on server)
token.c:3253:adjust_character_column_size:
	Server charset: UTF-8
	Server column_size: 1073741823
	Client charset: UTF-8
	Client column_size: 1073741823
token.c:1550:tds7_get_data_info: 
	colname = js
	type = 39 (varchar)
	server's type = 167 (xvarchar)
	column_varint_size = 8
	column_size = 1073741823 (1073741823 on server)
token.c:1622: name                 size/wsize      type/wtype      utype  
token.c:1623: -------------------- --------------- --------------- -------
token.c:1628: id                         4/4            38/38            0
token.c:1628: js                   1073741823/1073741823      39/167           0

that is like a varchar(max) without any hint I can see for something else. Last TDS documentation seems to suggest a different type, like for XML.

These queries instead produce our new type

select json_arrayagg(js) as xyz from #tmp1
go
select json_query(js) from #tmp1
go

[sebflaesch]

I also cannot find any update to ODBC constants ...

Since new JSON / VECTOR types are suppose to be seen as VARCHAR(MAX) by clients, I assume that there will be no changes in the ODBC or in any other client API/lib for these new types.

[freddy77]

The same could be true for XML type too. Also, why having to extend the protocol if you could just return VARCHAR(MAX) ? Returning a different type allows the client to better handle the data, for instance you could remove validation for JSON because you know it's JSON and not any text.
Apparently updated mssql 2025 now returns JSON column for the simple select * from <table_name> query.

[sebflaesch]

Your point makes sense of course.
What do you mean by "now returns JSON" ? Would SQLDescribeCol() return a different type code?!?
Or is this just about the TDS protocol?
What mssql update? Can you provide the exact version?
Tx!

[freddy77]

What do you mean by "now returns JSON" ? Would SQLDescribeCol() return a different type code?!? Or is this just about the TDS protocol? What mssql update? Can you provide the exact version? Tx!

I mean at protocol level, specifically

util.c:179:Changed query state from IDLE to WRITING
packet.c:846:Sending packet
0000 01 01 00 46 00 00 01 00-16 00 00 00 12 00 00 00 |...F.... ........|
0010 02 00 00 00 00 00 00 00-00 00 01 00 00 00 73 00 |........ ......s.|
0020 65 00 6c 00 65 00 63 00-74 00 20 00 2a 00 20 00 |e.l.e.c. t. .*. .|
0030 66 00 72 00 6f 00 6d 00-20 00 23 00 74 00 6d 00 |f.r.o.m.  .#.t.m.|
0040 70 00 31 00 0a 00      -                        |p.1...|

util.c:179:Changed query state from WRITING to PENDING
token.c:558:tds_process_tokens(0x2c6c8e30, 0x7ffc30ec41dc, (nil), 0x6914)
util.c:179:Changed query state from PENDING to READING
packet.c:404:Received packet
0000 04 01 00 53 00 33 01 00-81 02 00 00 00 00 00 09 |...S.3.. ........|
0010 00 26 04 02 69 00 64 00-00 00 00 00 09 00 f4 02 |.&..i.d. ........|
0020 6a 00 73 00 d1 04 01 00-00 00 fe ff ff ff ff ff |j.s..... ........|
0030 ff ff 0c 00 00 00 7b 22-74 65 73 74 22 3a 31 32 |......{" test":12|
0040 33 7d 00 00 00 00 fd 10-00 c1 00 01 00 00 00 00 |3}...... ........|
0050 00 00 00               -                        |...|

token.c:573:processing result tokens.  marker is  81(TDS7_RESULT)
token.c:1581:processing TDS7 result metadata.
mem.c:665:tds_free_all_results()
token.c:1606:set current_results (2 columns) to tds->res_info
token.c:1613:setting up 2 columns
token.c:1550:tds7_get_data_info: 
	colname = id
	type = 38 (integer-null)
	server's type = 38 (integer-null)
	column_varint_size = 1
	column_size = 4 (4 on server)
token.c:3254:adjust_character_column_size:
	Server charset: UTF-8
	Server column_size: 2147483647
	Client charset: UTF-8
	Client column_size: 2147483647
token.c:1550:tds7_get_data_info: 
	colname = js
	type = 175 (xchar)
	server's type = 244 (json)
	column_varint_size = 8
	column_size = 2147483647 (2147483647 on server)

The type returned is f4 which is JSON (data encoding is like VARCHAR(MAX)).
The output above is obtained from a patched version of FreeTDS adding some base support for JSON type. FreeTDS is telling the server that the client is supporting JSON so the server replies with the new type. Also note that the data is correctly parsed.
About SQLDescribeCol is depends, the ODBC driver (FreeTDS, Microsoft or any other) could tell the server that it support JSON or not. If not the server will return VARCHAR(MAX) instead of JSON and the client won't know that the data is JSON. Then the driver has to translate TDS type to ODBC type (in the case of SQLDescribeCol ODBC sql type). Obviously if the client didn't told the server that it support JSON the same sql type for VARCHAR(MAX) is returned. As I said there's a specific type for XML (SQL_SS_XML) but not for JSON so I don't know what constant to return specifically (possibly Microsoft didn't define such a constant as their driver does not specify JSON support, I didn't manage to check this).

A select @@version returns

Microsoft SQL Server 2025 (CTP2.0) - 17.0.700.9 (X64) 
	May  8 2025 19:14:27 
	Copyright (C) 2025 Microsoft Corporation
	Enterprise Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS) <X64>

[sebflaesch]

Fred,
FYI
I have just upgraded MSSQL 2025 on my Linux and Windows.
The latest version is now CTP2.1 - 17.0.800.3
Seb