Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We are hitting a problem with the extremely useful FOR JSON RAW option:

CREATE TABLE Blah67(PK int default autoincrement, stuff char(50), primary key (PK));

INSERT INTO Blah67(Stuff) values ('£'); INSERT INTO Blah67(Stuff) values ('€'); INSERT INTO Blah67(Stuff) values ('é');

If I select the contents of the table as XML
select * from Blah67 for xml raw;
everything is fine: forxml(1,'PK',xmltab."1",'stuff',xmltab."2") ==================================================================== <row PK="1" stuff="£"/><row PK="2" stuff="€"/><row PK="3" stuff="é"/>

but with JSON

select * from Blah67 for JSON raw;
it all goes haywire: forjson(256,'PK',jsontab."1",'stuff',jsontab."2") ==================================================================== [{"PK":1,"stuff":"£"},{"PK":2,"stuff":"€"},{"PK":3,"stuff":"é"}]

reproducible in &

I am using catalog collation 1252LATIN1 and character set windows-1252

asked 20 Dec '18, 10:51

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

edited 20 Dec '18, 11:12


FWIW, if you change the Stuff column to NCHAR(50), the behaviour is identical. I had suspected it might make a difference.

"SELECT EXPRTYPE(...)" returns XML for both queries. The XML data type is CHAR-based, so that may be relevant here. JSON itself uses Unicode strings.

Resume: I don't have a clue.

(20 Dec '18, 11:34) Volker Barth

That is a bit odd. I can see that, internally, FOR JSON always builds its strings as NCHAR since JSON is always in unicode according to rfc34627.

But the server seems to describe FOR JSON values as the "XML" datatype which seems to be equivalent to "LONG VARCHAR". That would mean that the UTF-8 string would be treated as if it were CHAR charset and, assuming your terminal is using 1252LATIN1, it would not undergo character set translation on the way to the client. Unfortunately, I'm not very familiar with our XML datatype handling but, given that you appear to be getting a UTF-8 encoded string back (a string that should have been converted back to 1252LATIN1), we do appear to be treating FOR JSON as CHAR which is incorrect.

In the end, you want your JSON to be in NCHAR (UTF-8) since valid JSON must always be encoded in Unicode according to the standard.

Given what I see, I think the only workaround to get truly valid JSON is to use a database with a CHAR encoding of UTF-8. And I will need to report a bug...

permanent link

answered 20 Dec '18, 12:45

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

Many thanks for that John - will look forward to a fix in due course. We'll try the workaround.

(20 Dec '18, 13:42) Justin Willey

FWIW, here's an older answer from Nick on the pecularties of XML being implemented as LONG VARCHAR...

(21 Dec '18, 03:18) Volker Barth
Comment Text Removed

The bug has not been fixed. Given the size of the change (effort involved, impact on apps by changes in datatypes, etc), I don't expect it to be addressed in the foreseeable future. I would recommend changing your database to using a UTF8-based encoding (UCA or UTF8BIN).

(13 Mar '23, 09:58) John Smirnios

Hi, i have the same problem as Justin. Is there any fix for this problem yet? I consume the JSON RAW result on a webserver and can't get the characters displayed correctly. I tried setting the JSON RAW as "LONG NVARCHAR" but that doesn't work.

permanent link

answered 16 Sep '20, 08:57

tbr-baehren's gravatar image

accept rate: 0%

which version & build are you using?

(16 Sep '20, 09:18) Justin Willey

(16 Sep '20, 09:23) tbr-baehren
Replies hidden

I think v16 was already out of support at the time of John's comment. The problem is still present in I'll check 6176 when I have it installed.

(16 Sep '20, 09:33) Justin Willey
Replies hidden

I can tell you that it is not fixed yet and I cannot tell you when it will. I recommend using a database CHAR collation that uses UTF8.

(16 Sep '20, 09:43) John Smirnios
Comment Text Removed

I've tested the script above in a UTF8 database, and can confirm that it works fine.

(16 Sep '20, 18:05) Justin Willey

Aside: Note that v16 is out of support since end of 2018 (as Justin has pointed out), but that there are a bunch of newer v16 EBFs than, the lstest one is from 01/2019. It does not fix the current issue but probably other ones...

(17 Sep '20, 02:14) Volker Barth

Hmmm, is it easy to change the database from latin to utf 8?

(18 Sep '20, 06:05) tbr-baehren
Replies hidden

Well, it's easy but requires a database reload as explained here - to quote:

You can change your database collation from one collation to another by performing an unload and reload into the collation of choice. Collations are chosen at database creation time and cannot be changed without rebuilding the database.

(18 Sep '20, 07:55) Volker Barth

Once you've done it, do check that any non-standard characters are displaying properly in your application - it may be expecting something else!

(18 Sep '20, 12:58) Justin Willey
showing 4 of 9 show all flat view

This workaround worked for me:

select * INTO vTemp from Blah67 for JSON raw;

It should even work (do nothing) if the bug gets fixed some time in the future...

permanent link

answered 13 Mar '23, 10:13

tedfroehlich's gravatar image

accept rate: 18%

edited 13 Mar '23, 11:29

That's a nice workaround, as it seems to work with unicode characters that are not valid in the CHAR charset, too.

You might even reduce one conversion:

   declare vTemp long binary;
   select * into vTemp from Blah67 for json raw;
   select to_nchar(vTemp);
(13 Mar '23, 12:55) Volker Barth
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: 20 Dec '18, 10:51

question was seen: 2,689 times

last updated: 13 Mar '23, 12:57