Question:
How can I copy the alarms being recorded on an Access Database to the Oracle?
Solution:
To do so, you must use a Query object on the Data Server to retrieve the Access’ registers and periodically update the Oracle’s table.
Therefore, you must create a Historic linked to a table previously created on the Oracle, which needs to have the same number of fields of the Alarm History, as well as Internal Tags to be linked to each of these fields, and a script on the Data Server that be executed on defined intervals.
This script must perform the following:
- Retrieve the registers from the Access’ table by using the Query’s GetADORecordset method;
- Write on the Oracle’s table by using the Historic’s WriteRecord method;
- Save the TimeStamp from the last register on a Data Server’s tag so that the next Query is performed from it, in order to not duplicate the registers on the Oracle’s table.
At the end of the script, you must use the Save command on the Data Folder to make the tag keep its value even when you stop the Domain’s execution.
Please notice that the Query’s filter of the script must be performed based on the E3TimeStamp, and not on the InTime, so no register is lost.
Below, you can see an example of this script:
Sub DataServer_OnMinuteChanging()
set query = Item(“QueryDB1”)
‘Sets the query date and time filter to return only new records
query.SetVariableValue “InitialDate”, Item(“LastTime”).Value
set rs = query.GetADORecordset()
if rs.RecordCount > 0 then
rs.MoveFirst
for aux = 1 to rs.RecordCount
‘Moves data from the query (DB1) to the tags linked to Historic (DB2)
Item(“TagsDB2”).Item(“E3TimeStamp”).Value = CDate(rs.Fields.Item(“E3TimeStamp”).Value)
Item(“TagsDB2”).Item(“InTime”).Value = CDate(rs.Fields.Item(“InTime”).Value)
Item(“TagsDB2”).Item(“Message”).Value = CStr(rs.Fields.Item(“Message”).Value)
Application.GetObject(“HistDB2”).WriteRecord()
‘Saves timestamp from the last record to use in the next execution
Item(“LastTime”).Value = CDate(rs.Fields.Item(“E3TimeStamp”).Value)
rs.MoveNext
next
end if
Save()
End Sub
