I have a view that collects the data in several components, labels (used to display data), text fields (used for user input). A button event that scripts all the component props into variables, then into a single variable to pass into a named query.
Query:
INSERT INTO [Transaction] (LocID, TypeID, Quantity, Comments, ReportedQty, UserName)
VALUES ((SELECT LocID FROM Bin_Item WHERE ItemID = :itemIdent), :transType, :quantity, :commnts, :rptQty, :userName)
Button Script:
def runAction(self, event):
"""
This event is fired when the 'action' of the component occurs.
Arguments:
self: A reference to the component that is invoking this function.
event: An empty event object.
"""
namedQuery = "textValueSelectTest01"
item = toInt(self.parent.parent.getChild("Spacer_1").getChild("Label_0").props.text)
qtyLeft = self.parent.parent.getChild("FlexContainer_2").getChild("QtyLeft_1").props.value
userName = self.parent.parent.getChild("FlexContainer_2").getChild("Name_1").props.text
comments = self.parent.parent.getChild("FlexContainer_2").getChild("Comments_1").props.text
qtyTaken = self.parent.parent.getChild("FlexContainer_2").getChild("Taken_1").props.value
transType = 1
params1 = {"myValueX":item, "rptQty":qtyLeft, "userName":userName, "commnts":comments, "quantity":qtyTaken, "transType":transType}
system.perspective.print(params1)
Question: how can I convert the integer values that are retrieved from the labels into actual integers? You notice the âLâ after three of the values.
I ran into this once before but donât recall how I dealt with it.
(Perhaps I need to use a numeric text field instead of a label and just style it differently?)
I did try to use toInt(), but apparently that does not work in this scripting environment. I havenât found a system function to call for this either.
In the scripting environment, you will have to use int() to covert the datatype.
Example: print int(str(19L).replace("L","")) produces the desired output of 19
To do it in the SQL query you will have to use cast or convert to do it. Here is my stack overflow solution to SQL integer casting to guide you: mysql-round-float-to-nearest-integer
Iâll give that a try, however, there is something tickling the back of my brain on thisâŚ
If I print each variable independently, no âLâ is appended, only when I drop them into one variable that is supposed to contain all the parameters for the named query.
The L means the value was interpreted as a long integer instead of a 32-bit integer. Printing something doesnât always clearly identify what it really is, but pythonâs repr tries to show what it can. Long integers generally correspond (-ish) to int8 elsewhere in Ignition. Parameters that are explicitly int8 can take both 32-bit integers and long integers.
Note also that when named queries apply the values in a dictionary to the query, they will try to coerce values into the target datatype, reinterpreting strings if necessary. Take a close look at what datatypes you chose for that NQ.
Do remember that printing atomic python types general results in implicit use of str(). But printing dictionaries or sequences usually invokes repr() instead.
This is the code for the button click event as modified to remove the L.
def runAction(self, event):
"""
This event is fired when the 'action' of the component occurs.
Arguments:
self: A reference to the component that is invoking this function.
event: An empty event object.
"""
namedQuery = "textValueSelectTest01"
item = int(str(self.parent.parent.getChild("Spacer_1").getChild("Label_0").props.text).replace("L",""))
qtyLeft = int(str(self.parent.parent.getChild("FlexContainer_2").getChild("QtyLeft_1").props.value).replace("L",""))
userName = self.parent.parent.getChild("FlexContainer_2").getChild("Name_1").props.text
comments = self.parent.parent.getChild("FlexContainer_2").getChild("Comments_1").props.text
qtyTaken = int(str(self.parent.parent.getChild("FlexContainer_2").getChild("Taken_1").props.value).replace("L",""))
transType = 1
# system.perspective.print(item)
params1 = {"myValueX":item, "rptQty":qtyLeft, "userName":userName, "commnts":comments, "quantity":qtyTaken, "transType":transType}
system.perspective.print(params1)
system.db.runNamedQuery(namedQuery, params1)
#should be: namedQuery = "insertSignoutData01"
#should be: params1 = {"itemIdent":item, "rptQty":qtyLeft, "userName":userName, "commnts":comments, "quantity":qtyTaken, "transType":transType}
Ok, I just realized I had changed the name of the query to test one value at a time!! Oops.
Now, using the correct query, I do get an error, and that is a result of using another parameter name instead of the correct one in the correct query.
Now⌠It works!
Thank you @pturmel for the info on Ignitionâs use of the two Python functions here, thank you @justinedwards.jle for your guide and script help. Thank you @tmahaf as well.
The int(str().replace()) dance really shouldnât be necessary. Jython likes to return âlongsâ for various reasons, but they should seamlessly pass in to anything that expects an integer. I suspect that having the correct named query and parameter names is all you need.