runNamedQuery issues with a string

Ignition
1

Hey Everyone,

I was hoping someone could help as my brain seems to be not working on this one. I am trying to run a Named Query from a script. The named query is deleting a row in a SQL database. When I run the query manually it works however something seems to be a bit strange when i run it from a script.

def runAction(self, event):
# Get current test number and deduct 1 to remove previous value
	Flow_Test_Num_Tag = "[Test_Well]Test_Parameters/Flow_Setpoints/Current_Flow_Step_Value.value"
	Flow_Test_Num=system.tag.readBlocking([Flow_Test_Num_Tag])[0].value
	Old_Flow_Test_Number = Flow_Test_Num-1
# Read current Sales order number
	Sales_Order_Tag = "[Test_Well]Test_Information/Sales_Order.value"
	salesOrderNumber=system.tag.readBlocking([Sales_Order_Tag])[0].value
	
# Build Parameters and then run Update
	parameters={"salesOrderNumber":salesOrderNumber, "testNumber":Old_Flow_Test_Number}
	system.perspective.print(parameters)
	system.db.runNamedQuery("Pump_Result_Delete_Row", parameters)
# Go back to previous step
	system.tag.writeBlocking([Flow_Test_Num_Tag],[Old_Flow_Test_Number])

What is happening is if I print the parameter salesOrderNumber I get the following

12:52:23.165 [Browser Thread: 746eb36b-5f02-42e9-b2b8-858b4977e276] INFO Perspective.Designer.Workspace - Test567890

but when i print the parameters I am using for the query I get this

12:54:18.010 [Browser Thread: 746eb36b-5f02-42e9-b2b8-858b4977e276] INFO Perspective.Designer.Workspace - {'testNumber': 5, 'salesOrderNumber': u'Test567890'}

It seems to be putting the salesOrderNumber in to singel commas with a u in the front which is obviously not going to work (and isn't)

What am I doing wrong?

Regards,
Sam

2

The u just denotes that the string is 'unicode' encoded. It will work just fine.

There is nothing incorrect about the script that I can see from what you've provided, other than a less than best practice.

Some things to check:

  1. Do the names of the parameters match exactly as you have them in the Named query authoring tab.
  2. Do the types "generally" match. Meaning is the type of the Sales_Order tag a string and the type of the salesOrderNumber also a string, as well as is the Database column type a type which is compatible with a string.
  3. Are you getting any errors in the logs or otherwise?
  4. Is the path to the Named Query correct?

Can you provide any more information:

  • Table schema
  • Snip of the NamedQuery Authoring Panel
  • DB in use (MSSQL, MySQL, Postgres, etc...)

Another note:

Probably not a huge deal for this script, and definitely not contributing to the issue you are seeing, but you should almost always consolidate all tag reads/writes into a single call. It doesn't take many calls and your performance will dive. Also, the "value" is the default property read, so there is no need to specify it in the path.

A script like this will have better performance:

def runAction(self, event):
    #Read test number and sales order number
    paths = [
        "[Test_Well]Test_Parameters/Flow_Setpoints/Current_Flow_Step_Value",
        "[Test_Well]Test_Information/Sales_Order"
        ]
    flowTestNum, salesOrderNum = [qv.value for qv in system.tag.readBlocking(paths)]
    params = {
        "salesOrderNumber":salesOrderNum,
        "testNumer":flowTestNum - 1
        }
    system.db.runNamedQuery("Pump_Result_Delete_Row", params)
    system.tag.writeBlocking(paths[:1],[flowTestNum - 1])
1 Like