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.
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?
I believe that is one of the "import" options on the Designer, when you are importing the meta data for that source. Look at
In Step 7: Source Object Names panel - Fully Qualified Source Names
Hope this helps.
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?
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.
"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')
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
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.
Thanks, that did it