midrange.com code scratchpad
Name:
Sample SQL program with multiple fetch
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
07/23/2008 12:40:32 pm
IP:
Logged
Description:
The user was to lazy to give a description
Code:
  1.      H ActGrp(*CALLER)
  2.      H DftActGrp(*NO)
  3.  
  4.      D OpenCursor      PR              n
  5.      D FetchCursor     PR              n
  6.      D CloseCursor     PR              n
  7.  
  8.      D MyAry         e ds                  extname(iim) dim(10) qualified
  9.      D rows            s              5i 0 inz(%elem(MyAry))
  10.      D RowsRetrieved   s              5i 0
  11.      D SqlErr5LastRow  C                   Const( 100 )
  12.  
  13.       /free
  14.        *inlr=*on;
  15.        if not OpenCursor();
  16.          // perform error routine to alert the troops
  17.          // ...
  18.        Else;
  19.          Dow FetchCursor();
  20.            // putting the fetchcursor on the do loop allows the user of
  21.            // iter, and thus iter will not perform an infinite loop
  22.            // normal processing here...
  23.          EndDo;
  24.          CloseCursor();
  25.        EndIf;
  26.        return;
  27.       /end-free
  28.  
  29.      P OpenCursor      B
  30.      D OpenCursor      PI                  like(ReturnVar)
  31.      D ReturnVar       s               n
  32.  
  33.      C/EXEC SQL
  34.      C+ Set Option
  35.      C+     Naming    = *Sys,
  36.      C+     Commit    = *None,
  37.      C+     UsrPrf    = *User,
  38.      C+     DynUsrPrf = *User,
  39.      C+     Datfmt    = *iso,
  40.      C+     CloSqlCsr = *EndMod
  41.      C/END-EXEC
  42.  
  43.      C/EXEC SQL
  44.      C+ Declare C1 cursor for
  45.      C+  Select *
  46.      C+  from iim
  47.      C+  for fetch only
  48.      C/END-EXEC
  49.  
  50.      C/EXEC SQL
  51.      C+ Open C1
  52.      C/END-EXEC
  53.  
  54.       /free
  55.        Select;
  56.          When SqlStt='00000';
  57.            return *on;
  58.          Other;
  59.            return *off;
  60.        EndSl;
  61.       /end-free
  62.      P OpenCursor      E
  63.  
  64.       /eject
  65.      P FetchCursor     B
  66.      D FetchCursor     PI                  like(ReturnVar)
  67.      D ReturnVar       s               n
  68.      C/EXEC SQL
  69.      C+ Fetch Next
  70.      C+   From C1
  71.      C+   For :rows Rows
  72.      C+   into :MyAry
  73.      C/END-EXEC
  74.       /free
  75.        Select;
  76.          When sqlstt='00000';
  77.            // row was received, normal
  78.            // Was last row received?
  79.            If SqlErrD( 5 ) = SqlErr5LastRow;
  80.              dsply 'End';
  81.            ENDIF;
  82.            ReturnVar=*on;
  83.          When sqlstt='02000';
  84.            // same as %eof, sooner or later this is normal
  85.            If SqlErrD( 5 ) = SqlErr5LastRow;
  86.              dsply 'End2';
  87.            ENDIF;
  88.            ReturnVar=*off;
  89.          Other;
  90.            // alert the troops!
  91.            ReturnVar=*off;
  92.        EndSl;
  93.        // Number of rows Retrieved
  94.        RowsRetrieved = SqlErrD( 3 );
  95.  
  96.        return ReturnVar;
  97.       /end-free
  98.      P FetchCursor     E
  99.  
  100.       /eject
  101.      P CloseCursor     B
  102.      D CloseCursor     PI                  like(ReturnVar)
  103.      D ReturnVar       s               n
  104.      C/EXEC SQL
  105.      C+ Close C1
  106.      C/END-EXEC
  107.       /free
  108.        Select;
  109.          When sqlstt='00000';
  110.            // cursor was closed, normal
  111.            ReturnVar=*on;
  112.          Other;
  113.            // alert the troops!
  114.            ReturnVar=*off;
  115.        EndSl;
  116.        return ReturnVar;
  117.       /end-free
  118.      P CloseCursor     E 
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css