The following will take a little bit to abstract, so please let me know where and how I can clarify.
Overview
I'm having issues with a Named Query and troubleshooting what I think might be a data type issue. The general flow of information:
- Allen Bradley PLC generate a data request and loads values in a UDT tag, sets Request bit
- Gateway script triggers off of that request bit and executes a python script. Python script reads data values from tags and builds a couple of data dictionaries to pass data to another common (and widely functional) script
- This script prints some useful debug information to the log then calls "system.db.runNamedQuery" to run a Named Query
- Named query calls an SP in SQLhas three inputs:
EXECUTE [SQL_StoredProc] @buildID = :buildID, @assoc1 = :assoc1, @assoc2 = :assoc2;- BuildID - int4
- assoc1 - string
- assoc2 - string
- Stored Procedure in SQL runs
- In the SP there is a specific problematic line:
SET @RecordNote = CONCAT('Built by Machine, BuildId: ', @buildID )
- In the SP there is a specific problematic line:
Stored Procedure
Stored procedure
defined/altered with inputs as:
ALTER PROCEDURE [dbo].[REDACTED]
@buildID int
,@assoc1 varchar(50)
,@assoc2 varchar(50)
as
...
DECLARE @RecordNote as varchar(50)
SET @RecordNote = CONCAT('Built by Machine, BuildId: ', @buildID )
...
So buildID is set as a SQL int
Run from MS SQL Management Studio
When I run the stored procedure from MSSMS, it operates as expected and inserts the correct RecordNote
DECLARE @RC int
DECLARE @buildID int
DECLARE @assoc1 varchar(50)
DECLARE @assoc2 varchar(50)
SET @buildID = 7028
SET @assoc1 = '131391'
-- TODO: Set parameter values here.
EXECUTE @RC = [dbo].[REDACTED]
@buildID
,@assoc1
,@assoc2
GO
During production runtime
Our Query script and associated logs below
Query script
## Query using the request tags
logger.debug("Query name: %s" % (queryName))
logger.debug("Params header: " + str(queryParamsHeader))
parameters = {} # Build parameters dictionary to feed into query
for header in queryParamsHeader:
i = queryParamsHeader.index(header)
parameters[header] = RequestTags[i].getValue()
logger.debug("Query parameters: " + str(parameters))
try:
if runStoreAndForward:
QueryResponse = system.db.runSFNamedQuery(queryName,parameters)
else:
QueryResponse = system.db.runNamedQuery(queryName,parameters)
Note - runStoreAndForward = false
relevant logs
In this log I can see the jython log conversion of queryName, which matches my NamedQuery, I can see the parameters tag values with assoc1 and assoc2 as strings and BuildID as an integer
DB records
It looks like BuildID is not coming across, yet it worked fine when executing from MSSMS (manually selecting the int data type in the SQL query)
troubleshooting
explicit casting
changed :
SET @RecordNote = CONCAT('Built by Machine, BuildId: ', @buildID )
to:
SET @RecordNote = CONCAT('Built by Machine, BuildId: ', CAST( @buildID as varchar(50)))
but no change in database record recreation
test record verification
I don't want to make any records when we're doing testing, so we do the following (excuse the comments and test code):
In the following image, we use a buildID from a test job and see the PRINT statement.
Because SELECT statements like this in the Stored Procedure executes (and some inserts and other SP calls later) and make correct records based on the BuildID, I have to believe it's getting into the SP but I'm unsure if it's a datatype or casting issue or what.
troubleshooting difficulty
When I execute from MSSMS the output console provides troubleshooting info like PRINT statements and rows effected throughout execution of the SP. The Named Query doesn't seem to provide that kind of console output so I can't figure out what kind of data casting or anything might be happening when executing the Named Query in Ignition vs executing the same Stored Procedure in MSSMS