How to call HANA store procedure with table variables in xsjs?

[Former Member]

Hi,

I am developing a web application using HANA XS.I have create stored procedure to insert and update data into DB and send back to front end.

My requirement is, i have to insert/update multiple records at a time into HANA DB.

Hence,my stored procedure is having  1 input parameter of table variable and another output parameter of table variable.

How to call thise type of procedures in HANA XSJS?

Please help me.

Thanks.

[thomas_jung · Accepted solution]

You should use temporary tables.  From XSJS write your data you want to pass into the procedure into the temporary table.  Then in the CALL statement for the procedure use the temporary table as the input parameter you pass you.

[Former Member · Accepted solution]

Thanks for your reply.

I dont have very good exposure at javascript.Could you please provide me the sample of code how to declare temporary table in XSJS and call store procedure with input and output of tablevariables.

Is there any development guide available for XSJS coding?

Thanks.

[thomas_jung · Accepted solution]

>Could you please provide me the sample of code how to declare temporary table in XSJS and call store procedure with input and output of tablevariables.

Actually there isn't really much JavaScript to this.  Its all done via SQL Statements. Here is a sample.  I didn't write it; but an SAP colleague posted it to our internal forums. In this case he is also creating the procedure directly in the catalog. This isn't something  I'd recommend in a real application, but is nice for a self contained example.

1. $.response.contentType = "text/plain"; 
2. var result = ""; 
3.  
4. var conn=$.db.getConnection(); 
5.  
6. function onesql(sql) { 
7.     try { 
8.         var ps = conn.prepareStatement(sql); 
9.         var execrc = ps.execute(); 
10.         ps.close(); 
11.     }catch(e){ 
12.         result += sql + ":\n" + e.toString() + "\n--------\n\n"; 
13.     } 
14. } 
15.  
16. // cleanup 
17. onesql("DROP TYPE tt_test"); 
18. onesql("DROP PROCEDURE example"); 
19.  
20. // create type and procedure 
21. onesql("CREATE TYPE tt_test AS TABLE (foo varchar(100))"); 
22. onesql("CREATE PROCEDURE example(\n" + 
23.        "  IN  in_table_1 tt_test,\n" + 
24.        "  IN  in_table_2 tt_test,\n" + 
25.        "  IN  max_rows int,\n" + 
26.        "  OUT out_table_1 tt_test,\n" + 
27.        "  OUT out_table_2 tt_test,\n" + 
28.        "  OUT total_input_rows int\n" + 
29.        "  ) LANGUAGE SQLSCRIPT READS SQL DATA AS\n" + 
30.        "BEGIN\n" + 
31.        " out_table_1 = SELECT TOP :max_rows * FROM :in_table_1;\n" + 
32.        " out_table_2 = SELECT TOP :max_rows * FROM :in_table_2;\n" + 
33.        " SELECT c1+c2 INTO total_input_rows FROM\n" +  
34.        "   (SELECT COUNT(*) AS c1 FROM :in_table_1),\n" + 
35.        "   (SELECT COUNT(*) AS c2 FROM :in_table_2);\n" + 
36.        "END;"); 
37.  
38.  
39. // create some local input tables for a test call 
40. onesql("CREATE LOCAL TEMPORARY TABLE #local_test_table_1(foo varchar(100))"); 
41. onesql("INSERT INTO #local_test_table_1 VALUES('bla')"); 
42. onesql("INSERT INTO #local_test_table_1 VALUES('blubb')"); 
43.  
44. onesql("CREATE LOCAL TEMPORARY TABLE #local_test_table_2(foo varchar(100))"); 
45. onesql("INSERT INTO #local_test_table_2 VALUES('bla')"); 
46.   
47. // call the procedure with the local data 
48. try{ 
49.     var pc = conn.prepareCall("CALL example(#local_test_table_1, #local_test_table_2, ?, ?, ?, ?)"); 
50.     var pmd = pc.getParameterMetaData(); 
51.     result += "Got " + pmd.getParameterCount()  + " unbound parameters\n"; 
52.     result += "1: " + pmd.getParameterName(1) + "-" + pmd.getParameterTypeName(1) + "\n"; 
53.     result += "2: " + pmd.getParameterName(2) + "-" + pmd.getParameterTypeName(2) + "\n"; 
54.     result += "3: " + pmd.getParameterName(3) + "-" + pmd.getParameterTypeName(3) + "\n"; 
55.     result += "4: " + pmd.getParameterName(4) + "-" + pmd.getParameterTypeName(4) + "\n"; 
56.      
57.     pc.setBigInt(1, 2); 
58.     result += "\n\n---------\n\nResults:\n\n"; 
59.      
60.     if(pc.execute()) { 
61.         result += "total_input_rows: " + pc.getBigInt(4) + "\n"; 
62.          
63.         do { 
64.             var rs = pc.getResultSet(); 
65.             result += "=========\n"; 
66.             result += "ResultSet has: " + rs.getMetaData().getColumnCount() + " column(s)\n"; 
67.             //result += rs.getMetaData().getColumnTypeName(1); 
68.             while(rs.next()) { 
69.                 result += rs.getString(1) + "\n"; 
70.             } 
71.             result += "=========\n\n"; 
72.         } while (pc.getMoreResults());  // get next resultset from stored procedure 
73.     } else { 
74.         result += "Failed to execute procedure"; 
75.     } 
76. } catch (e) { 
77.     result += e.toString(); 
78. } 
79.  
80.  
81. $.response.setBody(result); 

