OBJECTIVE
I was contracted to provide a way of viewing (some) AIM*Historian data via the Internet. The I/A was controlling a process of moulding with data in Historian about each product which came from the line. Data includes pressure, weight, temperature, etc, so they can track down data for every individual piece in case of warranty or customers’ request. The ISO standards also require them to have such information at hand. They need this data later in Sales and Support department, Management would like to see production totals from previous day, they also would like to know when the machines were working and when not, whether the reason for interrupting the production was lack of materials as Just-In-Time supplier didn’t do his job or was it a hardware problem. They also wanted to have constant access to real-time data where their own engineers would monitor the process from many different locations miles apart.
SOFTWARE
Installation of I/A 7.1 on Windows XP SP 1 included AIM*Historian 3.2.1 (the latest at that time).
SOLUTION
Other parts of the application were already written when I came to the alarm reports section. I needed to extract some alarms from legacy:opraction and iaalarm:alarmmesg tables. As OLEDB provider was introduced in 3.2, I went with it as it is the preffered way of data retrieval. And as I was gonna serve the data over IIS, this seemed like the ideal way. All I had to do was write an .ASP script which would create a few Server.Objects and transfer the data from Historian to the user. I knew at the time there is also ODBC way of doing this but OLEDB should be faster and more reliable.
AIM*AT package comes with a heavy manual. I soon found what I was looking for. Although the examples there are written for VB they should work with ASP. I was quickly able to connect with the Historian. But, not everything went smoothly. I started getting strange results. Sometimes OLEDB provider returned only one row instead of few hundreds. Or, the number of returned fields was not consistent between two consecutive calls (one field would appear in the returned RecordSet and after a page refresh if would not be present). I found out by experiment it was the first field which behave in this strange way.
Now I had a problem. Was it my code or is it a bug in the OLEDB provider?
AIM*AT package includes some examples. I went and installed the ADODB project. Compile, run, put in the same SQL select as in my ASP and here comes the bug again. The result set fields are not consistent between two calls. You can see two screnshots of ADODB here showing this behaviour. Also when testing within the VB, I found out I can’t iterate through the returned RecordSet as after the first call to rs.Next, I get the EOF() set to TRUE and the loop exits. But the rs.RecordCount returns the correct and expected number. You may ask how come ADODB displays the data corectly (with disregard to the first column problem)? My guess is it memory-copies the recordset to the grid and the grid takes over the navigation (or something like this). I may be missing something here because I am not very experienced in VB or ASP but this all looks to me like problems with the OLEDB provider within 3.2.1.
In the meantime I introduced the problem to the list. I received lots of mails describing different instalations which work and also lots of such which do not. I concluded from all the mails that majority is using 3.1.2 version of AIM*AT and NT 4.0. This put me to thinking. Could it be the Windows XP that is causing problems? Or is it 3.2.1 AIM*AT? Some people told me they are using 3.2.1 on NT 4.0 successfuly so it must be the combination of Windows XP and 3.2.1.
I installed AIM*AT 3.1 I found in our archives onto NT 4.0 Workstation. Installed IIS 4 from Option pack which is pretty hard to download these days as Microsoft is not supporting it anymore. Installed PHP (which became my choice after I found out I needed Server line of Microsoft OS to execute local programs from ASP script. PHP does this nicely with every version of Windows. I don’t mind the lack of security at this stage of testing) and uploaded my PHP scripts. As 3.1 doesn’t support OLEDB yet, I rewrote a few lines to use ODBC datasource. It worked for the first time! I finally found a way to retrieve alarms from AIM*Historian.
As soon as I had that connection working, I started experimenting with other versions of both Windows and AIM*AT. My conclusion is the following: It all comes down to the version of ODBC the client software is using. This means that you can connect to either AIM*AT 3.1 on NT4.0 or to AIM*AT 3.2.1 on XPProSP1 if your software, from which you are using the AIM*Historian ODBC source uses very basic version of ODBC calls. That is why I had immediate success with PHP’s ODBC connection as it obviously uses ODBC Level 1 commands. The latest version of ODBC I am aware of is 3.52 and if your Office application like Excel or Access or any other application is trying to connect to AIM AT Historian with ODBC API Conformance of Level 2, you are doomed! In my experience, use ODBC Level 1 commands and set SQL_DRIVER_ODBC_VER variable to 2.5. This way my application connects to AIM*Historian 3.1 and 3.2 running on Windows NT 4.0SP5, W2KSP4 and XPProSP1. As these values are usually hidden from user and can’t be set, some applications might never work on newer OS-es like Office 2003 on Windows XP as they by default use higher level ODBC calls (which are appropriate for todays databases, I am sure).
Good luck with your connections!
Ales Vaupotic