# Popup View, parameter, stored procedure

**URL:** <https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [September 22, 2022, 1:02am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354 "2022-09-22T01:02:39Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 22, 2022, 1:02am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/1 "2022-09-22T01:02:39Z")

</div>

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:

```auto
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:

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

```

Both data types are integer.

The query calling the procedure:

```auto
CALL usp_GetOnHandQty(:itemID);

```

Not sure what the deal is.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 22, 2022, 1:08am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/2 "2022-09-22T01:08:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 22, 2022, 7:38am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/3 "2022-09-22T07:38:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 22, 2022, 4:49pm UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/4 "2022-09-22T16:49:52Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 23, 2022, 1:11am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/5 "2022-09-23T01:11:32Z")

</div>

Here’s what I have now:

```auto
	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?

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 23, 2022, 1:40am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/6 "2022-09-23T01:40:29Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3d893c1c9dc172b109a4191f446d80199fb6f598.png)

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:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/7/87eafc3246bd607f67c573547113e7cb5137fded.png)

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.

---

<div class="post-metadata">

**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:** [September 23, 2022, 1:49am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/7 "2022-09-23T01:49:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 23, 2022, 4:01pm UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/8 "2022-09-23T16:01:56Z")

</div>

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

---

<div class="post-metadata">

**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:** [September 23, 2022, 10:57pm UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/9 "2022-09-23T22:57:33Z")

</div>

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

```auto
{view.custom.key}[0,'CatalogNumber']

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 24, 2022, 12:32am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/10 "2022-09-24T00:32:25Z")

</div>

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?

---

<div class="post-metadata">

**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:** [September 24, 2022, 12:33am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/11 "2022-09-24T00:33:18Z")

</div>

Row subscript. Datasets are two-dimensional.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 24, 2022, 12:34am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/12 "2022-09-24T00:34:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 24, 2022, 12:35am UTC](https://forum.inductiveautomation.com/t/popup-view-parameter-stored-procedure/64354/13 "2022-09-24T00:35:52Z")

</div>

Row 0, Row 1, Row 2… correct?