Gug - bad transfer string during exec remote sql command

Question

Hi

I have Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) (64-bit) with this simple procedure and table:

   CREATE OR ALTER  PROCEDURE [dbo].[TestJSON]  
   	@Json nvarchar(max) , @ID INT  
   AS  
   BEGIN  
   	SET NOCOUNT ON;  
     
   	INSERT INTO [mydb].[dbo].TabJson1(ID, [JSON]) SELECT @ID, @Json  
   END  
     
   CREATE TABLE [mydb].[dbo].[TabJson1](  
   	[Json] [nvarchar](max) NULL,  
   	[ID] [varchar](20) NULL  
   )  

And I have on: Debian GNU/Linux 11 (bullseye) installed [ODBC Driver 17 for SQL Server] (libmsodbcsql-17.10.so.1.1) and installed sql server 10.5.15-MariaDB. In MariaDB I have created table:

   CREATE TABLE `ExternCommand2` (  
     `cmd` varchar(8000) NOT NULL `flag`=0,  
     `number` int(5) NOT NULL `flag`=1,  
     `message` varchar(255) DEFAULT NULL `flag`=2  
   ) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=MSSQL;UID=xxx;PWD=xxx' `table_type`=odbc `BLOCK_SIZE`=1 `option_list`='Execsrc=1'  

If I am execute extern command on MS SQL server over table ExternCommnad2 procedure on above:

   SELECT  
     *  
   FROM `ExternCommand2`  
   WHERE `cmd` = "exec [DTB_INTERCHANGE].[dbo].[Remante_TestJSON] N'šččřžýýá', 3";  

In table TestJson1 no string stored which was passed as a procedure parameter, i.e: 'šččřžýýá' but this: 'Å¡Ä Ä Å Å¾Ã½Ã½Ã¡'

This command also fails:

   SELECT  
     *  
   FROM `ExternCommand2`  
   WHERE `cmd` = "INSERT INTO [mydb].[dbo].[TabJson1] (ID, [JSON]) SELECT N'šččřžýýá', 4";  

What must be set for it to work? (Please be specific) Or it is a bug in ODBC?

Jaroslav

Answer 1

No, this is not a bug in the ODBC SQL Server Driver 17. I don't work with ODBC programming myself, but I am confident that the ODBC driver is perfectly able to insert to transport Unicode characters correctly.

So MariaDB is the prime suspect. I guess that MariaDB uses UTF-8 for Unicode. The problem is what MariaDB tells the ODBC driver about the data. If MariaDB says that this is SQL_C_WCHAR (or what the type indicator may be) and then sends UTF-8, the ODBC driver will send the UTF-8 sequences as they if they were UTF-16. But, wait, that is not what is happening there, because then you would have seen Chinese. No, MariaDB just sends them as char, but without any character set conversion.

Anyway, I think you will need to explore the options for ExternCommand on MariaDB.