Error executing generated query in SQLExplorer

[aelmeswari]

Hello,

I have a view model generated from a source model from an Oracle Database.  There is no custom transformation involved.  Every table in the source model has a corresponding table in the view model.  I have created a VDB selecting both the view and source model with only the view model being visible.  I execute the vdb.  I then right click on one of the tables and click on the "Generate Select in SQL Editor" menu option.  When I execute the generated query I receive an "table or view does not exits" error.  Now, the user I listed in the connector has dba privileges, and using TOAD I am able to query this same table.  So it should't be related to any db privilege issue.  The interesting thing is that the execute query displayed in the stack trace is different from the one that was generated.  You'll notice that there is no schema prefix in the select statement.  I changed the user in the connector to be the schema owner and I still get the error

Generated Query in SQLExplorer:

     select column1, column2, column3,column4 from "MyVDB"."MyView.Table1"

Executed Query:

     SELECT g_0. column1, g_0. column2, g_0. column3, g_0. column4  FROM Table1 g_0

Any thoughts?

[rareddy]

Can you keep the visibility on the source model too and try it again?

The queries are different beacuse, in virtual model you can give any names to your view models, however during the execution time they will be converted to the source names before the execution happens.

[aelmeswari]

I made the source model visible.  But still no luck.  The tables in the view and source all have matching names.  Why does the executed query not have a schema prefix?  Any other thoughts?

[rareddy]

I believe that is one of the "import" options on the Designer, when you are importing the meta data for that source. Look at

http://docs.jboss.org/teiid/designer/6.0/user-guide/importers-chapter.html#jdbc-import-section

In Step 7: Source Object Names panel - Fully Qualified Source Names

Hope this helps.

Ramesh..

[aelmeswari]

Rameesh,

So I recreated the source and view models as well as the vdb using the fully qualified names and I get data back when I run the query in SQLExplorer.  I also have a junit test using 'jdbc:teiid:vdb-name@/opt/teiid-6.2.0/deploy.properties' as the url with the same query I executed in SQLExplorer.  However, when I run this test I get a MMSQLException: Group does not exist.  I copied the vdb to the deploy folder.  The vdb contains the source model as well as a view model containing all of the tables in the source model.  On top of that I have a second view model with just a few tables.  All three models are visible in the vdb.  Any idea what this exception means?

[rareddy]

The exception means there is no 'table' with the given name in any of the visible models inside the VDB.

From the JDBC URL string ''jdbc:teiid:vdb-name@/opt/teiid-6.2.0/deploy.properties", the VDB name is "vdb-name" is that correct? Check the log file and see if there are any errors. You can also turn up the logging by editing "log4j.xml" file.

Ramesh..

[aelmeswari]

Hi Ramesh,

"vdb-name" is not actually the name of the vdb, the name of the VDB is DAL and one of the view models is named "DAL_view".  The query I am using works fine in SQLExplorer, but not in the junit test.  Here is what I have in the test:

     def url = 'jdbc:teiid:DAL@/opt/teiid-6.2.0/deploy.properties'    
        def sql = '''select EquipmentId, Description, ModelNumber, Length, Width, Height, Volume, Area, Weight,
            UpdateDate from "DAL"."DAL_view.Table1" '''

        def db = Sql.newInstance(url,'admin','teiid','org.teiid.jdbc.TeiidDriver')

        db.eachRow(sql){row ->
            row.each{print it}
            println    ''
            }

I turned on more logging and this is what I get:

2010-03-04 10:54:13,357 INFO [main] org.teiid.COMMAND_LOG - 2010.03.04 10:54:13.357    START USER COMMAND:    startTime=2010.03.04 10:54:13.357    requestID=0.0    txID=null    sessionID=0    applicationName=JDBC    principal=admin    vdbName=DAL    vdbVersion=1    sql=select EquipmentId, Description, ModelNumber, Length, Width, Height, Volume, Area, Weight,
            UpdateDate from "DAL"."DAL_view.Table1"
2010-03-04 10:54:13,357 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.RESOURCE_POOLING - Beginning work with virtual worker Worker2_QueryProcessorQueue0
2010-03-04 10:54:13,357 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.DQP - 0.0 start processing MORE_WORK
2010-03-04 10:54:13,357 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.DQP - ############# PW PROCESSING on 0.0 with state NEW ###########
2010-03-04 10:54:13,357 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.DQP - 0.0 executing  select EquipmentId, Description, ModelNumber, Length, Width, Height, Volume, Area, Weight,
            UpdateDate from "DAL"."DAL_view.Table1"
2010-03-04 10:54:13,419 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.QUERY_RESOLVER - Resolving command SELECT EquipmentId, Description, ModelNumber, Length, Width, Height, Volume, Area, Weight, UpdateDate FROM DAL.DAL_view.Table1
2010-03-04 10:54:13,435 DEBUG [Worker2_QueryProcessorQueue0] org.teiid.DQP - [Ljava.lang.Object;@a33414
[QueryResolverException]Group does not exist: DAL.DAL_view.TUDET
    at com.metamatrix.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:879)
    at com.metamatrix.query.resolver.util.ResolverUtil.resolveGroup(ResolverUtil.java:970)
    at com.metamatrix.query.resolver.QueryResolver.isXMLQuery(QueryResolver.java:245)
    at com.metamatrix.query.resolver.QueryResolver.chooseResolver(QueryResolver.java:197)
    at com.metamatrix.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:171)
    at com.metamatrix.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:126)
    at com.metamatrix.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:98)
    at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:309)
    at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:460)
    at org.teiid.dqp.internal.process.Request.processRequest(Request.java:583)
    at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
    at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
    at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:619)

[rareddy]

hmm..

In your transformation of the table "DAL"."DAL_view.Table1", you are using another table called "DAL.DAL_view.TUDET", and engine is complaining that it does not have metadata for TUDET table inside the VDB.

Stop the Teiid server and try deleting the "DAL" folder in your "deploy" directory (this is exploded directory of the VDB, which is created upon the deployment) and restart and test.You need to do this every time you place a new VDB file inside the "deploy" directory to take on the new changes.

Also, if you have any duplicates of this VDB with different file names but same VDB name inside, remove them along with their deployed directory in "deploy" folder. Teiid 6.2 supports multiple VDBs with same name, however when it sees the second VDB with same name it deploys with updated version number. So, in that case when you connect to your VDB, it may be connecting to a wrong version than you expect.

Let us know if this does not help.

Ramesh..

[aelmeswari]

Thanks, that did it