Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29719

Bad transfer string during exec remote sql command

Details

    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

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          marko Marko Mäkelä added a comment -

          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.

          marko Marko Mäkelä added a comment - 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.
          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment -

          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

          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment - 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
          marko Marko Mäkelä added a comment -

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

          marko Marko Mäkelä added a comment - Thank you for the clarification. I only wanted to rule out the obvious.
          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment -

          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 'šččřžýýá'.

          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment - 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 'šččřžýýá'.
          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment -

          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

          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment - 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
          marko Marko Mäkelä added a comment - 

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


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

          marko Marko Mäkelä added a comment - SET NAMES utf8; SELECT HEX( BINARY ( CONVERT ( 'šččřžýýá' USING utf16le))); HEX(BINARY(CONVERT('šččřžýýá' USING utf16le))) 61010D010D0159017E01FD00FD00E100
          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment - - edited

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

          jaroslav.zeman13@post.cz Jaroslav Zeman added a comment - - edited Using utf16le doesn't solve anything, I just pointed out that the data is probably stored in a different way by the SQL server.

          People

            Unassigned Unassigned
            jaroslav.zeman13@post.cz Jaroslav Zeman
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.