Extracting Uniformance PHD data within SQL Server using the PHDOLEDB provider

Mon, 03 Jan 2011 13:00:00 GMT

It's possible to access PHD data via a simple SQL query within SQL Server (I say simple as at the time of writing, joins cannot be done, discussed here). In essence if you are retrieving live well data, say every 5 seconds, then you can query that live data using SQL either from within SQL Server or from an application using a stored procedure or inline code etc.

To do this you need to install the Honeywell Uniformance PHD software client tools/desktop components (which comes on the same disk as the server software). Once you have done this, a new data provider will be listed when extracting data from capable applications. It should read like "Uniformace PHD OLE/DB Provider".
Create a new SQL Server Linked Server (more info here), and with the Other data source radio button selected, select "Uniformace PHD OLE/DB Provider" as the provider. The Product name being PHDOLEDB and the Data source being the name of the PHD Server. You can call the linked server anything you want.
On the Security page, select "Be made using the login's current security context". This means it will run the query using the account of the user executing the query.
On the Server Options page change the RPC (remote procedure call) to Out.
Click OK, and a new node will appear in SSMS under Linked Servers.
Normally that would be that and you'd be able to view data from this linked server by expanding catalogs within this new node and querying against that catalog, however this isn't the case here. By default, all provider executions are run through a process seperate from SQL Server's. This doesn't work for the PHDOLEDB provider though. For this to work, the query must be called within the SQL Server process executing the query. SQL Server allows us to change this. To do this expand the Providers' node (sitting under the Linked Servers node). View the properties of the PHDOleDB provider and enable the "Allow inprocess" checkbox.
Now when you expand the linked server's catalogs, you will see PHD RemoteAPI and StandardAPI catalogs, with tables listed under the StandardAPI catalog. You can now query against these tables.

Keep in mind, that these are not real tables. By default, PHD does not store data in a relational database but as flat binary files. What this provider does is mimic standard database tables so that you can run SQL queries against it.

You can test this by running a query against the table phd_tag, such as:

SELECT * FROM YOUR_LINKEDSERVER_NAME.StandardAPI.phd.phd_tag

Note that the table name must be fully qualified with the server, catalog and schema name.

Don't get frustrated if you find your query returns errors (I've done that for you). With it not looking at real tables, the provider has tedious limitations discussed in detail here.

More ...

Tags: linked server,phd data

Category: 1. Honeywell SCADA

PHDOLEDB (OLEDB provider for Honeywells PHD) anomalies

Mon, 03 Jan 2011 13:00:00 GMT

There are a few show-stopping limitations and/or bugs to the PHD OLEDB provider (at the time of writing). The tables provided by the provider are:

PHD_DATA
PHD_LINK_LIST
PHD_PARENT_TAG
PHD_RDI_LIST
PHD_REDUCTIONS
PHD_TAG
PHD_TAG_BROWSE
PHD_WRITE_DATA

Anomaly One: Standard SQL cannot be used on all tables consistently

Some tables such as PHD_TAG can be queried successfully using a standard SELECT query against a linked server such as:

SELECT *
FROM your_linkedserver_name.standardapi.phd.phd_tag

This will return all your tags with relevant attributes such as their interpolation method, whether they have been manually inputted and their description.

However if you run a similar query against PHD_DATA (which holds the tag data such as the value, confidence, timestamps etc):

SELECT *
FROM your_linkedserver_name.standardapi.phd.phd_data

You get nothing.

If you specify a tag number and timestamp:

SELECT *
FROM   your_linkedserver_name.standardapi.phd.phd_data
WHERE  tagname = '301GXXXXI004.PV'
       AND start_timestamp = 'Now-30'
       AND end_timestamp = 'Now'

You still get no results.

If you run a query in the following format (which is normally valid):

EXEC ('SELECT * FROM phd_data') AT your_linkedserver_name

No results. But if you use the previous format with a tag number and timestamp, you DO get results back:

EXEC ('SELECT * FROM phd_data WHERE tagname = "301XXXXX2I004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000') AT your_linkedserver_name

That last query returns all columns for tag 301G00342I004.PV for the past 30 days with an hourly value. Use double quotes in your query to specify strings, otherwise you get an error.

Anomaly Two: Simple SQL Joins cannot be made on all tables

A basic requirement would be to combine data from two tables to return overall information on a tag. For example, the value of the tag is stored in phd_data. The tag's description is stored in phd_tag.

Running the following returns an error message:

SELECT dt.tagname,
       dt.tagno,
       tg.description
       dt.timestamp,
       dt.sample_interval,
       dt.confidence,
       dt.VALUE
FROM   phd_data dt
       INNER JOIN phd_tag tg
         ON tg.tagno = dt.tagno
WHERE  dt.tagname = '301XXXXI004.PV'
       AND dt.start_timestamp = 'Now-30'
       AND dt.raw_data = 0
       AND dt.end_timestamp = 'Now'
       AND dt.sample_interval = 3600000

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PHD_DATA'.

I reluctantly tried this:

