How to call a hana sql stored procedure with overview from Hana XS Server (file.xsjs) ?

[cphmedard]

Hello,

I am having trouble calling from a file.xsjs a hana sql stored procedure (with overview) that returns and persists into hana tables two output tables as result sets.

code-snippet.xsjs

var connection = $.db.getConnection();

1. connection.prepareStatement("SET SCHEMA \"AIRLINE_OPS\"").execute();

var spString = "{call rule_check_legality_in_recovery_window_api('2013-05-15','2013-05-17',\"_SYS_BIC\".\"crew/VW_RULE_CHAIN\",\"TBL_RULE_VIOLATIONS_RW\", \"TBL_RULE_CHECK\") with overview}";

$.trace.error("sp call : " + spString);

var checkLegality = connection.prepareCall(spString);

var result2 = checkLegality.execute();

the indexserver trace indicates invalid table name 😞

[44860]{0}[9171867] 2013-05-03 04:38:46.991717 e cePlanExec   cePlanExecutor.cpp(06157) : Error during Plan execution of model AIRLINE_OPS:_SYS_SS_CE_1336544_INS (-1), reason: AIRLINE_OPS.RULE_CHECK_LEGALITY_IN_RECOVERY_WINDOW_API: line 21 col 6 (at pos 547): invalid table name exception: AIRLINE_OPS.RULE_CHECK_MAX_CONNECTION_TIME: line 16 col 4 (at pos 501): invalid table name exception: invalid table name: invalid table namePlease check lines: 19,

[44860]{0}[0] 2013-05-03 04:38:46.993556 e xsa: Airline SandBox.cpp(00521) : Found the following errors:

===========================

Error: CallableStatement.execute: sql exception - invalid table name (line 44 position 0 in /AirlineUI5SP4/WebContent/airlineui5/saveRules.xsjs)

I have had no trouble calling a hana sql stored procedure that does NOT persists its results into hana tables (ignoring with overview and using ? as output arguments).

Thank you,

kind regards,

Claude Philippe

[Former Member]

Hi,

you should prepareCall instead of prepareStatment see the documentation here: http://help.sap.com/hana/SAP_HANA_XS_JavaScript_Reference_en/$.db.Connection.html

Regards

Mohamed Ali