Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3827

multi-source view insert throwing TEIID30492 error

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.7.3
    • Fix Version/s: 8.12.3, 8.13
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      Red Hat JBoss Data Virtualization 6.2 on EAP6.4.0 patched to version 6.4.3,

      JBoss Developer Studio 8.1.0GA with Teiid Designer plugin 9.0.3.Final.v20150810-1438-B1157

      MySQL 5.7 and Postgres 9.4

      64-bit Windows 7 environment

    • Steps to Reproduce:
      Hide
      • Create VBL with 1 to 1 mapping to data source tables.
      • Create EBL that is composed of both VBLs
      • Update the INSERT tab with the code pasted above
      • Create a VDB from the EBL you created above
      • Execute the VDB inside of JBoss Developer Studio to bring up a SQL command prompt, where you can send

        insert into ModelName.<tablename> (idtestm, randomstring,idtestp,randomint) values (0, "teststring", 0, 12345); 

      Show
      Create VBL with 1 to 1 mapping to data source tables. Create EBL that is composed of both VBLs Update the INSERT tab with the code pasted above Create a VDB from the EBL you created above Execute the VDB inside of JBoss Developer Studio to bring up a SQL command prompt, where you can send insert into ModelName.<tablename> (idtestm, randomstring,idtestp,randomint) values (0, "teststring", 0, 12345);

      Description

      I'm trying to POC this multi-source INSERT. I have a MySQL DB and Postgres DB on my local machine. Added the drivers, datasource, and created a VBL with 1:1 mapping to my test tables. I created an EBL that is a join of my two tables. Both VBLs and EBL are set to support updates. I'm trying to work through this error.

      insert into "Joined_EBL"."joined2"(idtestm, randomstring, idtestp, randomint) values(0, 'randomstring', 0, 12345)
      org.teiid.runtime.client.TeiidClientException: org.teiid.api.exception.query.QueryValidatorException: Remote TEIID30492 A multi-source table, Joined_EBL.joined2, cannot be used in an INSERT with query expression or SELECT INTO statement.

      I updated my INSERT tab on the EBL transformation page with the following.

      FOR EACH ROW
      BEGIN ATOMIC
      INSERT INTO Postgres_VBL.testp (Postgres_VBL.testp.idtestp, Postgres_VBL.testp.randomint) VALUES (0, "NEW".randomint);
      INSERT INTO MySQL_VBL.testm (MySQL_VBL.testm.idtestm, MySQL_VBL.testm.randomstring) VALUES (0, "NEW".randomstring);
      END
      

      Postgres table

      CREATE TABLE testp
      (
      idtestp integer NOT NULL,
      randomint integer,
      CONSTRAINT pk_id PRIMARY KEY (idtestp)
      )
      WITH (
      OIDS=FALSE
      );
      ALTER TABLE testp
      OWNER TO postgres;
      

      MySQL table

      CREATE TABLE `testm` (
      `idtestm` int(11) NOT NULL AUTO_INCREMENT,
      `randomstring` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`idtestm`),
      UNIQUE KEY `idtestm_UNIQUE` (`idtestm`)
      ) ENGINE=InnoDB DEFAULT CHARSET=tis620;
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                sqtran Steve Tran
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: