Allowing non-local characters on Windows

[MEO265]

Since in this package nanodbc was compiled without the flag NANODBC_USE_UNICODE, all connections are established in A mode and not in W mode. Systems that are not running with a UTF locale, but for example German_Germany.1252, do not support the transmission of special characters outside of this encoding (in the following I will simplify by assuming Latin-1).
This affects (even though R now supports it) almost all Windows clients, since the use of UTF-8 C locales in windows is only supported as beta.

This leads to the situation that queries via dbGetQuery containing non-Latin-1 characters reach the database without the special characters and thus produce incorrect results. And in the opposite direction, queries containing non-Latin-1 characters cannot be properly received.

I believe this could also be the underlying problem in the issues #301 and #479

The simplest solution would be to compile nanodbc with the flag NANODBC_USE_UNICODE. I have not yet been able to test this, but a first look at the code suggests to me that only minor changes would be necessary. Since nanodbc always uses UTF-8 as the return encoding for Unicode strings, this also covers UTF-16LE as used in the odbc W mode.

If I am mistaken, I would be very grateful for a solution. Here is my situation:

• Server: SQL Server 2022
• Client: Windows 11
• Tested under 4.1.2, 4.4.1 and 4.5.1
• Local: German_Germany.1252
• Connection via

DBI::dbConnect(
  odbc::odbc(),
  Driver = "ODBC Driver 17 for SQL Server",
  Server = server_name,
  Database = db_name,
  Trusted_Connection = "yes",
  encoding = "latin1"
)

[detule]

Hi! Thanks for your report.

Can you provide a minimal example showing the issue you are running into.

Thank you!