How to apply input parameters in a stored procedure referencing a SAP HANA DocStore Collection?

[iashishsingh]

Hello,

We are trying to work with a Collection in SAP HANA DocStore. We have defined SQL stored procedures which read and write to the Collection but there seems to be an issue with usage of input parameters in the WHERE clause of a SQL statement.

Here are some samples:

Tool - WebIDE > DataBase Explore > SQL Console

Create a Collection:

CREATE COLLECTION TABLE "STOCKS";

Procedure to Create new data:

PROCEDURE "create" (
	IN v_symbol NVARCHAR(6),
	IN v_info NCLOB
	)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
BEGIN

   INSERT INTO STOCKS VALUES('{
	  "symbol": "' || v_symbol || '",
	  "info":'	 || v_info	  || ' 
   }');
   
END

Try with values:

v_symbol - AMZN
v_info - {"price": "1990", "name": "Amazon Inc"}

Results in Success: A row gets created

{"symbol": "AMZN", "info": {"price": "1990", "name": "Amazon Inc"}}

Procedure to Read data:

CREATE PROCEDURE "read" (
	IN v_symbol NVARCHAR(6)
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
   
BEGIN
   
   SELECT * FROM stocks WHERE "symbol" = v_symbol;
   
END

Results in Error:

Error: (dberror) [7]: feature not supported: "read": line 14 col 3 (at pos 311): a where clause has an expression that cannot be supported by collection tables

Procedure to Update data:

PROCEDURE "update" (
	IN v_symbol VARCHAR(6),
	IN v_price DOUBLE
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
BEGIN
   
    UPDATE stocks SET "info"."price" = v_price WHERE "symbol" = v_symbol;

END

Results in Error:

Error: (dberror) [7]: feature not supported: "update": line 13 col 4 (at pos 271): Cannot reference docstore in the set clause

Same procedures when operated on a COLUMN table type get executed. What is going wrong in case of a COLLECTION? Any pointers would be very helpful. Thanks!

[Vitaliy-R · Accepted solution]

One thing: you need to put : in front of variables in statements in procedures:

CREATE OR REPLACE PROCEDURE "read" (
	IN v_symbol NVARCHAR(6)
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
   
BEGIN
   
   SELECT * FROM stocks WHERE "symbol" = :v_symbol;
   
END

Second thing: it seems indeed there is some problem with calling procs via UI, but calling with parameters looks fine:

CALL "DOCSTORE_TEST"."read"(V_SYMBOL => 'SAP'/*<NVARCHAR(6)>*/);

returns

{"symbol": "SAP", "info": {"price": "100", "name": "SAP SE"}}

as expected.

[iashishsingh · Accepted solution]

Thanks for your reply, vitaliy.rudnytskiy!

About usage with parameters, yes this method works. What we are after is using it in the statement binding. Here is a sample code using the @sap/hana-client node module in a NodeJS application.

var sCall = 'CALL "read"(?)';

var statement = client.prepare(sCall),
bindParams = ['SAP'];

statement.exec(bindParams, {}, function(execErr, results) {
  client.disconnect();
});<br>

This results in the error reported previously.

Error:(dberror)[7]: feature not supported:"read": line 14 col 3 (at pos 311): a where clause has an expression that cannot be supported by collection tables

We want to avoid calling the procedure inline using the parameter. Do you know of a way to make it work with Statement binding? Here is a reference to Statement Class

[Vitaliy-R · Accepted solution]

In that case I tried to explicitly use BIND_AS_VALUE:

CREATE OR REPLACE PROCEDURE "read" (
IN v_symbol NVARCHAR(10)
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
   
BEGIN
	SELECT * FROM stocks WHERE "symbol" = BIND_AS_VALUE(:v_symbol);
END;<br>

and it worked with both UI call and calls from Node.js

node test.js
Results: [ { STOCKS: '{"symbol": "SAP", "info": {"price": 107.8, "name": "SAP SE"}}' } ]
Query 'CALL "DOCSTORE_TEST"."read"(V_SYMBOL => ?)' with parameter 'SAP' returned 1 items

[iashishsingh · Accepted solution]

Query parameterization works as demostrated here. Thanks a lot, vitaliy.rudnytskiy!