String parsing

General Discussion
1

Hi, I have data in a MS-SQL database that looks like this:

I want to parse this string for the text highlighted green and then insert this value into a MySQL db.

Any tips for how to accomplish this in Ignition 7.8?

Thanks

2

I think in order to parse that reliably we’d need more information about what the surrounding characters mean, and what they can possibly be.

It looks like you can split the string into fields on semi-colons, but yeah, need more information from there.

3

Thanks Kevin for your prompt reply.

That string is for a passed (leak test) part. A failed part shows as:

There is a separate column for pass fail so I can filter by this column for only pass parts.

There are two pass string's (they alternate every other result) the difference being additional "1!Test_Pressure;0.098;"

[quote][/Program_Name;HSSI;1!Program_Number;05;1!Frame_Received;HSSI 05 (OK)1.69 cm3/mn2.99 bar 10/10/2015 19:10:25;1!

Program_Name;HSSI ;1!Program_Number;05;1!Test_Pressure;0.098;1!Frame_Received;HSSI 05 (OK) 0.098 cm3/mn 2.99 bar10/10/2003 21:16:50;1![/quote]

As for the other characters 99% of the time this machine runs HSSI (product type) on program 5 (product type select on machine). The time stamps are coming from the leak tester and aren't sync'd so I'm not worried about them I will take the timestamp from the DB stamp.

Thanks

4

You could search for the bits of the string before and after the data you want (as long as they don’t change) e.g.txt1='Program_Name;HSSI ;1!Program_Number;05;1!Test_Pressure;0.098;1!Frame_Received;HSSI 05 (OK) 0.098 cm3/mn 2.99 bar10/10/2003 21:16:50;1!' pos1 = txt1.find('(OK) ') + 5 pos2 = txt1.find(' cm3') print txt1[pos1:pos2]

5

Thanks Al for info.

We get a new part into the leak test station (of which there are two, but there is a separate column for each tester) around every 12 seconds.

I take it the best way of getting the string would be a system.db.runPrepQuery, and push the result into “txt1” as per your example?

6

Yes, use runQuery or runPrepQuery as appropriate to read the data into a string, run the code to extract the data and then use runUpdateQuery or runPrepUpdate to write the value back.

You will have to keep track of whether you’ve parsed the data in your source table, maybe by writing a value back to another field in the record. If you are writing to more than one table always use a transaction to ensure all the tables are updated.

7

I had a go at this last night between other jobs. Al I can’t write to the MS db it’s locked by the OEM for read only for remote connections.

I tried to use the runPrepQuery but I wasn’t getting far so I did it in a different way using several components which likely isn’t the best or cleanest way but I got it (mostly) working.

I added a label and bound the text to a SQL query running:

select top 1 station05results from finishedproducts where datetime > DateAdd(MINUTE,-5,getdate()) and failreason = '0'

at an absolute rate of 2 seconds and a fallback value of “No Data” as the machine doesn’t run over tea breaks.

I then added a numeric label and bound the value to a memory tag I created with history to MySQL db. Added a historical easy chart displaying this memory tag.

I added a timer step by 1 with bound 2 and a property change script:

txt1= event.source.parent.getComponent('Label').text print txt1 pos1 = txt1.find('(OK) ') + 68 pos2 = txt1.find(' cm3') print txt1[pos1:pos2] result = txt1[pos1:pos2] print result event.source.parent.getComponent('Numeric Label').value = float (txt1[pos1:pos2])

As you can see the search wasn’t finding the ‘OK’ for some reason so I just counted the characters from start to result.

Also on the timer I bound the ‘Running?’ property to the following expression:

if ({Root Container.Numeric Label.text} = "No Data", "False", "True") || if ({Root Container.Numeric Label.text} = " ", "False", "True")

However sometimes, even though I am only searching for past parts, I get a blank string returned. This is causing me run time errors:

[code]Traceback (most recent call last):
File “event:propertyChange”, line 9, in
ValueError: invalid literal for float:

at org.python.core.Py.ValueError(Py.java:309)
at org.python.core.PyString.atof(PyString.java:1545)
at org.python.core.PyUnicode.atof(PyUnicode.java:1364)
at org.python.core.PyString.__float__(PyString.java:772)
at org.python.core.PyFloat.float_new(PyFloat.java:61)
at org.python.core.PyFloat$exposed___new__.new_impl(Unknown Source)
at org.python.core.PyType.invokeNew(PyType.java:466)
at org.python.core.PyType.type___call__(PyType.java:1558)
at org.python.core.PyType.__call__(PyType.java:1548)
at org.python.core.PyObject.__call__(PyObject.java:387)
at org.python.core.PyObject.__call__(PyObject.java:391)
at org.python.pycode._pyx159.f$0(<event:propertyChange>:9)
at org.python.pycode._pyx159.call_function(<event:propertyChange>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:624)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:168)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.access$000(ActionAdapter.java:40)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter$ActionRunner.run(ActionAdapter.java:280)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.8.0 (b2015101414)
Java: Oracle Corporation 1.8.0_51

[/code]
I tried to get round this by using:

toFloat(event.source.parent.getComponent('Numeric Label').value, 0.0)

I tried many different ways of this but none of them worked, can someone point me in the right direction?

Thanks

8

I would approach this differently. I would create a gateway script which would run every few seconds (depending on how fast data was being added to the database) and parse the last record. If this value had changed it would write the new value to a SQLTag for display and would also write it out to a database. I would also think about reading the time information from the record - it seems a shame to throw this data away and would help identify if 2 subsequent records actually had identical values.

You will have to make sure you can reliably parse the text string - what you’re doing (searching for '(OK) ’ and then stepping forward 68 characters) doesn’t look right, particularly when the string can change in length.

9

I had another go last night and I now don’t have any errors. Still using my brute force un-elegant method.

I found another table in the db which had additional columns for sub station. From this I was able to filter for the differences in the two strings mentioned in post 2.

My SQL query is now:

select top 1 value from stationresults --where timestamp < DateAdd(MINUTE,-5,getdate()) where stationid = '5' and substationid = '0'

And my timer triggered script is:

[code]##//Get station 5 substation 0 results:

#event.source.text = event.source.parent.getComponent(‘Label’).text
txt1 = event.source.parent.getComponent(‘Label’).text
print txt1
pos1 = txt1.find(’(OK)’) + 4
pos2 = txt1.find(’ cm3’)
print txt1[pos1:pos2]
#result = txt1[pos1:pos2]
#print result
event.source.parent.getComponent(‘Numeric Label’).value = float (txt1[pos1:pos2])
#toFloat(event.source.parent.getComponent(‘Numeric Label’).value, 0.0)

##//Get station 5 substation 1 results:

#event.source.text = event.source.parent.getComponent(‘Label’).text
txt2 = event.source.parent.getComponent(‘Label 4’).text
print txt2
pos12 = txt2.find(’(OK) ‘) + 4
pos22 = txt2.find(’ cm3’)
print txt2[pos12:pos22]
#result2 = txt2[pos1:pos2]
#print result2[/code]

I realised the reason that I was stepping forward 68 was that there is an extra space in the (OK) field, different in the two sub station results. This is now fixed.

There are 4 leak testers in total on this machine, and none of the clocks are sync’d (looks like there’s a 2 hour difference between stations).