XSJS getting result from stored procedure in a variable

[former_member290321]

I have a situation where I pass a session user as a parameter to stored procedure and get a receiver value which I want to get in a variable so I can pass that to other function in xsjs file to send email.

I am getting sender from session user and doing concat to get sender email.

I am calling ReadAuditUser stored procedure will see if the appuser(session user) is valid or not

- if ReadAuditUser stored procedure finds appuser as a valid user then stored procedure will return a single record which will return receiver,

- if ReadAuditUser stored procedure finds appuser as a invalid user then the stored procedure will return 'null'.

I want to capture that receiver from getReceivername(), like I have capture appuser from getUsername()function.

Please let me know where I am going wrong.

function getUsername() {
    var username = $.session.getUsername();
    return username;
}
var appuser = getUsername();
var str2 = "@abc.com";
var sender_email = appuser.concat(str2);


function getReceivername() {
    var xreceiver = "";
    var conn = $.db.getConnection();
    var query = 'call \"AA\".\"PROCEDURE::ReadAuditUser\"(?)';
    var pstmt = conn.prepareCall(query);
    pstmt.setString(1, appuser);
    pstmt.execute();
    var rs = pstmt.getResultSet();
    if (rs.next()) {
        if (rs.getString(1) === 'null') {
            xreceiver = rs.getString(1);
        } else {
            xreceiver = rs.getString(1);
        }
    }
}
rs.close();
pstmt.close();
conn.close();

var receiver = getReceivername();
var str1 = "@abc.com";
var receiver_email = receiver.concat(str1);

[former_member290321 · Accepted solution]

Need to add return xreceiver; and it works

function getUsername() {
    var username = $.session.getUsername();
    return username;
}
var appuser = getUsername();
var str2 = "@abc.com";
var sender_email = appuser.concat(str2);


function getReceivername() {
    var xreceiver = "";
    var conn = $.db.getConnection();
    var query = 'call \"AA\".\"PROCEDURE::ReadAuditUser\"(?)';
    var pstmt = conn.prepareCall(query);
    pstmt.setString(1, appuser);
    pstmt.execute();
    var rs = pstmt.getResultSet();
    if (rs.next()) {
        if (rs.getString(1) === 'null') {
            xreceiver = rs.getString(1);
        } else {
            xreceiver = rs.getString(1);
        }
    }
 return xreceiver; /* have to add this code line and it works*/
}
rs.close();
pstmt.close();
conn.close();

var receiver = getReceivername();
var str1 = "@abc.com";
var receiver_email = receiver.concat(str1);