How to query Wonderware live values with python?

Question

I'd like to query live tag value from Wonderware historian with python. The following sql query works inside SQL server management studio and returns the live value of the tag:

USE Runtime
DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256)) 
INSERT @TempTable(tempTagName) VALUES ('TAG_A')
SELECT v_Live.TagName, DateTime, vValue
 FROM v_Live
LEFT JOIN @TempTable ON TagName = tempTagName
 WHERE v_Live.TagName IN ('TAG_A')
ORDER BY Seq

However, I get the error Previous SQL was not a query when I pass the query string above to cur.execute(). I am using pyodbc to connect to the SQL server.

with open_db_connection(server, database) as conn:
    cur = conn.cursor()
    query_string = textwrap.dedent("""USE Runtime
    DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256)) 
    INSERT @TempTable(tempTagName) VALUES ('TAG_A')
    SELECT v_Live.TagName, DateTime, vValue
    FROM v_Live
    LEFT JOIN @TempTable ON TagName = tempTagName
    WHERE v_Live.TagName IN ('TAG_A')
    ORDER BY Seq
    """)
    cur.execute(query_string)
    row = cur.fetchone()
    print(row[1])

Anyone has an idea why I get this error and how can I solve it?

well, if you print(query_string), what does it print? no idea what textwrap does — siggemannen, May 10 '23 at 12:41
as per documentation https://docs.python.org/3/library/textwrap.html textwrap.dedent(text) removes any common leading whitespace from every line in text. This helps to tidy up the query string. — Nili, May 10 '23 at 13:06
I have questions.... (1) Why are you building a temp table to pass the tag name? That isn't necessary with your example but perhaps you trimmed your example down. (2) are you against creating stored procs on the `Runtime` database? And less of a question but more of a statement: Historian's InSQL documentation does not advise using `LEFT JOIN` for joining user tables like that. Try using the `INNER REMOTE JOIN` — Grambot, May 10 '23 at 15:27
that query comes from the query application of the historian. I only have read permission access. — Nili, May 11 '23 at 10:58

Saxtheowl · Answer 1 · 2023-05-10T13:11:46.803

1

I think pyodbc have problem executing the multi-statement SQL script, lets try to split them into multiple statement then executing them separately.

edit: the scope of the table variable @TempTable is limited to the batch it was declared in, lets use global temp table, dont forget to drop the global temp table after

edit2: in this case we can concatenate all of your SQL commands into a single string then execute it, that might work.

with open_db_connection(server, database) as conn:
    cur = conn.cursor()
    query_string = """
        USE Runtime;
        DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256));
        INSERT INTO @TempTable(tempTagName) VALUES ('TAG_A');
        SELECT v_Live.TagName, DateTime, vValue
        FROM v_Live
        LEFT JOIN @TempTable ON TagName = tempTagName
        WHERE v_Live.TagName IN ('TAG_A')
        ORDER BY Seq;
    """
    cur.execute(query_string)
    row = cur.fetchone()
    print(row[1])

edited May 10 '23 at 13:11

answered May 10 '23 at 12:40

Saxtheowl

4,136
5
23
32

This raise the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the table variable "@TempTable". (1087) (SQLExecDirectW)') – Nili May 10 '23 at 12:52
I modified my answer with global temp variables – Saxtheowl May 10 '23 at 12:57
Thank you for your answer, but I don't want to create a global table preferably . – Nili May 10 '23 at 13:07
I modified again with a new solution :) – Saxtheowl May 10 '23 at 13:12

score 0 · Accepted Answer · answered May 10 '23 at 16:12

I'm going to leave an answer based on the comment I left on your original post, but I recommend making these changes:

1a: Do away with the temp table. It isn't doing anything for you besides generating a sequence ID. As your question stands right now, I don't see what benefit that has while generating more complexity:

with open_db_connection(server, database) as conn:  
  cur = conn.cursor()  
  query_string = """  
    SELECT TagName, DateTime, vValue 
    FROM Runtime..v_Live  
    WHERE TagName IN ('TAG_A')   
  """  
  cur.execute(query_string)  
  row = cur.fetchone()  
  print(row[1])

1b: Assuming you're going to keep that temp table, here's syntax using INNER REMOTE JOIN:

with open_db_connection(server, database) as conn:
  cur = conn.cursor()
  query_string = """
    DECLARE @TempTable TABLE (
      Seq INT IDENTITY, 
      tempTagName NVARCHAR(256)
    );
    INSERT INTO @TempTable (tempTagName) VALUES ('TAG_A');

    SELECT v_Live.TagName, DateTime, vValue
    FROM @TempTable
    INNER REMOTE JOIN v_Live ON TagName = tempTagName
    ORDER BY Seq;
  """
  cur.execute(query_string)
  row = cur.fetchone()
  print(row[1])

2: Since I'm not a python dev, I was asking about using Stored Procs to achieve your ends because you could just wrap your TSQL in a proc to do the work:

CREATE PROC QueryLiveData
  @Tags nvarchar(max)
AS
  SET NOCOUNT ON

  DECLARE @TempTable TABLE (
    Seq INT IDENTITY, 
    tempTagName NVARCHAR(256)
  );

  INSERT INTO @TempTable (tempTagName) VALUES (@Tags)

  SELECT v_Live.TagName, DateTime, vValue
  FROM @TempTable
  INNER REMOTE JOIN v_Live ON TagName = tempTagName
  ORDER BY Seq
GO

...or alternatively if you need to pass multiple tags you can comma separate them like TAG_A,TAG_B,TAG_C:

CREATE PROC QueryLiveData
  @Tags nvarchar(max)
AS
  SET NOCOUNT ON

  DECLARE @TempTable TABLE (
    Seq INT IDENTITY, 
    tempTagName NVARCHAR(256)
  );

  --Splits the @Tags on comma and inserts individual values to @TempTable
  WHILE LEN(@Tags) > 0 BEGIN
    IF CHARINDEX(',', @Tags) > 0 BEGIN        
      INSERT INTO @TempTable 
        SELECT LEFT(@Tags, CHARINDEX(',', @Tags ) - 1)
      SET @Tags = RIGHT(@Tags, LEN(@Tags) - CHARINDEX(',', @Tags))
    END ELSE BEGIN            
      INSERT INTO @TempTable VALUES (@Tags)
      SET @Tags = ''
    END
  END

  SELECT v_Live.TagName, DateTime, vValue
  FROM @TempTable
  INNER REMOTE JOIN v_Live ON TagName = tempTagName
  ORDER BY Seq
GO

Then for your python (I'm assuming you'd be able to clean up the query and use proper params in Python instead of a string passed like I have):

with open_db_connection(server, database) as conn:
  cur = conn.cursor()
  query_string = """
    EXEC RunTime..QueryLiveData 'TAG_A,TAG_B,TAG_C'
  """
  cur.execute(query_string)
  row = cur.fetchone()
  print(row[1])

How to query Wonderware live values with python?

2 Answers2