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

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

Rob and all,

I wasn't going to get this deep, but since you brought it up--a service
program I'm working on will probably need to call itself (probably only
once) some time in the future. The code below shows the CloseSQLCursor I'm
considering--the other SQL subprocedures will need something similar. It
seems to work for multiple cursors within one call stack entry, but we
haven't tested multiple calls yet:
...
      // Need global SQLStmnt for SQL pre-compiler
     D gSQLStmnt      S           2048A   Varying
     D SQLSuccess     S              5A   Inz('00000')
     D NoMoreRows     S              5A   Inz('02000')
     D CursorType     S              5A   Varying       
     D RcrLevel       S              5U 0
...
      /Free
       RcrLevel = RcrLevel + 1;
       Cursor Type = BuildSQL(PassSomthing);
       If PrepSQLGetRS(CursorType:RcrLevel) = SQLSuccess;
         DoW FetchNextRow(CursorType:RcrLevel) = SQLSuccess;
           ProcessRow();
         EndDo;
         If SQLSTT = NoMoreRows;
           CloseSQLCursor(CursorType:RcrLevel);
         EndIf;
       EndIf;
       If SQLSTT <> NoMoreRows;
         DoSQLErr();  // Handle SQL Errors
       EndIf;
...
       RcrLevel = RcrLevel - 1;
       Return;
      /End-Free
      //-----------------------------------
     P CloseSQLCursor  B
     D CloseSQLCursor  PI             5A
     D  CrsType                       5A   Const Varying
     D  RcrLevel                      1S 0 Const

     C                   Select
     C                   When      RcrLevel = 1
     C                   If        CrsType = 'ITEM'
     C/EXEC SQL
     C+    CLOSE ITEM1
     C/END-EXEC
     C                   ElseIf    CrsType = 'OPT'
     C/EXEC SQL
     C+    CLOSE OPT1
     C/END-EXEC
     C                   EndIf

     C                   When      RcrLevel = 2
     C                   If        CrsType = 'ITEM'
     C/EXEC SQL
     C+    CLOSE ITEM2
     C/END-EXEC
     C                   ElseIf    CrsType = 'OPT'
     C/EXEC SQL
     C+    CLOSE OPT2
     C/END-EXEC
     C                   EndIf
      // repeat for the maximum anticipated value of RcrLevel
     C                   EndSl     
     C                   RETURN    SQLSTT 
     P CloseSQLCursor  E
      //-----------------------------------
...

The hard-coding is miserable, but at least only one subprocedure is needed.
>From my reading of the SQL manual it appeared that host variables could only
be used as data receivers, so I haven't even tried this:
...
     D gCursorID      S              6A   Varying
...
      //-----------------------------------
     P CloseSQLCursor  B
     D CloseSQLCursor  PI             5A
     D  Type                          5A   Const Varying
     D  RcrLevel                      1S 0 Const

     C                   Eval      gCursorID = Type +
%TrimL(%Char(RcrLevel))
     C/EXEC SQL
     C+    CLOSE :gCursorID
     C/END-EXEC
     C                   Return    SQLSTT 
     P CloseSQLCursor  E
      //-----------------------------------     

Can something like this be done? Could a SQLDA replacement variable be used
instead of a host variable in the CLOSE statement? Can anyone point me in a
better direction?

Thanks,
Roger Mackie

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Thursday, July 10, 2003 14:37
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL in free form work-around (was: Free form code
suggestion)


Not bad.  If you have multiple cursors CloseSqlCursor might have to be 
CloseSqlCursorC1.

<snip>

Rob Berendt