Execution sequence in Teiid Custom Translator.

[anilnair]

Hi All,

I have written a custom translator which would execute a stored procedure every time(before and after) I execute a query. The function of storeprocedure is set some value and the query that i would be issuing would read values based on the value set and the once the value is set the last storedprocedure woudl reset the value which was set  by the first storedprocedure.

The custom translator that i have written for this as follows

@Translator(name="interceptor", description="interceptor")
public class InterceptOrExecutionFactory  extends org.teiid.translator.BaseDelegatingExecutionFactory<Object,Object>{
  private String tenantAccountNumber;

  @Override
  public ResultSetExecution createResultSetExecution(QueryExpression command,
  ExecutionContext executionContext, RuntimeMetadata metadata,
  Object connection) throws TranslatorException {


  Object payload=  executionContext.getCommandPayload();

  String tenantAccountNumber=(String)payload;
  System.out.println((String)payload + "Anil");
  CallableStatement statement3;
  try {
  statement3 = ((Connection) connection)
  .prepareCall("call UpdateStatus(?)");
  statement3.setString("name", tenantAccountNumber);
  statement3.execute();
  statement3.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  ResultSetExecution result=super.createResultSetExecution(command, executionContext,
  metadata, connection);
  //This is the reset proc
  CallableStatement statement4;
  try {
  statement4 = ((Connection) connection)
  .prepareCall("call ResetStatus(?)");
  statement4.setString("name", tenantAccountNumber);
  statement4.execute();
  statement4.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  return result;
  }
}

The problem is that

1. both the storeprocedures gets called first and then the query that I am issuing gets fired and  does not find any record even though I have code for resultset execution separated from 2 storedprocedure calls

So is there a way to achieve I can specify the or control the sequence of execution, also the other question is will caching of queries(frequently used ones) hinder my logic.

Thanks

Anil

[rareddy]

Because "createResultsetExecution" is not same as executing it. so, you want do like

public class MyExecutor extends JDBCQueryExecution {
    private Connection connection;
    private Object payload;

    public MyExecutor(Command command, Connection connection,
            ExecutionContext context, JDBCExecutionFactory env, Object payload) {
        super(command, connection, context, env);
        this.connection = connection;
       this.payload = payload;
    }

    @Override
    public void execute() throws TranslatorException {
        beforeExecution();
        super.execute();
    }
    @Override
    public List<?> next() throws TranslatorException, DataNotAvailableException {
        List<?> result = super.next();
        if (result == null) {
            afterExecution();
        }
        return result;
    }
    private void beforeExecution() {
        // TODO Auto-generated method stub
    }    
    private void afterExecution() {
        // TODO Auto-generated method stub
    }
}

Then In extended translator

@Translator(name = "interceptor", description = "interceptor")
public class InterceptOrExecutionFactory extends JDBCExecutionFactory {
    private String tenantAccountNumber;

    @Override
    public ResultSetExecution createResultSetExecution(QueryExpression command,
            ExecutionContext executionContext, RuntimeMetadata metadata,
            Connection conn) {
        Object payload = executionContext.getCommandPayload();
        MyExecutor exec = new MyExecutor(command, conn, executionContext, this, payload);
        return exec;
    }
}

[anilnair]

Thanks Ramesh,

When I  use this approach the deployment was failing with the below exception.

12:30:19,573 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-11)  MSC000001: Failed to start service jboss.deployment.unit."translator2-0.0.1-SNAPSHOT.jar".INSTALL: org.jb

oss.msc.service.StartException in service jboss.deployment.unit."translator2-0.0.1-SNAPSHOT.jar".INSTALL: JBAS018733: Failed to process phase INSTALL of deployment "translator2-0.0

.1-SNAPSHOT.jar"

        at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:166) [jboss-as-server-7.4.0.Final-redhat-4.jar:7.4.0.Final-redhat-4]

        at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1980) [jboss-msc-1.1.5.Final.jar:1.1.5.Final]

        at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1913) [jboss-msc-1.1.5.Final.jar:1.1.5.Final]

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_75]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_75]

        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_75]

