Stored Procedure is not returning response from xsjs

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

firstly thank you for feedback. it was my problem 1 week ago. but I tried your solution. and it is working.

thank you.