Re: [foxboro] AIM*Historian connection on Windows
- From: "foxpat" <foxpat@xxxxxxxxxxxxx>
- To: <foxboro@xxxxxxxxxxxxx>
- Date: Thu, 12 Apr 2012 22:28:39 +0200
It is perfectly feasible to use AIM* ODBC with MSAccess to retrieve info.
(see below for some code examples).
We have several applications which run 24/7 retrieving system monitor
messages, alarm messages operator action journal info, rtp definition and
historical trend data. The latter allows you to retrieve significant amounts
of data of several tags over periods as long as retained in your historian
with one simple query. Linearization is done in vba code which allows fixed
time-stamping of say every 10 seconds so different tags can be
compared/plotted in one graph.
The sysmon, alarm and operator actions are retrieved nearly real-time (every
50 seconds, which is within the connection time-out of AIM* and prevents a
log message/file which would appear every minute on the AIM* server !?)
Our applications include e-mail notifications on configurable messages which
is very convenient for sysmon messages but also for system related alarms.
You can create some pretty complex reports with tools like these; for
example, for our gas detector alarms (LEL's), whenever an alarm is activated
we will fetch from the historical trend table, wind-direction, wind-speed
and if available, the actual LEL analog values at the time of the event and
report the maximum value. (this answers the question if the threshold was
exceeded slightly, significant or drastically).
Reports like these are generated unattended on a daily base.
Some problems I faced:
With earlier versions of AIM* (historians still on solaris) retrieving more
then a couple of days in one query would crash the vba application.
This seams to be solved with our current version (can't check versions as I
do not have access to the system).
Retrieving data from different hist instances and/or different tables from
one application would only retrieve data for the first call. All subsequent
calls would fail. I solved this for creating different DSN for each hist
instance and table giving them logical names. That solved my problem. I
never modified this after the move to windows so I do not know if this still
works like that but I believe it does.
The AIM* tables are linked but data is transferred to local MSaccess tables
as I could not get random access (forward/reverse browsing) to work without
problems. Querying the local MSaccess tables works much faster once you have
transferred the data.
Some code subtracts with some comments which could be helpfull.
'Each hist needs it's own dedicated ODBC DSN (Database Source Name,
configured with ODBC config from windows)
'strODBCPath = "ODBC;DSN=AIM_Historian_hist51;AP=T5A5101;DB=Event Messages"
(example)
strODBCPath = "ODBC;DSN=" & strODBCDataSource & ";AP=" & strAP & ";DB=Event
Messages"
'Open the ODBC database;
'OpenDatabase("AIM_Historian_hist51_oaj",dbDriverComplete, True,
"ODBC;DSN=AIM_Historian_hist51;AP=T5A5101;DB=Event Messages")
'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)
'Table names for sysmon messages and alarm messages, code is for operator
action journal.
'Make sure that you have these tables configured in your historian and
verify with spy or something that data is collected!
'".LEGACY:sysmonmsg"
'".iaalarm:alarmmesg"
'MIND !!!!!
'Timeformat is 'YYYY-MM-DD HH:MM:SS' UT (GMT)
'If wintertime you should add 1 hour to the desired time (so if you want
10:00:00 you should enter 11:00:00)
'If summtertime you should add 2 hours!
'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'}"
SqlStr = "SELECT * FROM " & strHist & ".LEGACY:OPRACTION " & _
"WHERE Time BETWEEN {ts '" & strStart & "'} AND {ts '" & strEnd &
"'}"
Set RecSet = ODBCdB.OpenRecordset(SqlStr, dbOpenForwardOnly,
dbSQLPassThrough)
With oajRecSet
'.Index = "DateTime"
'Transfer the OAJ data to local table strTableName
While Not RecSet.EOF
.AddNew
'Convert timeformat to that used by the Imac (includes
milli-seconds)
'This so data could be easily merged with Imac data.
!Time = GetWord(RecSet!TIME_TAG, 2) & ",000"
!Date = GetWord(RecSet!TIME_TAG, 1)
!STATION = RecSet!STATION
!Compound = RecSet!Compound
!Block = RecSet!Block
!PARM = RecSet!PARM
!Description = RecSet!Description
!srcHist = strHist
oajRecSet.Update
RecSet.MoveNext
Wend
End With
RecSet.Close
ODBCdB.Close
-----Oorspronkelijk bericht-----
Van: foxboro-bounce@xxxxxxxxxxxxx [mailto:foxboro-bounce@xxxxxxxxxxxxx]
Namens Yolanda Dickerson
Verzonden: woensdag 11 april 2012 20:43
Aan: foxboro@xxxxxxxxxxxxx
Onderwerp: Re: [foxboro] AIM*Historian connection on Windows
To the list:
I'm trying to test the ODBC connection with MS Access for hisf52. I define
a new DSN to access the "Event Messages" database. I make a linked table
from Access to AIM*Historian using this "Event Messages" ODBC connection and
I can't get any event messages from hisf52 with IAALARM:STATEMESG
Query.Because when I run it I get a error as follows ODBC --call failed. Do
any know what is mean?
Thanks ,
Yo
Yo Dickerson
DCS Technician
Phone: (409) 723-3518
Pager: (409) 726-4281
Email: yolanda_dickerson@xxxxxxxxxxxx
Yolanda
Dickerson/US/PC/H
UNTSMAN To
foxboro@xxxxxxxxxxxxx
04/11/2012 07:01 cc
AM jim.pan@xxxxxxxxxxxxxxxx,
James.Vaught@xxxxxxxxxxxx
Subject
Re: [foxboro] AIM*Historian
connection on Windows(Document
link: Yolanda Dickerson)
To the list
One quick Question
Do you have to turn off the Instance or the Collector for that historian
before checking the "Logging Msgs" field of the message Names (such as
ALARMMESG, OPRACTION, and SYSMONMSG). And then Setting the field to "Yes"
will make these event messages available to ODBC access.
Thanks Yo
Yo Dickerson
DCS Technician
Phone: (409) 723-3518
Pager: (409) 726-4281
Email: yolanda_dickerson@xxxxxxxxxxxx
"Pan, Jim"
<Jim.Pan@Foxboro.
com> To
Sent by: "'foxboro@xxxxxxxxxxxxx'"
foxboro-bounce@fr <foxboro@xxxxxxxxxxxxx>
eelists.org cc
Subject
11/03/2004 10:41 Re: [foxboro] AIM*Historian
PM connection on Windows
Please respond to
foxboro@freelists
.org
I never used AIM*API function calls, but I did use AIM*ODBC for other
applications to query event messages from AIM*Historian.
The first thing I would do is to check the "Logging Msgs" field of the
message Names (such as ALARMMESG, OPRACTION, and SYSMONMSG). Setting the
field to "Yes" will make these event messages available to ODBC access.
The next I would do is to test the ODBC connection with MS Access. You need
to define a new DSN to access the "Event Messages" database, not the
"Message" database. Make a linked table from Access to AIM*Historian using
this "Event Messages" ODBC connection and see if you can get any event
messages from Access Query.
Jim Pan
Lifetime Learning Center
Houston
-----Original Message-----
From: foxboro-bounce@xxxxxxxxxxxxx [mailto:foxboro-bounce@xxxxxxxxxxxxx] On
Behalf Of Ales Vaupotic
Sent: Wednesday, November 03, 2004 5:24 PM
To: foxboro@xxxxxxxxxxxxx
Subject: [foxboro] AIM*Historian connection on Windows
Hi,
here is my real problem:
I have written a program which connects to AIM*Historian and collects the
RTP samples and reduction values. It works perfectly. I got stuck when
trying to get the alarm messages from AIM*Historian. I work in the following
70-series environment:
Windows XP with SP1
I/A release 7.1
AIM*AT 3.2.1
AIM*API version 5.4.0
The problem is the fh_FdbMsgQuery call which doesn't seem to return the data
as specified in the manual. Has anyone got any experience with this call?
Also, there is fh_FdbMsgQueryFilterNew function which is not described in
the manual but works in apitst. I looked over The Cassandra project and
couldn't find a similar program. Have I missed someting? My manual is
B0193YN, rev. C, from March 3, 2003.
The other problem is ODBC and OLE*DB on Windows. I keep getting strange
resultsets from them. Sometimes RecordCount is accurate but after first
record it is already at EOF. Or, it returns different fields on two
subsequent calls with the same SQL command. The first field is sometimes a
table name or tag name and after another call there is no such field.
Wierd! Is there anyone actually using OLE*DB connetion to AIM*Historian from
PHP or ASP?
I would be happy with any other solution to get to the data I need into my
PHP or ASP or C or ....
Thanks for you help, everyone!
Ales
_______________________________________________________________________
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: //www.freelists.org/list/foxboro
to subscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=join
to unsubscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=leave
_______________________________________________________________________
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: //www.freelists.org/list/foxboro
to subscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=join
to unsubscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=leave
_______________________________________________________________________
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: //www.freelists.org/list/foxboro
to subscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=join
to unsubscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=leave
_______________________________________________________________________
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: //www.freelists.org/list/foxboro
to subscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=join
to unsubscribe: mailto:foxboro-request@xxxxxxxxxxxxx?subject=leave
Other related posts:
