Re: [foxboro] Documenting AIM Historian Database

>Has anyone succeeded in pulling long term historical message data from
>the AIM Historian using ODBC. We were told in AIM class we could, but
>when we tried it all we get is the last hour or so of alarm message
>data. What we are looking for is older alarm message data stored in the
>mxxx.fdb files.

Just some code snipped from a VBA application which runs scheduled 3 times a
day to retrieve info from AIM* (sysmon, alarms, OAJ and RTP definitions)
Watch de line-ends carefully, outlook probably will mess things up!

Kind regards,

Patrick Martens,
Total Raff. Ned. NV.



Function GetAlarmMessagesFromHist()
'If bAppend is True, data will be appended to strTableName, if bAppend is
False, strTableName will be cleared first!!!
Dim strODBCPath As String
Dim wrkODBC As Workspace
Dim ODBCdB As Database
Dim SqlStr As String
Dim RecSet As Recordset
Dim x As Long
Dim MyDB As Database
Dim strAP As String
Dim strODBCDataSource As String
Dim strHist As String
Dim strStart As String, strEnd As String
Dim strName As String
Dim fField As Field
Dim bAppend As Boolean
Dim strTableName As String
Dim tmpRecSet As Recordset
Dim dDate As Date

Set MyDB = CurrentDb

strHist = "hist01"

'The databasesource should be configured in windows ODBC datasources
'For this, AIM* must be installed on the PC running this code.
'Configure the datasource as follows:
'Select Add and select the AIM AT Historian ODBC Driver (if not visible then
AIM* is not (correctly) installed on this PC)
'In the configure dialog give it a name like AIM_hist01. This name must be
the same as used in the next line of code.
'Set Preferred Server and Preferred Database to 'None' and make sure all
other fields are left empty (unselected).
'You can then check this by trying to import an AIM* ODBC database from the
normal MSaccess import menu.
'Try importing through standard MSaccess import menu, select filetype ODBC
and import (not link) the 'Event Messages' table.
'Copy the structure of the retrieved table to tmp_alarmmesg for the rest of
this code to work.
'If the tables are imported (they might not show any data yet) your ODBC
set-up is ok.
strODBCDataSource = "AIM_" & strHist 'example: AIM_hist01, define in Window
ODBC system datasources!

'Change a per your needs, second ethernet names!
Select Case strHist
    Case "hist01"
        strAP = "AW7001"
    Case "hist41"
        strAP = "T4A5101"
    Case "hist42"
        strAP = "T4A5102"
    Case "hist51"
        strAP = "T5A5101"
    Case "hist52"
        strAP = "T5A5102"
    Case "hist11"
        strAP = "T1AW511"
    Case "hist14"
        strAP = "T1A5104"
End Select

'Where to get the data from?
'strODBCPath = "ODBC;DSN=AIM_Historian_hist51;AP=T5A5101;DB=Event" (example)
'strODBCPath = "ODBC;DSN=" & strODBCDataSource & ";AP=" & strAP &
";DB=Sample"
strODBCPath = "ODBC;DSN=" & strODBCDataSource & ";AP=" & strAP & ";DB=Event"

' Create an ODBCDirect workspace.
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrkODBC

'Open the ODBC database;
'OpenDatabase("AIM_Historian_hist51_oaj",dbDriverComplete, True,
"ODBC;DSN=AIM_Historian_hist51;AP=T5A5101;DB=Event")
'dbDriverComplete = if missing ODBC parameters then a ODBC dialog will
appear requesting the missing parameters.
'True = Read Only
'Set ODBCdB = OpenDatabase(strODBCDataSource, dbDriverComplete, True,
strODBCPath)
Set ODBCdB = OpenDatabase("", 0, 0, strODBCPath)

'MIND !!!!!
'Timeformat is 'YYYY-MM-DD HH:MM:SS' UT (GMT)
'Depending on your I/A timesettings you might have like 1 hour offset in
case of daylight saving time!
'We solved this by timesyncing the PC which runs this code with I/A time.
'Update until Now() minus one minute

'Do not retrieve data up to exactly Now() but give it some slack.
'This to prevent duplicate records by next retrieval cycle.
dDate = DateAdd("n", -1, Now())

strEnd = Format(dDate, "yyyy-mm-dd Hh:Nn:00")

'Example
'sqlStr = "SELECT Time, TIME_TAG, STATION, COMPOUND, BLOCK, PARM,
DESCRIPTION FROM " & strHist & ".LEGACY:OPRACTION " & _
         "WHERE Time BETWEEN {ts '2006-01-01 00:00:00'} AND {ts '2006-01-18
00:00:00'}"

strName = strHist & ".iaalarm:alarmmesg"
'Other usefull AIM* data sources
'Mind, these must be configured in your historian instance (see
/opt/fox/aim/examples)
'strName = strHist & ".LEGACY:sysmonmsg"
'strName = strHist & ".LEGACY:OPRACTION"
'strName = strHist & ".Playback"

'Define the destination table, first retrieved through
MSaccess->Import->ODBC database etc.
strTableName = "hist01_IAALARM:ALARMMESG"

'Open the destination table
Set tmpRecSet = MyDB.OpenRecordset(strTableName, dbOpenTable)
'Retrieve the last stored record, if any.
If tmpRecSet.RecordCount = 0 Then
    strStart = "2009-01-11 13:00:00"        '! ! ! ! Required first time
retrieval only ! ! !
Else
    tmpRecSet.MoveLast
    'Get the last record Time, we must start from there + 1 second (to
prevent overlapping)
    dDate = DateAdd("s", 1, tmpRecSet!Time)
    strStart = Format(dDate, "yyyy-mm-dd Hh:Nn:Ss")
End If

'Set bAppend to false if the destination table should be cleared first.
bAppend = True
If Not bAppend Then
    Application.SetOption "Confirm Action Queries", False
    DoCmd.RunSQL "DELETE * FROM " & strTableName & ";"
    Application.SetOption "Confirm Action Queries", True
End If

'Define the select query
SqlStr = "SELECT * FROM " & strName & " " & _
         "WHERE Time BETWEEN {ts '" & strStart & "'} AND {ts '" & strEnd &
"'}"

'Retrieve the data
On Error GoTo ErrorHandler
Set RecSet = ODBCdB.OpenRecordset(SqlStr, dbOpenForwardOnly,
dbSQLPassThrough)

'For all records retrieved
While Not RecSet.EOF
    tmpRecSet.AddNew
    For Each fField In RecSet.Fields
        tmpRecSet.Fields(fField.Name) = fField
    Next
    tmpRecSet.Update
    RecSet.MoveNext
Wend
  
GoTo Finished
RecSet.Close

ErrorHandler:
Debug.Print "Error"

Finished:
tmpRecSet.Close
ODBCdB.Close
wrkODBC.Close

Debug.Print "Finished"

End Function



 
 
_______________________________________________________________________
This mailing list is neither sponsored nor endorsed by Invensys Process
Systems (formerly The Foxboro Company). Use the info you obtain here at
your own risks. Read http://www.thecassandraproject.org/disclaimer.html
 
foxboro mailing list:             http://www.freelists.org/list/foxboro
to subscribe:         mailto:foxboro-request@xxxxxxxxxxxxx?subject=join
to unsubscribe:      mailto:foxboro-request@xxxxxxxxxxxxx?subject=leave

Other related posts: