Using the API to access Historical Data from a DMZ server

[bmcneer]

I was hoping to use an Excel Add-In to replicate some of the functionality of something like PI Data Link.  I originally tried using queries against CDBHistoric and was able to successfully retrieve values but this didn't seem like a scalable solution since it seemed to be fairly slow when there was a significant amount of records needed and each record was a separate query

I did find this solution in the forum for using InvokeMethod:

Solved: Getting historic data via .NET client API - Communities (se.com)

but I am unable to use this since the server is a DMZ server and it won't allow the InvokeMethod to be used

[BevanWeiss]

---

@bmcneer wrote:

...it seemed to be fairly slow when there was a significant amount of records needed and each record was a separate query

---

I'm confused by why you had each record as a separate query.

If you're only getting historic aggregates (like Min/Max/Average) then you really want to minimize the round trips.

You can do this in several ways.  How I would recommend is:

1. Retrieve a list of the IDs of the various objects you want the historical data for

2. Issue the historic query and use the WHERE ID IN {...} to filter the result for the objects (based on the IDs you had before)

3. Then you can split out the result back to the IDs that you wanted

If you're doing this as a UDF type of situation, then it is indeed going to be trickier, since each formula will have its own context.

The .NET Client does have an ability to execute a Query, from memory this was more performant than instantiating an ODBC client etc.  Especially if you already have such a connection cached.

I'm a bit surprised that the InvokeMethod isn't capable of being used on a client connected to the DMZ server.

What error message does it provide for this?

[sbeadle · Accepted solution]

The first InvokeMethod in that example will write data, so would be blocked on the DMZ. The others read data.

Alternatives include:

a) Use the c# API to read history

HistoricTag tag = new HistoricTag(PointName + "." + HistoricAggregateName, 0, 0, 0);
var tags = new HistoricTag[1];
tags[0] = tag;

var historicItems = AdvConnection.ReadRawHistory(LastChangeDate, DateTime.UtcNow, LastReadCount, false, tags);

b) Query SQL from the c# API

			// Connect to database
			ClearScada.Client.Simple.Connection SimpleConnection;
			ServerNode node = new ClearScada.Client.ServerNode("127.0.0.1", 5481);
			SimpleConnection = new ClearScada.Client.Simple.Connection("Utility");
			SimpleConnection.Connect(node);
			var AdvConnection = SimpleConnection.Server;
			SimpleConnection.LogOn( UserName.Text, password.SecurePassword);
			// Query points

			string sql = "SELECT O.FullName As OName, O.TypeDesc FROM CHistory H INNER JOIN CDBObject O ON H.Id=O.Id WHERE OName LIKE '" + NameFilter.Text + "' ORDER BY OName";
			ClearScada.Client.Advanced.IQuery serverQuery = AdvConnection.PrepareQuery(sql, new ClearScada.Client.Advanced.QueryParseParameters());
			ClearScada.Client.Advanced.QueryResult queryResult = serverQuery.ExecuteSync(new ClearScada.Client.Advanced.QueryExecuteParameters());
			if (queryResult.Status == ClearScada.Client.Advanced.QueryStatus.Succeeded || queryResult.Status == ClearScada.Client.Advanced.QueryStatus.NoDataFound)
			{
				if (queryResult.Rows.Count > 0)
				{
					Dispatcher.Invoke(new Action(() => {
						listBox.Items.Clear();
					}));
					IEnumerator<ClearScada.Client.Advanced.QueryRow> rows = queryResult.Rows.GetEnumerator();
					while (rows.MoveNext())
					{
						Console.WriteLine( (string)rows.Current.Data[0] + " (" + (string)rows.Current.Data[1] + ")" );
					}
				}
				else
				{
					MessageBox.Show("Database query no data");
					return;
				}
			}
			else
			{
				MessageBox.Show("Database query error");
				return;
			}
			serverQuery.Dispose();