AW: RPG with embedded SQL

You always should check either SQL State (SQLSTT) or SQL Code (SQLCOD)
immediately after having executed an SQL statement.
An SQLCOD < *Zeros or an SQLSTT beginning with anything else than 00, 01 or
02 is an error.
Please check what is returned.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von KBushard@xxxxxxxxxxxxxxxx
Gesendet: Friday, 08. July 2011 16:05
An: rpg400-l@xxxxxxxxxxxx
Betreff: RPG with embedded SQL

Dear All:

I'm having a problem with some embedded SQL and thought I had it working;
now I'm seeing the problem again.

The program is supposed to recalculate physical inventory count statistics
for the cycle whenever a batch is posted. I have a file of history which
is retained for a couple of cycles. I recalculate because there could be
items "found" in the batch being posted that were "not found" in a
previous batch and now will calculate differently.

I've abbreviated the code (I hope not too much) but basically, I am
deleting all the statistics for the cycle, recalculating, and (re)
writing, which now includes the batch that was just posted.

It's acting like the delete isn't working, and doubles-up the statistics.
I have used the "Set Option Closqlcsr = *Endmod" in other programs to fix
open cursor issues if the job runs again, but it's not working here.

Here's my RPG program, abbreviated, but I've included all the sql
statements:


Exec Sql
Set Option Closqlcsr = *Endmod;

chain ($_strwh:$_whse#:' ') picyclectl;
if %found(picyclectl);
exsr #deleteStats;
exsr #calcStats;
endif;

*inlr = *on;
//*************************************************************
// delete stats for this store/warehouse/cycle *
//*************************************************************
begsr #deleteStats;
Exec Sql
Delete From PIAISLESTS
Where STRWH = :$_STRWH And
WHSE# = :$_WHSE# And
PIYEAR = :CYC_PIYEAR And
PICYCLE# = :CYC_PICYCLE#;
Exec Sql
Commit;
endsr;
//*************************************************************
// process a store/warehouse *
//*************************************************************
begsr #calcStats;
clear sts.sts_info;

Exec Sql
Declare Z@ Cursor For
Select *
From IVPILOCCYC
Where STRWH = :$_STRWH And
WHSE# = :$_WHSE# And
PIYEAR = :CYC_PIYEAR And
PICYCLE# = :CYC_PICYCLE#
For Read Only;
Exec Sql
Open Z@;

dou sqlcode <> *zeros;
Exec Sql
Fetch Next From Z@ Into :IVPILOCCYCDS;
if sqlcode = *zeros;

... calculate stuff here ...

endif;
enddo;

// write the totals
exsr #writetotals;

Exec Sql
Close Z@;
endsr;
//*************************************************************
// write the totals *
//*************************************************************
begsr #writetotals;

.... set up data to be written .......

for x = 1 to %elem(bch.bch_info);
als_pizone = bch.aisle(x);
als_pidatecls = bch.datecls(x);
write piaislest#;
endfor;
endsr;
//*************************************************************

Any advice/assistance is most appreciated!

Karleen Bushard
System i Software Devloper
HOM Furniture, Inc & Gabberts
kbushard@xxxxxxxxxxxxxxxx