[teiid-issues] [JBoss JIRA] (TEIID-3827) multi-source view insert throwing TEIID30492 error
[
https://issues.jboss.org/browse/TEIID-3827?page=com.atlassian.jira.plugin...
]
Steve Tran updated TEIID-3827:
------------------------------
Steps to Reproduce:
- 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 {code}insert into ModelNam.<tablename> (idtestm,
randomstring,idtestp,randomint) values (0, "teststring", 0, 12345); {code}
was:
- 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 {code}insert into VDBName.<ttablename> (idtestm,
randomstring,idtestp,randomint) values (0, "teststring", 0, 12345); {code}
multi-source view insert throwing TEIID30492 error
--------------------------------------------------
Key: TEIID-3827
URL: https://issues.jboss.org/browse/TEIID-3827
Project: Teiid
Issue Type: Bug
Affects Versions: 8.7.3
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
Reporter: Steve Tran
Assignee: Steven Hawkins
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.
{quote}
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.
{quote}
I updated my INSERT tab on the EBL transformation page with the following.
{code}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
{code}
Postgres table
{code}
CREATE TABLE testp
(
idtestp integer NOT NULL,
randomint integer,
CONSTRAINT pk_id PRIMARY KEY (idtestp)
)
WITH (
OIDS=FALSE
);
ALTER TABLE testp
OWNER TO postgres;
{code}
MySQL table
{code}
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;
{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)