I have a Procedure like this: ALTER PROCEDURE "appblddbo"."PRC_TestNVarchar"( ) RESULT( TheText NVARCHAR(4000)) BEGIN DECLARE LOCAL TEMPORARY TABLE Result ( TheText NVARCHAR(4000) ); INSERT INTO Result VALUES('Starém'); SELECT * FROM Result; END If I call it locally the text is returned correctly. On a remote server this procedure is created as a remote procedure using an ODBC data source: ALTER PROCEDURE "appblddbo"."PRC_TEST_REMOTE"() result( "TheText" nvarchar(4000) ) at 'RemoteDB;;appblddbo;PRC_TestNVarchar' If I call the remote procedure the result is garbled as Star� If I use the ODBC data source to connect the "local" database directly, executing the procedure gives the correct result. Database version is 17.0.10.5820 on the "local" side and 17.0.11.6933 on the "remote" side. I'd like to avoid upgrading the local database if possible. Settings for both databases:
The result does not change when changing the literal expression in the procedure to "N'Starém'" or "UNISTR(N'Starém')". Since é is part of windows-1252 IMHO it should be converted to NCHAR correctly, or am I missing something? Applying UNISTR to the remote result leads to a (probably) infinite loop. Can anyone give me a hint what I am doing wrong? Can this be an ODBC problem? Or better to look at some database settings? |
Does it also fail if you prefix the string literal with N (I.e. "VALUES(N'Starém')"?
What's your database's CHAR charset? If it's not UTF8 but a single byte charset like Windows-1252, note, that SQL statements including string literals are parsed in the database charset, whereas you want your unicode literals taken as such... (I tend to forget that myself.)
Sigh. As to the docs:
So you might need to use UNISTR to build your literal - or not.
Hm, I don't really have a clue. Does it work if you specify the 'é' via the UNICODE code point (i.e. '\uXXXX' with the according value) instead?
Confine that quite new FAQ.
A few things you could try:
John, is that fix already contained in a published EBF/SP?
Sorry, I misread the logs. That change was actually quite a long time ago.
I have verified that this is a bug (fetching results from a remote procedure returning an nchar type). A fix is in the works. One workaround is to change the return type on the remote to binary and then cast the result to nchar at the local server.
Just verified your workaround, this will help me for the moment! Thanks!