Skip to Content
0

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

Apr 24 at 03:40 PM

193

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Apr 26 at 06:37 AM
0

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

Show 16 Share
10 |10000 characters needed characters left characters exceeded

Hi Florian,

thank you very much, it works!!!

The additional declaration is not so bad for me. I'm really happy that it works. :)

Thanks & BR,

Dirk

0

The additional declaration (v_json_string) is not really necessary. You can create the JSON string directly within the Insert statement. What I meant with cumbersome is the topic that no JSON object can be used; I don't like the string concatenation things :)

0

Yes, you are right.

Nevertheless a nice idea to solve this issue. :)

0

Hi Florian,

it doesn't work if the value is null.

Do you have also a nice suggestion two handle it, if the value is null?

Thanks a lot!

BR,Dirk

0

Ok, it looks like I found a solution for this case. It seems to work.

Select

IFNULL(CAST("Zahl" as nvarchar(1)),''),

...

0

With the coalesce function it would work too, especially if you wanna have a real null instead of a blank in your JSON. The string concatenation in the first expression of the coalesce function would result in null in case v_test is null.

declare v_json_string nclob := '{ "ID": "9999", "a": "' || coalesce('"' || :v_test || '"','null') || '" }';
1

Good to know. Thanks for your second option!

0

Hi Florian,

may be you could help me one more time.

I don't get it to run for an update.

		UPDATE "dummy" SET  "A" = '{
		"n" : "' || :lv_sn || '",
		"s" : "' || :lv_s || '"
		}' where "MpID" = lv_MID;

Any ideas? Thanks again!

0

What should this do? Update a "sub-object" within an existing JSON object? Not sure if that is supported/at least this is not documentated as supported.

0

"Update a "sub-object" within an existing JSON object?" Exactly!

And the issue is the same, if I use a JSON Object with values than it works, but not with variables.

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/8f3a3bf0a2c44099acf355004c665d80.html

Thanks!

0

Hello Dirk,

can you please provide a full example? Thank you.

Best regards,

Kai

0

Hi Kai,

I think the most is explained already in this post, but will try it again.

The main issue is that I'm not able to Insert, Update a Collection with variables within stored procedures on an easy way. For an Insert with variables we have found a solution, which you can find above.

Now I'm looking for an way also to Update a collection. To do it, I'm using the SAP sample for this showcase.

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/8f3a3bf0a2c44099acf355004c665d80.html

1. Create and Insert a JSON string in a collection

(In this case we skip the Insert with variables, because its already described above)

CREATE COLLECTION OverSeasContacts;
INSERT INTO OverSeasContacts VALUES ('{ "firstname":"John", "lastname":"Smith", "age":45, "address": { "street": "Dietmar-Hopp-Allee", "city": "Heidelberg" } }');

2. Now we try to update this SQL statement with a variable!

This sample works by using Sql without variables as expected.

UPDATE OverSeasContacts UNSET "address"."street" WHERE "address"."city" = 'Heidelberg';

Now we try to use this easy sample with variables.

PROCEDURE "dummy.db.procedures::OverSeasContacts" ()
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   --READS SQL DATA
   AS
BEGIN
	DECLARE lv_sStreet		string := 'Hauptstraße';
	DECLARE lv_sCity		string := 'Walldorf';
-- UPDATE OverSeasContacts SET "address"."street" = 'Hauptstraße' WHERE "address"."city" = 'Walldorf';
UPDATE "dummy.db::OverSeasContacts" SET  "address"."street" =  lv_sStreet WHERE "address"."city" = lv_sCity;

It doesn't work for me. But you are the expert I would guess, after reading your nice blog. Maybe I'm doing something wrong.

Thanks for helping us!

0

Hi Dirk,

thank you for that example. It's sometime hard to follow the conversation in the comments and to track the SQL.

So, good news, I fixed your problem (or let's say I fixed the problem I found). If you error was something like "SAP DBTech JDBC: [7]: feature not supported: "SYSTEM"."MODOVERSEASCONTACTS": line 10 col 2 (at pos 335): Cannot reference docstore in the set clause", then you missed ":" in the procedure body.

Here is a working example (tested SPS02 and SPS03 of SAP HANA 2.0)

DROP COLLECTION OverSeasContacts;
CREATE COLLECTION OverSeasContacts;
INSERT INTO OverSeasContacts VALUES ('{ "firstname":"John", "lastname":"Smith", "age":45, "address": { "street": "Dietmar-Hopp-Allee", "city": "Heidelberg" } }');
DROP PROCEDURE ModOverSeasContacts;
CREATE PROCEDURE ModOverSeasContacts ()
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
BEGIN
	DECLARE lv_sStreet		string := 'Hauptstraße';
	DECLARE lv_sCity		string := 'Heidelberg';
	
	UPDATE OverSeasContacts SET  "address"."street" =  :lv_sStreet WHERE "address"."city" = :lv_sCity;
END;
CALL ModOverSeasContacts();
SELECT * FROM OverSeasContacts;

Please note the ":" before lv_sCity and lv_sStreet. If this character is missing the above mentioned error occurs. I hope that solved your problem.

Best regards, Kai

0

Hi Kai,

thanks this helped me to find a solution for an update with an JSON object. :)

I think I had the whole time only problems with the debugger. In the past I used also ":" but not when I made this sample for you.

I can only speak for the XSA dev environment, but there I was struggling always with issues while I was working with the debugger.

After trying this coding without the debugger I realized that all works fine.

I.E. this

DECLARE lv_sCity		string := 'Heidelberg';
DECLARE lv_sPhonenumber		string := '123434';

UPDATE "dummy.db::OverSeasContacts" SET  "Phone" =  '{Telephone : ' || :lv_sPhonenumber || '}' WHERE "address"."city" = :lv_sCity;

Result:
{"firstname": "John", "lastname": "Smith", "age": 45, "address": {"street": "Dietmar-Hopp-Allee", "city": "Heidelberg"}, "Phone": "{Telephone : 123434}"}

May be if you know also a solution without "concatenating" , please let me know.

Thanks for any help!

BR, Dirk

0

Hi Dirk,

as far as I know there is currently no other way then concatenating to generate new JSON in SQL.

The only other way I can image is to use the SELECT command to generate new JSON on the fly:

CREATE COLLECTION dummyCollection;
insert into dummyCollection values({"v":2});
select {a:"v", b:22} from dummyCollection;

This returns:

{"A": 2, "B": 22}

Please note that only fields of a collection and constants can be used inside this statement, so no parameters are supported.

Best regards,Kai

0
Show more comments