Skip to Content
1

What would be the URL for XSJS calling with Multiple parameter ?

Nov 05, 2016 at 01:29 AM

792

avatar image

Hi Folks

I am trying to run a query against calculation view ( consumed via XSJS) and trying to pass multiple customers in where clause . What should be my URL ?

URL with one customer returns data but unable to generate URL for multiple customers.

https://server:port/UI5_APP_BW1/WebContent/test.xsjs?cmd=select&customer%5b%5d=C20001

Here is my Code:

function getDataFromTable(){
    var cust = $.request.parameters.get('customer[]');
    var conn = $.db.getConnection();
    var pstmt;
    var rs;
    var query;
    var output = {results: [] };
    try {
        query = 'SELECT CUST,MAT,NDC, SUM(\"Qty\") AS QTY FROM \"_SYS_BIC\".\"CALC_VIEW\" WHERE CUST IN(?)' +
        	    'GROUP BY CUST,MAT,NDC ';
        pstmt = conn.prepareStatement(query);
      pstmt.setString(1, cust);
        rs = pstmt.executeQuery();

        while (rs.next()) {
               var record = {};
                record.CUST = rs.getString(1);
                record.MAT = rs.getString(2);
                record.NDC = rs.getString(3);
                record.QTY = rs.getString(4);
                output.results.push(record);
        }
        rs.close();
        pstmt.close();
        conn.close();
                } 
    catch (e) {
        $.response.status = $.net.http.INTERNAL_SERVER_ERROR;
        $.response.setBody(e.message);
        return;
    }
    var body = JSON.stringify(output);
    $.response.contentType = 'application/json';
    $.response.setBody(body);
    $.response.status = $.net.http.OK;
}

var aCmd = $.request.parameters.get('cmd');
switch (aCmd) {
    case "select":
        getDataFromTable();
        break;
    default:
        $.response.status = $.net.http.INTERNAL_SERVER_ERROR;
        $.response.setBody('Invalid Command: ', aCmd);
}
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Anindya Bose Nov 07, 2016 at 08:52 PM
0

@florian.pfeffer may be you can help me .

Share
10 |10000 characters needed characters left characters exceeded
Florian Pfeffer
Nov 07, 2016 at 10:35 PM
0

Hello Anindya,

following an example which does a select on a dummy table from me. Basically it is the same than you do with the with the calc. view.

If several times the same parameter "id" is in the query string, the $.request.parameters.get function returns an array with the values. For each value a placeholder is dynamically added to the selection string. To be able to use that, the function prototype function apply is called for function executeQuery of the new $.hdb interface. With than an arguments array can be passed. The first array element is the select statement, the further array elements are the values compared to the ID column in my case.

let paramIDs = $.request.parameters.get('id');

let conn = $.hdb.getConnection();

let inQMarks = '';

for (let i = 0; i < paramIDs.length; i++) {
	inQMarks += '?';
	if (i < paramIDs.length - 1) {
		inQMarks += ', ';
	}
}

let selArgs = ['SELECT * FROM "MISC"."MISC_NVARCHAR" WHERE ID in (' + inQMarks + ')'].concat(paramIDs);


try {
	let result = conn.executeQuery.apply(conn, selArgs);
	$.response.contentType='application/json';
	$.response.setBody(JSON.stringify(result));
} catch (e) {
        $.response.contentType='application/json';
	$.response.setBody(JSON.stringify(e));
}

Calling that service like that ...

.../multiValueParam.xsjs?id=1&id=2&id=3

... produces following response for my dummy table:

Best Regards,
Florian


tmp01.jpg (15.3 kB)
Share
10 |10000 characters needed characters left characters exceeded