SQL in free form work-around (was: Free form code suggestion)

["Mackie, Roger L. (Precision Press)" <RLMackie@xxxxxxxxxxxxx>]

In the previous thread, John Taylor wrote (in part):
> It's when using the latest, greatest features that things start to get
> really ugly. For example:
> 
>  //free
>    set some selection criteria...
>  //end-free
> 
> C/exec sql
> C+  open a cursor
> C/end-exec
> 
>  //free
>    test for errors and/or warnings
>  //end-free
> 
> C/exec sql
> C+  read a record
> C/end-exec
> 
> Looking at source that's littered with those tags makes my hair hurt.

In reply Hans Boldt wrote:
> ...Rochester is indeed keen on improving the prep. But this necessary
> improvement is not in the current batch of items under development.

Why not isolate SQL statements in sub-procedures to avoid tag overload? For
example:
...
      // Need global SQLStmnt for SQL pre-compiler
     D gSQLStmnt      S           2048A   Varying
     D SQLSuccess     S              5A   Inz('00000')
     D NoMoreRows     S              5A   Inz('02000')
...
      /Free
       BuildSQL();
       If PrepSQLGetRS() = SQLSuccess;
         DoW FetchNextRow() = SQLSuccess;
           ProcessRow();
         EndDo;
         If SQLSTT = NoMoreRows;
           CloseSQLCursor();
         EndIf;
       EndIf;
       If SQLSTT <> NoMoreRows;
         DoSQLErr();  // Handle SQL Errors
       EndIf;
...
       *InLR = *On;
      /End-Free
...
      //-----------------------------------
     P PrepSQLGetRS    B
     D PrepSQLGetRS    PI             5A
                                             
     C/EXEC SQL
     C+    PREPARE SELECT FROM :gSQLStmnt
     C/END-EXEC
     C/EXEC SQL
     C+    DECLARE C1 CURSOR FOR SELECT
     C/END-EXEC
     C/EXEC SQL
     C+    OPEN C1
     C/END-EXEC
     C                   RETURN    SQLSTT 
     P PrepSQLGetRS    E
      //-----------------------------------

      //-----------------------------------
     P FetchNextRow    B
     D FetchNextRow    PI             5A
                                                                  
     C/EXEC SQL
     C+    FETCH FROM C1 INTO :gResult
     C/END-EXEC
     C                   RETURN    SQLSTT 
     P FetchNextRow    E
      //-----------------------------------

      //-----------------------------------
     P CloseSQLCursor  B
     D CloseSQLCursor  PI             5A
                                                
     C/EXEC SQL
     C+    CLOSE C1
     C/END-EXEC
     C                   RETURN    SQLSTT 
     P CloseSQLCursor  E
      //-----------------------------------
...
The first subprocedure could be coded differently or split for more granular
error checking. Since SQLSTT is a global, the subprocedures do not need to
return it, but this way seems more readable to me. YMMV.

This work-round doesn't require coding /Free and /End-Free directives around
SQL statements. It would be nice if gSQLStmnt did not need to be a global
and free-form SQL statements did not need positional C-specs (especially to
handle multiple cursors in one set of subprocedures). But until we get those
enhancements, does this approach make sense? Since my shop hasn't
implemented it yet, does anyone foresee any problems? How can it be
improved?

Thanks,
Roger Mackie