Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

I have a Procedure like this:

ALTER PROCEDURE "appblddbo"."PRC_TestNVarchar"( )
    TheText NVARCHAR(4000)
  INSERT INTO Result VALUES('Starém');
  SELECT * FROM Result;

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:

result( "TheText" nvarchar(4000) ) 
at 'RemoteDB;;appblddbo;PRC_TestNVarchar'

If I call the remote procedure the result is garbled as


If I use the ODBC data source to connect the "local" database directly, executing the procedure gives the correct result.

Database version is on the "local" side and 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 '22, 09:31

tedfroehlich's gravatar image

accept rate: 18%

edited 09 Sep '22, 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 '22, 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 '22, 13:04) Volker Barth

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, 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 '22, 02:56) Volker Barth

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

(12 Sep '22, 07:12) John Smirnios

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.

(23 Sep '22, 22:52) JBSchueler
Replies hidden

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

(04 Oct '22, 07:09) tedfroehlich
showing 3 of 7 show all flat view

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.


forward to RemoteSA;
create or replace procedure P_nvarchar()
result( TheText nvarchar(250) )
    select unistr('To infinity and \u2135\u2080');
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...

permanent link

answered 03 Jun, 11:12

tedfroehlich's gravatar image

accept rate: 18%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 09 Sep '22, 09:31

question was seen: 715 times

last updated: 03 Jun, 11:12