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

Bad transfer string during exec remote sql command

    XMLWordPrintable

    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

            People

            Assignee:
            TheLinuxJedi Andrew Hutchings
            Reporter:
            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.