[MDEV-29719] Bad transfer string during exec remote sql command Created: 2022-10-06  Updated: 2022-11-07

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Storage Engine - Connect
Affects Version/s: 10.5.15
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Jaroslav Zeman Assignee: Andrew Hutchings
Resolution: Unresolved Votes: 0
Labels: CONNECT, character-set


 Description   

Hi

This is my SW: 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 and Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) (64-bit).

On MariaDB I have created table (according to the manual):

CREATE TABLE `ExternCommand` (
 
`cmd` VARCHAR(8000) NOT NULL flag=0,
 
`number` INT flag=1,
 
`message` VARCHAR(255) flag=2)
 
ENGINE=connect
 
TABLE_TYPE=odbc
 
CONNECTION='DSN=MSSQL;UID=xxx;PWD=xxx'
 
CHARSET = utf8
 
BLOCK_SIZE = 1
 
OPTION_LIST='Execsrc=1

And in MS SQL I have:

CREATE OR ALTER  PROCEDURE  [mydb].[dbo].[Test]
 
    @Text nvarchar(max) , @ID INT
 
AS
 
BEGIN
 
    SET NOCOUNT ON;
 
    INSERT INTO [mydb].[dbo].Tab(ID, [Text]) SELECT @ID, @Text
 
END
 
 
 
CREATE TABLE [mydb].[dbo].[Tab](
 
    [ID] [varchar](20) NULL,
 
    [Text] NVARCHAR(max) NULL
 
)

If run those:

SELECT  *
 
FROM `ExternCommand`
 
WHERE `cmd` = "EXEC [mydb].[dbo].[Test] N'šččřžýýá', 1";
 
 
 
SELECT  *
 
FROM `ExternCommand`
 
WHERE `cmd` = "INSERT INTO [mydb].[dbo].Tab([ID], [Text]) SELECT 1, N'šččřžýýá'";

In the table Test in MS SQL string 'šččřžýýá' is stored as 'Å¡Ä Ä Å Å¾Ã½Ã½Ã¡'.

How to set the character set encoding so that the string is transferred correctly?

Inserting data direct into tables mapped to MariaDB via engine connect is correct. However, I need to pass a parameter to the procedure. Isn't it a problem that the data is stored with a different order of BE and LE bytes in both systems? Or just when passing data to ODBC, ODBC evaluates the data as being in a different encoding?

Thanks for any idea how to fix this

Jaroslav

 Comments   
Comment by Marko Mäkelä [ 2022-10-06 ]

jaroslav.zeman13@post.cz, what would the following return?

SELECT _latin1 0xc5a1;

If it returns 'š', your connection to the MariaDB Server is configured incorrectly: the server thinks that you are using latin1. SET NAMES utf8 or something similar should help.

The correct answer would be 'Å¡'. If you are getting that, then something is wrong between the Connect storage engine and MS SQL.

Comment by Jaroslav Zeman [ 2022-10-06 ]

Hi,

I do not have problem with coding data in MariaDB, there is all correct. I am usig default coding UTF8 and

SELECT _utf8 0xc5a1;

I get, correct 'š'. But problem is, if send `š` as parameter function to MS SQL sever over table ExterCommand.

Jaroslav

Comment by Marko Mäkelä [ 2022-10-06 ]

Thank you for the clarification. I only wanted to rule out the obvious.

Comment by Jaroslav Zeman [ 2022-10-06 ]

OK, the ExternCommand table returns what was sent to the remote server and the characters are displayed correctly here (in MariaDB). i.e. displayed locally 'šččřžýýá'.

Comment by Jaroslav Zeman [ 2022-10-06 ]

And there is no problem in this?

MS SQL CLI
 
SELECT CONVERT(VARBINARY(100), CAST(N'šččřžýýá' as varbinary(100)), 1)
 
 
 
-> 0x61010D010D0159017E01FD00FD00E100
 
 
 
MariaDB CLI
 
SELECT HEX(BINARY(CONVERT('šččřžýýá' USING utf16)))
 
 
 
-> 0161010D010D0159017E00FD00FD00E1

Comment by Marko Mäkelä [ 2022-10-07 ] 

SET NAMES utf8;
 
SELECT HEX(BINARY(CONVERT('šččřžýýá' USING utf16le)));


HEX(BINARY(CONVERT('šččřžýýá' USING utf16le)))
 
61010D010D0159017E01FD00FD00E100

Comment by Jaroslav Zeman [ 2022-10-09 ]

Using utf16le doesn't solve anything, I just pointed out that the data is probably stored in a different way by the SQL server.

Generated at Thu Feb 08 10:10:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.