2

Is it possible to query data from InfoPlus 21 (IP21) AspenTech using php?

I am willing to create a php application that can access tags and historical data from AspenTech Historian.

Is ODBC my answer? Even thinking that is, I am not quite sure how to proceed.

UPDATE: I ended up using python and pyODBC. This worked like a charm! Thank you all for supporting.

tburd
  • 71
  • 2
  • 9
  • 4
    "Nothing is impossible". I have answered the question. Note Stack Overflow is not a free code writing service. –  Oct 13 '17 at 14:11
  • @tburd Could you provide an answer of how you did this using python? I am looking for ways how to do connect to AspenTech IP 21 using python, but have not found anything useful yet :/ – DaveTheAl Mar 04 '19 at 09:35
  • @DaveTheAl sorry for the delay, I added my code here. Let me know if you have additional questions. Just text me if necessary, I can try to support. – tburd Apr 28 '19 at 09:34

3 Answers3

5

As asked by @DaveTheAI, I am sharing how I solve the issue here:

I was able to read data from AspenTech historians using pyODBC connector. At first you need to make sure that you have the required ODBC drivers installed (I am using Windows). Important point here is to have compatible drivers with your python/anaconda version: 32/64 bits

After that:

import pyodbc
#---- Connect to IP21
conn = pyodbc.connect("DRIVER={AspenTech ODBC driver for Production Record Manager};HOST=hostname;PORT=port")

#---- Query string
tag = 'YOUR_TAG'
start = '2019-01-01 12:00:00'
end = '2019-01-02 12:00:00'
sql = "select TS,VALUE from HISTORY "\
        "where NAME='%s'"\
        "and PERIOD = 60*10"\
        "and REQUEST = 2"\
        "and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tag, start, end)
data = pd.read_sql(sql,conn) # Pandas DataFrame with your data!
tburd
  • 71
  • 2
  • 9
3

I am unaware of a method to access IP21 data directly via PHP, however, if you're happy to access data via a web service, there are both REST and a SOAP options.

Both methods are extremely fast and responsive.

AFW Security still applies to clients accessing the Web Services. Clients will require SQL Plus read (at lesast) access.

SOAP

  • Requires the "Aspen SQL plus Web Server/Service and Health Monitor" component to be installed on IP21 server (Selected during install of IP21).

  • Recent versions of IP21 require a slight modification to the web.config file to allow remote access. If you cannot execute the web service remotely, try doing it locally (i.e. on the same machine as the IP21 server) and see if this is an issue.

    • Example: http://IP21ServerHostName/SQLPlusWebService/SQLplusWebService.asmx/ExecuteSQL?command=select%20*%20from%20compquerydef;

REST

  • My preference (over SOAP), as it is super easy to access using JQuery (JavaScript) - a couple of lines of code!
  • Unsure of exactly what IP21 component is required on install for this, but it appears to be on most of my IP21 servers already.
  • Arguments in the URL can control the number of rows returned (handy).

  • If using within Jquery / JavaScript, web page must be hosted on the AspenOneServerHostName machine, else you'll run into Cross-Origin Resource Sharing (CORS) issues.

    • Example: http://AspenOneServerHostName/ProcessData/AtProcessDataREST.dll/SQL?%3CSQL%20c=%22DRIVER={AspenTech%20SQLplus};HOST=IP21ServerHostName;Port=10014;CHARINT=N;CHARFLOAT=N;CHARTIME=N;CONVERTERRORS=N%22%20m=%22DesiredMaxNumberOfRowsReturned%22%20s=%221%22%3E%3C![CDATA[select%20*%20from%20compquerydef]]%3E%3C/SQL%3E
      • Notes:
        • AspenOneServerHostName can be the same as IP21ServerHostName
        • AspenOneServerHostName must have ADSA configured to view IP21ServerHostName
        • Replace DesiredMaxNumberOfRowsReturned with a number
Marty131
  • 343
  • 2
  • 5
  • 14
  • Wow! Thank you so much Marty131, this will open more possibilties to me. I will check SAOP and REST now to see if I can succeed. Best regards! – tburd Oct 18 '17 at 13:35
  • You can use PHP to call one of the above 2 web services, thus achieving what you want. – Marty131 May 10 '18 at 05:03
2

Yes ODBC driver should be applicable to meet your requirement. We have already developed an application to insert the data into IP21 historian which uses same protocol. Similarly some analytical tools (e.g. Seeq Cooperation) also uses ODBC to fetch the data from IP21 historian. Therefore it should be possible in your case as well.

iskbaloch
  • 187
  • 11