"XS Procedures API" -> Calling Procedures with Arguments that Reference an Existing Table

[draschke]

Hi,

I try to get familiar with "XS Procedures API" (SPS09).

http://help.sap.com/hana/SAP_HANA_Developer_Guide_for_SAP_HANA_Web_Workbench_en.pdf

"Calling Procedures with Arguments that Reference an Existing Table"

"The WITH OVERVIEW expression also allows you to write the results of a procedure into a global temporary table; that is, a table that is truncated at session close. To use XS Procedures to write the results of a procedure into a global temporary table, you do not specify a name for the result table; you include an empty string (''), as illustrated in the following example:

var conn = $.db.getConnection(); resCall = getRating(rating, 3, '', conn); // {"RESULT": [{"variable":"RESULT","table":"\"SCHEMA\". \"RESULT_5270ECB8F7061B7EE10000000A379516\""}]} "

My question is how can I query the temporary table, after I called the Procedures.

If I debug the procedures I can see the entries of the table (ex_value). But  how can I see the entries of the global temporary table?

Does anyone has a simple example, where I can check the steps after calling a procedures?

Thanks for your help,

Dirk

[pfefferf · Accepted solution]

Hello Dirk,

you can query the temporary table via the db interface.

Simple example which queries the top 10 objects from table "_SYS_REPO"."ACTIVE_OBJECT".

Procedure:

PROCEDURE "TEST"."test.procedures::test_for_xsjs_xsproc" ( OUT et_active_object "_SYS_REPO"."ACTIVE_OBJECT" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   READS SQL DATA AS
BEGIN
   et_active_object = select top 10 * from "_SYS_REPO"."ACTIVE_OBJECT";
END

XSJS:

var oConnection = $.db.getConnection();
var XSProc = $.import("sap.hana.xs.libs.dbutils", "procedures");
XSProc.setTempSchema("TEST");

var oProcedure = XSProc.procedure("TEST", "test.procedures", "test_for_xsjs_xsproc", {connection: oConnection});
var oResult = oProcedure('');

var oPrepStatement = oConnection.prepareStatement("SELECT * FROM " + oResult.ET_ACTIVE_OBJECT[0].table );

var oResultSet = oPrepStatement.executeQuery();

var sResult = "";
while(oResultSet.next()){
    sResult += oResultSet.getString(2) + "</br>";
}

$.response.contentType = "text/html";
$.response.setBody(sResult);

Best Regards,

Florian

[draschke · Accepted solution]

Hi Florian,

thank you! This is exactly what I wanted to know.

Was only wondering, because I get an result from a procedure and I need to query it again to work with it.

Best regards,

Dirk

[pfefferf · Accepted solution]

Hello Dirk,

cause you are on SPS09 you can of course use the new $.hdb interface.

Here sample xsjs for the sample procedure above producing the same result than the xsjs with the "old" code:

var oConnection = $.hdb.getConnection();

var fnProcedure = oConnection.loadProcedure("TEST", "test.procedures::test_for_xsjs_xsproc");

var result = fnProcedure();

var activeObjects = result.ET_ACTIVE_OBJECT;

var sResult = "";


for (var i = 0; i < activeObjects.length; i++) {
    sResult += activeObjects[i].OBJECT_NAME + "</br>";
}

$.response.contentType = "text/html";
$.response.setBody(sResult);

Best Regards,

Florian

[draschke · Accepted solution]

Hi Florian,

and this is exactly the point, I was looking for.   This is very nice.

(For the beginning its difficult to understand the whole concept and additionally there are the old and the new interfaces. The different interfaces are confusing me. )

Thanks for your help!

BR,

Dirk

[SergioG_TX · Accepted solution]

what is even better is that the new DB api to load the stored proc could return the output as a JSON object.. if you define an output param as a table (or table type) then on your xsjs you can simply access that objct as response['yourOutPutVariableName']  -- this would be your json object going back therefore no need to create an additional object and you can also iterate thru it as a javascript array. it is very handy

[draschke · Accepted solution]

Hi Sergio,

based on Florians sample, would it look like in that way?

$.response.contentType = "text/json";

$.response["ET_ACTIVE_OBJECT"];

[pfefferf · Accepted solution]

Hi Dirk,

it would be:

$.response.contentType = "text/json";
$.response.setBody(JSON.stringify(result.ET_ACTIVE_OBJECT));

Best Regards,

Florian

[draschke · Accepted solution]

Hi Florian,

I tried your coding at first but I got an error, but now it works.

Cool!

Thank you!

Only one more question.

Usually I'm used to work with the chrome debugger and prove my coding changes directly in the chrome console.

Is there a similar way in eclipse?

[pfefferf · Accepted solution]

You can create a XS JavaScript Debug Configuration in Eclipse to be able to debug your XSJS coding.

I personally prefer the HANA Web-based development workbench which is quite good in the meantime (>= SPS09). With the corresponding rights you can directly debug your coding within the browser using the Web-based development workbench. If you are interested in this please check the first chapter in http://help.sap.com/hana/SAP_HANA_Developer_Guide_for_SAP_HANA_Web_Workbench_en.pdf.

[draschke · Accepted solution]

yes, i tried them both and same to you, I prefer the web-based debugger.

But what I really miss, is that I can during the run time change the coding and check if it valid. This is really a great feature of the chrome console.

But thanks for your hints!