Popup View, parameter, stored procedure

Hello all,

I have a table with minimal columns, when a user clicks on a row to select it, a view pops up. I have passed the first column data, an ID field, by using a parameter, and pulling from the event.value dictionary.

The popup view needs more data than what the table presents, so I’d like to populate the rest of the components with the relating data from the database (MYSSQL).

I have a stored procedure in the DB that will return the data set based on the value (ID passed to the popup), but I am having trouble calling the stored procedure from the popup. I created a named query to call it, but I get a syntax error. When testing the stored procedure on the DB it works fine.

This is the procedure:

CREATE PROCEDURE [inv].[usp_getOnHandQty]
	-- Add the parameters for the stored procedure here
	@p1 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT i.CatalogNumber, i.MasterNum, i.Description, bi.CurrentStock, bc.Bin 
	FROM inv.Items AS i
	INNER JOIN inv.Bin_Item AS bi ON i.ItemID = bi.ItemID 
	INNER JOIN inv.Bin_Cbo AS bc ON bc.BinID = bi.BinID
	WHERE i.ItemID = @p1;
END

The error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

Both data types are integer.

The query calling the procedure:

CALL usp_GetOnHandQty(:itemID);

Not sure what the deal is.

From there, I think I can bind a custom array parameter to the named query, then I must figure out how to retrieve that data from the array, or bind the view’s components to specific array points.

Not sure about the sp, but could you just move the select query into a named query and do away with the sp?

1 Like

Well, I was thinking of reducing potential overhead for queries as we continue to build out the application.

Question: how do I report what I think may be a bug?

For example, this query

SELECT i.CatalogNumber, i.MasterNum, i.Description, bi.CurrentStock, bc.Bin
FROM Items AS i
INNER JOIN Bin_Items AS bi ON i.ItemID = bi.ItemID
INNER JOIN Bin_Cbo AS bc on bc.BinID = bi.BinId
WHERE (i.ItemID = :itemId)

will produce a syntax error if the data type in Ignition is Int4, instead of Int8. A syntax error is not the correct error type.

Here’s what I have now:

	params1 = {"itemId":event.value["ItemID"]}
	ds = system.db.runNamedQuery(getOnHandQty, params1)
	data = [ds.getValueAt(i,0 for i in range(ds.getRowCount())]
	params2 = {'data':data}
	
	system.perspective.openPopup("itemSignoutId",'itemSignout', params=params2, showCloseIcon = True, resizable = True)

I grabbed much of this off a thread I found, but I didn’t mark the thread…
This code should send a data set, as a JSON dictionary or object, right?
So how do I find the individual data within it on the popup view and feed those to the corresponding components?

Regarding the named query: I created a custom property as an array

the prop is named ‘key’, incidentally… hard coding the value for the query, I have one row returned.

I am attempting to bind several components to the various columns within the data set:

If I bind it to ‘key’, I get everything in one component. I’ve tried adding a key name as shown in the picture, but that doesn’t work.

Does your named query have a WHERE clause that constrains to that key? I don’t see one in your screenshot.

oh, yes it does: WHERE (i.ItemID = :itemId)

Ah! You will need an expression binding with dataset selection syntax:

{view.custom.key}[0,'CatalogNumber']
1 Like

Aha! But why is the 0 used? Is this an index based set? I thought this would be a key/value set, similar to a dictionary, yes?

Row subscript. Datasets are two-dimensional.

Hm, not familiar with row subset. I take it they are not 2D, then, maybe 1D?

Row 0, Row 1, Row 2… correct?

1 Like