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

CONNECT+ODBC - UPDATE and DELETE queries contains unwanted MariaDB database name

    XMLWordPrintable

Details

    Description

      Hi,

      i have CONNECT table created table "company__contract" in MariaDB database "myproject", by command below. This table is connected to MSSQL schema "externaldatabase" on server "mydsn" (defined properly by FreeTDS + UnixODBC).

      Select and insert into table works properly, but UPDATE or DELETE queries produced by CONNECT/ODBC engine generates unwanted "myproject" prefix into MSSQL queries. String "myproject" is name of MariaDB database, not MSSQL database. Proper name of MSSQL database/schema is "externaldatabase" defined by DATABASE in CONNECTION.

      Here are queries recorded by tracing in FreeTDS/UnixODBC:

      SELECT (OK): SELECT id, note FROM dbo.contract
      INSERT (OK): INSERT INTO dbo.contract(id, note) VALUES (?,?)
      UPDATE (ERROR): UPDATE myproject . dbo.contract SET note = 'abc' WHERE (id= 2)
      DELETE (ERROR): DELETE FROM myproject . dbo.contract WHERE id = 217 AND note = 'xyz'

      UPDATE or DELETE of course ends by an error Invalid object name 'myproject.dbo.contract'.

      I tried any "hacks" with DBNAME, DATABASE, SCHEMA, CATALOG, but i don't know, how to remove unwanted "myproject" from MSSQL query. I read whole documentation about ODBC and CONNECT engine, but nothing helps.

      Is there any option or workaround how to disable this invalid "prefixing", or override it to proper name "externaldatabase"?

      Thank you.

      CREATE TABLE `company__contract`
      ENGINE=CONNECT
      TABLE_TYPE=odbc
      BLOCK_SIZE=100
      DEFAULT CHARSET=utf8mb4
      COLLATE=utf8mb4_czech_ci
      CONNECTION='DSN=mydsn;UID=myuid;PWD=mypwd;DATABASE=externaldatabase'
      DATA_CHARSET=utf8
      TABNAME='dbo.contract'
      ;

      Attachments

        Activity

          People

            Unassigned Unassigned
            jan.reges Ján Regeš
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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