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

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

klmurty Jul 4, 2018 10:41 AM

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

klmurty Jul 4, 2018 10:52 AM (in response to klmurty)

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)
Actions
2. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

shawkins Jul 4, 2018 3:13 PM (in response to klmurty)

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).
Actions
3. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

klmurty Jul 5, 2018 12:29 AM (in response to shawkins)

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);
Actions
4. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

klmurty Jul 5, 2018 12:31 AM (in response to klmurty)

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);
Actions
5. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

klmurty Jul 5, 2018 12:51 AM (in response to klmurty)

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.
Actions
6. Re: It fails to execute Oracle database query using teiid, but is successfull with MySQL and MSSQL server database tables

shawkins Jul 5, 2018 8:02 AM (in response to klmurty)

> 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.
Actions

Go to original post