Skip to Content

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

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);
}
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 07, 2016 at 08:52 PM

    @florian.pfeffer may be you can help me .

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2016 at 10:35 PM

    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

    Add comment
    10|10000 characters needed characters exceeded