Skip to Content
2
Former Member
Dec 24, 2014 at 02:17 PM

Stored Procedure is not returning response from xsjs

318 Views

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