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?
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])