Informix SQL Execute Function Inside FOREACH Cursor

Question

Informix versions 11.70 - 12.10

Trying to create a Stored Procedure to get the Session ID of the user that has locked a table, and then Execute a built-in Function using that Session ID. I don't write too many procedures in Informix.

Getting a syntax error around WITH RESUME.

Any help?

CREATE PROCEDURE sp_kill_lock ()

RETURNING
  INT as User_Session
;

-- declaration of variables
DEFINE rsUser_Session INT;

-- SELECT each User Session that has a lock on a table
SELECT DISTINCT s.sid AS User_Session
FROM sysmaster:syslocks l, sysmaster:syssessions s 
WHERE s.sid = l.owner 
   AND dbsname <> 'sysmaster' 
ORDER BY 1 
INTO TEMP tempSession;

--Cursor
FOREACH 
   SELECT User_Session
   INTO rsUser_Session
   FROM tempSession

RETURN
rsUser_Session;

--kill the session returned
EXECUTE FUNCTION sysadmin:task("onmode","z",rsUser_Session)

WITH RESUME
;

END FOREACH
END PROCEDURE;


 -- Permissions for routine "sp_kill_lock"
 grant execute on function sp_kill_lock to 'public';

I changed the code, and at least I'm not getting a syntax error anymore. Now it says the function is returning too many values:

CREATE PROCEDURE sp_kill_lock ()

RETURNING
  INT as User_Session
;

-- declaration of variables
DEFINE rsUser_Session INT;

-- SELECT each User Session that has a lock on a table
SELECT DISTINCT s.sid AS User_Session
FROM sysmaster:syslocks l, sysmaster:syssessions s 
WHERE s.sid = l.owner 
   AND dbsname <> 'sysmaster' 
ORDER BY 1 
INTO TEMP tempSession;

--Cursor
FOREACH 
   SELECT User_Session
   INTO rsUser_Session
   FROM tempSession

--removed this RETURN
--RETURN
--rsUser_Session;

--kill the session returned
EXECUTE FUNCTION sysadmin:admin("onmode","z",rsUser_Session);

--removed the WITH RESUME

END FOREACH
END PROCEDURE;

For testing a locked table:

BEGIN WORK;
LOCK TABLE mytable IN EXCLUSIVE MODE

Answer 1

WITH RESUME is part of the RETURN statement, so in your first example it should be:

RETURN rsUser_Session WITH RESUME;

But it will still give you an error when executed because PROCEDUREcannot return values. You need to change to it to CREATE FUNCTION.

Then it probably does not do what you want, because it is returning before it executes the onmode -z, so, for example, if there is only 1 session to kill, it will never execute the onmode -z . Plus it is leaving a temp table behind.

So lets rewrite it, to always execute the onmode -z and not use a temp table (I am using Informix 12.10.FC10DE):

CREATE FUNCTION sp_kill_lock ()
RETURNING
    INT AS user_session
    , INT AS admin_result
;
-- declaration of variables
DEFINE rsuser_session INT;
DEFINE rsadmin_result INT;

-- SELECT each User Session that has a lock on a table
FOREACH 
    SELECT DISTINCT 
        s.sid AS user_session
    INTO
        rsuser_session
    FROM 
        sysmaster:syslocks AS l
        INNER JOIN 
        sysmaster:syssessions AS s 
        ON
            s.sid = l.owner
    WHERE  
        dbsname <> 'sysmaster' 
    ORDER BY 1 

   LET rsadmin_result = sysadmin:admin('onmode', 'z', rsuser_session);
   -- returning session id and admin execute result which is the value of cmd_number in the table sysadmin:command_history
   RETURN rsuser_session, rsadmin_result WITH RESUME;

END FOREACH
END FUNCTION;

1. Informix SPL statements
2. Informix Relationship Between Routines, Functions, and Procedures