Solution: GetEASnap for PI JDBC Driver

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Sep 28, 2018
Version 3Show Document
  • View in full screen mode

This document contains the answers to the Exercise: GetEASnap for PI JDBC Driver.

 

The first step is to open the GetEASnap source file in notepad. This file is located in %pihome%\JDBC\Samples\GetEASnap\src.

 

1. What was the full JDBC url being used in this case?

answer: url = "jdbc:pioledbent://localhost/Data Source=AFSRV01; Integrated Security=SSPI";

 

explanation:

[Line 198]: url = "jdbc:pioledbent://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";

[Line 74]: dasName was set as the Data Access Server name

[Line 82]: dataSourceName was set as the AF Server name

 

2. What was the query being executed in this case?

answer:

SELECT ea.Name, s.ValueStr

FROM [NuGreen].[Asset].[ElementHierarchy] eh

INNER JOIN [NuGreen].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID

INNER JOIN [NuGreen].[Data].[Snapshot] s ON s.ElementAttributeID = ea.ID

WHERE eh.Name = N'Houston'

ORDER BY ea.Name

OPTION (FORCE ORDER, EMBED ERRORS)

 

 

explanation:

[Line 214]: connection.setCatalog(catalogName);

[Line 216-22]:

     "SELECT ea.Name, s.ValueStr " +

    "FROM [Asset].[ElementHierarchy] eh " +

    "INNER JOIN [Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID " +

    "INNER JOIN [Data].[Snapshot] s ON s.ElementAttributeID = ea.ID " + "WHERE eh.Name = ? " +

    "ORDER BY ea.Name " + "OPTION (FORCE ORDER, EMBED ERRORS)"

[Line 228]: pStatement.setString(1, elementName);

[Line 140]: catalogName is set to the specified AF Database

[Line 146]: elementName is set to the specified AF Element

 

To further confirm that this was the correct query, it can be run in PI SQL Commander against the AF Server directly:

 

3. What would the query become if we only wanted to output the Safety attribute of the specified element?

answer:

SELECT ea.Name, s.ValueStr

FROM [NuGreen].[Asset].[ElementHierarchy] eh

INNER JOIN [NuGreen].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID

INNER JOIN [NuGreen].[Data].[Snapshot] s ON s.ElementAttributeID = ea.ID

WHERE eh.Name = N'Houston'

AND ea.Name = N'Safety'

ORDER BY ea.Name

OPTION (FORCE ORDER, EMBED ERRORS)

 

Explanation:

You could use the PI OLEDB Enterprise query compendium to look for an example of filtering by attribute name, but the query already used ea.Name in the ORDER BY clause, so this was hopefully straight forward to add it to as an additional WHERE clause.

 

Again, the query can (and should) be confirmed in PI SQL Commander:

 

4. Implement the change in question 3 and recompile the GetEASnap utility into GetEASnapCustom. Demonstrate it working by rerunning the utility from the command prompt.

answer:

Add the AND ea.Name = N'Safety' clause to the query on line 220, then follow the steps in How to Customize GetSnap and GetEASnap for PI JDBC Driver, but for GetEASnap instead of GetSnap.

 

The final result should look like this:

Attachments

    Outcomes