Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Stored Procedure is not returning response from xsjs

Hi all,

I'm getting some problem during calling the stored procedure from xsjs.

When I call the procedure from the SQL Console it is working well. ( CALL LOGINUSER (2 , 'b@b. com',  'pass',?,?,?) )

However, when I try to call it from xsjs Server is not returning me any response. I found that something is blocking the operation.

In addition, it is always in running state and when I call it from xsjs again, first one is blocked.

Status of the blocked thread.

Status: BLOCKED

Host: **

Port: 30107

Service: xsengine

Hierarchy: 300514/-1/9

Connection ID: 300514

Thread ID: 16015

Calling: 15888@**

Caller:

Thread Type: XS

Thread Method: running

Thread Detail: /WEB_T**

Duration (ms): 1008600

User:

Application User:

CPU Time: 0

Cumulative CPU Time: 0

Transaction ID: 9

Update Transaction ID: -1

Thread Status: Network Read

Connection Transaction ID: 9

Connection Start Time: 2014-12-24 15:23:31.128757

Connection Idle Time (ms): 0

Connection Status: IDLE

Client Host:

Client IP:

Client PID: 0

Connection Type: Local

Own Connection: FALSE

Memory Size per Connection: 13472

Auto Commit: FALSE

Last Action:

Current Statement ID:

Current Operator Name:

Fetched Record Count: 0

Sent Message Size (Bytes): 0

Sent Message Count: 0

Received Message Size (Byte): 0

Received Message Count: 0

Creator Thread ID: 16015

Created By: XS

Is Encrypted: FALSE

Connection End Time: null

Blocked Update Transaction ID: 2338394

Blocking Transaction ID: 28

Thread ID of Lock Owner: 0

Blocking Update Transaction ID: 2338335

Transactional Lock Type: RECORD_LOCK

Transactional Lock Mode: EXCLUSIVE

Lock Wait Component: Other

Lock Wait Name:

Timestamp of Blocked Transaction: 2014-12-24 15:23:31.17

Waiting Record ID: OID=0x000001e0, PARTID=0x0, OFFSET=0x130

Waiting Table Name: USERS

Waiting Object Name: USERS

Waiting Object Type: TABLE

Waiting Schema Name: SCHEMA_T**

After this block happens I'm also not able to call the procedure from SQL Console. It is also not returning me any response.

Also I get this error from the xsengine_alert:

[5700]{-1}[-1/-1] 2014-12-24 00:34:30.764340 e xsa:WEB_T** WEB_T**(00041) : InternalError: dberror(CallableStatement.execute): 131 - transaction rolled back by lock wait timeout:  [131] "LOGINUSER": line 44 col 2 (at pos 1198): [131] (range 3): transaction rolled back by lock wait timeout:  [131] "INSERTLOG": line 10 col 2 (at pos 207): [131] (range 3): transaction rolled back by lock wait timeout: TrexUpdate failed on table 'LOG' with error: transaction rolled back by lock wait timeout: Lock timeout occurs while waiting RECORD_LOCK of mode EXCLUSIVE(TRANSACTION_ID=17, UPDATE_TRANSACTION_ID=2321742), rc=4628 at ptime/session/eapi/jdbc/ExternalStatement.cc:916

The problem may be occured because of the code which I written but I couldn't find it because the system is blocking it every time.

LOGINUSER (Stored Procedure)

CREATE PROCEDURE "LOGINUSER" (

IN email NVARCHAR(80),

IN pass NVARCHAR(50),

OUT userid INTEGER,

OUT status NVARCHAR(20),

OUT statusmsg NVARCHAR(500)

)

language SQLSCRIPT AS

/********* Begin Procedure Script ************/

BEGIN

DECLARE uid INTEGER;

/********* BEGIN ERROR HANDLING AND ROLLBACK *******/

DECLARE var_commit  VARCHAR(100) := 'COMMIT';

DECLARE var_rollback VARCHAR(100) := 'ROLLBACK' ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

    BEGIN

    userid := ::SQL_ERROR_CODE * -1;

    status := 'ERROR';

    statusmsg := 'SQL Exception occured. Error Code is: ' || ::SQL_ERROR_CODE || '* Error message is: ' || ::SQL_ERROR_MESSAGE || '*';

    EXEC (:var_rollback);

    CALL "INSERTLOG" (status,0,'LOGINUSER',statusmsg);

     END;

/********* END ERROR HANDLING AND ROLLBACK *******/

/********* BEGIN MAIN FUNCTION *******/

--It checks if user exist in the system or not.

--If user not exist function will give an error.

    SELECT "user_id" INTO uid from USERS WHERE "email" = :email and "password" = :pass;

    userid := uid;

    status := 'SUCCESS';

    statusmsg := 'Successfull';

    CALL "INSERTLOG" (status,:uid,'LOGINUSER',:uid || 'is logged in.');

    EXEC (:var_commit);

END;

/********* END MAIN FUNCTION *******/

test.xsjs

$.response.contentType = "text/html";

var retVal = {};

var rs, pc, conn;

try {

    conn = $.db.getConnection("WEB_T**");

    conn.prepareStatement("SET SCHEMA SCHEMA_**").execute();

    pc = conn.prepareCall("CALL LOGINUSER ('b@b. com','pass',?,?,?)");

    var pmd = pc.getParameterMetaData();

    $.trace.info("LOGINUSER CALLED");

    if(pc.execute()){

        rs = pc.getResultSet(); //can me more than one

    

        retVal = [];

        do {

            while (rs.next()) {

                var user = {};

                user.status = rs.getString(2);

                user.message =rs.getString(3);

                retVal.push(user);

            }

    

        } while (pc.getMoreResults());  // get next resultset from stored procedure

    }

    retVal.success = 1;

} catch (e) {

    retVal.success = 0;

    retVal.error = e;

    retVal.errorText = e.message;

    $.trace.error(e);

}

$.response.setBody(JSON.stringify(retVal));

if (rs) {

    rs.close();

}

if (pc) {

    pc.close();

}

if (conn) {

    conn.close();

}

Could you please help me on this topic?

Best Regards,

Kemal

Tags:
Former Member
Former Member replied

Problem solved.

We shouldn't use EXEC (:var_commit) and EXEC (:var_rollback) while calling the procedure from xsjs. They are working fine while we are calling the procedure from SQL Console but if we call them from xsjs in some way they are locking the tables and xsjs is not getting any response from procedure. Removing these lines solved the problem.

2 View this answer in context
Not what you were looking for? View more on this topic or Ask a question