Error With ASCII Control Character In Data Field

I’m having an issue with an ASCII string that has a control character in it. Specifically the “Group Separator” (Decimal 29, Hex 1d)

We are updating a table with a SQL query. The customer has serial numbers with the Group Separator in them. When we execute the query using the serial numbers in the where clause we get the attached error message.

Can you replace the group separator with a space wherever you’re getting these serial numbers from?

The serial number is laser etched into a 2D code permanently on the finished product. So it must be preserved in this format in the database for a future lookups. MS SQL seems to have no trouble with it. Also the SQL bridge module has no problems with storing the serial number to a database or recalling it from one.

Yeah, the problem is that the query ends up getting sent to the gateway inside an XML CDATA block and the XML spec doesn’t allow most of the ascii character codes <= 0x20.

We should be able to get this fixed somehow for 7.5.9 though…

+1 waiting patiently for that upgrade.

Ok, so I’ve changed it so that system.db.runPrepUpdate and system.db.runPrepQuery can accept any unicode characters in strings in their argument lists. Actually, system.db.runPrepQuery already could, so all I changed is system.db.runPrepUpdate. This is what you’re using, right? It wasn’t clear from your posts.

Edit - not sure what you’re doing now that I look over your posts. You certainly won’t be able to include this string into a normal query. You’ve got to use the prepared versions.

We are doing a SQL Query with Unicast characters in the ASCII string of the where clause.

You cannot insert ASCII control characters directly into a normal query. You’ll have to use the runPrepQuery and runPrepUpdate versions, where the query arguments get passed in as objects