Skip to Content
0
Apr 24, 2018 at 03:40 PM

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

612 Views Last edit Apr 25, 2018 at 06:42 PM 4 rev

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