Data type confusion with Integer and Named Query

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 )

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

image

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

image

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):

image

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

BuildID in your headers list is capitalized but in the name query editor the parameter is buildID. Parameters are case sensitive. I'm basing this off the screenshot showing the logging you added to list the headers and parameters.

If a parameter value is not passed to a named query it assigns it a value of null.

2 Likes

Sometimes it's the simplest stuff isn't it?

Update and see how it goes tomorrow

1 Like

I've lost count of how many times I've either typoed or mis-capitalized a named query parameter when calling them in a script.

On my list to fix in 8.3 with a new named query function. Passing parameters the NQ doesn't expect should strictly throw an error, and params should not attempt to come up with a default value.

5 Likes

If you execute a Named Query and there are PRINT statements or other console outputs, are those currently logged anywhere?

I checked wrapper.log but nothing appears for things executed via the Named Query Test tab as far as I could find.

If that isnt present anywhere, could that be made as a Feature request?

Do you mean within the SQL?

If so, two points:

  • SQL runs on the database not in Ignition. Any console actions within SQL would occur in the database process, and its logging (if any).

  • Any kind of PRINT in SQL means you are executing a SQL script via JDBC. (If you see an @variable, it is a SQL script, not a SQL statement.) This is supported by some JDBC drivers, but is outside the specification. JDBC supports single statement only. You may encounter trouble as JDBC drivers change or your DB backend is changed.

1 Like

Thats helpful perspective thank you, I am missing the technical knowledge.

From the less-technical perspective, when I use a client like MS SQL Management Studio, any PRINT statements and other returns (1 row inserted for each INSERT in a stored procedure) show up in MSSMS's console.

I assume, probably incorrectly, that the same return/debug/callback info would be available to any SQL client and implemented in most connectors/drivers.

If thats not the case, then consider me informed.

Not.

Use a branded management studio or workbench to manage your database. Such interfaces have much more capability than exposed by the JDBC standard. Do your testing of SQL statements in Ignition, through the JDBC driver.

Last follow-up, case sensitivity was 100% the issue.

I was missing a WHERE statement in a later SELECT, so when I thought I was looking up info based on BuildID, I was actually just assigning the most recent record, which happened to be the correct one.

Lesson learned - do more testing and validation with null inputs.

Thanks all for the guidance and review.