Handling conversion of characters between different systems using different character sets is always very challenging. If all systems could just work natively with e.g. UTF8 then it would be much easier. But until that is the case, we (as a provider of data integration solutions) have to do our best to handle these things with as little impact for our end users as possible.
Therefore its a pleasure for me to introduce you to a new way of handling the character conversion between systems that support UTF8 (like Salesforce.com) and systems using a MS SQL Database (like Microsoft Dynamics NAV, GP, AX).
Internally at RapidiOnline we have had support for handling texts as UTF8 (unicode) for some time now. The starting point is still using a standard character set (e.g. cp850 or what the source and destination systems use) as not all systems support unicode and we do not always know what character set is used in locally installed systems. However, we can setup a RapidiOnline service to use UTF8 internally for all texts that are transferred to/from a given system.
For systems that support UTF8 this works perfectly because there is no conversion and we are sure not to loose any information - so data can be transferred correctly.
However, if transferring data between a system that uses UTF8 and another system that does not support UTF8 or unicode - which is still the case for most Microsoft Dynamics NAV installations - a conversion has to take place at some point between the systems.
For systems running on MS-SQL Server we now have the possibility to let the MS-SQL driver handle this conversion. We will supply the texts to the MS-SQL driver in UTF8 format and ask the driver to do the conversion. The same case applies to when reading texts; the MS-SQL driver will deliver the texts in UTF8 format. In this way we keep data in the UTF8 format when transferring the data, hence we dont loose any information. In other words we let the system that needs data in another format (the MS-SQL database server) do the conversion. As the MS-SQL database server is the system of the client and it is setup to know and support the character set used internally in that database, it is also the best system to do a correct conversion of the characters.
How to setup the RapidiConnector to let the MS-SQL driver handle the conversion
To setup the RapidiConnector to let the MS-SQL driver handle the conversion between UTF8 and the character set internally used in the database, you have to add two lines (parameters) to the rapidi.cfg file for each RapidiConnector (rapidi.exe) that you have installed (locally on your server).
Please add the following lines to the rapidi.cfg file(s):
MSSQLUseUnicodeText : 1
MSSQLUseUnicodeTextVarChar : 1
Both parameters are default 0, so you need to set them to 1 to take effect. The MSSQLUseUnicodeText will enable that we send/receive UTF8 for fields with types SQL_WCHAR and SQL_WVARCHAR (multibyte - unicode char fields in MS SQL). The MSSQLUseUnicodeTextVarChar parameter does the same for SQL_CHAR and SQL_VARCHAR (singlebyte char columns).
How to setup your central RapidiOnline Service to use UTF8 texts
On the central side, we also need to let the RapidiOnline Service know that it should keep texts in the UTF8 format and not convert them to any other character set. This is also done with a cfg file parameter as follows:
SForceUseUnicodeText : 1
This parameter is also default 0 (so it need to be set to 1 in order to work). Setting parameters in the central cfg file is currently not possible in the user interface, so you need to ask our support to do this for you.
These features were introduced in version 3.2.92v. Please ask our support to help you with an upgrade if needed.
Handling UTF8 texts correctly and preserving them as UTF8 in formulas
When switching to have texts in UTF8 format internally in RapidiOnline, the functions that manipulate text strings also need to recognize that a string is in UTF8 format and need to handle it correctly. Also evenly important they also need to deliver the result as a UTF8 encoded string. A number of functions were already doing this (like POS, LENGTH and COPY), but some functions still delivered the result as normalt text.
Now the functions BEFORE, AFTER, LAST, MIDDLE, DEL, POSTDEL also work with UTF8 (unicode) text strings, preserving the string as unicode for further processing.
This change was introduced in version 3.2.92x. You need to upgrade both the central and the RapidiConnector to this version. Please ask our support to help you with any upgrades.
As always, your comment and questions are most welcome.
Thanks
Michael
