cancel
Showing results for 
Search instead for 
Did you mean: 

Calling Procedure from XSJS

shahid
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi All,

I have two questions.

1. How to write a where condition in the below case

function test(){

var body = '';

   var val1 = $.request.parameters.get('oVal1');

             try {

                    var query =

    'select \"A\", \"B\"

                              + 'FROM \"amohas97.session.data::sdyn\" + where \"A\" =



var conn = $.db.getConnection();


var pstmt = conn.prepareStatement(query);


var rs = pstmt.executeQuery();

without the where condition my query works fine and it pulls/display all the records.

I am not sure how to write clause using my variable val1 in this case.

2. I have created a get_data.procedure. Now i want to use this .procedure in my .XSJS service. I want to use val1 as my importing parameter.

Any Alternatives/Suggestions.

Thanks for comments in advance.

Message was edited by: Shahid Mohammed Syed

Accepted Solutions (1)

Accepted Solutions (1)

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Have you had a look in the Developers' Guide? 

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

There are examples of this in Section 8.  You should use the prepared statement and set the value of your where condition in it.  Here is an example:


var conn = $.db.getConnection();

var pstmt;

var rs;

var query = 'SELECT "ROLE_NAME", "ROLE_ID", "ROLE_MODE", "GLOBAL_IDENTITY", "CREATOR", "CREATE_TIME" FROM "SYS"."ROLES" WHERE "CREATOR" = ? ORDER BY "ROLE_NAME" ';

pstmt = conn.prepareStatement(query);

pstmt.setString(1, '_SYS_REPO');

rs = pstmt.executeQuery();

Calling a procedure isn't much different:


var conn = $.db.getConnection();

var query3 = 'CALL "workshop.admin.procedures::CREATE_USERS_XS"(?, ?)';



var cst = conn.prepareCall(query3);




cst.setString(1, userId);




cst.setString(2, userJson.roles[x].role_name);




cst.execute();
shahid
Product and Topic Expert
Product and Topic Expert
0 Kudos

Thomas, Appreciate your Assistance.

Will go thru the "Dev Guide" and implement the above case.

Also Can you please share your thoughts on the other post.

Can we bind oModel to a layout? i was trying to read the values from the model rather than binding.

http://scn.sap.com/thread/3388466

Former Member
0 Kudos

Hi Thomas, I tried calling hana procedure with overview using the same callable statement syntax in XSJS but it didn't executed......Can u please help me...by replying here with sample

Answers (2)

Answers (2)

Former Member
0 Kudos

Just like Mohan Kumar above

my st.setString does not work at the moment

my code

var output = {}; 

output.data = []; 

var conn = $.db.getConnection(); 

conn.prepareStatement("SET SCHEMA \"SYSTEM\"").execute(); 

var st = "INSERT INTO \"PEOPLE\" values(?,?,?,?,?,?,?,?)"; 

  st.setString(1,idResult); 

  st.setString(2,NAME);

  st.setString(3,ADDRESS); 

  st.setString(4,FRIENDSLEVEL);

  st.setString(5,VALUE); 

  st.setString(6,COLABORATE);

  st.setString(7,DATE);

  st.setString(8,CONTACT_TYPE);

st.execute(); 

Error Msg

Found the following errors:

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

TypeError: st.setString is not a function (line 74 position 0 in /TFOLDER/insert.xsjs)

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

That's because st is just a sting with the SQL statement in it. You need to use setString on a prepared statement object. This is an Update example but basically the same as insert for the general demonstration purpose.

query = "UPDATE \"sap.hana.democontent.epmNext.data::EPM.Purchase.Header\" set LIFECYCLESTATUS = 'X' where PURCHASEORDERID = ?";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, purchaseOrderID);
var iRows = pstmt.executeUpdate();
pstmt.close();
conn.commit();
Former Member
0 Kudos

OK, Thanks Thomas, I just checked it out again and it works just as expected

shahid
Product and Topic Expert
Product and Topic Expert
0 Kudos

Any thoughts?