AW: Calling CL program to delete .ZIP file in IFS as part of an SQL Delete

Hi Charles;

But the compiler doesn't like me doing a SELECT instead of a SELECT

INTO...

If you want to access several rows (I assume why you are replacing SELECT
INTO with SELECT) you need to Declare a cursor, Open, Fetch rows (execute
your delete statement per row) and finally close the cursor (just like you'd
do it with embedded SQL).

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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Charles Wilt
Gesendet: Wednesday, 12. May 2010 21:28
An: Midrange Systems Technical Discussion
Betreff: Calling CL program to delete .ZIP file in IFS as part of an SQL
Delete

All,

We've got an existing SQLRPGLE program that contains basically the
statement:

delete from mytable
where datefld <= CURRENT_DATE - 1 month;


Datefld is non-unique, so many records could have the same date.

One of the other developers came to me and asked if there was a way to
call a CL program that will delete a .ZIP file with a naming
convention that includes the date for any dates being deleted from the
table. Example: MY_ARCHIVE_yyyyy-mm-dd.ZIP

The developer already had a CL program that when passed the date,
would delete the .ZIP.

My first thought, sure we can create a user defined function to call
the CL during the execution of the SQL statement...
delete from mytable
where datefld <= CURRENT_DATE - 1 month
and Delete_Zip_File(char(datefld,ISO)) = '0';


Anybody see a problem with this? (hint consider non short circuit
evaluations ;)

Then I thought well we'll just run two statements:
with tbl as (select distinct datefld
from mytable
where datefld <= CURRENT_DATE - 1 month
)
select Delete_Zip_File(char(datefld,ISO))
from tbl;

delete from mytable
where datefld <= CURRENT_DATE - 1 month;


But the compiler doesn't like me doing a SELECT instead of a SELECT INTO...

Anyway, the developer is looking at just using RPG RLA to delete the
rows, calling the CL program normally when the date changes.

But I thought it's an interesting issue and I figured I thought it out
here to see if anybody had any better ideas.

Thanks!
Charles Wilt