Invalid data type? Type casting?

Hello all,

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)

Result from print stmt:

{'quantity': 1L, 'commnts': u'none', 'rptQty': 19L, 'userName': u'mike', 'myValueX': 4L, 'transType': 1}

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.

Thanks,
Mike

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.

Thats a good question. I’m not sure why it does that.

This does indeed work!

{'quantity': 1, 'commnts': u'none', 'rptQty': 19, 'userName': u'mike', 'myValueX': 4, 'transType': 1}

Next question: what is the ‘u’ mean? And does it have any effect on the query?

It indicates unicode in the print statement. It shouldn’t effect the query.

Well, for some reason the query is not executing. I’ve tested it independently, so I know that it connects and inserts data.

Got any troubleshooting tips on how to see what is or is not going on? Like a ‘pause during execution’ button?

Does the diagnostic console [ctrl+shift+c] offer any information?

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.

2 Likes

Do remember that printing atomic python types general results in implicit use of str(). But printing dictionaries or sequences usually invokes repr() instead.

3 Likes

@pturmel
That makes perfect sense. Thanks for the clarification

‘u’ means it’s unicode.

I had a suspicion L meant Long. All integers are Int8 in the NQ.

There is nothing except the printed dictionary when the button is clicked.

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! :slight_smile:

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.

Mike

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.

3 Likes

Thanks Paul!