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

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Roger,

While I haven't done this personally, I believe that you *can* make your
idea work *if* you take some precautions.  There was a thread on this some
time ago, but as I recall, the problem was that you must keep the procedures
in a specific order within your source member so the pre-compiler would not
think that you're running the fetches before the declare cursor, and so on.
It was idiotic stuff like this that makes SQL and ILE a PITA.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Mackie, Roger L. (Precision Press) [mailto:RLMackie@xxxxxxxxxxxxx]
Sent: Thursday, July 10, 2003 1:41 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: SQL in free form work-around (was: Free form code suggestion)


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
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.