[Former Member · Accepted solution]

Hi Thomas,

As you suggested, i have created a local temporary table and inserted the records. Then, i passed this table as input parameter to procedure.

My code is almost same as above.

I created procedre to create and drop local table.That procedure i am calling in different XSJS.

Thanks.

[fabian_krger · Accepted solution]

Just in case anybody is trying this and getting errors too:

Somehow the default schema in the .xsjs DB Connection is "SYSTEM".

That gave me errors on create of a local temporary table - the user was not authorized.

To solve this I made a query "SELECT CURRENT_USER FROM DUMMY" first and then set the schema to this username manually.

In the user's own schema there is no authorization issue...

[former_member102219 · Accepted solution]

Thanks for sharing the knowledge Thomas! However, I'm not quite sure, though, that I would like to create temporary db tables in a multi-user environment. One question then would be about marking the created tables with some unique user/timestamps, another about "cleaning the garbage".

An OData service cannot be defined directly on a database response (at least, I haven't find anything about that in documentation), however, it's relatively easy translated to JSON, and then you can create a JSON-based model and bind it to SAPUI5 table. I guess something similar might be done for an 'inbound' table parameter, i.e. translate from table to JSON, pass to the stored procedure as a (potentially very long) string and there convert to a table variable. Do you think it's a viable approach?

[thomas_jung · Accepted solution]

>One question then would be about marking the created tables with some unique user/timestamps, another about "cleaning the garbage".

Local temporary tables are session specific.  Therefore no need to create with unique user or timestamps.  They are already isolated. No reason to clean up either as they are destroyed at the end of the session anyway.

Remember HANA is an in-memory database. A temporary local table is really just a memory variable.

[former_member102219 · Accepted solution]

Sounds good, thanks. Is there a way to dynamically define an OData service on top of that temporary table? So far, my understanding is that you have to create an .xsodata file and activate it in the repository, and that's not possible at runtime.

Cheers!
/RB

[thomas_jung · Accepted solution]

>Is there a way to dynamically define an OData service on top of that temporary table?

No.  If you want such a dynamic OData service you should use XSJS.

[former_member102219 · Accepted solution]

Well, that's what I did so far - XSJS gets the database response, builds a JSON string and returns it to the client, where a JSON Model is built and then bound to a Table control.

Is there any more direct approach, like creating a real OData service in the XSJS?

[Former Member · Accepted solution]

Hello Thomas,

I have an xsjs calling a stored procedure with a similar coding than your example. It was working fine some month ago (January 2014). It is now no more working. I get the answer "Failed to execute procedure". Note that I'm using HANA cloud trial. I guess that an upgrade has probably been done in the meantime causing this regression. Do you have any idea of the issue? Do you have some coding example with a better error management that would give additionnal information on the error? Note that when I execute the same SQL Statements via HANA studio SQL console, it is working fine.

Thanks in advance for your help.

Here is the xsjs coding :

var output = "";

var conn = $.db.getConnection();

function onesql(sql) { 
try { 
var ps = conn.prepareStatement(sql); 
var execrc = ps.execute(); 
ps.close(); 
}catch(e){output += sql + ":\n" + e.toString() + "\n--------\n\n"; 
} 
} 

try { 

onesql("DROP PROCEDURE example"); 

onesql("CREATE PROCEDURE example(IN TestLow INTEGER,\n" +
  "IN TestHigh INTEGER,\n" +
  "IN ESSLow INTEGER,\n" +
  "IN ESSHigh INTEGER,\n" +
  "IN EmployeeLow INTEGER,\n" +
  "IN EmployeeHigh INTEGER,\n" +
  "IN LimitedLow INTEGER,\n" +
  "IN LimitedHigh INTEGER,\n" +
  "IN ProfessionalLow INTEGER,\n" +
  "IN ProfessionalHigh INTEGER,\n" +
  "OUT usernumberTEST INTEGER,\n" +
  "OUT usernumberESS INTEGER,\n" +
  "OUT usernumberEMPLOYEE INTEGER,\n" +
  "OUT usernumberLIMIT INTEGER,\n" +
  "OUT usernumberPROF INTEGER\n" +
  ")\n" +
  "LANGUAGE SQLSCRIPT\n" +
  "SQL SECURITY DEFINER\n" +
  "READS SQL DATA AS\n" +
  "BEGIN\n" +
  "usermaxCPU = SELECT USER, MAX(CPU) AS \"CPU\"  FROM \"NEO_DG3K7BUFABMZ9JA70BSF3JEUR\".\"s0009333833trial.myhana.useraudit.db.table::usercpu\" GROUP BY USER;\n" +
  "SELECT count(USER) into usernumberTEST FROM :usermaxCPU WHERE CPU <= :TestHigh;\n" +
  "SELECT count(USER) into usernumberESS FROM :usermaxCPU WHERE CPU >= :ESSLow AND CPU <=:ESSHigh;\n" +
  "SELECT count(USER) into usernumberEMPLOYEE FROM :usermaxCPU WHERE CPU >= :EmployeeLow AND CPU <=:EmployeeHigh;\n" +
  "SELECT count(USER) into usernumberLIMIT FROM :usermaxCPU WHERE CPU >= :LimitedLow AND CPU <=LimitedHigh;\n" +
  "SELECT count(USER) into usernumberPROF FROM :usermaxCPU WHERE CPU >= :ProfessionalLow;\n" +
  "END;"); 

var pc = conn.prepareCall("CALL example(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"); 
var TestLow = parseInt($.request.parameters.get("TestLow"),10);
var TestHigh = parseInt($.request.parameters.get("TestHigh"),10);
pc.setInteger(1, TestLow);
pc.setInteger(2, TestHigh);
var ESSLow = parseInt($.request.parameters.get("ESSLow"),10);
var ESSHigh = parseInt($.request.parameters.get("ESSHigh"),10);
pc.setInteger(3, ESSLow);
pc.setInteger(4, ESSHigh);
var EmployeeLow = parseInt($.request.parameters.get("EmployeeLow"),10);
var EmployeeHigh = parseInt($.request.parameters.get("EmployeeHigh"),10);
pc.setInteger(5, EmployeeLow);
pc.setInteger(6, EmployeeHigh);
var LimitedLow = parseInt($.request.parameters.get("LimitedLow"),10);
var LimitedHigh = parseInt($.request.parameters.get("LimitedHigh"),10);
pc.setInteger(7, LimitedLow);
pc.setInteger(8, LimitedHigh);
var ProfessionalLow = parseInt($.request.parameters.get("ProfessionalLow"),10);
var ProfessionalHigh = parseInt($.request.parameters.get("ProfessionalHigh"),10);
pc.setInteger(9, ProfessionalLow);
pc.setInteger(10, ProfessionalHigh);

if(pc.execute()) { 
var businessData = [];
var data = {};
data.UserType = "Professional";
data.udd = 31367;
data.measure =  pc.getInteger(15);
businessData.push(data);
data = {};
data.UserType = "Limited";
data.udd = 32582;
data.measure =  pc.getInteger(14);
businessData.push(data);
data = {};
data.UserType = "Employee";
data.udd = 106412;
data.measure =  pc.getInteger(13);
businessData.push(data);
data = {};
data.UserType = "ESS";
data.udd = 33630;
data.measure =  pc.getInteger(12);
businessData.push(data);
data = {};
data.UserType = "Test";
data.udd = 0;
data.measure =  pc.getInteger(11);
businessData.push(data);
$.response.contentType = "application/json";
output += JSON.stringify({"businessData": businessData});
} else { 
$.response.contentType = "text/html";
output += "Failed to execute procedure"; 
} 

}catch(e){
$.response.contentType = "text/html";
output += "jddk" + ":\n" + e.toString() + "\n--------\n\n"; 
} 
 
conn.close();
$.response.setBody(output);

[thomas_jung · Accepted solution]

Not aware of any regression that would cause that to stop working.  One that stands out to me is that you don't have a commit between the procedure creation and the calling of it.  You might try adding a commit.

Otherwise I could suggest looking at the trace log to see if there are more details about the failure.

[Former Member · Accepted solution]

Hello Thomas,

Thanks for your help.

Adding a commit didn't solve the issue. In addition, with my hana cloud trial account, I don't have enough privileges to look at the trace log. I finally found a solution to solve my issue by adding an out parameter of type TABLE to my procedure. It seems that, with SP7,  the statement ‘pc.execute()’ is returning false if there is not, at least, one OUT parameter of type TABLE. With SP6, it was returning true even having only OUT parameters of type INTEGER. Do you think that this change is a regression?

[former_member2839 · Accepted solution]

Hi,

pc.setInteger(10, ProfessionalHigh);
if(pc.execute()) {  //<----- Avoid this a wrap in side try - catch
var businessData = [];

You may not want to check the return value of pc.execute() if you know what the procedure returns already. If the execution fails, the pc.execute() is expected to throw an exception.

Regards,

Dinu

[Former Member · Accepted solution]

Hi,

I have replaced the if statement by a try-catch and it is now working even if I remove my dummy OUT table parameter. Thank you very much.

Regards,

Michaël

[former_member186768 · Accepted solution]

Hi Fabian,

Even i'm facing the same issue when i try to create temporary table.

Can you please tell how to set the schema to the username manually like you mentioned in your

above post.

If you can share some piece of code it will be helpful.

Thanks and regards,

Arjun

[fabian_krger · Accepted solution]

Hi Arjun,

somehow I can't find this code any more.

But if you have the username in

var user = "ARJUN"; (you might want to read this with the SQL given, but I don't have the code available now)

then you would do

var conn = $.db.getConnection();

conn.prepareStatement('SET SCHEMA "' + user + '"').execute(); 

[former_member186768 · Accepted solution]

Hi Fabian,

It didn't work.

Basically i'm creating a local table type and inserting one entry in the same table.

Now whenever i try to execute my service, its giving an error :

invalid table name:  Could not find table/view #LOCAL_TEST_TABLE in schema " user schema ".

What should be the issue??

[former_member194780 · Accepted solution]

Hi Arjun,

Try giving the full default _SYS_BIC path for your defined Table type in PROCEDURE OUT parameter

if you could share a sample code it would be better to check and tell And are your on Studio 1.7 ?

-Avinash

[shoban12 · Accepted solution]

Hi Thomas,

Can you please give me some idea, how do we create a temporary table through java/typescript/any programming?

I am also struck while passing parameter as input table.

Thanks,

Shoban.

[rama_shankar3]

Thanks Thomas!

[cphmedard]

HANA Release 73.

I have one xsjs code calling a stored procedure with overview -- works fine (The one that started working since release 70 in the thread above)

I have another xsjs code calling another stored procedure with overview --causes error

The logic works fine in SQL Editor in HANA Studio :

set schema pax3;

delete from tbl_trips;

call generate_trips_no_buckets(25,6,3,"TBL_TRIPS") with overview;

select count(*) from tbl_trips;

Statement 'select count(*) from tbl_trips'

successfully executed in 283 ms 380 µs  (server processing time: 44 ms 999 µs)

Duration of 4 statements: 11.274 seconds

Fetched 1 row(s) in 0 ms 10 µs (server processing time: 0 ms 0 µs)

Where may i submit this bug to XS Team ?

for reference, the trace files and xsjs code (the one that works and the one that causes error) are found in the following link where i also I submitted the bug :

https://community.wdf.sap.corp/message/482336#482336

Kind Regards,

Claude Ph

[Former Member]

Hi Claude ,

I see the same issue where i'm able to execute the same code successfully in Studio where as when i execute it from xsjs , i get error as "

Found the following errors:

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

InternalError: dberror(CallableStatement.execute): 2048 - column store error: search table error: [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_546AB1560E7E19B3E10000007F000002 (-1)" into table ""ASINGH12"."CNMR_APRIORI_RESULT_TBL1"" at ptime/session/eapi/jdbc/ExternalStatement.cc:927 (line 45 position 0 in /CentralRepo/HMKRecommends1/Apriori.xsjs)

Cannot insert data from temporary table into Result table.

Please advise , if you have come across this issue

Thanks & Regards,

Anup Singh

[Former Member]

Hi Anup

I am also facing same issue, so just want to know were you able to resolve this issue?

Thanks

Deepak