GET DIAGNOSTICS statement....

[N.V.Dev]

Hi,

I am trying to use GET DIAGNOSTICS statement after an INSERT. DB2
does not return the # of rows inserted. Below is the code and output.

----Code snippet

DROP PROCEDURE DIAG_CHECK @

CREATE PROCEDURE DIAG_CHECK ()
LANGUAGE SQL
BEGIN

DECLARE v_tmp_var CHAR(10);
DECLARE v_temp_SQLCODE INT DEFAULT 0;
DECLARE v_temp_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT;

DECLARE v_row_inserted INT;

DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND
BEGIN NOT ATOMIC
SET v_tmp_var = SQLSTATE|| CAST(SQLCODE AS CHAR(5));
SET v_temp_SQLSTATE = SUBSTR(v_tmp_var, 1, 5);
SET v_temp_SQLCODE = INT(SUBSTR(v_tmp_var, 6, 5));
END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN NOT ATOMIC
SET v_tmp_var = SQLSTATE|| CAST(SQLCODE AS CHAR(5));
SET v_temp_SQLSTATE = SUBSTR(v_tmp_var, 1, 5);
SET v_temp_SQLCODE = INT(SUBSTR(v_tmp_var, 6, 5));
RESIGNAL;
END;

SET v_row_inserted = 0;

INSERT INTO DBUG VALUES ( MICROSECOND( CURRENT TIMESTAMP), 'New row
inserted' );

GET DIAGNOSTICS v_row_inserted = ROW_COUNT;

INSERT INTO DBUG_CHK VALUES ( v_row_inserted, '# rows inserted' );

COMMIT;
END
@
---------Output:

from dbug table

ROW_ID DESC
-------- -------------------
328826 New row inserted
from dbug_chk

NROWS DESC
----------- -------------------------
0 # rows inserted

Please let me know the mistake.

Thanks in advance,

Dev

[Serge Rielau]

One thought:
Could be you fall into one of the handlers whcih wipe out the row_count.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[N.V.Dev]

Serge Rielau <sr*****@ca.eye-bee-m.com> wrote in message news:<bp**********@hanover.torolab.ibm.com>...

One thought:
Could be you fall into one of the handlers whcih wipe out the row_count.

Cheers
Serge

Nopes, tried commenting handlers and ran but in vain... and below is the output.

added below in addition to the exisiting procedure

--------------------------------------------------------
INSERT INTO DBUG
SELECT MICROSECOND( CURRENT TIMESTAMP ), 'From select' FROM SYSIBM.SYSDUMMY1;

GET DIAGNOSTICS v_row_inserted = ROW_COUNT;

INSERT INTO DBUG_CHK VALUES ( v_row_inserted, '# rows from select' );

----------------------------------------------------

Output :

select * from dbug_chk

NROWS DESC
----------- -------------------------
0 # rows inserted
1 # rows from select

Thanks,
Dev