RE: Free format sql, was: Nice trick for easy viewing free-form RPG and embedded SQL

On Tue, 2005-07-19 at 16:07, rob@xxxxxxxxx wrote:
> I'm Rob, not Ron.  Basically I am not a big fan of externalizing I/O 
> because it's been my impression that a bulk of the people who are enthused 
> about it are the ones who write the external I/O routines.  Not the 
> consumers of the routines.

Since I am "enthused" by this approach (evidence at
http://www.rpgbeans.com), I'd like to say that this is something I did
for myself that I shared with the community afterwards.  Yes, I write
external I/O routines, but I do so for the purpose of consuming them,
and so that other developers at my company can consume them.  I do not
sell or distribute any External I/O routines for other "consumers".

I'd rather write a program that does something like
>FMyFile....
> /free
> read(e) MyFile;
> Dow %status(MyFile)=ReadOk;
>  // process the data
>  read(e) MyFile;
> EndDo;
> Select;
> When %status(MyFile)=EOF;
>  // Ok, don't worry about this one
> Other;
>  // Oh darn!!!
>  ...
> EndSl;

Here is a code sample from one of my articles:

<code>
    // Retrieves the record set of all records with
    // PHONENUM equal to 8005551212 and orders the
    // results by USERNAME
    // returns true if SQL statement is OK and set is opened
    if mynames_getRecordSet( 'PHONENUM = 8005551212' : 'USERNAME' );
      // Do While loop for records remaining
      dow mynames_fetchRecordSet();
        message = 'User: ' + %trimr( mynames_getUserName() ) +
                 ' EMail: ' + %trimr( mynames_getUserEmail() );
        dsply message ;
      enddo ;
      endif ;
</code>

Obviously, you could add a lot more to it, but I think it shows that
it's easy to use.

> Some externalizing gurus might say you wouldn't have a GetInventoryData. 
> Instead the keys would be on the GetBalance, etc.  Then what, does it read 
> a record for each field requested?  I'm not expecting an answer.  It's 
> just a sample of how externalizing may raise more questions than it 
> solves.

I'll answer anyway :-) 

You could write it that way, but I certainly wouldn't advise it.  In my
approach, the fields are stored in a service program.  They are global
(to the service program only, of course), so a single call to initialize
the service program populates all the variables.  Therefore, you only
hit the database once, just like native I/O.

> Oh, there's the popular myth that if I change a file structure then I do 
> not need to worry about it if I've externalized.  My counter is that a 
> popular package changed the size of their key field and all the rest of 
> their fields.  So if I have a program
> D MyItem         s            15a
> D ItemDesc       s            30a

The encapsulating service program would (perhaps) need to be
recompiled.  If it was written using native I/O this is almost certain,
and would probably need some modifications as well to handle the new
field sizes.  But if it was written using SQL then it may not need any
adjustments.  

The "myth" you refer to indicates that the fields in the consumer must
be defined exactly the same as the file/field definition.  This is not
the case: if your procedures use 'value' or 'const' for all the
parameters, then most parameter differences are handled for you, at
least within the same generic type.  And return types don't have to
match *exactly* either.

> I still have issues.  Now, if I've LIKEd these fields off of an external 
> data structure I'm better off.  I still have to compile though.  (And 
> maybe redesign any 5250 screens to change the layout, etc).

But you don't have to recompile.  Unless you are talking about radical
changes (say from character to numeric or something like that), the
program will continue to function IF the sub-procedures were designed
properly.  Yes, you *may* have to recompile the Encapsulator. 
Certainly, if new fields have been added, you'll probably want to add
some new sub-procedures.  But if the new program doesn't need the new
fields, then there is no change to the program.

Consider this: I have a program that calculates standard deviations for
a dozen numeric fields in FILEA.  If I add 20 new character fields to
the end of the file, it doesn't affect what this program does in the
slightest, so no changes necessary.  Even if I change the numeric
lengths of the fields I'm calculating, I don't need to make any changes
for the program to keep functioning over the existing data.  I will need
to make some adjustments if I start populating the fields with data
larger than the current definitions will allow, but it isn't an
immediate show-stopper.

> Then again, 
> isn't using an external method supposed to hide from your developers what 
> the name of the actual physical file is?  If so, how would they know 
> file(s) to use in the data structure(s)?

The field definitions that were there at compile time are still valid
(again, unless a type has been changed).  The procedures in the
Encapsulator will handle the differences... which is exactly the kind of
detail they are designed to "hide".  Along with things like math
formulas, always trimming incoming strings, insuring proper formatting,
enforcing date formats, etc. etc. etc.

Look, if you don't like it, don't use it, no skin off my nose.  But
implying to the list at large that it has no value, or that you have to
"read a record for each field" is disingenous at best.

The last thing I'll say is that no matter how you code, some
circumstances are going to force you to modify and recompile some
objects.  There is no holy grail... but I think the Encapsulation
technique can do more than you give it credit for.

Joel Cochran
http://www.rpgnext.com