Spanish Characters Encoding issue with Ignition while committing to SQL Server DB

Ignition
1

We have some incoming inventory XMLs which has Spanish characters. We store the XML in a SQL Server DB table with XML datatype for the column. XML datatype already supports UTF-8 encoding, so there shouldn’t be any issue with storing Spanish characters.

If I directly use an insert command to store Spanish characters on SQL Server, they remain intact. But when I try to insert the XML using system.db.runUpdateQuery() with N’ ‘ prefix or system.db.runPrepUpdate with ? parameters from a script, the Spanish encoding breaks during storage in DB. Garbled characters get stored.

Has anyone else seen this behavior from Ignition script or know a fix for this ?

I also saw this old post. Not sure whether a similar issue could be there for XML data type on SQL Server DB - Special Characters

Special Characters
2

Maybe make sure you’re passing a real Unicode string from Jython u" befreo store it on the SQL Server

xml_u = unicode(xml_string) # or ensure it’s u"..."
system.db.runPrepUpdate("INSERT ... VALUES (?)", [xml_u], "CONN")

1 Like
3

Tried this. Didn’t work.

4

I wonder if you need to also add the "u" in front of the SQL query like so:

xml_u = unicode(xml_string) # or ensure it’s u"..."
system.db.runPrepUpdate(u"INSERT ... VALUES (?)", [xml_u], "CONN")
5

Have you tried via a named query?

6

At the point where you get the XML from the external source, log repr(xml) to see if the encoding is broken before you ever try to put it in the DB.

1 Like
7

@michael.flagler - Tried your recommendation. Didn’t work.

8

Thanks for this tip. Encoding was breaking when XML was being retrieved from an url. I used the following code to fix it along with system.db.runPrepUpdate()

client = system.net.httpClient()
response = client.get(url)
filecontent = response.getText("UTF-8")
2 Likes
9

Interesting. getText uses the charset returned in the response headers, falling back to UTF-8 automatically if not specified. You had to add the explicit charset to get it to work, or you had to migrate to system.net.httpClient vs system.net.httpGet or something?

10

I was using system.net.httpGet initially.

I switched to system.net.httpClient and set UTF-8 explicitly as all the XMLs we receive are in UTF-8 format.