6 Replies Latest reply on Jul 5, 2018 8:02 AM by Steven Hawkins

    It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

    Murty Kirlampalli Newbie

      The below logic is good and able to connect two database tables(SQL Server and MySQL databases) using Dyanmic VDB:

       

      public static void main(String[] args) throws Exception {

       

      EmbeddedServer server = new EmbeddedServer();

       

      DataSource sqlServerDatasource = getDataSource("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://localhost:1433;databaseName=teiid", "user", "password");

      server.addConnectionFactory("java:/hello-ds", sqlServerDatasource);

      SQLServerExecutionFactory sqlServerExecutionFactory = new SQLServerExecutionFactory() ;

      sqlServerExecutionFactory.setSupportsDirectQueryProcedure(true);

      sqlServerExecutionFactory.start();

      server.addTranslator("translator-hello-ds", sqlServerExecutionFactory);

       

      DataSource mySqlDatasource = getDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/sakila", "user", "password");

      server.addConnectionFactory("java:/sakila-ds", mySqlDatasource);

      MySQL5ExecutionFactory mySqlExecutionFactory = new MySQL5ExecutionFactory() ;

      mySqlExecutionFactory.setSupportsDirectQueryProcedure(true);

      mySqlExecutionFactory.start();

      server.addTranslator("translator-sakila-ds", mySqlExecutionFactory);

       

      server.start(new EmbeddedConfiguration());

       

      Properties properties1 = new Properties();

      properties1.setProperty("importer.tableTypes", "TABLE,VIEW");

      properties1.setProperty("importer.tableNamePattern", "city");

      properties1.setProperty("importer.useQualifiedName", "true");

      properties1.setProperty("importer.useFullSchemaName", "true");

      properties1.setProperty("importer.schemapattern", "%");

      properties1.setProperty("importer.importKeys", "false");

      properties1.setProperty("importer.importProcedures", "false");

       

       

      ModelMetaData mmd1 = new ModelMetaData();

      mmd1.setName("mysql");

      mmd1.addSourceMapping("mysql", "translator-sakila-ds", "java:/sakila-ds");

      mmd1.setProperties(properties1);

       

      Properties properties2 = new Properties();

      properties2.setProperty("importer.tableTypes", "TABLE,VIEW");

      properties2.setProperty("importer.tableNamePattern", "orb_jdbc_driver_info");

      properties2.setProperty("importer.useQualifiedName", "true");

      properties2.setProperty("importer.useFullSchemaName", "true");

      properties2.setProperty("importer.schemapattern", "%");

      properties2.setProperty("importer.importKeys", "false");

      properties2.setProperty("importer.importProcedures", "false");

       

       

      ModelMetaData mmd2 = new ModelMetaData();

      mmd2.setName("sqlserver");

      mmd2.addSourceMapping("sqlserver", "translator-hello-ds", "java:/hello-ds");

      mmd2.setProperties(properties2);

       

       

      server.deployVDB("test", mmd1,mmd2);

       

      Connection c = server.getDriver().connect("jdbc:teiid:test;showplan=on", null);

       

      execute(c, "select max(b.id) from (select * from city) a, orb_jdbc_driver_info b where a.city_id=b.id", false);

      execute(c, "select a.city, b.id from (select * from city) a, orb_jdbc_driver_info b where a.city_id=b.id", false);

      }

       

       

      private static DataSource getDataSource(String driverClassName, String url, String userName, String password) {

      DriverManagerDataSource datasource = new DriverManagerDataSource();

      datasource.setDriverClassName(driverClassName);

      datasource.setUrl(url);

      datasource.setUsername(userName);

      datasource.setPassword(password);

      return (DataSource)datasource;

      }

       

       

      But when I used to connect with Oracle database, It fails and gives the error:

       

      public static void main(String[] args) throws Exception {

       

      EmbeddedServer server = new EmbeddedServer();

       

      DataSource oracleDatasource = getDataSource("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@localhost:1521:ORCL", "user", "password");

      server.addConnectionFactory("java:/orbqa1-ds", oracleDatasource);

      OracleExecutionFactory oracleExecutionFactory = new OracleExecutionFactory() ;

      oracleExecutionFactory.setSupportsDirectQueryProcedure(true);

      oracleExecutionFactory.start();

      server.addTranslator("translator-Oracle", oracleExecutionFactory);

       

      server.start(new EmbeddedConfiguration());

       

      Properties properties = new Properties();

      properties.setProperty("importer.tableTypes", "TABLE,VIEW");

      properties.setProperty("importer.tableNamePattern", "ORB_USER");

      properties.setProperty("importer.useQualifiedName", "true");

      properties.setProperty("importer.useFullSchemaName", "true");

      //properties2.setProperty("importer.schemapattern", "%");

      //properties2.setProperty("importer.importKeys", "false");

      //properties2.setProperty("importer.importProcedures", "false");

       

       

      ModelMetaData mmd2 = new ModelMetaData();

      mmd2.setName("oracle");

      mmd2.addSourceMapping("oracle", "translator-Oracle", "java:/orbqa1-ds");

      mmd2.setProperties(properties);

       

      server.deployVDB("test", mmd2);

       

      Connection c = server.getDriver().connect("jdbc:teiid:test;showplan=on", null);

       

      execute(c, "select * from ORB_USER", false);

      }

       

       

      private static DataSource getDataSource(String driverClassName, String url, String userName, String password) {

      DriverManagerDataSource datasource = new DriverManagerDataSource();

      datasource.setDriverClassName(driverClassName);

      datasource.setUrl(url);

      datasource.setUsername(userName);

      datasource.setPassword(password);

      return (DataSource)datasource;

      }

       

       

      I get the error as :

       

      org.teiid.jdbc.TeiidSQLException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: ORB_USER

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

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

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

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

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

      at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:144)

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

      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1095)

      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:335)

      at com.orbit.reporting.mashing.JDBCUtils.execute(JDBCUtils.java:82)

      at com.orbit.reporting.mashing.TeiidOracleAndSQLServerTest.main(TeiidOracleAndSQLServerTest.java:51)

      Caused by: org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: ORB_USER

      at org.teiid.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:921)

      at org.teiid.query.resolver.util.ResolverUtil.resolveGroup(ResolverUtil.java:983)

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

      at org.teiid.query.sql.symbol.GroupSymbol.acceptVisitor(GroupSymbol.java:127)

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

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

      at org.teiid.query.sql.lang.UnaryFromClause.acceptVisitor(UnaryFromClause.java:78)

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

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

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

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

      at org.teiid.query.sql.lang.From.acceptVisitor(From.java:142)

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

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

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

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

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

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

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

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

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

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

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

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

      at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:340)

      at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:255)

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

      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

      at java.lang.reflect.Method.invoke(Method.java:498)

      at org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:117)

      at org.teiid.transport.SessionCheckingProxy.invoke(SessionCheckingProxy.java:60)

      at com.sun.proxy.$Proxy16.executeRequest(Unknown Source)

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

      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

      at java.lang.reflect.Method.invoke(Method.java:498)

      at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:213)

      at java.util.concurrent.FutureTask.run(FutureTask.java:266)

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

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

      at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:211)

      at com.sun.proxy.$Proxy16.executeRequest(Unknown Source)

      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:709)

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

       

       

      I really appreciate if you provide resolutions on this ?

       

      Thanks

      Murty

        • 1. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
          Murty Kirlampalli Newbie

          I go through couple your responses regarding the above error and you suggested to add model name, I teid to execute in the following way :

          execute(c, "select * from oracle.ORB_USER", false);  "oracle" is the model name according to above example. But i still the below error :

           

          SQL: select * from oracle.ORB_USER

          INFO : org.teiid.COMMAND_LOG - START USER COMMAND: startTime=2018-07-04 20:18:07.248 requestID=xHYQ4lXlrsoH.0 txID=null sessionID=xHYQ4lXlrsoH applicationName=JDBC principal=anonymous vdbName=test vdbVersion=1 sql=select * from oracle.ORB_USER

          INFO : org.teiid.COMMAND_LOG - ERROR USER COMMAND: endTime=2018-07-04 20:18:07.256 requestID=xHYQ4lXlrsoH.0 txID=null sessionID=xHYQ4lXlrsoH principal=anonymous vdbName=test vdbVersion=1 finalRowCount=null

          WARN : org.teiid.PROCESSOR - TEIID30020 Processing exception for request xHYQ4lXlrsoH.0 'Group does not exist: oracle.ORB_USER'. Originally QueryResolverException ResolverUtil.java:921. Enable more detailed logging to see the entire stacktrace.

          INFO : org.teiid.COMMAND_LOG - END USER COMMAND: endTime=2018-07-04 20:18:07.256 requestID=xHYQ4lXlrsoH.0 txID=null sessionID=xHYQ4lXlrsoH principal=anonymous vdbName=test vdbVersion=1 finalRowCount=-1

          org.teiid.jdbc.TeiidSQLException: Group does not exist: oracle.ORB_USER

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

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

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

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

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

          at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:144)

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

          at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1095)

          at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:335)

          at com.orbit.reporting.mashing.JDBCUtils.execute(JDBCUtils.java:82)

          at com.orbit.reporting.mashing.TeiidOracleAndSQLServerTest.main(TeiidOracleAndSQLServerTest.java:51)

          Caused by: org.teiid.api.exception.query.QueryResolverException: Group does not exist: oracle.ORB_USER

          at org.teiid.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:921)

          at org.teiid.query.resolver.util.ResolverUtil.resolveGroup(ResolverUtil.java:1000)

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

          at org.teiid.query.sql.symbol.GroupSymbol.acceptVisitor(GroupSymbol.java:127)

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

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

          at org.teiid.query.sql.lang.UnaryFromClause.acceptVisitor(UnaryFromClause.java:78)

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

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

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

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

          at org.teiid.query.sql.lang.From.acceptVisitor(From.java:142)

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

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

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

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

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

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

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

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

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

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

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

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

          at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:340)

          at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:255)

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

          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

          at java.lang.reflect.Method.invoke(Method.java:498)

          at org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:117)

          at org.teiid.transport.SessionCheckingProxy.invoke(SessionCheckingProxy.java:60)

          at com.sun.proxy.$Proxy16.executeRequest(Unknown Source)

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

          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

          at java.lang.reflect.Method.invoke(Method.java:498)

          at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:213)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

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

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

          at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:211)

          at com.sun.proxy.$Proxy16.executeRequest(Unknown Source)

          at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:709)

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

          • 2. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
            Steven Hawkins Master

            You should query SYS.Tables to ensure that the table exists as you expect in the oracle schema/model.  Since you are importing the full schema name and not specifying a schema pattern, it's possible that ORB_USER exists in multiple source schema and would become Teiid tables named "oracle"."schema1.ORB_USER" and "oracle"."schema2.ORB_USER" - both of which can resolve against ORB_USER.  But neither would resolve against oracle.ORB_USER (assuming that neither source schema name is oracle).

            • 3. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
              Murty Kirlampalli Newbie

              Finally, I resolved this using the following approach :

              ModelMetaData mmd1 = new ModelMetaData();

              mmd1.setName("mysql");

              mmd1.addSourceMapping("mysql", "translator-sakila-ds", "java:/sakila-ds");

              mmd1.setProperties(properties1);

               

              Properties properties2 = new Properties();

              properties2.setProperty("importer.tableTypes", "TABLE,VIEW");

              properties2.setProperty("importer.tableNamePattern", "ORB_%");

              //properties.setProperty("importer.useQualifiedName", "true");

              properties2.setProperty("importer.useFullSchemaName", "false");

              properties2.setProperty("importer.schemapattern", "%ORB_MURTY");

               

               

               

              ModelMetaData mmd2 = new ModelMetaData();

              mmd2.setName("oracle");

              mmd2.addSourceMapping("oracle", "translator-Oracle", "java:/orbqa1-ds");

              mmd2.setProperties(properties2);

              • 4. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
                Murty Kirlampalli Newbie

                without giving modelname.schemaname.tablename syntax, it worked for me.

                 

                Ex :

                 

                execute(c, "select a.city, b.id from (select * from city) a, orb_jdbc_driver_info b where a.city_id=b.id", false);

                • 5. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
                  Murty Kirlampalli Newbie

                  This works good when I give table pattern and schema pattern correctly

                   

                  If I get a SQL guery dynamically, then How teiid import the metadata ?? Any solution for this ??

                   

                  Is there any importer property defined for this ? Please help me, I really struggle to resolve this.

                  • 6. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables
                    Steven Hawkins Master

                    > If I get a SQL guery dynamically, then How teiid import the metadata ?? Any solution for this ??

                     

                    The import properties should import as many tables/schemas as you want querable.  The issue that you are having is with importing from multiple source schema into a single Teiid schema/model - if the same table name exists in multiple source schema that causes the Teiid metadata to not quite be what you want.  An alternative is to import only a single source schema per Teiid schema/model.