Non-ASCII characters are garbled in remote procedure call

[former_SQLA_member1694901]

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:

• CHAR collation Sequence: 1252LATIN1
• CHAR character encoding: windows-1252
• CHAR case sensitivity: Ignore
• NCHAR collation sequence: UCA
• NCHAR character set encoding: UTF-8
• NCHAR case insensitivity: Ignore

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?

[VolkerBarth]

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:

"The bytes within an NCHAR string literal are interpreted using the database's CHAR character set, and then converted to NCHAR."

So you might need to use UNISTR to build your literal - or not.

[VolkerBarth]

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.

[johnsmirnios]

A few things you could try:

• Turn on ODBC tracing
• Try a slightly longer string like 'Starém123456'. There was a recent fix related to ODBC RPC calls defining NCHAR results to have byte length semantics rather than char length semantics. I wonder if it could be related. Given that the 'm' isn't showing in your output, I think someone somewhere either truncated it or treated the é (in cp1252) followed by 'm' as a UTF-8 character.
• In the PRC_TestNVarchar procedure, use UNLOAD to dump some definitive information out to a local file. For example (and just going by memory, not actually checking the syntax): UNLOAD select property('charset'), db_property('charset'), connection_property('charset'), cast( TheText as BINARY ) from Result to 'source.txt' format text hexadecimal on.
• Do something similar at the point where you call it from remote: UNLOAD select cast( TheText as binary ) from PRC_TEST_REMOTE to 'rpc.txt' format text hexadecimal on

[VolkerBarth]

There was a recent fix related to ODBC RPC

John, is that fix already contained in a published EBF/SP?

[johnsmirnios]

Sorry, I misread the logs. That change was actually quite a long time ago.

[jack_schueler]

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.

[former_SQLA_member1694901]

Just verified your workaround, this will help me for the moment! Thanks!

[former_SQLA_member1694901]

According to Release Notes this seems to be fixed bug in EBF Build 7181:

================(Build #7181  - Engineering Case #827221)================

The result from a remote procedure that returns an NCHAR, NVARCHAR, LONG 
NVARCHAR, etc. column is not returned to the local server correctly.  The 
nchar data values are garbled.

Example:

forward to RemoteSA;
create or replace procedure P_nvarchar()
result( TheText nvarchar(250) )
begin
    select unistr('To infinity and \\u2135\\u2080');
end;
forward to;
create or replace procedure P_nvarchar_et()
result( TheText nvarchar(250) )
at 'RemoteSA;;;P_nvarchar';
select TheText from P_nvarchar_et();

This problem has been fixed. Now a remote procedure result is handled in 
the same way the result from a remote table is handled.

Note that I have not verified this yet...