Encoding for query on SQLServer break characters

[DimRochette]

Discovery

I tried multiple way to do an insert with nanodbc on SQLserver and each time the text is unicode I have issues.
I wrote a unit test that try to insert the emoji N'😀' into a table
https://github.com/dimrochette/nanodbc/tree/encoding_issue
The unit test file was converted to UTF8 to handle emoji

Actual behavior

This test failed on nvarchar columns in both windows and linux.
It try to insert the emoji and check that the result is the same.
In the test I probably have an encoding issue on write and maybe an other on read.
I made the test succeed if the insert is done without nanodbc.
So if the insert is good the test pass.

other tests

I tested on SQLServer 2019 with varchar and UTF8 collation. The test failed too but as it require last SQLServer I upload the more broad issue.
I experiment with windows SQLServer 2022 utf8 storage compilation nothing relevant to our usecase

add_compile_options("$<$<C_COMPILER_ID:MSVC>:/utf-8>")
add_compile_options("$<$<CXX_COMPILER_ID:MSVC>:/utf-8>")

Environment

• nanodbc version: HEAD
• DBMS name/version: SQLServer 2019
• ODBC connection string: ODBC driver 18
• OS and Compiler: Windows MSVC 2022 and Ubuntu 22.04 GCC12

Minimal Working Example

insert into test_string_with_utf8_nvarchar(s)
values (N'😀')

full unit test https://github.com/dimrochette/nanodbc/tree/encoding_issue