All Forums Database
gpolanch 46 posts Joined 12/11
21 Feb 2012
Error SPL1105 when using Dynamic Cursor and FOR UPDATE

Hello,  I need to read a relatively small lookup table and parse a varchar column that contains data that looks like '12,345,6789', then update 3 columns in the SAME record with each of the parsed (token) values.  (For brevity, the example below shows updating only one column)    I tried using POSITION and SUBSTRING in a non-cursor solution, but it got very ugly when trying to parse the second and third tokens.  The procedure below works when doing a hardcoded DECLARE with no PREPARE.  But when I try to declare the cursor dynamically, and prepare, I get error SPL1105 - Cursor with name 'cstmt' is not updatable.  I am running in ANSI transaction mode.   I searched the forum but could not find this particular situation.  Assuming I get past this error, I dont know how to run the UPDATE WHERE CURRENT OF dynamically.  Thanks for your help! 

CREATE PROCEDURE TEST_CURSOR_DYN( IN in_table VARCHAR(10) )
BEGIN
  -- these must be declared before the cursor
  DECLARE t_modstring VARCHAR(254);
  DECLARE t_mod_id VARCHAR(20);
  DECLARE t_com_pos INTEGER;
 
  DECLARE sql_stmt1 VARCHAR(500);
 
  /* DYN - declare cursor with dynamic form, see p43 */
  DECLARE cstmt CURSOR FOR stmt1;
  SET sql_stmt1 =
  ' SELECT EM_CODE_MOD_ID ' ||
  ' FROM ' || in_table || ' WHERE EM_CODE_MOD_ID IS NOT NULL ' ||
  ' FOR UPDATE';
  PREPARE stmt1 FROM sql_stmt1;
 
  OPEN cstmt;
  Label1:
  LOOP
   FETCH cstmt INTO t_modstring;
   IF (SQLSTATE = '02000') THEN
   LEAVE Label1;
   END IF;

   -- UPDATE MOD_1
   SET t_com_pos = POSITION(',' IN t_modstring); 
   IF t_com_pos = 0 OR t_com_pos IS NULL THEN
    SET t_mod_id = t_modstring;
    SET t_modstring = NULL;
   ELSE
    SET t_mod_id = SUBSTRING(t_modstring FROM 1 FOR t_com_pos-1);
    SET t_modstring = SUBSTRING(t_modstring FROM t_com_pos+1);
   END IF;
   UPDATE TEST_ADDL_EM_CODE
   SET MODIFIER1_ID = t_mod_id WHERE CURRENT OF cstmt;

  -- UPDATE MOD_2

....

  -- UPDATE MOD-3

  END LOOP Label1;

  CLOSE cstmt;
  COMMIT WORK;

END;

You must sign in to leave a comment.