Table Query Fail

I have a table running a simple query.

SELECT*FROM  alarms_r1
where date between time and time

I noticed while working that the table was encountering an error as shown below.

On: Alarms.Root
caused by GatewayException: Unable to read response from Gateway.
caused by org.xml.sax.SAXParseException; lineNumber: 31; columnNumber: 43; An invalid XML character (Unicode: 0x1) was found in the element content of the document.

I narrowed it down to 2 db entries that were causing the error.
The entries are of type string, and they contained “PS79 PROGRAM NOT MATCH” and “BG79 PROGRAM NOT MATCH”
When I edited the db and put a space in to make the entries “PS 79 PROGRAM NOT MATCH” and “BG 79 PROGRAM NOT MATCH” the error stopped? What is up with that??

According to the error, you had a special Unicode control character in your result: 0x1 - SOH

How was the data entered? Even if it’s a string directly from a PLC you might still need to sanitize it by at least stripping out control codes.

It also means that Ignition’s serialization/deserialization of the result dataset via XML failed to accommodate a legal character in a string. Sounds like a bug to me.

If the data comes from Ignition first, you can use Python’s strip function to remove any nasty Unicode characters before storing it:

sanitized = original.strip(' \f\t\r\n\00')		# strip out control characters

I encountered this same issue reading strings from Mitsubishi Q-Series PLCs.

The string is being logged by a transaction group from an OPC tag originating from a Fanuc OPC server. The error codes come from a Fanuc CNC controller. The error codes are verbatim to the error code/message displayed on the controller itself.

What confuses me is I have logged other error codes like “PS125 MACRO STATEMENT FORMAT ERROR” with no issues
the PS125 being the same format as the PS79 that caused an error.