1 2 Previous Next 27 Replies Latest reply on Jun 1, 2016 10:03 AM by Al S

    Teiid PI JDBC Connection Error - Null Pointer Exception

    Al S Newbie

      Hello,

       

      I am trying to set up a JDBC connection to PI. I have configured the PI connection correctly as I am able to test it out using DB Visualizer and using a test Java application provided by the vendor. I can also "ping" the connection successfully from within Eclipse.  My end goal is to consume PI data through Teiid.

       

      However, when trying to set up a JDBC connection using Teiid in Eclipse, I get the error:

      Error creating SQL Model Connection connection to PI JDBC. (Error: null)

      java.lang.NullPointerException

      Could not connect to PI JDBC.

      Error creating SQL Model Connection connection to PI JDBC. (Error: null)

      java.lang.NullPointerException

      OK

       

      In the Data Sources Window in Eclipse, despite throwing the error it seems to indicate that it was able to connect to PI data access gateway successfully since it knows the version number.Teiid.PNG

      Here is the working configuration string from DB Visualizer:

      DBVisualizer.PNG

      I tried this with both the 32 and 64 bit versions of Eclipse.

       

      Any help is much appreciated!

        • 1. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
          Ramesh Reddy Master

          Al,

           

          Where in Eclipse you are trying setup the JDBC connection, in Database Explorer? or using the JDBC import? Database Explorer gives you the DB visualizer kind of environment, where as Import --> JDBC Source gives the ability to import the metadata in Teiid Model.  I think the NPE you are seeing may be due to the PI JDBC driver's non compliance to support all the metadata calls that eclipse is interrogating. May be you can find the full stack to see the issue.

           

          Ramesh..

          • 2. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
            Al S Newbie

            I have now been able to successfully deploy the JDBC adapter on the server. There is still a problem using Eclipse but I am fine trying to work with this through the VDB XML files. There was a problem with the JDBC adapter as it did not contain the driver definition file inside META-INF/services.

             

            I am now having a problem whereby the deployment of the VDB throws a duplicate table exception, but strangely I'm not even calling the particular table function mentioned in the query. Any ideas as to why this is happening?

             

            Example 1

            The following query works: select tag from pipoint.pipoint where tag='sinusoid'

            JDBC connection: jdbc:pisql://iscapps/Data Source=iscpi;Integrated Security=SSPI; 

            Example 2 - this query does not work: SELECT Name FROM [Calgary Flames].[Asset].[ElementHierarchy] eh.

            JDBC Connection: jdbc:pioledbent://iscapps/Data Source=iscaf;Integrated Security=SSPI;

             

            Here is the error message that is thrown:

            VDB PIAF.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID60013 Du 
            plicate Table ft_GetPIPoint: org.teiid.metadata.DuplicateRecordException: TEIID6 
            0013 Duplicate Table ft_GetPIPoint 
                    at org.teiid.metadata.Schema.addTable(Schema.java:49) 

             

            Here is the definition of the VDB file:

            <vdb name="PIAF" version="1">  
                <description>OSIsoft PI AF</description>    
                <model name="PIAF-Physical" type="PHYSICAL"> 
                    <property name="importer.useFullSchemaName" value="false"/> 
              <property name="importer.tableTypes" value="TABLE,VIEW"/> 
                   <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 IceData ( 
                        Parent varchar(max)
                    ) AS  
                      SELECT eh.Name as Parent FROM [Calgary Flames].[Asset].[ElementHierarchy]
                    ]]> </metadata> 
                </model> 
            </vdb> 

             

            Any thoughts? Are VDBs that query from the Asset.* hierarchies currently supported?

            • 3. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
              Al S Newbie

              Here's a quick update. The query in example 1 was working earlier today but for some reason does not work now. It's the exact same query, but now when the server loads up I see the following error:

               

              6 VDB PIAF2.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID11010 j

              ava.sql.SQLException: java.lang.Integer cannot be cast to java.lang.Short: org.t

              eiid.translator.TranslatorException: TEIID11010 java.sql.SQLException: java.lang

              .Integer cannot be cast to java.lang.Short

                      at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut

              ionFactory.java:297)

                      at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut

              ionFactory.java:68)

                      at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeM

              etadataRepository.java:92)

                      at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(Native

              MetadataRepository.java:60)

                      at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(Chai

               

              Not sure where it's trying to do the conversion as I'm only selecting a string tag. Here is the VDB again:

              <vdb name="PIAF2" version="1"> 

                  <description>OSIsoft PI AF</description>   

                  <model name="PIAF-Physical" type="PHYSICAL">

                      <property name="importer.useFullSchemaName" value="false"/>

                <property name="importer.tableTypes" value="TABLE,VIEW"/>

                      <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 IceDataTwo (

                          tag varchar(max)

                      ) AS 

                        select tag from pipoint.pipoint where tag='sinusoid'

                      ]]> </metadata>

                  </model>

              </vdb>

              • 4. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                Steven Hawkins Master

                Can you provide the full stack trace?  java.lang.Integer cannot be cast to java.lang.Short could be coming from the driver itself on a DatabaseMetaData call to ResultSet.getShort, which is expected to work.  Also which version of Teiid are you using?

                • 5. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                  Al S Newbie

                  I am using Teiid 8.13.3.

                   

                  Here is the full stack trace:

                  21:27:03,920 WARN  [org.teiid.RUNTIME] (Worker0_async-teiid-threads0)  TEIID5003

                  6 VDB PIAF2.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID11010 j

                  ava.sql.SQLException: java.lang.Integer cannot be cast to java.lang.Short: org.t

                  eiid.translator.TranslatorException: TEIID11010 java.sql.SQLException: java.lang

                  .Integer cannot be cast to java.lang.Short

                          at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut

                  ionFactory.java:297)

                          at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut

                  ionFactory.java:68)

                          at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeM

                  etadataRepository.java:92)

                          at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(Native

                  MetadataRepository.java:60)

                          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)

                          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkCon

                  text.java:276)

                          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.ru

                  n(ThreadReuseExecutor.java:119)

                          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseE

                  xecutor.java:210)

                          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.

                  java:1142)

                          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor

                  .java:617)

                          at java.lang.Thread.run(Thread.java:745)

                  Caused by: java.sql.SQLException: java.lang.Integer cannot be cast to java.lang.

                  Short

                          at com.osisoft.jdbc.ResultSetImpl.getShort(ResultSetImpl.java:544)

                          at org.teiid.translator.jdbc.JDBCMetdataProcessor.getPrimaryKeys(JDBCMet

                  dataProcessor.java:495)

                          at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(J

                  DBCMetdataProcessor.java:162)

                          at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut

                  ionFactory.java:295)

                  • 6. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                    Steven Hawkins Master

                    So this is effectively a bug with their ResultSet/DatabaseMetaData.  You could try disabling the import of keys - set the model property importer.importKeys=false - and that should get you past this error.  It will take a code change though to address this in general, can you log a JIRA for that?

                    • 7. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                      Al S Newbie

                      Thanks Steven. Where exactly can I make this model property change?

                       

                      I saw your response to the JIRA and modified the query to use varchar (see below). This got rid of the QueryParserException but did not get rid of the Integer / Short conversion error mentioned in the earlier post.

                      CREATE VIEW IceDataTwo (

                                  tag varchar

                              ) AS 

                                select tag from pipoint.pipoint where tag='sinusoid'

                            

                      https://issues.jboss.org/browse/TEIID-4173

                      Also, what about the original error with the different query above - is this also a problem with the vendor's schema? Nowhere in my query do I call ft_GetPIPoint

                      VDB PIAF.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID60013 Du 

                      plicate Table ft_GetPIPoint: org.teiid.metadata.DuplicateRecordException: TEIID6 

                      0013 Duplicate Table ft_GetPIPoint 

                              at org.teiid.metadata.Schema.addTable(Schema.java:49)

                      CREATE VIEW IceData ( 

                                  Parent varchar(max)

                              ) AS  

                                SELECT eh.Name as Parent FROM [Calgary Flames].[Asset].[ElementHierarchy]

                      • 8. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                        Ramesh Reddy Master

                        he suggesting to change the VDB importer options to exclude importing of the index key information like

                         

                        <?xml version="1.0" encoding="UTF-8"?>
                        <vdb name="PIAF2" version="1">
                          <description>OSIsoft PI AF</description>
                            <model name="PIAF-Physical" type="PHYSICAL">
                            <property name="importer.useFullSchemaName" value="false" />
                            <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 IceDataTwo (
                              tag varchar
                              ) AS 
                              select tag from pipoint.pipoint where tag='sinusoid']]></metadata>
                          </model>
                        </vdb>
                        
                        
                        

                         

                        All the available importer options can be found here JDBC Translators | Teiid Documentation look at importer properties for JDBC based sources.

                         

                        Ramesh..

                        • 9. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                          Al S Newbie

                          Thanks Ramesh. I changed the property value as described and the model was able to deploy, but there is still a warning in the logs. Executing a Teiid client query against the model fails (tried it with DBVisualizer).

                           

                          Warning in Teiid:

                          - 65)  WFLYUT0021: Registered web context: /odata
                          18:10:09,455 INFO  [org.teiid.CONNECTOR] (Worker0_async-teiid-threads0)  PIExecu
                          tionFactory Commit=true;DatabaseProductName=PI SQL Data Access Server 1.4.1.403
                          using PIOLEDB;DatabaseProductVersion=PIOLEDB: 3.3.1.2;DriverMajorVersion=1;Drive
                          rMajorVersion=4;DriverName=com.osisoft.jdbc.Driver;DriverVersion=1.4.1.404;Isola
                          tionLevel=0
                          18:10:09,689 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 35)  WFLY
                          SRV0010: Deployed "PIJDBCDriver.jar" (runtime-name : "PIJDBCDriver.jar")
                          18:10:09,705 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 35)  WFLY
                          SRV0010: Deployed "PIAF-vdb.xml" (runtime-name : "PIAF-vdb.xml")
                          18:10:09,721 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 35)  WFLY
                          18:10:10,455 INFO  [org.jboss.as] (Controller Boot Thread)  WFLYSRV0025: WildFly
                          Full 9.0.2.Final (WildFly Core 1.0.2.Final) started in 25859ms - Started 632 of
                          878 services (313 services are lazy, passive or on-demand)
                          18:10:10,986 INFO  [org.teiid.RUNTIME] (Worker0_async-teiid-threads0)  TEIID5003
                          0 VDB PIAF2.1 model "PIAF-Physical" metadata loaded. End Time: 4/26/16 6:10 PM
                          18:10:11,049 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads0)
                          TEIID31080 PIAF-Virtual.IceDataTwo validation error: Group does not exist: pipoi
                          nt.pipoint
                          18:10:11,064 INFO  [org.teiid.RUNTIME] (Worker0_async-teiid-threads0)  TEIID4007
                          3 The metadata for the VDB PIAF2.1 is loaded, however it is not valid. Check mod
                          els for errors. Correct the metadata and re-deploy.
                          18:10:11,064 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker0_async-teiid
                          -threads0)  TEIID40003 VDB PIAF2.1 is set to FAILED
                          

                          Error in DBVisualizer:

                          Physical database connection acquired for: Teiid
                          18:12:59  [SELECT - 0 row(s), 0.094 secs]  [Error Code: 31099, SQL State: 50000]  TEIID31099 VDB PIAF2.1[PIAF-Physical{piaf-connector=piaf-connector, osisoft-pi, java:/pi-ds}, PIAF-Virtual{}] is not active, but FAILED.  If loading you can resubmit your query after loading has completed or after the errors have been corrected.
                          ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.094/0.000 sec  [0 successful, 0 warnings, 1 errors]
                          

                           

                          Latest VDB:

                          <vdb name="PIAF2" version="1"> 
                              <description>OSIsoft PI AF</description>   
                              <model name="PIAF-Physical" type="PHYSICAL">
                                  <property name="importer.useFullSchemaName" value="false"/>
                            <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 IceDataTwo (
                                      tag varchar
                                  ) AS 
                                    select tag from pipoint.pipoint where tag='sinusoid'
                                  ]]> </metadata>
                              </model>
                          </vdb>
                          
                          • 10. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                            Ramesh Reddy Master

                            The error is saying that your usage of "pipoint.pipoint" table is not found with the schema import that has been done. It may be possible it is just "pipoint" or some other catalog name before it. If you want to use full name, it needs to be "PIAF-Physical"."xxx.pipoint". One way to make sure is remove your virtual model from the vdb.xml, deploy the VDB, then take look at the metadata imported using SquirreL or Web-Console, then insert back the virtual model with correct names in your View transformation of "IceDataTwo"

                            • 11. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                              Al S Newbie

                              Thanks Ramesh. This helped quite a bit. We are inching closer. I'm now able to get through to the source system and run some very simple queries using the PI OLEDB connection. However, to be useful these queries typically filter on tag and time, but the translation engine seems to be adding some extra text which is causing an error in the query. I'm also still trying to figure out why the OLEDB Enterprise connection query throws a duplicate record error. I've broken these down into two items below. I really appreciate your assistance.

                               

                              1) The simple PI OLEDB query deploys successfully but fails when I run the query mentioned below to filter on the tag and timestamp. Error says 'syntax error near ts'...

                              VDB:

                              <vdb name="PIAF" version="1">  
                                  <description>OSIsoft PI AF</description>    
                                  <model name="PIAF-Physical" type="PHYSICAL">
                                      <property name="importer.useFullSchemaName" value="false"/>
                                <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 (tag varchar, timecol timestamp, valcol object) AS  
                                        select "tag","time","value" from "PIAF-Physical"."picomp2"
                                      ]]> </metadata>
                                  </model>
                              </vdb>
                              

                               

                              Query:
                              This doesn't work (see error below):

                              select * from IceDataThree where tag='sinusoid' and timecol>'2016-01-01 00:00:00'
                              

                              but this works:

                              select * from IceDataThree
                              

                               

                              Error:

                              20:02:57,250 WARN  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue21) sV28Up1
                              GiGOM Connector worker process failed for atomic-request=sV28Up1GiGOM.2.0.3: org
                              .teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error exe
                              cuting statement(s): [SQL: 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" > {ts '2016-04-20 00:00:00.0'}]
                                      at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecuti
                              on.java:131)
                                      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWor
                              kItem.java:359)
                                      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                                      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
                              java:62)
                                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
                              sorImpl.java:43)
                                      at java.lang.reflect.Method.invoke(Method.java:498)
                                      at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorMan
                              ager.java:211)
                                      at com.sun.proxy.$Proxy57.execute(Unknown Source)
                                      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTie
                              rTupleSource.java:306)
                                      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTup
                              leSource.java:112)
                                      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTup
                              leSource.java:108)
                                      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
                                      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
                                      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkCon
                              text.java:276)
                                      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.ru
                              n(ThreadReuseExecutor.java:119)
                                      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseE
                              xecutor.java:210)
                                      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.
                              java:1142)
                                      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor
                              .java:617)
                                      at java.lang.Thread.run(Thread.java:745)
                              Caused by: java.sql.SQLException: [PIOLEDB] [SQL Parser] [Line 1] Syntax error n
                              ear 'ts'.
                                      at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:306)
                                      at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStat
                              ement.java:381)
                                      at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecuti
                              on.java:119)
                                      ... 18 more
                              Caused by: com.osisoft.rdsa.RdsaException: [PIOLEDB] [SQL Parser] [Line 1] Synta
                              x error near 'ts'.
                                      at com.osisoft.rdsa.Command.ExecuteReader(Native Method)
                                      at com.osisoft.rdsa.Command.executeReader(Command.java:72)
                                      at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:302)
                              

                               

                              2) I'm still trying to figure out why this query results in the error about the duplicate table - any thoughts on this one?

                              SELECT Name FROM [Calgary Flames].[Asset].[ElementHierarchy] eh
                              

                              JDBC Connection: jdbc:pioledbent://iscapps/Data Source=iscaf;Integrated Security=SSPI;

                               

                              Here is the error message that is thrown:

                              VDB PIAF.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID60013 Du  
                              plicate Table ft_GetPIPoint: org.teiid.metadata.DuplicateRecordException: TEIID6  
                              0013 Duplicate Table ft_GetPIPoint  
                                      at org.teiid.metadata.Schema.addTable(Schema.java:49
                              
                              • 12. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                                Ramesh Reddy Master

                                1)

                                Caused by: com.osisoft.rdsa.RdsaException: [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'ts'.


                                is telling me that this driver does not like JDBC escaped timestamp format. What is difference in this PI OLEDB connection? Is this a standard JDBC driver provided from PI? either case it is not treating the timestamp string form not correctly. If it is then we need to make modification to the PI translator in the Teiid to convert the timestamp to a form that it understands. What you can do is execute the below query using JDBC tool like SquirreL


                                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" > {ts '2016-04-20 00:00:00.0'}
                                


                                and modify the time value to a format that it understands, and post it back here the results.


                                2) is due to the fact that you used


                                 

                                <property name="importer.useFullSchemaName" value="false"/>

                                 


                                What that telling the importer is to ignore the catalog and schema name and just use the Table name when importing, thus there is a collision in names of the tables. If you change this to "true" then it will continue with importing, but in that case you would need to change the transformation of view to use some thing like ""PIAF-Physical"."catalog_name"."schema.name"."picomp2".


                                Ramesh..

                                • 13. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                                  Al S Newbie

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

                                  Capture.PNG

                                  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!

                                  • 14. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                                    Ramesh Reddy Master

                                    For #1, you should create a JIRA, we can fix the translator to make sure it does not send 'ts{...}' escape, and sends plain text to PI.

                                     

                                    For #2, You need to take look the schema of the the ElementAttribute and ElementHierarchy and find the data type differences. I mean take look at the schema as Teiid interpreting, see my first reply as to how to check the schema of model. Then either fix the data incompatibility by using convert on one of the elements in the query. But if you think the Teiid import process did not import the type correctly then that can be logged as issue. See the comment Steve made here [TEIID-3327] Translator for OSIsoft PI - JBoss Issue Tracker on possible issue.

                                     

                                    #b, you are trying to use PI's specific SQL, that is not possible. You are only allowed to used Teiid specific SQL dialect, which is ANSI-SQL. If you think extra options are required, we need to implement in the translator before they can be used. Then in that case, I say lets create a JIRA for it full description about what FORCE ORDER, EMBED ERROR do, and how Teiid should be interpreting those options when results return from the PI database.

                                     

                                    Ramesh..

                                    1 2 Previous Next