INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can't get CONTINUE Handler to CONITINUE with code (next statement)

Can't get CONTINUE Handler to CONITINUE with code (next statement)

Can't get CONTINUE Handler to CONITINUE with code (next statement)

(OP)
Hello all, as I come from Oracle , I'm now having difficulty with DB2 Exception Handling.

I can't get the CONTINUE Handler below to CONTINUE with code, it is EXITING instead of CONTINUING when getting an SQL error.

Can someone review the below and let me know what the heck I'm missing here?

Thanks so very much for any assistance you can provide.

Sincerely,

Pat



****************************************************************************************************************




CREATE or replace
PROCEDURE ceud.log_err(p_job_name char, p_proc_position char)
LANGUAGE SQL
AUTONOMOUS
BEGIN
DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_SQLCODE INT;

declare y varchar(1);

ROLLBACK;
INSERT INTO CEUD.BATCH_LOG_NEW (batch_job_name, batch_error_message, creation_date) VALUES (p_job_name,p_proc_position,sysdate);
COMMIT;

END


CREATE or replace
PROCEDURE ceud.MERGE_PCO()
--SPECIFIC read_emp
LANGUAGE SQL
BEGIN

DECLARE v_commit_count INTEGER DEFAULT 1;
DECLARE v_record_count INTEGER DEFAULT 0;
DECLARE v_pco_count INTEGER;
DECLARE v_active_address VARCHAR(1);
declare x varchar(1000);
declare v_proc_position varchar(500);
declare v_site_addr_valid_from DATE;
declare v_site_addr_valid_to DATE;
Declare v_zip varchar(50);
Declare v_zip_ext varchar(50);
declare v_ext_pos integer;
declare v_zipext_len integer;
declare v_job_name varchar(50) DEFAULT 'MERGE_PCO';

DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_SQLCODE INT;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN

CALL ceud.LOG_ERR(v_job_name, v_proc_position);

END;



set v_proc_position ='start: '||to_char(SYSDATE,'MM/DD/YY HH:MI:SS');
INSERT INTO CEUD.BATCH_MESSAGES (BAM_JOB_NAME, BAM_ERROR_MESSAGE, CREATION_DATE) VALUES (v_job_name, v_proc_position, SYSDATE);
COMMIT;

FOR r_delta AS cur_delta CURSOR WITH HOLD FOR SELECT * FROM CEUD.PCO_FILE_LOAD
DO

SET v_record_count = v_record_count + 1;
SET v_proc_position = concat('PREMISE :',r_delta.premisenumber);
SET v_pco_count = 0;

SELECT COUNT(1)
INTO v_pco_count
FROM CEUD.PREMISE_CONNECTION
WHERE PREMISE_NUMBER = r_delta.PREMISENUMBER
AND INSTALLATION_NUMBER = r_delta.installation_number;

IF v_pco_count = 0 THEN

IF r_delta.dmlcmd IN ('I','U') THEN

INSERT INTO CEUD.PREMISE_CONNECTION
(
PREMISE_NUMBER,
INSTALLATION_NUMBER,
CONNECTION_OBJECT,
PREMISE_TYPE,
PREMISE_TYPE_TEXT,
LEGACY_PREMISE_ID,
CIRCUIT_NAME,
SERVICE_TOWNSHIP_ID,
SERVICE_CITY_ID,
OAS_PRIORITY_CODE,
OAS_PRIORITY_CODE_TEXT,
SECURITY_CODE,
SECURITY_CODE_TEXT,
TAX_JURISDICTION_CODE,
LONGITUDE,
LATITUDE,
CREATION_DATE,
CREATION_USER_ID
)
VALUES
(
r_delta.PREMISENUMBER,
r_delta.INSTALLATION_NUMBER,
r_delta.connectionobject,
r_delta.premisetype,
r_delta.premise_type_text,
r_delta.legacy_premise_id,
r_delta.Circuit_Name_co_characteristic,
r_delta.svc_township_id,
r_delta.svc_city_id,
r_delta.oas_priority_code,
r_delta.oas_priority_code_text,
r_delta.security_code,
r_delta.security_code_text,
r_delta.tax_jurisdiction_code,
r_delta.Longitutude,
r_delta.Latitude,
SYSDATE,
v_job_name);

END IF;

ELSE

IF r_delta.dmlcmd IN ('I','U') THEN

UPDATE CEUD.PREMISE_CONNECTION PC
SET PC.INSTALLATION_NUMBER = r_delta.INSTALLATION_NUMBER,
PC.CONNECTION_OBJECT = r_delta.connectionobject,
PC.PREMISE_TYPE = r_delta.premisetype,
PC.PREMISE_TYPE_TEXT = r_delta.PREMISE_TYPE_TEXT,
PC.LEGACY_PREMISE_ID = r_delta.LEGACY_PREMISE_ID,
PC.CIRCUIT_NAME = r_delta.Circuit_Name_co_characteristic,
PC.SERVICE_TOWNSHIP_ID = r_delta.svc_township_id,
PC.SERVICE_CITY_ID = r_delta. svc_city_id,
PC.OAS_PRIORITY_CODE = r_delta.oas_priority_code,
PC.OAS_PRIORITY_CODE_TEXT = r_delta.OAS_PRIORITY_CODE_TEXT,
PC.SECURITY_CODE = r_delta.SECURITY_CODE,
PC.SECURITY_CODE_TEXT = r_delta.SECURITY_CODE_TEXT,
PC.TAX_JURISDICTION_CODE = r_delta.TAX_JURISDICTION_CODE,
PC.LONGITUDE = r_delta.Longitutude,
PC.LATITUDE = r_delta.LATITUDE,
PC.MODIFICATION_DATE = SYSDATE,
PC.MODIFICATION_USER_ID = v_job_name
WHERE PREMISE_NUMBER = r_delta.PREMISENUMBER
AND INSTALLATION_NUMBER = r_delta.installation_number;

END IF;

END IF;


set v_proc_position ='finish:'||to_char(SYSDATE,'MM/DD/YY HH:MI:SS');
INSERT INTO CEUD.BATCH_MESSAGES (BAM_JOB_NAME, BAM_ERROR_MESSAGE, CREATION_DATE) VALUES (v_job_name, v_proc_position, SYSDATE);
COMMIT;


END

RE: Can't get CONTINUE Handler to CONITINUE with code (next statement)

Have you looked at the SQLSTATE and are you sure that it's SQLEXCEPTION and not SQLWARNING or NOT FOUND ?
Maybe instead of

CODE

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
... 
try to use

CODE

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
... 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close