Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Dynamic cursor - what's wrong?

    Can anyone tell me why the following procedure gives me at runtime:
    Exception occurred while debugging:
    A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0504N The cursor "C" is not defined. SQLSTATE=34000

    I am trying to input a shema, table, and column, and have the procedure loop thru all records in that column and update those by replacing certain characters with ''. Any tips? Thanks

    CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    BEGIN

    DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
    DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
    DECLARE v_CURR_ROW INTEGER DEFAULT 0;
    DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
    DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
    DECLARE s1 VARCHAR(255) DEFAULT NULL;
    DECLARE s2 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

    DECLARE at_end INT DEFAULT 0;

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE c CURSOR WITH HOLD FOR s1;
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



    SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
    SET v_SQL1 = 'SELECT ' || IN_COLUMN || ' FROM ' || v_IN_SCHEMA_IN_TABLE || ' FOR UPDATE';
    SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR( ' || CHAR(v_CURR_ASCII)
    || ' ),'''') WHERE CURRENT OF c';

    PREPARE s1 FROM v_SQL1;
    WHILE (v_CURR_ASCII < 266) DO


    OPEN c;

    WHILE(at_end = 0) DO
    FETCH c INTO v_CURR_PHONE_NUM;
    PREPARE s2 FROM v_SQL2;
    EXECUTE s2;
    COMMIT;
    SET v_CURR_ROW = v_CURR_ROW + 1;
    END WHILE;
    CLOSE c;
    SET v_CURR_ASCII = v_CURR_ASCII + 1;
    SET v_ROW_COUNT = 0;
    SET at_end = 0;
    END WHILE;
    CLOSE c;
    END

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you getting the error when you build or run the SP? Also, please state which OS and db2 Version.

    Andy

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    Error is at runtime. Proc builds with no problems. DB2 version 8.1 FP3 and O/S is AIX. Just so you don't have to dig thru the code, I am trying to execute a dynamic update statement within a dynamic cursor.

    Thanks

  4. #4
    Join Date
    Mar 2004
    Posts
    16
    By the way when I debug, the error occurs at this line:

    PREPARE s2 FROM v_SQL2;

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The code looks OK to me, but then again I have not tried doing what you are.
    In looking at the code, you will update every row of the table anyway, so why use the cursor.

    CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    BEGIN

    DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
    DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
    DECLARE v_CURR_ROW INTEGER DEFAULT 0;
    DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
    DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
    DECLARE s1 VARCHAR(255) DEFAULT NULL;
    DECLARE s2 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

    DECLARE at_end INT DEFAULT 0;

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



    SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
    SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR( ' || CHAR(v_CURR_ASCII)
    || ' ),'''') ';

    WHILE (v_CURR_ASCII < 266) DO

    PREPARE s2 FROM v_SQL2;
    EXECUTE s2;
    COMMIT;
    SET v_CURR_ASCII = v_CURR_ASCII + 1;
    SET v_ROW_COUNT = 0;
    SET at_end = 0;
    END WHILE;
    CLOSE c;
    END

    HTH

    Andy

  6. #6
    Join Date
    Mar 2004
    Posts
    16
    Andy,
    good suggestion, I removed the cursor and now I am just doing blanket updates on the whole table. It now completes successfully, but it still doesn't update the column properly. (It should remove any ascii character 0-254 from that column). When I run the update statement by itself outside the procedure it works fine.

  7. #7
    Join Date
    Mar 2004
    Posts
    16
    To be more specific, the update works fine as a standalone if i hardcode one ascii code instead of getting the variable v_CURR_ASCII and using that.

    Example:
    UPDATE SCHEMANAME.CONTACTS SET PHONENUMBER = REPLACE(PHONENUMBER,CHR(45),'');

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try a variation on what you want:

    CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    BEGIN

    DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
    DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
    DECLARE v_CURR_ROW INTEGER DEFAULT 0;
    DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
    DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
    DECLARE s1 VARCHAR(255) DEFAULT NULL;
    DECLARE s2 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
    DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

    DECLARE at_end INT DEFAULT 0;

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



    SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
    SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR(?),'''') ';

    WHILE (v_CURR_ASCII < 266) DO

    PREPARE s2 FROM v_SQL2;
    EXECUTE s2 using v_CURR_ASCII;
    COMMIT;
    SET v_CURR_ASCII = v_CURR_ASCII + 1;
    SET v_ROW_COUNT = 0;
    SET at_end = 0;
    END WHILE;
    CLOSE c;
    END


    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •