Thanks for your input Ramesh.
For #1, the following query works in DBVisualizer:
SELECT TOP 1000 g_0."tag" AS c_0, g_0."time" AS c_1, g_0."value" AS c_2 FROM "piarchive"."picomp2" AS g_0
WHERE g_0."tag" = 'sinusoid' AND g_0."time" > '2016-04-20 00:00:00.0'
However, putting the {ts } syntax in makes it fail with the same error as shown in Teiid.
Yes, this is the standard PI JDBC adapter from OSIsoft. I had to make one change to it as noted above, which was to add the java.sql.driver file inside the META-INF\services
For #2: using full schema names as per your suggestion worked. I was able to run the simple query I was originally trying and that deployed fine. However, trying to write a more useful query, I'm running into a couple of errors.
a) for the query below, I get the error
20:29:09,217 WARN [org.teiid.PLANNER.RESOLVER] (Worker2_async-teiid-threads8)
TEIID31080 PIAF-Virtual.IceDataThree validation error: TEIID30492 Non-comparable
expression of type object cannot be used in comparison: ea.ElementID = eh.Eleme
ntID.
20:29:09,233 INFO [org.teiid.RUNTIME] (Worker2_async-teiid-threads8) TEIID4007
3 The metadata for the VDB PIAF.1 is loaded, however it is not valid. Check mode
ls for errors. Correct the metadata and re-deploy.
20:29:09,233 INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker2_async-teiid
-threads8) TEIID40003 VDB PIAF.1 is set to FAILED
Model:
<model name="PIAF-Physical" type="PHYSICAL">
<property name="importer.useFullSchemaName" value="true"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false" />
<source name="piaf-connector" translator-name="osisoft-pi" connection-jndi-name="java:/pi-ds"/>
</model>
<model name="PIAF-Virtual" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIEW IceDataThree (Parent varchar, ElementName varchar, Attribute varchar, TimestampResult timestamp, ValueResult object) AS
SELECT eh.Path, eh.Name, ea.Name, a.Time, a.Value
FROM "PIAF-Physical"."Calgary Flames"."Asset"."ElementHierarchy" eh
INNER JOIN "PIAF-Physical"."Calgary Flames"."Asset"."ElementAttribute" ea ON ea.ElementID = eh.ElementID
INNER JOIN "PIAF-Physical"."Calgary Flames"."Data"."Archive" a ON a.ElementAttributeID = ea.ID
WHERE eh.Path = N'\'
AND a.Time BETWEEN N'*-1h' AND N'*'
]]> </metadata>
</model>
I can see the schema in DBVisualizer and the data types are Guids..
b) It seems to dislike adding extra options to queries which are normally supported by PI OLEDB Enterprise. For instance, it complains about the following query with the error below:
CREATE VIEW IceDataThree (Parent varchar, ElementName varchar, Attribute varchar, TimestampResult timestamp, ValueResult object) AS
SELECT eh.Path, eh.Name, ea.Name, a.Time, a.Value
FROM "PIAF-Physical"."Calgary Flames"."Asset"."ElementHierarchy" eh
INNER JOIN "PIAF-Physical"."Calgary Flames"."Asset"."ElementAttribute" ea ON ea.ElementID = eh.ElementID
INNER JOIN "PIAF-Physical"."Calgary Flames"."Data"."Archive" a ON a.ElementAttributeID = ea.ID
WHERE eh.Path = N'\'
AND a.Time BETWEEN N'*-1h' AND N'*'
OPTION (FORCE ORDER, EMBED ERRORS)
Error:
20:44:57,879 WARN [org.teiid.RUNTIME] (Worker1_async-teiid-threads1) TEIID5003
6 VDB PIAF.1 model "PIAF-Virtual" metadata failed to load. Reason:TEIID30386 org
.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encou
ntered "AND N'*' OPTION [*]([*]FORCE ORDER" at line 9, column 32.
Was expecting: <EOF> | "alter" | "create" | "makedep" | "makeind" | "makenotdep"
| "nocache" | "set" | ";": org.teiid.metadata.ParseException: TEIID30386 org.te
iid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encounte
red "AND N'*' OPTION [*]([*]FORCE ORDER" at line 9, column 32.
Was expecting: <EOF> | "alter" | "create" | "makedep" | "makeind" | "makenotdep"
| "nocache" | "set" | ";"
at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:472)
at org.teiid.metadata.MetadataFactory.parse(MetadataFactory.java:768)
at org.teiid.query.metadata.DDLMetadataRepository.loadMetadata(DDLMetada
taRepository.java:40)
at org.teiid.runtime.AbstractVDBDeployer$MetadataRepositoryWrapper.loadM
etadata(AbstractVDBDeployer.java:84)
at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(Chai
ningMetadataRepository.java:55)
at org.teiid.jboss.VDBService$6.run(VDBService.java:395)
at org.teiid.jboss.VDBService$7.run(VDBService.java:446)
Thanks again in advance!
