 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / June 2007DB2 trigger failing while calling stored procedure
I have the following trigger that calls a DB2 stored procedure: DROP TRIGGER GGWU.TRI_A_MULTI_PROP@ CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON GGWU.MULTIPLIER_PROPERTY REFERENCING NEW AS POST FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL GGWU.PKG_MULT_PROP_INSERT(POST.C_PROPERTY_CODE,POST.I_MULTIPLIER_ID); END@ When I insert a row into the GGWU.MULTIPLIER_PROPERTY table I get the following error: SQL30020N Execution of the command or SQL statement failed because of a syntax error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements: Reason Code "0x124C"("011D")"". SQLSTATE=58009 And then disconnects me from the database. However if I drop the trigger, insert the row, then call the stored proc with the same data, it functions normally. Any ideas? I can put the body of the stored procedure into the trigger if necessary (this is the only place the stored proc is called) however it gives me the error that the create cursor statements are invalid. Below is the stored procedure. Here is the stored procedure: CREATE PROCEDURE GGWU.PKG_MULT_PROP_INSERT( IN p_propertyCode CHAR(3), IN p_multiplierId BIGINT ) LANGUAGE SQL MODIFIES SQL DATA P1: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000'; DECLARE v_notFound INT DEFAULT 0; DECLARE v_promotionId BIGINT; DECLARE v_gameType CHAR(1); DECLARE v_creditType CHAR(1); DECLARE v_qualifyType CHAR(1); DECLARE v_factor DECIMAL(8,2); DECLARE v_multiplierDatesId BIGINT; DECLARE v_startDate DATE; DECLARE v_endDate DATE; DECLARE v_monday SMALLINT; DECLARE v_tuesday SMALLINT; DECLARE v_wednesday SMALLINT; DECLARE v_thursday SMALLINT; DECLARE v_friday SMALLINT; DECLARE v_saturday SMALLINT; DECLARE v_sunday SMALLINT; DECLARE v_success SMALLINT; DECLARE v_sqlState CHAR(5); DECLARE v_createRecord SMALLINT; DECLARE v_currentDate DATE; DECLARE v_exceptions SMALLINT; DECLARE v_sql VARCHAR(500); DECLARE v_temp CHAR(5); DECLARE curDates CURSOR WITH HOLD FOR SELECT I_MULTIPLIER_DATES_ID, D_START, D_END, B_MONDAY, B_TUESDAY, B_WEDNESDAY, B_THURSDAY, B_FRIDAY, B_SATURDAY, B_SUNDAY FROM GGWU.MULTIPLIER_DATES WHERE I_MULTIPLIER_ID = p_multiplierId FOR READ ONLY WITH UR; DECLARE curException CURSOR WITH HOLD FOR SELECT I_MULTIPLIER_DATES_ID, D_EXCEPTION FROM GGWU.MULTIPLIER_DATES_EXCEPTION WHERE I_MULTIPLIER_ID = p_multiplierId FOR READ ONLY WITH UR; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; DECLARE CONTINUE HANDLER FOR NOT_FOUND SET v_notFound = 1; SELECT M.I_PROMOTION_ID, M.C_GAME_TYPE, M.C_CREDIT_TYPE, M.C_QUALIFY_TYPE, M.F_FACTOR INTO v_promotionId, v_gameType, v_creditType, v_qualifyType, v_factor FROM GGWU.MULTIPLIER M WHERE M.I_MULTIPLIER_ID = p_multiplierId WITH UR; OPEN curDates; FETCH curDates INTO v_multiplierDatesId, v_startDate, v_endDate, v_monday, v_tuesday, v_wednesday, v_thursday, v_friday, v_saturday, v_sunday; WHILE (v_notFound = 0) DO IF (v_startDate = v_endDate) THEN CALL GGWU.PKG_INS_MULT_CHILD( v_multiplierDatesId, p_multiplierId, v_promotionId, p_propertyCode, v_startDate, v_factor, v_qualifyType, v_gameType, v_creditType, v_success, v_sqlState ); ELSE SET v_currentDate = v_startDate; WHILE (v_currentDate <= v_endDate) DO SET v_createRecord = CASE DAYOFWEEK(v_currentDate) WHEN 1 THEN v_sunday WHEN 2 THEN v_monday WHEN 3 THEN v_tuesday WHEN 4 THEN v_wednesday WHEN 4 THEN v_thursday WHEN 5 THEN v_friday WHEN 6 THEN v_saturday WHEN 7 THEN v_sunday ELSE 0 END; IF (v_createRecord = 1) THEN CALL GGWU.PKG_INS_MULT_CHILD( v_multiplierDatesId, p_multiplierId, v_promotionId, p_propertyCode, v_currentDate, v_factor, v_qualifyType, v_gameType, v_creditType, v_success, v_sqlState ); END IF; SET v_currentDate = v_currentDate + 1 DAY; END WHILE; END IF; FETCH curDates INTO v_multiplierDatesId, v_startDate, v_endDate, v_monday, v_tuesday, v_wednesday, v_thursday, v_friday, v_saturday, v_sunday; END WHILE; CLOSE curDates; SET v_notFound = 0; OPEN curException; FETCH curException INTO v_multiplierDatesId, v_currentDate; WHILE (v_notFound = 0) DO DELETE FROM GGWU.MULTIPLIER_CHILD WHERE I_MULTIPLIER_DATES_ID = v_multiplierDatesId AND D_CASINO_DATE = v_currentDate; FETCH curException INTO v_multiplierDatesId, v_currentDate; END WHILE; CLOSE curException; COMMIT; END P1 Bizarre, I recommend a PMR. Maybe a bug. Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Did you use federated tables in the procedure called from trigger(GGWU.PKG_MULT_PROP_INSERT) or another procedure called by the procedure(GGWU.PKG_INS_MULT_CHILD)? > Did you use federated tables in the procedure called from > trigger(GGWU.PKG_MULT_PROP_INSERT) or another procedure called by the > procedure(GGWU.PKG_INS_MULT_CHILD)? Here is the body of PKG_INS_MULT_CHILD CREATE PROCEDURE GGWU.PKG_INS_MULT_CHILD ( IN p_promotionId INTEGER, IN p_multiplierId INTEGER, IN p_multDatesId BIGINT, IN p_propertyCode CHARACTER(3), IN p_casinoDate DATE, IN p_factor DECIMAL(8,2), IN p_qualifyType CHARACTER(1), IN p_gameType CHARACTER(2), IN p_creditType CHARACTER(1) , OUT p_success SMALLINT, OUT p_sqlState VARCHAR(5), OUT p_inserted SMALLINT) LANGUAGE SQL MODIFIES SQL DATA ------------------------------------------------------------------------ -- SQL Stored Procedure -- p_promotionId -- p_multiplierId -- p_multDatesId -- p_propertyCode -- p_casinoDate -- p_factor -- p_qualifyType -- p_gameType -- p_creditType ------------------------------------------------------------------------ P1: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000'; DECLARE v_notFound INT DEFAULT 0; DECLARE v_inserted SMALLINT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN DECLARE v_temp CHAR(5); SET v_temp = SQLSTATE; ROLLBACK; SET p_sqlState = v_temp; END; DECLARE CONTINUE HANDLER FOR NOT_FOUND SET v_notFound = 1; SET p_success = 0; SET v_notFound = 0; IF (p_gameType IS NULL) THEN IF (p_creditType IS NULL) THEN INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', 'N' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', 'N' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', 'N' ); SET v_inserted = 6; ELSE INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', p_creditType ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', p_creditType ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE, D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', p_creditType ); SET v_inserted = 3; END IF; ELSE IF (LOCATE('T',p_gameType) > 0) THEN IF (p_creditType IS NULL) THEN INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', 'N' ); SET v_inserted = v_inserted + 2; ELSE INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'T', p_creditType ); SET v_inserted = v_inserted + 1; END IF; END IF; IF (LOCATE('O',p_gameType) > 0) THEN IF (p_creditType IS NULL) THEN INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', 'N' ); SET v_inserted = v_inserted + 2; ELSE INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'O', p_creditType ); SET v_inserted = v_inserted + 1; END IF; END IF; IF (LOCATE('S',p_gameType) > 0) THEN IF (p_creditType IS NULL) THEN INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', 'B' ); INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', 'N' ); SET v_inserted = v_inserted + 2; ELSE INSERT INTO GGWU.MULTIPLIER_CHILD ( I_PROMOTION_ID, I_MULTIPLIER_ID, I_MULTIPLIER_DATES_ID, C_PROPERTY_CODE , D_CASINO_DATE, F_FACTOR, C_QUALIFY_TYPE, C_GAME_TYPE, C_CREDIT_TYPE) VALUES ( p_promotionId, p_multiplierId, p_multDatesId, p_propertyCode, p_casinoDate, p_factor, p_qualifyType, 'S', p_creditType ); SET v_inserted = v_inserted + 1; END IF; END IF; END IF; SET p_inserted = v_inserted; SET p_success = 1; END P1 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.