How to pass data to a input table parameter of a procedure while calling it from XSJS file

[Jagadeesh]

Hi,

How can i pass table data to the input variable of a procedure when i am calling it from a xsjs file.

Here is my code:

hdbprocedure:

PROCEDURE "Schema"."procedures::ProcedureName" (

in it_records table( EMPID integer,

NAME nvarchar(50) ),

in iv_action varchar(10) )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA "Schema" AS

BEGIN

// here i will update the data(it_recordfs) to my DB tables basing on the action(iv_action)//

END;

xsjs:

var conn = $.db.getConnection();

var data = [{"EMPID":123,"NAME":'New'}];

var action = 'insert';

var qry = 'CALL "Schema"."procedures::ProcedureName" (?,?)';

var stmt = conn.prepareCall(qry);

stmt.setString(1,data);

stmt.setString(2, action);

stmt.execute();

When i am executing this, i am getting the error as:

InternalError: dberror(Connection.prepareCall): 8 - invalid argument: Input parameter is inadequate as table parameter: line 1 col 41 (at pos 40) (line 6 position 1 in /services/sample.xsjs)

Can anyone help me on this?

Thanks in advance.

[SergioG_TX · Accepted solution]

there are a few things you should consider....

1) use the $.hdb api instead of the $.db api

2) you can pass a JS array to a stored proc function -- simulates the table as input param in the stored proc

here is an example:

https://blogs.sap.com/2015/11/18/callling-stored-proc-with-multiple-values/

[Jagadeesh · Accepted solution]

Hi sergio,

Thank you for your quick response.

Already i tried the way that you specified in the link ,it was working fine.

But when i try to call procedure like this:

var conn = $.hdb.getConnection();

var data = [{"EMPID":123,"NAME":'New'}];

var action = 'insert';

var qry = 'CALL "Schema"."procedures::ProcedureName" (?,?)';

var stmt = conn.prepareCall(qry);

stmt.setString(1,data);

stmt.setString(2, action);

stmt.execute();

It is showing the error: TypeError: conn.prepareCall is not a function (line 6 position 1 in /services/sample.xsjs) , So can u suggest on this.

[SergioG_TX · Accepted solution]

look in the blog how to get the stored proc loaded and then pass the array to the function as an input param... instead of using the embedded sql - (my function is called getSPOutput)

[former_member208449]

Hi

You can use the newer $.hdb database api.

var fnSell = connection.loadProcedure('DB_EXAMPLE','icecream.shop::sell');

var result = fnSell('CHOCOLATE',3,30.0);