EXEC ('SELECT dt.tagname, dt.tagno, dt.timestamp, dt.sample_interval, dt.confidence,
dt.VALUE FROM phd_data dt INNER JOIN phd_tag tg ON tg.tagno = dt.tagno
WHERE dt.tagname = "301GM003FI004.PV" AND dt.start_timestamp = "Now-30" AND dt.raw_data = 0
AND dt.end_timestamp = "Now" AND dt.sample_interval = 3600000') AT your_linkedserver_name

However, this returns:

"PHDOleDB" for linked server "myphdlink" returned message "Execute Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "PHD Server Initialisation Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "ParseSQLString Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "Bad Column Ordinal".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'your_linked_server'

So what's the solution? I've no idea. If you find one, let me know. In the meantime, I have put a call in and am awaiting a response from Honeywell.

Anomaly Three: Query results cannot be stored in temporary or memory tables.

I figured if results are being returned to a screen, then they can be stored in temp tables. If the data is in temp tables, then they can be joined.

SELECT *
INTO   #temp_tag
FROM   your_linkedserver_name.standardapi.phd.phd_tag

SELECT *
FROM   #temp_tag

DROP TABLE #temp_tag

This works. Now we need to put PHD_DATA into a temp table. For the reasons mentioned above in anomaly one, neither of the following work:

SELECT *
INTO   #temp_data
FROM   your_linkedserver_name.standardapi.phd.phd_data
WHERE  tagname = "301GM003FI004.PV"
       AND start_timestamp = "Now-30"
       AND end_timestamp = "Now"

SELECT *
FROM   #temp_data

DROP TABLE #temp_data

SELECT *
INTO   #temp_data
FROM   your_linkedserver_name.standardapi.phd.phd_data
WHERE  tagname = '301GM003FI004.PV'
       AND start_timestamp = 'Now-30'
       AND end_timestamp = 'Now'

SELECT *
FROM   #temp_data

DROP TABLE #temp_data

INTO #TEMP_DATA
EXEC ('SELECT * FROM phd_data WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND end_timestamp = "Now"') at you_linkedserver_name
SELECT * FROM #TEMP_DATA
DROP TABLE #TEMP_DATA

Using Dynamic SQL doesn't even work for PHD_TAG

DECLARE @SQL NVARCHAR (4000)

SET @SQL =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'

CREATE TABLE #tagno
  (
     tagno INT
  )

INSERT INTO #tagno
EXEC Sp_executesql @sql

SELECT *
FROM   #tagno

DROP TABLE #tagno

or

DECLARE @SQL2 NVARCHAR (4000)

SET @SQL2 =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'

CREATE TABLE #tagno
  (
     tagno INT
  )

INSERT INTO #tagno
EXEC (@sql2)

SELECT *
FROM   #tagno

DROP TABLE #tagno

The error returned for the last two is:

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "PHDOleDB" for linked server "myphdlink" does not support the required transaction interface.

Finally, I thought I'd try a memory table:

DECLARE @SQL3 NVARCHAR (4000)

SET @SQL3 =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'

DECLARE @MemoryTable TABLE (
tagno INT)

INSERT @MemoryTable
EXEC Sp_executesql @SQL3

SELECT *
FROM @MemoryTable

Anomaly Four: Using SQL Server's Import and Export Wizard to pull data into a relational database table...

...doesn't work. It looks like it does right up until you execute the package. When previewing the data within the Wizard, it is using it's own process to view the data. However, when executing the package, a seperate process is created and thus it fails as this isn't supported by the provider. When creating a linked server to a PHD server, there is a "Allow Inprocess" checkbox for the provider offered by SQL Server that allows you to have the query executed using the same process that SQL Server is using. This option however is not available in the Import Export Wizard. You could probably get round this in SSIS, but as I wanted to enable the business by allowing users to create their own reports, I didn't bother going down that route.

Conclusion?

Well, compared to standard providers, it's a bit flakey. That said, it's fairly impressive what they've done when you think about it. If you want to get fancy with your reporting using standard techniques without the worry of running into unexpected and painful obstacles and obtuse workarounds, you may want to use PHDTOREL instead (though there are draw backs to this too).

More ...

Tags: phd oledb,sql server,data extraction

Category: 1. Honeywell SCADA

PHDTOREL - No SnapShot retrieval type

Mon, 03 Jan 2011 13:00:00 GMT

It's a little odd, but at the time or writing there is no SnapShot retrieval type available for selection when creating a PHDTOREL query in the PHD Configuration tool (confirmed by Honeywell).

The values available are

Average
Maximum
Minimum
Delta
Raw
Standard Deviation
Regression Constant
Regression Slope
Regression Deviation
First
Last
Value

The description for Value is the same as Raw; "Requests the raw value", however what is actually does is return the first Raw value for that retrieval period.

Despite SnapShot being available for other APIs such as the .Net Wrapper and the PHD OLEDB providor, it is not available in PHDTOREL. Bit of a pain.

The closest to it is Average.

More ...

Tags: phd configuration tool,snapshot

Category: 1. Honeywell SCADA

Drew Salem

Extracting Uniformance PHD data within SQL Server using the PHDOLEDB provider

PHDOLEDB (OLEDB provider for Honeywells PHD) anomalies

PHDTOREL - No SnapShot retrieval type