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'.
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.
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.
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?