I am having a problem inserting text with special characters into a table using Ignition. For instance, If I try to write words that have an accent or tilde, it converts them to unreadable characters. Here are the conditions:
I have a table with a NVARCHAR(MAX) column. I’m using SQL Server 2014.
I have some text with Spanish characters (I’ll use “‘cuánto piñata’” for this example).
If I write a simple update query from the Management Studio, it works fine, i.e. “UPDATE Faults SET Fault=N’cuánto piñata’”. It also works if I just past the text into the column.
If I write the same update query from Ignition, the table updates with “cuánto piñata”
So far I’ve tried both system.db.runUpdateQuery and system.db.runPrepUpdate. If I output my query text to the debug console, it displays correctly, so something is happening to the query via the JDBC driver. I did a test with modifying the collation settings in both the table and column, and that didn’t help, plus I didn’t want to do it that way anyway because it should be able to accept multiple languages.
Any ideas?
(To the Spanish speaking members here, I know my example is probably gibberish. I was just looking for a couple of random words with special characters.)
You have to make sure the database is set up to use a locale that supports your character set, and that the column data type is set to use it. The most compatible way to do this is to set the database to use UTF-8 everywhere, and make the columns accept that encoding. The gibberish you are seeing is typical of UTF-8 (from Ignition) being incorrectly stored under a single-byte encoding.
For future people who find this thread, for some databases you’ll also need to add to the connection properties. For example, in MySQL you need to add useUnicode=yes;characterEncoding=utf8; . For SQL Server, the sendStringParametersAsUnicode property (docs) looks interesting, but I rarely play with SQL Server so I haven’t tested using it.
It is database-specific, typically set up during database creation with a default for all tables and indices to follow. In PostgreSQL, when I setup the empty cluster, I use --encoding=UTF-8 as an option to the initdb command.
You'll have to find the equivalent for whichever database you are using, and find the documentation for changing these settings after-the-fact.
I did some digging, and found this: "SQL Server stores Unicode data (i.e. that which is found in the XML and N-prefixed types) in UCS-2 / UTF-16 (storage is the same, UTF-16 merely handles Supplementary Characters correctly). This is not configurable: there is no option to use either UTF-8 or UTF-32. "
I also tried something else, and stored “piñata” as VARBINARY by first converting in in the INSERT statement, and then converting it back when I attached it to a label, but I had the same results. I didn’t think VARBINARY would care about encoding, but apparently piñata is modified before it gets to the database.
One other odd thing I found: I did a test and intentionally made an error in the UPDATE query so it would throw an error. In the error dialog, “piñata” is garbled, but in the Output Console, it is displayed correctly. So something is happening to it before the query is executed, or so it seems. Also, if I write to the database with the Query editor and read the value back through Ignition, it works fine, so it doesn’t seem to be an encoding issue in the database.
Well, I guess you're screwed. You have characters that need a multibyte representation, and your database isn't set up to hold them. Consider changing all of your CHAR and VARCHAR columns to NCHAR and NVARCHAR. Otherwise, you'll have to pick an encoding/locale that covers all the accented characters you need in single bytes. Then you'll have to enforce that in every place in the server that handles these characters, and on every client that can query for them. Good luck.
Phil, UTF-16 is a two-byte representation. It’s what Java uses internally, so it should be fine. But OP will definitely have to use NVARCHAR instead of VARCHAR.
I’d suggest having support go over your set up with you. We have a lot of end users with non-latin alphabets using Ignition with SQL Server, so this definitely something they know how to do. Unfortunately, I don’t have a SQL Server instance on this machine, so it’s going to be way more efficient to have them help.
While Java and SQL both operate on UTF-16 by default, Python prefers UTF-8 (which fits a newer language that prefers a superior encoding). So the issue is probably inside python.
Could you try to explicitly encode the string to utf-16? "MyString".encode("utf-16")
When I do that, I get this error: “org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 302; An invalid XML character (Unicode: 0x0) was found in the CDATA section.”
But I think you are on the right track. To get around this temporarily, what I did was use Ignition to create some *.SQL files on the local drive and I manually executed them from Management Studio, and all the special characters displayed correctly (this is a database of foreign language fault texts for a machine that is installed in various countries). So it definitely looks like Ignition prepares the query correctly, but the encoding breaks when it is passed through the jdbc connection.