How to return more than one row from HANA database using XS?

Question

I'm trying to retrieve all data from a db table into json object, like so:

function getTableData()
{
    var vals = {};
    var data = [];
    try {
    var dbCon = $.db.getConnection();

    var query = 'SELECT * FROM SAPPRD.ZUSERDATATAB';
    var pstmt = dbCon.prepareStatement(query);
    var rs = {};
    rs = pstmt.executeQuery();

     while (rs.next()) {
        vals.team = rs.getString(1);
        vals.fname  = rs.getString(3);
        vals.lname = rs.getString(2);
        data.push(vals);
        $.response.status = $.net.http.OK;
     }

 $.response.setBody(JSON.stringify(data));
  //      $.response.contentType = contentType;
  //      $.response.headers.set('Content-Disposition', 'filename=' + filename);
} catch (e) {
    $.response.setBody('errors: ' + e.message);
}
}

The query works only partially, because in data I get number of rows x last row content, like so:

[{"team":"I313766","fname":"0","lname":"LEGOWSKI"},  
 {"team":"I313766","fname":"0","lname":"LEGOWSKI"},
  etc. etc.]

How would I make it retrieve all the data instead of one row number of times?

Answer 1

Okay, I got the solution. Moving a single line declaring array vals into the while statement solved the problem - the array vals was initialized as an empty array each time, therefore allowing the proper .push of each row, instead of pushing last row from db table into data multiple times. Thanks to everybody who took time and tried answering.

function getTableData()
{

     var data = [];
    try {
        var dbCon = $.db.getConnection();

        var query = 'SELECT * FROM SAPPRD.ZUSERDATATAB';
        var pstmt = dbCon.prepareStatement(query);
        var rs = pstmt.executeQuery();

         while (rs.next()) {
            var vals = {}; // this is the moved line of code...
            vals.team = rs.getString(1);
            vals.fname  = rs.getString(3);
            vals.lname = rs.getString(2);
            data.push(vals);
            $.response.status = $.net.http.OK;
         }

 $.response.setBody(JSON.stringify(data));
  //      $.response.contentType = contentType;
  //      $.response.headers.set('Content-Disposition', 'filename=' + filename);

    } catch (e) {
        $.response.setBody('errors: ' + e.message);
    }
}

solution above just in case someone needs it in future.

Answer 2

This is XSJS(server side JS) and not SAPUI5. The read of DB is pretty similar to the JDBC framework in Java to read DB tables and the result set collection will have the data and you iterate over them and move them to a local object. There is only call to the DB during execute_query and rs.next() is just a loop to read each row.