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