Named Query in Script, with Returned Values

Ignition
1

Server: SQL Server 2019

In this edition, I am inserting a new record into a table using a named query and a stored procedure. The SP returns two values, the record ID and another Integer.

I would like to be able to retrieve both values and use them in the rest of the process. The record ID will be used as the user adds additional information into associated tables.

This is the code I have so far:

params = {"p_Category":category, "p_Classification":classification, "p_Description":desc, "p_ShortDesc":shortDesc, "p_Stock":stocked, "p_Review":review}
returnNQ = system.db.runNamedQuery(namedQuery, params)
self.parent.parent.getChild("flx_InputArea").getChild("TabContainer").getChild("flx_General").getChild("coord_Left").getChild("TextArea_0").props.text = returnNQ

This is what is returned from returnNQ

[{"":4021}]

When I query the table, the other value has been created, and when I test the named query, i get both values back. 103854 and 4021.

Just to show the named query results from another insert:

image
image1460×86 2.46 KB

Thoughts?

Edit:
this is the named query:

EXEC inv.usp_Item_NewInsert :p_Category, :p_Classification, :p_Description, :p_ShortDesc, :p_Stock, :p_Review

Edit 2:
This is the SP:

CREATE PROCEDURE [inv].[usp_Item_NewInsert] 
	-- Add the parameters for the stored procedure here
	@Category INT,
	@Classification INT,
	@Description VARCHAR(255),
	@ShortDesc VARCHAR(66),
	@Stocked BIT = 0,
	@Review BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	DECLARE @Catalog INT
	DECLARE @ItemId INT

	SET NOCOUNT ON;

	EXEC inv.usp_GenerateCatalogNumber01 @Cat = @Catalog OUTPUT

    -- Insert statements for procedure here
	BEGIN TRANSACTION
		INSERT INTO inv.Items (CatalogNumber, [Description], CategoryID, ClassificationID, ReviewThisPart, Stocked)
		VALUES (@Catalog, @Description, @Category, @Classification, @Review, @Stocked);
	COMMIT TRANSACTION

	BEGIN
		SET @ItemId = (SELECT ItemID
		FROM inv.Items
		WHERE CatalogNumber = @Catalog)
	END

	SELECT @Catalog, @ItemId
END
GO
2

How is your names query constructed?

3

Sounds like you've got it setup as a scalar query.

4

Standard query type

5

Yeah, I should've known that from your result screenshot...

6

How did you determine the output of the query? Did you look at the property after the assignment? Try printing returnNQ from the script

system.perspective.print(returnNQ)
7

I set the text of a text area to the returnNQ variable.

in the output console I get, nothing meaningful

com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset@36275b6e
9

So I updated the SP to return column names for the returned values, then I used jsonEncode on returnNQ.

Now, I have this:

{"columns":[{"name":"CatalogNum","type":"java.lang.Integer"},{"name":"ItemID","type":"java.lang.Integer"}],"rows":[[103866,4033]]}

So the question is: How do I retrieve the two values, 103866 and 4033? ( I feel like I've done this once before, but I can't recall where! :frowning_face:)

10

Using jsonEncode or setting the named query to return JSON you should be able to use this:

returnNQ['rows'][0][0]

Not using the jsonEncode and not modifying the named query you should be able to use this:

returnNQ.getValueAt(0,'CatalogNum')

or

returnNQ = system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery,params))
system.perspective.print(returnNQ[0]['CatalogNum'])
2 Likes
11

To do this, I need to use jsonGet. And the rest of your examples work fine as well.

Thank you!