How to get SQLCODE for Multiple Insert

kalaikala · New User Joined: 21 Jul 2007 Posts: 27 Location: Chennai

Hi,

In COBOL-DB2 program(BMC), How to get SQLCODE for each row on Multiple Insert?. Depending on the return value of each row I have to do different process.

Is it possible to get?.

abhishek mitra · New User Joined: 24 Feb 2008 Posts: 48 Location: Kolkata

It depends on the process of insert. If you are using a cobol loop for multiple insert for each loop you will get SQLCODE easily.

but in case your are inserting more than one rows 'in a go' (INSERT through SELECT in sub query) then it is not possible to get SQLCODE for each row. Here you have to understand that COBOL replaces an 'EXEC SQL - END-EXEC' by API calls and the SQLCA gets updated after each API finish. Here an INSERT with SUB-SELECT (For muliple insert) generates a single API.

any thoughts are welcome on this !!

kalaikala · New User Joined: 21 Jul 2007 Posts: 27 Location: Chennai

I not using cobol loop & also sub-query.

My method is,

You can insert the number of rows that are specified in the host variable NUM-ROWS by using the following INSERT statement:

EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3 :IVA3)
FOR :NUM-ROWS ROWS
END-EXEC.

My expectation is, getting SQLCODE for each row by array.[/b]

abhishek mitra · New User Joined: 24 Feb 2008 Posts: 48 Location: Kolkata

This is a good learning for me

. I was unaware of this technique. But still my idea stays same here. A single 'EXEC SQL ... END-EXEC' generates a single API and SQLCA gets updated after each API execution (so is our SQLCODE). Hence Possibly getting "SQLCODE for each row by array" is looking difficult to me if not impossible.

Regards
Abhishek

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

Yes, I agree too. That we can't get sqlcode for each insert

kalaikala · New User Joined: 21 Jul 2007 Posts: 27 Location: Chennai

Is there anyway to insert mutiple rows at a time?. and also i want to manipulate the return code.

Can anyone help me?!!!

Bharath Bhat · Posted: Fri Mar 13, 2009 5:43 pm

You can use get diagnostics.
GET DIAGNOSTICS