Re: replace opnqryf with sql

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

David

There's no question that cycle coding is "cleaner" even than 
full-procedural code. The main benefit of moving from OPNQRYF, in my 
opinion, is the really amazing flexibility of SQL. And coding the actual 
statement is so much easier for me than the funky parameters of OPNQRYF 
- a great utility in its day.

It does seem you can get DISTINCT results by using all the fields in the 
KEYFLD parameter and setting the UNIQUEKEY parameter as needed. Haven't 
done this in years, so am guessing. No need to "simulate" distinct then.

SQL always involves more coding than cycle coding. You do multiple 
operations to "open" the file, where a Primary gets all that open 
activity done behind the covers - even opening a file that is not 
user-open is hidden.

Managing SQL errors? That's really no different from what you do now. 
There's usually a single, usable condition after fetches, so it's pretty 
much the same all the time.

For me, the benefits of SQL far outweigh any "convenience" of using OPNQRYF.

And now for the religious wars, please!

Vern

On 1/20/2011 2:51 AM, David FOXWELL wrote:
> This is often the advice, but it seems easier to say than do.
>
> I was just given a small program to write where, in the good 'ol days, I'd have done an opnqryf followed by a client file read with the rpg cycle and use the calculation specs beginning or end to treat the client once only if it occurs multiple times in the file.
>
> As I can no longer write opnqryf that complex without looking it up, I plunged straight into the rpgsqle solution :
>
> Declare result set DS
>
> Dynamically prepare a cursor
> Declare cursor
> Open cursor
>
> Then logic to Fetch
>
> Manage sql errors after each operation.
>
> The query was a select distinct on one file only using a date field that was returned from a subprocedure.
>
> The CLP that calls the rpg is now very clear, but the rpg is not as simple as it would have been had I used the cycle.
> Also during development, I had to add another field to the result set. As my Fetch was in a subroutine and it was returning its results as parameters, this meant changing the DS, the parameters, the prototype, the callp and the cursor. With RLA, the extra field would already have been available.
>
> The non sql solution would have been :
> CALLP getDateParameter
> OPNQRYF using dateParameter
> CALL RPG using cycle to 'simulate' select distinct.