get record by Id XSJS

Question

I'm new to xsjs, i know to retrieve data from db(HANA db) we need to use prepare statements. I tried using it but any how my .xsjs file is returning me empty JSON. can some one help me? Sharing my code. Below is user table

id | user_name | password | marks
 1 | Prasanna  | test@123 | 10
 2 | sasne     | test@123 | 20
 3 | Ankush    | test@123 | 0
 4 | Shubham   | test@123 | 30
 5 | Anupama   | test@123 | 15

Now i want to write get request for accessing data according to id requested. My XSJS code is as follows

if ($.request.method === $.net.http.GET) {
var conn = $.db.getConnection();
var id = $.request.parameters.get('id');
var statement = 'SELECT * FROM "USER_TABLE" WHERE ID = ?';
$.response.contentType = "application/json";

try {
    var pstmt = conn.prepareStatement(statement);
    pstmt.setString(1, id);
    var result = pstmt.executeQuery();
    $.response.setBody(JSON.stringify(result));

    $.response.status = $.net.http.OK;

} catch (ex) {
    $.response.setBody(ex.toString());
} finally {
    if (conn) {
        conn.close();
    }
}
}

Help will be appriciated

this code is wriiten in `.xsjs` file, i can't add new tag for this — Prasanna, May 11 '18 at 08:01
Have you tried to debug? Your result is of type $.db. ResultSet. By the way, why you use $.db and not $.hdb which is newer? — Rufi, May 16 '18 at 12:57
@Rufi i'm new to this .Please provide me better ans to this in answer section. So that i can accept your ans if it is helpful to me — Prasanna, May 17 '18 at 06:44

score 0 · Answer 1 · answered May 17 '18 at 11:23

This should work, just checked it:

if ($.request.method === $.net.http.GET) {
    const id = $.request.parameters.get('id');
    $.response.contentType = "application/json";

    const conn = $.hdb.getConnection();
    try {
        const resultSet = conn.executeQuery('select * from "user_table" where "id" = ?', id);
        $.response.setBody(JSON.stringify(resultSet));
        $.response.status = $.net.http.OK;
    } catch (ex) {
        $.response.setBody(ex.toString());
    } finally {
        if (conn) {
            conn.close();
        }
    }
}

I think that your problem was that id was not in double quotes ("), this is quite important.

As you can see with hdb connection it becomes easier and hopefully even faster to handle the sql queries, so use this one instead of db - link to hdb documentation.

Moreover, use debugger to investigate the issue (I have heard that there are some issues debugging xsjs files in Eclipse, but I do it without problems in Intellij IDEA). And yes, I would guess that your id is unique thus you could/should get only one row and then stringify only resultSet[0].

get record by Id XSJS

1 Answers1