TRANSLATE CRITERIA WITH () ERROR
Hi, i'm creating a VDB with 2 datasources : MySQL and Oracle 11g. I created 2 source model called: Defence_MySQL and HR_ORACLE.
Then i create a view model called "Employees" with below SQL for the SELECT:
SELECT
Defence_MySQL.Defence.Person.nric, Defence_MySQL.Defence.Person.orgid, Defence_MySQL.Defence.Person.firstname, Defence_MySQL.Defence.Person.lastname, Defence_MySQL.Defence.Person.sex, Defence_MySQL.Defence.Person.address, Defence_MySQL.Defence.Person.citizenship
FROM
Defence_MySQL.Defence.Person
UNION ALL
SELECT
HR_ORACLE.EMPLOYEES.NRIC AS nric, HR_ORACLE.EMPLOYEES.JOB_ID AS orgid, HR_ORACLE.EMPLOYEES.FIRST_NAME AS firstname, HR_ORACLE.EMPLOYEES.LAST_NAME AS lastname, 'M' AS sex, 'singapore' AS address, 'SG' AS citizenship
FROM
HR_ORACLE.EMPLOYEES INNER JOIN HR_ORACLE.ORGANIZATION ON HR_ORACLE.EMPLOYEES.JOB_ID = HR_ORACLE.ORGANIZATION.ORGID
I made this table "updatable", and i cannot use the default SQL because of UNION ALL clause, so below is my update procedure:
CREATE PROCEDURE
BEGIN
UPDATE Defence_MySQL.Defence.Person SET lastname = INPUTS.lastname WHERE TRANSLATE CRITERIA;
ROWS_UPDATED = UPDATE HR_ORACLE.EMPLOYEES SET LAST_NAME = INPUTS.lastname WHERE TRANSLATE CRITERIA;
END
This works fine if i update the MySQL database but when i tried to update the Oracle database i got this error:
Error Code:0 Message:Remote org.teiid.api.exception.query.ExpressionEvaluationException: Error Code:0 Message:Unable to evaluate (UPDATE HR_ORACLE.EMPLOYEES SET LAST_NAME = 'Hoffman' WHERE Defence_MySQL.Defence.Person.nric = 'S3030455C'): Error Code:0 Message:Error Code:0 Message:Error Code:0 Message:Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 7f000001:e161:4e0195c3:3d2c status: ActionStatus.ABORT_ONLY >); - nested throwable: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 7f000001:e161:4e0195c3:3d2c status: ActionStatus.ABORT_ONLY >))
I expect this to happen because UNION ALL made the wrong update SQL so i thought TRANSLATE CRITERIA WITH can help me to solve the problem, so i changed my new Update Procedure to:
CREATE PROCEDURE
BEGIN
UPDATE Defence_MySQL.Defence.Person SET lastname = INPUTS.lastname WHERE TRANSLATE CRITERIA;
ROWS_UPDATED = UPDATE HR_ORACLE.EMPLOYEES SET LAST_NAME = INPUTS.lastname WHERE TRANSLATE CRITERIA WITH (Defence_MySQL.Defence.Person.nric = HR_ORACLE.EMPLOYEES.NRIC);
END
i'm hoping that TRANSLATE CRITERIA WITH (Defence_MySQL.Defence.Person.nric = HR_ORACLE.EMPLOYEES.NRIC) can change the update SQL from
"UPDATE HR_ORACLE.EMPLOYEES SET LAST_NAME = 'Hoffman' WHERE Defence_MySQL.Defence.Person.nric = 'S3030455C'"
into
"UPDATE HR_ORACLE.EMPLOYEES SET LAST_NAME = 'Hoffman' WHERE HR_ORACLE.EMPLOYEES.NRIC = 'S3030455C'"
but when i validated the update procedure, the designer gave an error message below:
The query is parsable but not resolvable. Resolver Error: Symbol Defence_MySQL.Defence.Person.nric is specified with an unknown group context |
I read the documentation on TRANSLATE CRITERIA WITH again and again but i still don't understand what's causing the error. I read in this forum that above update procedure worked for virtual table that join 2 tables (not using UNION ALL).
Can anyone tell me what happen and what's the best way to create an update procedure for virtual table that using UNION ALL clause ?
I think UNION ALL will be used a lot in this kind of virtual database project and i cannot find much information on Teiid documentation on this.
Thank you.
