|
SQLA 16.0.0.1691 I have a working Procedure I have a working function When I execute the Procedure from within a trigger, I get an error "COMMIT/ROLLBACK not allowed within atomic operation". Tracing with the debugger on the trigger proceeds as expected into the Procedure. But it appears when the CLOSE CURSOR executes at the end of that Procedure, I lose any debugger steps and the error appears in my ISQL client. The trigger executes successfully when I comment out the one line which executes the Procedure. The trigger is lengthy, so I have only included the code down to the point where the procedure call is. I have included all the code for the Procedure and Function. Any help with what I might be doing wrong will be greatly appreciated. Bill -----TRIGGER SNIPPET------------------------------------------------------------ ALTER TRIGGER prreg_pay_register_bu BEFORE UPDATE OF prreg_status ON app_owner.prreg_pay_register REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW BEGIN DECLARE ls_done CHAR(1); DECLARE ln_prset_sick_accrual_rate NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate_basic NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate_1 NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate_6 NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate_13 NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate_21 NUMERIC(7, 6); DECLARE ln_prset_vacation_accrual_rate NUMERIC(7, 6); DECLARE ln_employee_vacation_accrual_rate NUMERIC(7, 6); DECLARE li_debit_account integer; DECLARE li_credit_account integer; DECLARE li_syset_co_ei_expense_account integer; DECLARE li_syset_co_cpp_expense_account integer; DECLARE li_syset_ei_payable_account integer; DECLARE li_syset_cpp_payable_account integer; DECLARE li_syset_wages_payable_account integer; DECLARE ls_premp_id varchar(6); DECLARE ls_lst_premp_id varchar(6); DECLARE li_prreg_id integer; DECLARE li_prldc_line_number integer; DECLARE ld_prreg_pay_date date; DECLARE ld_prreg_accounting_date date; DECLARE ldc_prldc_wage_payable_amount decimal; DECLARE ldc_prldc_stat_co_amount decimal; DECLARE ls_gldpt_department varchar(6); DECLARE ls_glprg_program varchar(6); DECLARE ls_glfun_funding_source varchar(6); DECLARE ls_gldtl_detail varchar(6); DECLARE li_glact_account_cr integer; DECLARE li_glact_account_dr integer; DECLARE ls_prlin_type varchar(128); DECLARE ls_prlin_id varchar(10); DECLARE ls_prldc_description varchar(35); DECLARE ls_prlin_ern_absence_type varchar(6); DECLARE ldc_ern_hours decimal; DECLARE ldc_comp_hours decimal; DECLARE ldc_overtime_hours decimal; DECLARE ldc_sick_hours decimal; DECLARE ldc_holiday_hours decimal; DECLARE ldc_bereavement_hours decimal; DECLARE ldc_absence_paid decimal; DECLARE ldc_absence_not_paid decimal; DECLARE ldc_late_paid decimal; DECLARE ldc_late_not_paid decimal; DECLARE li_prabs_line_number integer; DECLARE lui_prar_created_from_id unsigned integer; DECLARE lui_prarpt_created_from_id unsigned integer; DECLARE ls_prar_type varchar(128); DECLARE lui_prar_arsetu_id unsigned integer; DECLARE lui_prarpt_arsetu_id unsigned integer; DECLARE lui_prar_payroll_deduction_id unsigned integer; DECLARE ls_prarpt_automatically_distribute CHAR(1); DECLARE li_arsetu_glact_account INTEGER; DECLARE lbi_hsehld_id BIGINT; DECLARE lbi_htran_id BIGINT; DECLARE li_employee_seniority_days INTEGER; DECLARE li_employee_seniority_years INTEGER; DECLARE ls_accrue_holiday_on_this_entry CHAR(1); DECLARE lui_housing_arsetu_id UNSIGNED INTEGER; DECLARE ldc_accrue_sick_hours DECIMAL(9, 5); DECLARE ldc_accrue_holiday_hours DECIMAL(9, 5); DECLARE cursor_prldc INSENSITIVE CURSOR FOR SELECT prldc_ledger.premp_id, prldc_ledger.prreg_id, prldc_ledger.prldc_line_number, prreg_pay_register.prreg_pay_date, prreg_pay_register.prreg_accounting_date, prldc_ledger.prldc_wage_payable_amount, prldc_ledger.prldc_stat_co_amount, prldc_ledger.prldc_ern_hours, prldc_ledger.gldpt_department, prldc_ledger.glprg_program, prldc_ledger.glfun_funding_source, prldc_ledger.gldtl_detail, prldc_ledger.glact_account_cr, prldc_ledger.glact_account_dr, Upper(prlin_line_item_setup.prlin_type), Upper(prlin_line_item_setup.prlin_id), prldc_ledger.prldc_description, Upper(prlin_line_item_setup.prlin_ern_absence_type), prar_created_from_id, prar_type, prar_emp_acct_rec.arsetu_id, prarpt_created_from_id, prarpt_emp_acct_rec_pay_terms.arsetu_id, prarpt_automatically_distribute, hsehld_id, (SELECT IF EXISTS(SELECT empworkauth_id FROM empworkauth_employee_work_authorization WHERE prldc_ledger.empworkauth_id = empworkauth_employee_work_authorization.empworkauth_id AND empworkauth_vacation_accrued_paid = 'accrued' AND (empworkauth_automatic_hours IS NULL OR empworkauth_automatic_hours = 0)) THEN 'Y' ELSE 'N' ENDIF) AS accrue_holiday_on_this_entry FROM prldc_ledger LEFT OUTER JOIN prar_emp_acct_rec ON prldc_ledger.prar_created_from_id = prar_emp_acct_rec.prar_id LEFT OUTER JOIN prarpt_emp_acct_rec_pay_terms ON prldc_ledger.prarpt_created_from_id = prarpt_emp_acct_rec_pay_terms.prarpt_id, prlin_line_item_setup, prreg_pay_register WHERE prlin_line_item_setup.prlin_id = prldc_ledger.prlin_id AND prldc_ledger.prreg_id = prreg_pay_register.prreg_id AND prldc_ledger.prreg_id = "new".prreg_id ORDER BY prldc_ledger.premp_id asc, prldc_ledger.prldc_line_number asc FOR READ ONLY; IF "new".prreg_status = 'S' AND old.prreg_status <> 'S' THEN //Get Control Codes SELECT syset_co_ei_expense_account, syset_co_cpp_expense_account, syset_ei_payable_account, syset_cpp_payable_account, syset_wages_payable_account INTO li_syset_co_ei_expense_account, li_syset_co_cpp_expense_account, li_syset_ei_payable_account, li_syset_cpp_payable_account, li_syset_wages_payable_account FROM syset_system_setup WHERE syset_id = 0; IF sqlcode <> 0 THEN RAISERROR 18406 '18406- Prreg Trigger Failed read on syset_system_setup.syset_payables_account: ' || STRING(sqlcode) END IF; --RETRIEVE ACCRUAL RATES SELECT TOP 1 prset_sick_accrual_rate, prset_vacation_accrual_rate_basic, prset_vacation_accrual_rate_1, prset_vacation_accrual_rate_6, prset_vacation_accrual_rate_13, prset_vacation_accrual_rate_21 INTO ln_prset_sick_accrual_rate, ln_prset_vacation_accrual_rate_basic, ln_prset_vacation_accrual_rate_1, ln_prset_vacation_accrual_rate_6, ln_prset_vacation_accrual_rate_13, ln_prset_vacation_accrual_rate_21 FROM prset_setup ORDER BY prset_id; IF sqlcode < 0 THEN RAISERROR 18901 '18901 prreg_pay_register_bu prset SQL error ' || STRING(sqlcode); END IF; OPEN cursor_prldc; cursorloop1: LOOP FETCH NEXT cursor_prldc INTO ls_premp_id, li_prreg_id, li_prldc_line_number, ld_prreg_pay_date, ld_prreg_accounting_date, ldc_prldc_wage_payable_amount, ldc_prldc_stat_co_amount, ldc_ern_hours, ls_gldpt_department, ls_glprg_program, ls_glfun_funding_source, ls_gldtl_detail, li_glact_account_cr, li_glact_account_dr, ls_prlin_type, ls_prlin_id, ls_prldc_description, ls_prlin_ern_absence_type, lui_prar_created_from_id, ls_prar_type, lui_prar_arsetu_id, lui_prarpt_created_from_id, lui_prarpt_arsetu_id, ls_prarpt_automatically_distribute, lbi_hsehld_id, ls_accrue_holiday_on_this_entry; IF sqlcode = 100 THEN SET ls_done = 'Y'; END IF; IF sqlcode < 0 THEN RAISERROR 18400 '18400- Prreg Trigger Failed read on prldc cursor: ' || STRING(sqlcode); END IF; IF ls_done IS NOT NULL OR ls_premp_id <> COALESCE(ls_lst_premp_id, '') THEN //WRITE ACCRUAL TOTALS FOR PREVIOUS EMPLOYEE IF ls_lst_premp_id IS NOT NULL THEN IF ldc_accrue_sick_hours <> 0 OR ldc_accrue_holiday_hours <> 0 THEN INSERT INTO prabs_employee_absence (premp_id, prabs_date, prabs_type, prabs_sick_hours, prabs_holiday_hours, prabs_comments) VALUES (ls_lst_premp_id, ld_prreg_pay_date, 'pay-period-accrual-generated', IF ldc_accrue_sick_hours = 0 THEN NULL ELSE ROUND(ldc_accrue_sick_hours, 2) ENDIF, IF ldc_accrue_holiday_hours = 0 THEN NULL ELSE ROUND(ldc_accrue_holiday_hours, 2) ENDIF, 'Accrued from Pay Register ' + STRING(li_prreg_id)); IF sqlcode <> 0 THEN RAISERROR 18900 '18900 - Prreg Trigger Failed insert on prabs_employee_absence: ' || STRING(sqlcode); END IF; END IF; END IF; //HAVE ALL ROWS BEEN PROCESSED? IF ls_done IS NOT NULL THEN LEAVE cursorloop1; END IF; //ZERO ACCRUAL ACCUMULATORS SET ls_lst_premp_id = ls_premp_id; SET ldc_accrue_sick_hours = 0; SET ldc_accrue_holiday_hours = 0; //DETERMINE VACATION ACCRUAL RATE FOR THIS EMPLOYEE BASED ON SENIORITY MESSAGE 'prreg_pay_register_bu trigger processing employee ' || ls_premp_id TO CONSOLE DEBUG ONLY; SELECT return_premp_seniority(ls_premp_id, ld_prreg_pay_date) INTO li_employee_seniority_days; SET li_employee_seniority_years = li_employee_seniority_days / 365; //VACATION ACCRUAL RATES ARE BASED ON WHEN YOU *START* YOUR YEAR, SO IF YOU ARE STARTING YEAR 12 YOU GET THE 13 YEAR RATE CASE WHEN li_employee_seniority_years >= 20 THEN SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_21; WHEN li_employee_seniority_years >= 12 THEN SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_13; WHEN li_employee_seniority_years >= 5 THEN SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_6; ELSE SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_1; END CASE; END IF; code continues but is not included..... -----FUNCTION------------------------------------------------------------------------------------ ALTER FUNCTION app_owner.return_premp_seniority(IN as_premp_id VARCHAR(128), IN ad_as_of_date DATE) RETURNS INTEGER BEGIN DECLARE ld_max_prsenior_date DATE; DECLARE li_total_prsenior_days INTEGER; DECLARE li_days INTEGER; SET li_total_prsenior_days = 0; FOR prsenior AS prsenior_csr NO SCROLL CURSOR FOR SELECT prsenior_action, prsenior_date, prsenior_days FROM prsenior_employee_seniority WHERE premp_id = as_premp_id AND prsenior_date <= ad_as_of_date ORDER BY prsenior_date DO SET ld_max_prsenior_date = prsenior_date; IF prsenior_action = 'new-beginning-balance' THEN SET li_total_prsenior_days = 0; END IF; SET li_total_prsenior_days = li_total_prsenior_days + prsenior_days; END FOR; IF ld_max_prsenior_date IS NULL OR ld_max_prsenior_date < ad_as_of_date THEN SELECT an_days INTO li_days FROM return_premp_seniority_increment(as_premp_id, ad_as_of_date); IF sqlcode < 0 THEN RAISERROR 18330 '18330 return_premp_seniority SQL error ' || STRING(sqlcode); END IF; SET li_total_prsenior_days = li_total_prsenior_days + li_days; END IF; RETURN li_total_prsenior_days; END ` ---PROCEDURE------------------------------------------------------------------ :::SQL ALTER PROCEDURE app_owner.return_premp_seniority_increment(IN as_premp_id VARCHAR(128), IN ad_effective_date DATE) RESULT( an_days INTEGER, as_narrative VARCHAR(32767)) BEGIN DECLARE LD_PAY_HISTORY_START_DATE DATE; DECLARE LD_EMPLOYMENT_STATUS_START_DATE DATE; DECLARE ln_seniority_days INTEGER; DECLARE ls_narrative VARCHAR(32767); DECLARE li_days INTEGER; DECLARE ld_start_date DATE; DECLARE ld_prsenior_date_starting DATE; DECLARE ld_prreg_pay_date DATE; DECLARE ld_prreg_pay_date_ending DATE; DECLARE ld_lst_prreg_pay_date_ending DATE; DECLARE ld_emptype_date_start DATE; DECLARE ld_emptype_date_end DATE; DECLARE ld_empstat_date_start DATE; DECLARE ld_empstat_date_end DATE; DECLARE ls_empstatcode_seniority_ind CHAR(1); DECLARE li_employment_status_done INTEGER; DECLARE li_pay_history_done INTEGER; DECLARE li_paid_days INTEGER; DECLARE li_paid_days_total INTEGER; DECLARE li_not_paid_days INTEGER; DECLARE li_not_paid_days_total INTEGER; DECLARE employment_cursor NO SCROLL CURSOR FOR SELECT emptype_date_start, emptype_date_end, empstat_date_start, IF emptype_date_end IS NOT NULL THEN IF empstat_date_end IS NULL THEN emptype_date_end ELSE IF emptype_date_end < empstat_date_end THEN emptype_date_end ELSE empstat_date_end ENDIF ENDIF ELSE empstat_date_end ENDIF AS empstat_date_end, empstatcode_seniority_ind FROM empstat_employee_status RIGHT OUTER JOIN emptype_employee_type ON empstat_employee_status.emptype_id = emptype_employee_type.emptype_id, empstatcode_employee_status_code RIGHT OUTER JOIN empstat_employee_status ON empstatcode_employee_status_code.empstatcode_code = empstat_employee_status.empstatcode_code WHERE premp_id = as_premp_id ORDER BY emptype_date_start, empstat_employee_status.emptype_id, empstat_date_start, empstat_id FOR READ ONLY; DECLARE pay_history_cursor NO SCROLL CURSOR FOR SELECT DISTINCT prreg_pay_date, CAST(IF prcyc_id = 'M' THEN DATEADD(DAY, -1, (DATEADD(MONTH, 1, prreg_pay_date))) ELSE DATEADD(DAY, 13, prreg_pay_date) ENDIF AS DATE) AS prreg_pay_date_ending FROM prldp_ledger KEY JOIN prreg_pay_register WHERE premp_id = as_premp_id AND prreg_pay_date_ending <= ad_effective_date ORDER BY prreg_pay_date FOR READ ONLY; --SET OPTION debug_messages = 'ON'; SET LD_PAY_HISTORY_START_DATE = DATE('1995-1-1'); SET LD_EMPLOYMENT_STATUS_START_DATE = DATE('2010-7-24'); SET ln_seniority_days = 0; --FETCH FIRST PAY HISTORY ROW FOR POSSIBLE USE IN MULTIPLE PLACES OPEN pay_history_cursor; IF sqlcode <> 0 THEN RAISERROR 18310 '18310 return_premp_seniority_increment, Failed open cursor, sqlcode=' || STRING(Sqlcode); END IF; FETCH NEXT pay_history_cursor INTO ld_prreg_pay_date, ld_prreg_pay_date_ending; IF sqlcode = 100 THEN SET li_pay_history_done = 1; END IF; IF sqlcode < 0 THEN RAISERROR 18315 '18315 return_premp_seniority_increment, sql error ' || STRING(sqlcode); END IF; --FETCH FIRST EMPLOYMENT HISTORY ROW FOR POSSIBLE USE IN MULTIPLE PLACES OPEN employment_cursor; IF sqlcode <> 0 THEN RAISERROR 18330 '18330 return_premp_seniority_increment, Failed open cursor, sqlcode=' || STRING(Sqlcode); END IF; FETCH NEXT employment_cursor INTO ld_emptype_date_start, ld_emptype_date_end, ld_empstat_date_start, ld_empstat_date_end, ls_empstatcode_seniority_ind; IF sqlcode = 100 THEN SET li_employment_status_done = 1; END IF; IF sqlcode < 0 THEN RAISERROR 18335 '18335 return_premp_seniority_increment, sql error ' || STRING(sqlcode); END IF; --STEP 1: DETERMINE STARTING DATE FOR CALCULATIONS--------------------------------------------------------------- -- IF THERE ARE ANY PRSENIOR ROWS, THAT DETERMINES STARTING DATE -- OTHERWISE PICK EARLIEST OF: -- a) EARLIEST PAY HISTORY DATE -- b) EARLIEST EMPTYPE_EMPLOYEE_TYPE START DATE SELECT DATEADD(DAY, 1, MAX(prsenior_date)) INTO ld_prsenior_date_starting FROM prsenior_employee_seniority WHERE premp_id = as_premp_id AND prsenior_date < ad_effective_date; IF sqlcode < 0 THEN RAISERROR 18400 '18400 return_premp_seniority_increment, sql error ' || STRING(sqlcode); END IF; IF ld_prsenior_date_starting IS NOT NULL THEN SET ld_start_date = ld_prsenior_date_starting; ELSE IF ld_prreg_pay_date IS NOT NULL THEN SET ld_start_date = ld_prreg_pay_date; ELSE SET ld_start_date = ld_emptype_date_start; END IF; END IF; SET ls_narrative = concatenate_text(ls_narrative, 'Start Date:' || COALESCE(DATEFORMAT(ld_start_date, 'dd-Mmm-yyyy'), 'Undeterminable') || '. End Date:' + DATEFORMAT(ad_effective_date, 'dd-Mmm-yyyy')); IF ld_start_date IS NULL THEN SET ls_narrative = concatenate_text(ls_narrative, '>>>Undeterminable Start Date: No previous Seniority entries, no Pay History, and no Employment History<<<'); SET ld_start_date = ad_effective_date; END IF; --STEP 2: ACCUMULATE DAYS BETWEEN STARTING DATE AND 31 DEC 1994 (WHEN WE DONT HAVE PAY HISTORY AND HAVE TO ASSUME FULL EMPLOYMENT)-------------------------------------- IF ld_start_date < LD_PAY_HISTORY_START_DATE THEN SET li_days = DATEDIFF(DAY, ld_start_date, LD_PAY_HISTORY_START_DATE) + 1; SET ln_seniority_days = ln_seniority_days + li_days; SET ls_narrative = concatenate_text(ls_narrative, 'Accumulate days prior to 1995: ' || STRING(li_days)); END IF; --STEP 3: FROM 1 JAN 1995 THRU 23 JUL 2010 (WHEN NEW EMPLOYEE STATUS WENT INTO EFFECT) ACCUMULATE DAYS BASED ON PAY HISTORY---------------------------------------------- SET li_paid_days_total = 0; SET li_not_paid_days_total = 0; WHILE li_pay_history_done IS NULL LOOP IF ld_prreg_pay_date >= ld_start_date AND ld_prreg_pay_date BETWEEN LD_PAY_HISTORY_START_DATE AND LD_EMPLOYMENT_STATUS_START_DATE THEN IF ld_lst_prreg_pay_date_ending IS NOT NULL THEN SET li_not_paid_days = DATEDIFF(DAY, ld_lst_prreg_pay_date_ending, ld_prreg_pay_date) - 1; IF li_not_paid_days > 0 THEN SET li_not_paid_days_total = li_not_paid_days_total + li_not_paid_days; SET ls_narrative = concatenate_text(ls_narrative, 'No Pay between ' || DATEFORMAT(ld_lst_prreg_pay_date_ending, 'dd-Mmm-yyyy') || ' and ' || DATEFORMAT(ld_prreg_pay_date, 'dd-Mmm-yyyy') || ', ' || STRING(li_not_paid_days) || ' not paid days'); END IF; END IF; SET li_paid_days = DATEDIFF(DAY, ld_prreg_pay_date, ld_prreg_pay_date_ending) + 1; SET li_paid_days_total = li_paid_days_total + li_paid_days; SET ls_narrative = concatenate_text(ls_narrative, 'Pay History ' || DATEFORMAT(ld_prreg_pay_date, 'dd-Mmm-yyyy') || ' thru ' || DATEFORMAT(ld_prreg_pay_date_ending, 'dd-Mmm-yyyy') || ', ' || STRING(li_paid_days) || ' paid days'); SET ld_lst_prreg_pay_date_ending = ld_prreg_pay_date_ending; END IF; FETCH NEXT pay_history_cursor INTO ld_prreg_pay_date, ld_prreg_pay_date_ending; IF sqlcode = 100 THEN SET li_pay_history_done = 1; END IF; IF sqlcode < 0 THEN RAISERROR 18500 '18500 return_premp_seniority_increment, sql error ' || STRING(sqlcode); END IF; END LOOP; SET ln_seniority_days = ln_seniority_days + li_paid_days_total; SET ls_narrative = concatenate_text(ls_narrative, 'Pay History = ' || STRING(li_paid_days_total) || ' paid days total. Breaks in Pay History = ' || STRING(li_not_paid_days_total) || ' days total'); --STEP 4: BEYOND 23 JUL 2010 (WHEN NEW EMPLOYEE STATUS WENT INTO EFFECT) ACCUMULATE DAYS BASED ON EMPLOYMENT STATUS---------------------------------------------- SET li_paid_days_total = 0; WHILE li_employment_status_done IS NULL LOOP IF ld_empstat_date_end IS NULL THEN //NULL INDICATES IS STILL CONTINUING, STOP CALCULATIONS AT EFFECTIVE DATE SET ld_empstat_date_end = ad_effective_date; END IF; IF ld_empstat_date_end > ad_effective_date THEN SET ld_empstat_date_end = ad_effective_date; END IF; IF ld_empstat_date_start <= ld_start_date THEN SET ld_empstat_date_start = ld_start_date; END IF; IF ld_empstat_date_start <= LD_EMPLOYMENT_STATUS_START_DATE THEN SET ld_empstat_date_start = LD_EMPLOYMENT_STATUS_START_DATE; END IF; SET li_days = DATEDIFF(DAY, ld_empstat_date_start, ld_empstat_date_end) + 1; IF li_days > 0 THEN SET ls_narrative = concatenate_text(ls_narrative, 'Employment Status History ' || DATEFORMAT(ld_empstat_date_start, 'dd-Mmm-yyyy') || ' thru ' || DATEFORMAT(ld_empstat_date_end, 'dd-Mmm-yyyy') || ', ' || STRING(li_days)); IF ls_empstatcode_seniority_ind = 'Y' THEN SET li_paid_days_total = li_paid_days_total + li_days; SET ls_narrative = ls_narrative || ' eligible days' ELSE SET ls_narrative = ls_narrative || ' Ineligible days' END IF; END IF; FETCH NEXT employment_cursor INTO ld_emptype_date_start, ld_emptype_date_end, ld_empstat_date_start, ld_empstat_date_end, ls_empstatcode_seniority_ind; IF sqlcode = 100 THEN SET li_employment_status_done = 1; END IF; IF sqlcode < 0 THEN RAISERROR 18600 '18600 return_premp_seniority_increment, sql error ' || STRING(sqlcode); END IF; END LOOP; SET ln_seniority_days = ln_seniority_days + li_paid_days_total; CLOSE pay_history_cursor; CLOSE employment_cursor; SET OPTION debug_messages = 'OFF'; SELECT ln_seniority_days, ls_narrative FROM DUMMY; END |
|
Found the problem! After adding the EXCEPTION blocks and adding quite a few MESSAGE statements to confirm what code was executing, I found that removing the statement “SET OPTION debug_messages = 'OFF';” from the end of return_premp_seniority_increment cleared up the error. I see once again I am not an SQLA guru, but I have to say this solution seems to me to be closely related to waving a dead chicken over the keyboard. But hey, any solution is a good thing! Thanks everyone for your help. message '----return_premp_seniority_increment ABOUT TO CLOSE CURSOR---------------------------------------------------------' TO CONSOLE; CLOSE pay_history_cursor; message '----return_premp_seniority_increment pay_history_cursor IS CLOSED--------------------------------------------------' TO CONSOLE; CLOSE employment_cursor; message '----return_premp_seniority_increment employment_cursor IS CLOSED---------------------------------------------------' TO CONSOLE; SET OPTION debug_messages = 'OFF'; message '----return_premp_seniority_increment ABOUT TO SELECT RESULT SET AT END OF PROC-------------------------------------' TO CONSOLE; SELECT ln_seniority_days, ls_narrative FROM DUMMY; EXCEPTION WHEN OTHERS THEN OF COURSE! That's not a dead chicken. That's the SET OPTION doing it's own COMMIT. I feel much better with a hard fact than a mysterious solution.
(30 Jan '14, 12:23)
Bill Aumen
Replies hidden
Inside of a procedure... does using the TEMPORARY keyword work here? It won't do a COMMIT and it won't affect all other connections then. I'm not familiar with TSQL syntax though so that keyword may not be available to you.
(30 Jan '14, 12:35)
Erik Anderson
1
Note, Bill is not using T-SQL (of course...). Yes, SET TEMPORARY OPTION... should work, and the docs tell:
I would suggest a doc page with all those statements that do an automatic commit. Everyone will expect the typical DDL statements here, but SET OPTION...?
(30 Jan '14, 12:43)
Volker Barth
That SET OPTION was just a standard statement I put at the end of all my stored procs so that I don't inadvertently leave debugging on after a round of testing. Has been a "good idea" up until now :)
(30 Jan '14, 13:59)
Bill Aumen
Replies hidden
Sorry for not seeing the SET OPTION earlier... note that SET TEMPORARY OPTION should always be the first choice for embedding with other logic since it affects only the current connection. SET OPTION changes the initial setting for all future connections, generally NOT what you want. I suggest you scan all your procs for "SET OPTION" and get rid of them :)
(30 Jan '14, 21:11)
Breck Carter
Somewhat funny that the "SET OPTION debug_messages = 'OFF';" statement has made you debug all that code:)
(31 Jan '14, 03:42)
Volker Barth
1
I remember, after the TEMPORARY debug option didn't help me when my code involved firing an event (which created a separate connection), having the bright idea that the permanent option would be bulletproof. I'll add that to the list of bright ideas I have had in my career that came back to bite me in the butt :).
(01 Feb '14, 11:57)
Bill Aumen
|
|
RAISERROR alone does not a robust error-handling mechanism make... intermediate error conditions may be silently lost on the way back up the food chain. Highly recommended is an EXCEPTION handler in each and every BEGIN block that may have issues, and that handler should contain some reliable mechanism for reporting the error. Here is a template for the condition where you don't actually handle any errors, just report them all... BEGIN
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
"some code"
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'DIAG ',
CURRENT TIMESTAMP,
' SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
END;
...meaning you don't necessarily have to use the debugger to find issues... - just look in the console log:) Often enough, I have not taken the time to code such generic handlers, and that saved time and more was spent later... Just to add a FAQ with a similar template from Mark - and his explanation that this does not lead to a performance penalty: How do I notice errors in a function used to calculate a computed column?
(30 Jan '14, 11:19)
Volker Barth
That makes sense, I'll modify the code and see what that tells me. I have always had what I considered really good success with raiserror and sometimes change the continue_after_raiserror option temporarily to make problems more evident (after you helped me with a mysterious error several years ago). But that didn't do anything different for me this time. off to try exception block.
(30 Jan '14, 11:30)
Bill Aumen
Comment Text Removed
|
|
You can try if explicitly defining the cursor with the INSENSITIVE keyword as a read-only cursor changes this behavior Thanks Martin. I have just tried changing the cursors in the trigger and both procs from NO SCROLL to INSENSITIVE. But I am getting the same error.
(30 Jan '14, 10:46)
Bill Aumen
|
A very very wild guess: Is the CHAINED option set to 'OFF' here, so that server-side autocommit is in effect (meaning without further transaction control, each statement is committed individually)?
(I'm just wondering what else might raise that error message here when you do no explicit commits - and as far as I can see, there are no statements with automatic commits involved...)
I just verified that Chained is still set to the default "On".
There are certainly no commits or rollbacks. That's probably why this confuses me so much. What I see happening is: the CLOSE CURSOR must cause some error or crash: the debugger immediately drops to the END statement in the proc, next drops to the END statement in the function, next drops to the END statement in the trigger, and then I get that error message.