# Data type confusion with Integer and Named Query **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [August 9, 2023, 10:10pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678 "2023-08-09T22:10:53Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Derek\_Rowland](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Derek\_Rowland](https://forum.inductiveautomation.com/u/Derek_Rowland)\ **Post date:** [August 9, 2023, 10:10pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/1 "2023-08-09T22:10:53Z")
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 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/0/b0d4b1c37bd6adc792f4ff0b457bb13d24de901f.png) # Stored procedure defined/altered with inputs as: ```python 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 ```python 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/4/b470e6f43fea070e3a270ed1a34b3c83527098f2.png) # During production runtime Our Query script and associated logs below ## Query script ```python ## 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 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/622d0b33d075866312ded2a4b1d6eb611e9a1496.png) 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c271ccb05449661ab3ef14b9c22b373d60faeb3.png) 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/f/1f5801689bc29455d3ca4401a105fb48d31b3e26.png) In the following image, we use a buildID from a test job and see the PRINT statement. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/c/6c43cdbcfe26e26c33c93a08b01ceb7042d9a59f.png) 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 ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [August 9, 2023, 10:19pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/2 "2023-08-09T22:19:28Z")
`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. ---
**Author:** ![Derek\_Rowland](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Derek\_Rowland](https://forum.inductiveautomation.com/u/Derek_Rowland)\ **Post date:** [August 9, 2023, 10:44pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/3 "2023-08-09T22:44:45Z")
Sometimes it's the simplest stuff isn't it? Update and see how it goes tomorrow ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [August 9, 2023, 10:56pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/4 "2023-08-09T22:56:07Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [August 9, 2023, 11:38pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/5 "2023-08-09T23:38:43Z")
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. ---
**Author:** ![Derek\_Rowland](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Derek\_Rowland](https://forum.inductiveautomation.com/u/Derek_Rowland)\ **Post date:** [August 9, 2023, 11:52pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/6 "2023-08-09T23:52:28Z")
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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 10, 2023, 12:08am UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/7 "2023-08-10T00:08:58Z")
> [@Derek\_Rowland](#): > > If you execute a Named Query and there are PRINT statements 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. ---
**Author:** ![Derek\_Rowland](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Derek\_Rowland](https://forum.inductiveautomation.com/u/Derek_Rowland)\ **Post date:** [August 10, 2023, 12:13am UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/8 "2023-08-10T00:13:03Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 10, 2023, 12:14am UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/9 "2023-08-10T00:14:38Z")
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. ---
**Author:** ![Derek\_Rowland](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Derek\_Rowland](https://forum.inductiveautomation.com/u/Derek_Rowland)\ **Post date:** [August 10, 2023, 2:17pm UTC](https://forum.inductiveautomation.com/t/data-type-confusion-with-integer-and-named-query/77678/10 "2023-08-10T14:17:53Z")
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.