Abstract: This paper introduces the practical application of using ADO technology to implement iFix historical reports. The host computer uses the configuration software iFix 3.5, and the development tool is Visual Basic 6.0. The historical report software is designed and implemented using ADO technology. The report application designed using the method introduced in this paper is universal and can be ported to other projects with simple settings. Keywords : ADO; iFix; History Report; Visual Basic 1 Introduction iFIX is the world's leading HMI/SCADA automation monitoring configuration software. Over 300,000 software installations are running globally. Many of the world's most successful manufacturers rely on GE Fanuc's iFIX software for comprehensive monitoring and distributed management of plant-wide production data. iFIX software has a powerful built-in VBA script compiler, enabling it to meet the software requirements of complex projects. However, because different projects have varying requirements for historical data reports, and iFIX software does not provide a universal component for historical data reporting, the usual method for implementing reports is to use third-party components (such as Crystal Reports). These components have many encapsulated functions, lack programming flexibility, and are cumbersome for implementing complex reporting functions. Furthermore, they need to be purchased separately when configuring reports. ADO components are integrated into Visual Basic 6.0. For complex reporting functions, using ADO technology to access the iFIX historical database is very flexible. 2 ADO Technology and iFIX Historical Database 2.1 ADO Technology ADO (ActiveX Data Objects) is a COM component for accessing data sources. It is a new database access technology from Microsoft, a high-level database access technology built on OLE DB. ADO's ease of use, high speed, and low memory footprint make programming more efficient. Commonly used ADO component objects include ADODB.Connection and ADODB.Recordset. The basic workflow for accessing a database through ADO component objects is: connect to the database using a Connection object, execute SQL commands using the established connection and Command objects, or retrieve the result set using a Recordset object for querying and processing. 2.2 iFIX Historical Database During iFIX installation, the iFIX installer automatically creates two system data sources on the workstation: "FIX Dynamics Historical Data" and "FIX Dynamics Real Time Data". The "FIX Dynamics Real Time Data" data source connects to the iFIX real-time database; the "FIX Dynamics Historical Data" data source connects to the iFIX historical database. Both the real-time and historical databases can be accessed via ODBC. When configuring an iFIX project, the historical data to be queried can be defined in the "Historical Definitions" section under the iFIX workbench system tree. When iFIX runs, it saves the real-time values ​​of the configured tags for recording historical data in the form of historical files. For example, if the project node is named "FIX" and a 24-hour historical file is configured to be generated, these historical files will be saved as xxxx.H24 (where xxxx is the recording time) in the project directory under "...\HTRDATA\FIX\". The iFIX historical database mainly has the following fields: NODE is the logical node name of the project; TAG is the database tag name; FIELD is the domain name of the database tag; VALUE is the database tag value; DATETIME is the recording date and time; INTERVAL is the query time interval. 3 Design and Implementation of Historical Report Software 3.1 Function Introduction This software can achieve seamless connection with iFIX project application software, satisfying the query of iFIX historical data and displaying it in the form of reports. 1) Users can arbitrarily select the variables to be queried, and the display order of variables in the report is only related to the order in which the variables to be queried are selected. 2) When querying variables, users can query by date. Users only need to set the variables to be queried and enter the "start time", "end time", and "time interval" to perform the query. 3) Query results can be exported. 3.2 Design and Implementation The software design mainly includes using ADO component objects to query the iFIX historical database through the "FIX Dynamics Historical Data" data source and displaying the query results. 1) Create a database tag name description file Because the iFIX historical database stores data according to TAG (database tag name), and the tag name is usually composed of letters and numbers, which is inconvenient to remember, in order to make the report more intuitive, we create a database tag name description file. This file is a text file that maps each tag name (the same as the iFIX database manager tag name) to the description of the tag, as shown in Figure 1: [align=center] Figure 1: Database tag description file[/align] 2) Configure the software development environment, add ActiveX controls and reference the ADO object library This software development environment requires Visual Basic 6.0 and Microsoft Office components to be installed. To perform report queries, a time control (DTPicker) needs to be inserted in the VB project. To display the report, a display control (SpreadSheet) is needed. To add an ActiveX control to a VB project, follow these steps: Open the project → In the "Project" menu → Select "Components" → In the "Components" dialog box, find "Microsoft Office XP Web Components" and "Microsoft Windows Common Controls-2.6.0", select the corresponding checkboxes → Click OK. This adds the ActiveX control to the VB toolbox. Before using ADO objects, the object library must be imported into the current project. Use the "References" dialog box in the "Project" menu of the VB integrated development environment and select "Microsoft ActiveX Data Objects 2.6 Library". 3) "Add Query Variable" Window Design The window design is shown in Figure 2: [align=center] Figure 2: "Add Query Variable" window[/align] This window allows you to add any variable to be queried. The design idea is as follows: ① In the window initialization program, read the database label name description file and display it in the "All Points List" list box. Private Sub UserForm_Activate() …… If Dir(VaribleFilePath) <> "" Then Open VaribleFilePath For Input As 1 Do While Not EOF(1) Input #1, int1, str1, str2 ListBox1.AddItem str1 Loop Close …… End Sub Where VaribleFilePath is the path where the database tag name description file is located. ② Click the ">" button to move the query variable selected from the "All Points List" list box to the right "Selected Points List" list box. Private Sub CommandButton1_Click() Dim strtemp As Integer If ListBox1.ListIndex <> -1 Then ListBox2.AddItem ListBox1.Text strtemp = ListBox1.ListIndex ListBox1.RemoveItem strtemp …… End Sub ③ Click the "<" button to move the query variable selected from the "Selected Points List" list box to the left "All Points List" list box. The code is similar to ②, and will not be repeated. ④ Click the "OK" button to retrieve the corresponding iFIX database manager label name from the database label name description file for each query variable in the "Selected Points List" list box. Private Sub CommandButton4_Click() …… Dim VaribleTemp() As String Dim DecTemp() As String ReDim Preserve VaribleTemp(ListBox2.ListCount) ReDim Preserve DecTemp(ListBox2.ListCount) If Dir(VaribleFilePath) <> "" Then i1 = 0 For i = 1 To ListBox2.ListCount Open VaribleFilePath For Input As 1 LabelTemp = ListBox2.List(i - 1) Do While i1 = 0 Input #1, str1, str2, str3 If str2 = LabelTemp Then DecTemp(i) = str2 VaribleTemp(i) = str3 i1 = 1 End If Loop i1 = 0 Close #1 Form1.ListBox1.AddItem VaribleTemp(i) Form1.ListBox2.AddItem DecTemp(i) Next … End Sub In this code snippet, two string arrays are defined: `VaribleTemp()` stores the iFIX data tag names to be queried for historical data and assigns them to the listbox `ListBox1` in the main window `Form1`; `DecTemp()` stores the database tag name descriptions to be queried for historical data and assigns them to the listbox `ListBox2` in the main window `Form1`. 4) Main Form Design: The "Historical Data Query" main form completes the function of setting the query start time, end time, query interval, and querying the iFIX historical database. The window design is shown in Figure 3: ① Add a date and time control `DTPicker` and a spreadsheet control `SpreadSheet` to this form. ② Clicking the query button will cause the software to query data according to the specified selected query variables. The code written under this button is the core of the report program, including the following parts: I. Convert the date and time controls into formatted strings. ... strStartTime = Format(Me.DTStartTime, "yyyy-MM-dd HH:mm:ss") ' Format the start time strEndTime = Format(Me.DTEndTime, "yyyy-MM-dd HH:mm:ss") ' Format the end time strInterval = Format(Me.Interval1.Value, "HH:MM:SS") ' Format the time interval [align=center] Figure 3: Historical Data Query Window[/align] II. Connecting to the iFIX Historical Database. ... Dim conADO As ADODB.Connection ' Define a variable of type Connection Set conADO = New ADODB.Connection ' Create a new Connection object and assign it to this variable conADO.ConnectionString = "Provider=Microsoft OLE DB Provider for ODBC Drivers; " + _ "DSN=FIX Dynamics Historical Data;UID=;PWD=;" ' Specify the connection string conADO.Open ' Open the connection using the Open method... III. Create and return a recordset, and display the results in the SpreadSheet table control. Dim TagGroup() As String ' Defines a string array to store iFIX tag names ReDim Preserve TagGroup(ListBox1.ListCount) For i1 = 1 To ListBox1.ListCount TagGroup(i1) = Me.ListBox1.List(i1 - 1) strQuery = "SELECT VALUE,DATETIME " + _ ' Select the field to query "FROM FIX " + _ ' The historical database node name is Fix "WHERE TAG = '" + TagGroup(i1) + "' " + _ ' Select the iFIX tag to query "AND INTERVAL = '" + strInterval + "'" + _ ' Select the time interval to query "AND (DATETIME >= {ts '" + strStartTime + "'} AND " + _ ' The query time is not less than the start time "DATETIME <= {ts '" + strEndTime + "'})" 'Query time is not greater than the end time Dim rsADO As Recordset 'Define a variable of type Recordset Set rsADO = New ADODB.Recordset 'Create a new Recordset object and assign it to the variable rsADO.CursorLocation = adUseClient rsADO.Open strQuery, conADO, adOpenDynamic, adLockUnspecified, -1 'Open the recordset If rsADO.RecordCount <= 0 Then MsgBox "No data in this time range" Set cnADO = Nothing Set rsADO = Nothing Exit Sub End If Me.Spreadsheet2.Range(Chr(Asc("B") + (i1 - 1)) & "1") = Me.ListBox2.List(i1 - 1) 'Display the iFIX label name and description of the variable to be queried starting from the 1st row and 2nd column of the spreadsheet control For i = 1 To rsADO.RecordCount Me.Spreadsheet2.Range("A" & (i + 1)) = rsADO!DateTime & "" 'Displays the date and time of the variable to be queried, starting from the second row and first column of the spreadsheet control. If rsADO!Value & "" = "" Then Me.Spreadsheet2.Range(Chr(Asc("B") + (i1 - 1)) & (i + 1)) = "No data" Else Me.Spreadsheet2.Range(Chr(Asc("B") + (i1 - 1)) & (i + 1)) = rsADO!Value & "" 'Displays the data retrieved by this label, starting from the second row and second column of the spreadsheet control. End If rsADO.MoveNext Next i Set rsADO = Nothing 'Release Recordset object Next i1 Set conADO = Nothing 'Release Connection object IV. Data Export Click the "Export" button to export the queried data as an XML file, which can be opened in Excel. Me.Spreadsheet2.Export 3.3 Calling the program in iFIX software Generate an .EXE file (e.g., iFix historical data query.exe) in Visual Basic 6.0, and then call the program in iFIX. Dim MyAppID Dim appPath As String appPath = System.ProjectPath & "\APP\iFix historical data query.exe" MyAppID = Shell(appPath, 0) 4 Conclusion This article explores the application of ADO technology in iFIX historical reports. Real-time reports can also be designed using ADO technology. Programmers only need some VB knowledge to implement this. The program designed using the method described in this article can be easily modified and ported to other projects. This method is superior to using SQD and SQT to connect to the database because using the latter requires not only pre-defining SQL language in the SQLLIB data table, but also configuring the SQL account in the SCU configuration file. The programming efficiency and program execution efficiency are relatively low. References: [1] GE Intellution. iFIX eBook. [2] Wang Dong. Visual Basic 6.0 Program Development Example Tutorial [M]. Tsinghua University Press