7 Replies Latest reply on Jul 6, 2017 2:09 PM by kulbhushanc

    teiid: How to execute database specific function?

    kulbhushanc

      Hi,

       

      I am using teiid admin api version: 9.1.3 and WildFly 10.0.0 to test the scenario.

       

      I need to execute database specific function using teiid server.

      e.g. in mysql database we can apply encryption on a column using AES_ENCRYPT function so the query  will look like SELECT AES_ENCRYPT(a_Column,'1234') FROM a_Table a

      where a_Column is from table a_Table.

      When I tried to execute the query using teiid admin api it throws exception:  TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException.

       

      Similar exception I got when I tried executing hash8 and Encrypt functions of netezza database.

      I am also seeking for workaround if teiid not support these functions.

       

      Full stack trace:

      TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function 'AES_ENCRYPT(a.a_Column, '1234')' is an unknown form.  Check that the function name and number of arguments is correct.org.teiid.jdbc.TeiidSQLException: TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function 'AES_ENCRYPT(a.a_Column, '1234')' is an unknown form.  Check that the function name and number of arguments is correct.

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

        at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

        at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

        at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

        at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

        at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

        at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

        at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

        at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

        at java.lang.reflect.Method.invoke(Unknown Source)

        at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

        at com.sun.proxy.$Proxy1.read(Unknown Source)

        at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)

        at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:338)

        at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:501)

        at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:114)

        at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:81)

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function 'AES_ENCRYPT(a.a_Column, '1234')' is an unknown form.  Check that the function name and number of arguments is correct.

        at org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:691)

        at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:371)

        at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173)

        at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:54)

        at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:61)

        at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.postVisitVisitor(SimpleQueryResolver.java:223)

        at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:198)

        at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173)

        at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63)

        at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:182)

        at org.teiid.query.sql.symbol.ExpressionSymbol.acceptVisitor(ExpressionSymbol.java:63)

        at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63)

        at org.teiid.query.sql.navigator.AbstractNavigator.visitNodes(AbstractNavigator.java:76)

        at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:342)

        at org.teiid.query.sql.lang.Select.acceptVisitor(Select.java:168)

        at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63)

        at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.visit(SimpleQueryResolver.java:241)

        at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:70)

        at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:281)

        at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:127)

        at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:283)

        at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:413)

        at org.teiid.dqp.internal.process.Request.processRequest(Request.java:481)

        at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:657)

       

       

        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:338)

        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

        at java.lang.Thread.run(Unknown Source)

       

      Thanks,

      Kulbhushan Chaskar.