|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 612
Location: Cheshire, United Kingdom.
|
After creating the reusable PI-ODBC connection method we can now look to retrieve some data from the PI database.
Using PI-ODBC we are exposed to the following "virtual" tables:
PIpoint
The PI point database. The column names in PIpoint are the same as the keywords used by PIDIFF.
PIcomp
The PI System archive. Each event is viewed as a single row.
PIinterp
The PI System archive viewed as consisting of rows with evenly spaced times.
PIavg
The archive viewed as consisting of time weighted averages. Since an average implies a period of time, a time interval must be specified when querying this table.
PImean
The archive viewed as consisting of arithmetic averages.
PItotal
The archive viewed as consisting of totals.
PImin
The archive viewed as consisting of minima.
PImax
The archive viewed as consisting of maxima.
PIrange
The archive viewed as ranges of data for each tag.
PIstd
The archive viewed as consisting of standard deviations.
PIbatch
The PI-BA batch history table.
PIalias
The PI-BA equipment alias table.
For this example we are only going to utilise "PIcomp".
We need to create a PI Query and then parse the result of the query. Our PI-ODBC query to retrieve the current value of SINUSOID looks like the following:
Code:SELECT value FROM PIcomp WHERE TAG='SINUSOID' AND time =DATE('*')
There is plenty of documentation from OSI that explains syntax, columns, functions (including PE) that can be used. I will not go into detail of those here and just cover what is required to achieve these examples.
And how we could use this in a method would be:
Code:Sub RetrieveValues()
If PIODBC_Connection Then
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Set RS = Conn.Execute("SELECT value FROM PIcomp WHERE TAG='SINUSOID' AND time =DATE('*')")
Debug.Print RS.Fields("VALUE").Value
Set RS = Nothing
Else
End If
End Sub
This is essentially the equivalent of "PISDK.PIPoint.Data.Snapshot.Value".Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
|
|
OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System. We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ or contact OSIsoft vCampus for more details.
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 612
Location: Cheshire, United Kingdom.
|
Next, to retrieve a value from the Archive (providing a value exists for the passed timestamp) you simply change your SQL query to include a date instead of PI's current time character *. (Current time will retrieve the snapshot value).
Code:
Sub RetrieveValues()
If PIODBC_Connection Then
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Set RS = Conn.Execute("SELECT value FROM PIcomp WHERE TAG='SINUSOID' AND time =DATE('18-Nov-08 09:00')")
On Error Resume Next
Debug.Print RS.Fields("VALUE").Value
If Err.Number <> 0 Then Debug.Print "No archive value exists at this time."
On Error GoTo 0
Set RS = Nothing
Else
End If
End Sub
Again, we make use of a standard connect to PI via ODBC routine and pass our query. This is a simple example but hopefully enough to help you see the power of ODBC and PI.Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
|
Guest
|
