we are currently replacing our SAP Business Connector integrations with iFlows on SAP CPI. And now we have found a strange behavior with some special characters, where I would be interested in, if you had similar issues in the past and how you solved it.
The relevant integrations provide data that comes from our backend systems directly into the databases of other systems via JDBC. On CPI we use the "new" on-premise JDBC-connectivity for the replacement by inputting an SQL-XML structure with several fields in the adapter.
A simple example of our insert in SQL is:
INSERT INTO [dbo].[Test_CPI_Tab] ([Test_CPI]) VALUES ('Maranhăo');
the result in the MSSQL-DB is 'Maranhao'
So the special character gets replaced by an a.
We have tried to change the CamelCharset of CPI as well as the Collation of the database to UTF-8, Latin-1 and Cp1252.
After some time we found the following article nchar and nvarchar (Transact-SQL) - SQL Server | Microsoft Docs, which suggest putting in a N in front of the String which should be inserted:
INSERT INTO [dbo].[Test_CPI_Tab] ([Test_CPI]) VALUES (N'Maranhăo');
the result in the MSSQL-DB is now correct: 'Maranhăo'.
What is wondering us, is that within SAP BC it works with the SQL-XML out of the box, but with SAP CPI not.
Do you have any suggestions, what we can try in SAP CPI to make it work/is there a way to prefix the XML-fields in CPI with the N?
PS: This is for our testing the SQL-XML structure with the same problem with the SQL string: