Skip to Content

SAP HANA XSA 2.3 SQL JSON DocStore - Stringify values in SQL

Hi,

I want to insert values in a DocStore.

This works manually:

INSERT INTO "dummy" values('{	
"ID": "9993",
    "v": "11",
    "o": "B5000000B82",
    "m": "0",
    "n": "0",
    "l": "DE"
    }');

But in my code I've to Cast the values into a nvarchar

i.e.
Select 	
	CAST("M" AS NVARCHAR(10)),
	CAST("P" AS NVARCHAR(20)),
	CAST("I" AS NVARCHAR(15)),
,...

After casting my values look like this in this object

INSERT INTO "dummy" values('{	
"ID": "47",
    "v": "01",
    "o": "FB0000508901DE2C1600282C34CFBD2B",
    "m": null,
    "n": null,
    "l": "de-DE"
    }');

If I try it manually (sql console) then it works fine.

I think the problem is that it doesn't work with variables.

Get this error: invalid argument:

Tried Type as a Nvarchar and as a String.

i.e.
	DECLARE lv_sM NVARCHAR(10);
	DECLARE lv_sV NVARCHAR(20);.....

 INSERT INTO "dummy" values('{	
	"M": lv_sM,
    "v": lv_sV,
    "U": lv_sU,
    "a": lv_sA,
    "z": lv_sZ,
    "l": lv_sL
    }');

Should it work with variables in stored procedures or are there any restrictions?

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Apr 26 at 06:37 AM

    Hello Dirk,

    the issue in your case is that you pass a JSON string which includes the variable names in the string instead of the variable contents. That leads to the invalid JSON object resulting in the error.

    A simple solution would be to pass just a JSON object instead of a JSON string.

    insert into dummy values ({ "ID": '9999', "a": :v_test });

    But unfortunately there is a restriction that only constant values and path expressions can be used in that case at the moment.

    What works is to build the JSON string with the variable contents.

    declare v_test nvarchar(256) := 'test value';
    declare v_json_string nclob := '{ "ID": "9999", "a": "' || :v_test || '" }'; insert into dummy values (:v_json_string);

    A little bit cumbersome from my point of view, but maybe in the future the restriction that only constant values and path expressions can be used in JSON objects will be removed.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks for your fast reply and hint!

      Will only correct myself, today I don't have trouble to work with the debugger in combination with JSON objects. May be there were other issues the days before.

      The only big difference which I realized between SPS 2 and 3 is that a refresh from the context menu isn't enough. I have always to refresh with F5 to be sure that the coding is refreshed and to convince myself by checking for coding with "Open for Debugging"