1 Reply Latest reply on Jun 23, 2011 9:02 AM by Ramesh Reddy

    TRANSLATE CRITERIA WITH () ERROR

    iniciam Newbie

      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.