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.

As a follow-up to this FAQ, it seems that the sp_parse_json system procedure only supports numbers as double or integer:

begin
   declare MyJson long varchar;
   select '{"MyDouble": -12345678901234.567890,
            "MyEValue": -1e6,
            "MyInt": -2147483648,
            "MyBigInt1": 2147483648,
            "MyBigInt2": -1234567890123456789,
            "MyBit": 1}' into MyJson;
   drop variable if exists sql_row;
   call sp_parse_json ('sql_row', MyJson);

   select '1 MyDouble' as "Variable", exprtype('select sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value 
   union all
   select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue
   union all
   select '3 MyInt', exprtype('select sql_row.MyInt', 1), sql_row.MyInt
   union all
   select '4 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1
   union all
   select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2
   union all
   select '6 MyBit', exprtype('select sql_row.MyBit', 1), sql_row.MyBit
   order by 1;
end;

returns

1 MyDouble;double;-12345678901234,568
2 MyEValue;double;-1000000
3 MyInt;integer;-2147483648
4 MyBigInt1;integer;2147483647 <-- wrong
5 MyBigInt2;integer;-2147483648 <-- wrong
6 MyBit;integer;1

This was tested with 17.0.10.6175.

Resume: MyBigInt1 and MyBigInt2 are wrongly limited to the maximum positive resp. negative integer values, so it seems that JSON numbers in the format without fraction and exponent are wrongly casted to integer instead of bigint or other "huger" types.

asked 30 Sep '20, 05:03

Volker%20Barth's gravatar image

Volker Barth
40.5k365556827
accept rate: 34%

FWIW, it does not make a difference if the according variable used with sp_parse_json is declared to have a bigint component beforehand - it will nevertheless be returned as an integer:

begin
   declare MyJson long varchar;
   select '{"MyBigInt1": 2147483648}' into MyJson;
   drop variable if exists sql_row;
   create variable sql_row row(MyBigInt1 bigint);
   select '1 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1;
   call sp_parse_json ('sql_row', MyJson);
   select '1 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1;
end;

returns

1 MyBigInt1,bigint,(NULL) -- before sp_parse_json
1 MyBigInt1,integer,2147483647 -- after sp_parse_json
(05 Oct '20, 03:36) Volker Barth

FWIW, this has been partly fixed with SQL Anywhere 17.0.11.6933 and above, according to the docs the parsing of integral values now allows for signed bigint numbers.

However, large bigint values seem to get rounded to thousands, see values 5 and 6 with the minimum and maximum bigint values (tested with 17.0.11.7236):

begin
   declare MyJson long varchar;
   select '{"MyDouble": -12345678901234.567890,
            "MyEValue": -1e6,
            "MyInt": -2147483648,
            "MyBigInt1": 2147483648,
            "MyBigInt2": -9223372036854775808, 
            "MyBigInt3":  9223372036854775807, 
            "MyBit": 1}' into MyJson;
   drop variable if exists sql_row;
   call sp_parse_json ('sql_row', MyJson);

   select '1 MyDouble' as "Variable", exprtype('select sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value 
   union all
   select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue
   union all
   select '3 MyInt', exprtype('select sql_row.MyInt', 1), sql_row.MyInt
   union all
   select '4 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1
   union all
   select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2
   union all
   select '6 MyBigInt3', exprtype('select sql_row.MyBigInt3', 1), sql_row.MyBigInt3
   union all
   select '7 MyBit', exprtype('select sql_row.MyBit', 1), sql_row.MyBit
   order by 1;
end;

returns

1 MyDouble;double;-12345678901234,568
2 MyEValue;double;-1000000
3 MyInt;bigint;-2147483648
4 MyBigInt1;bigint;2147483648
5 MyBigInt2;bigint;-9223372036854776000 --< note the difference to -9223372036854775808
6 MyBigInt3;bigint;9223372036854776000  --< note the difference to 9223372036854775807
7 MyBit;bigint;1

permanent link

answered 23 Feb '23, 06:09

Volker%20Barth's gravatar image

Volker Barth
40.5k365556827
accept rate: 34%

edited 23 Feb '23, 06:10

This behavior appears to only occur when one or both of MyDouble and MyValue are selected in the query. For example, the following will work

select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2 union all select '6 MyBigInt3', exprtype('select sql_row.MyBigInt3', 1), sql_row.MyBigInt3 -- union all -- select '1 MyDouble' as "Variable", exprtype('select ----sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value -- union all -- select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue

If you uncomment the select for MyDouble and/or MyEValue, the behaviour reported will be seen.

(23 Feb '23, 08:34) Chris Keating
Your answer
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
×30

question asked: 30 Sep '20, 05:03

question was seen: 1,259 times

last updated: 23 Feb '23, 08:34