Caused by: java.lang.LinkageError: Failed to link com/anil/teiid/translator2/InterceptOrExecutionFactory (Module "deployment.translator2-0.0.1-SNAPSHOT.jar:main" from Service Modul

e Loader)

        at org.jboss.modules.ModuleClassLoader.defineClass(ModuleClassLoader.java:428) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ModuleClassLoader.loadClassLocal(ModuleClassLoader.java:261) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ModuleClassLoader$1.loadClassLocal(ModuleClassLoader.java:76) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.Module.loadModuleClass(Module.java:548) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:189) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:443) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:431) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:373) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:118) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at java.lang.Class.forName0(Native Method) [rt.jar:1.7.0_75]

        at java.lang.Class.forName(Class.java:274) [rt.jar:1.7.0_75]

        at java.util.ServiceLoader$LazyIterator.next(ServiceLoader.java:363) [rt.jar:1.7.0_75]

        at java.util.ServiceLoader$1.next(ServiceLoader.java:445) [rt.jar:1.7.0_75]

        at org.teiid.jboss.TranslatorDeployer.deploy(TranslatorDeployer.java:67)

        at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:159) [jboss-as-server-7.4.0.Final-redhat-4.jar:7.4.0.Final-redhat-4]

        ... 5 more

Caused by: java.lang.NoClassDefFoundError: org/teiid/translator/jdbc/JDBCExecutionFactory

        at java.lang.ClassLoader.defineClass1(Native Method) [rt.jar:1.7.0_75]

        at java.lang.ClassLoader.defineClass(ClassLoader.java:800) [rt.jar:1.7.0_75]

        at org.jboss.modules.ModuleClassLoader.doDefineOrLoadClass(ModuleClassLoader.java:345) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ModuleClassLoader.defineClass(ModuleClassLoader.java:423) [jboss-modules.jar:1.3.0.Final-redhat-2]

        ... 19 more

Caused by: java.lang.ClassNotFoundException: org.teiid.translator.jdbc.JDBCExecutionFactory from [Module "deployment.translator2-0.0.1-SNAPSHOT.jar:main" from Service Module Loader

]

        at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:197) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:443) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:431) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:373) [jboss-modules.jar:1.3.0.Final-redhat-2]

        at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:118) [jboss-modules.jar:1.3.0.Final-redhat-2]

Since I was getting the NoClassDefFoundError I changed the porm file to include the transalator-jdbc  (previously in the porm file had scope for this as "provided" when i was getting this error

<dependency>
  <groupId>org.jboss.teiid.connectors</groupId>
  <artifactId>translator-jdbc</artifactId>
  <version>8.10.1</version>
  </dependency>

So now I have a module file, translator jar file and translator-jdbc-8.10.1 jar file(this was previously not there)

Now during the install the   I am getting the following error.

{"JBAS014653: Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => {"JBAS014671: Failed services" => {"jboss.deployment.unit.\"translator-jdbc-8.10.1.jar\".INSTALL" => "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"translator-jdbc-8.10.1.jar\".INSTALL: JBAS018733: Failed to process phase INSTALL of deployment \"translator-jdbc-8.10.1.jar\"

    Caused by: org.jboss.msc.service.DuplicateServiceException: Service jboss.teiid.translator.jdbc-ansi is already registered"}}}}

Thanks

Anil

[rareddy]

Did you use the Archtype to define your translator project structure as defined here? https://docs.jboss.org/author/display/TEIID/Archetype+Template+Translator+Project

• Create project structure using the above.
• Then replace the ExecutionFactory that is generated with yours, and the same goes for the ResultSetExecution above
• Remove any other verbose classes it generated.
• Add the above dependency to "pom.xml" and leave it "provided"
• In "module.xml" file, add <module name="org.jboss.teiid.translator.jdbc" /> under dependencies.

For an example see this rareddy/pi · GitHub or just copy this, and edit for your needs. Then build using "mvn clean install", it will build "dist" file in "target", unzip that over your <jboss-eap> then edit the standaalone-teiid.xml and under Teiid subsystem add something like

<translator name="interceptor" module="org.jboss.teiid.translator.interceptor"/>

assuming your translator name you gave is "interceptor" and used the above package naming correctly. Restart the server, and use "interceptor" as translator name on your source model in your VDB.

That is it. HTH

Ramesh..

[rareddy]

So, in that case you do not have Teiid properly installed. If installed, then some issue with your modules directory. Make sure the new translator and jdbc translator jar files are in "modules/system/layers/base/org/jboss/teiid/translators/" directory under "vi/main" and "jdbc/main" directories and with correct names in the module.xml files.

I know I test PI translator above, that worked fine in my environment.

Ramesh..

[anilnair]

Thank you Ramesh. I was able to fix it with my old teiid installation.

The step that i was missing from your previous reply was to the overlaying the translator on the modules folder.

I was trying to deploy it by copying it to the deployment folder and that'w when I was getting the error.

once again thank you for the help