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?

asked 09 Sep, 09:31

tedfroehlich's gravatar image

tedfroehlich
1702211
accept rate: 20%

edited 09 Sep, 11:57

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.

(09 Sep, 10:57) Volker Barth
Replies hidden

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.

(09 Sep, 13:04) Volker Barth
1

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
(11 Sep, 22:39) John Smirnios
Replies hidden

There was a recent fix related to ODBC RPC

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

(12 Sep, 02:56) Volker Barth
1

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

(12 Sep, 07:12) John Smirnios
3

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.

(2 days ago) JBSchueler
showing 3 of 6 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×13

question asked: 09 Sep, 09:31

question was seen: 109 times

last updated: 2 days ago