ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Page at a time subfile with SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Page at a time subfile with SQL

    Well here I go again!
    First I’d like to say to Birgitta – Notice my sqldata is now called MySqlData…
    Here is the cursor definition
    Code:
     *-------------------------------------------------------- 
     *  openList  - Open a cursor to read file                 
     *-------------------------------------------------------- 
    p openList        b                                        
                                                              
    d openList        pi                                       
                                                              
     /free                                                     
                                                               
      exec sql                                                 
       declare MyCursor scroll cursor for statement;           
                                                              
                                                               
      exec sql                                                 
       prepare statement from :mysqlstmt;                      
                                                              
      exec sql                                                 
       open mycursor;
    Then I have a page at a time subfile… where the page down works smashingly
    Code:
    //----------------------------------------                  
     // $read next group of records                              
     //----------------------------------------                  
          begsr $ReadNext;                                       
                                                                
           exsr $clearSubfile;                                   
           exec sql                                              
           fetch from mycursor for :Rows  rows into : mysqldata; 
           exsr $writeSubfile;                                   
                                                                
          endsr;
    Variables for the selection
    Code:
    d  mysqldata    e ds                  extname(ii) occurs(10) qualified             
    d rows            s              5  0 inz(%elem(mysqldata))
    So now the question my pageup/rolldown looks like this.
    Can I position up 20 records without actually reading the data?
    Code:
    //----------------------------------------             
     // $read previous group of records                     
     //----------------------------------------             
          begsr $ReadPrevious;                              
                                                           
           exsr $clearSubfile;                              
           for count2 = 1 to (Rows*2);                      
            exec sql                                        
            fetch prior from mycursor into :mysqldata;      
           endfor;                                          
           exsr $ReadNext;                                  
                                                           
          endsr;
    If anyone cares here is the load subfile section
    Code:
                                                                   
       //--------------------------------------------------------  
       // $writesubfile - wrote the group of records...            
       //--------------------------------------------------------  
            begsr $writesubfile;                                   
                                                                   
             if  SavRrn1  > *zeros;                                
              RRN1  =  SavRrn1;                                    
              SCRRN1 =  SavRrn1;                                   
             endif;                                                
                                                                   
               rows_fetched = SQLER3;                              
                                                                   
               for count = 1 to rows_fetched;                      
                clear sub01;                                       
                %occur(mysqldata) = count;                         
                s1item = MySqlData.icat;                           
                s1iname = MySqlData.idsco;                         
                RRN1 += 1;                                         
                SCRRN1 = RRN1;        
              write SUB01;                     
             endfor;                           
                                               
         // *in33 = *on;                       
                                               
                                               
           savrrn1 = rrn1;                     
                                               
                                               
          endsr;
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  • #2
    Re: Page at a time subfile with SQL

    i love the code you posted that checks the SQL status after each SQL statement
    I'm not anti-social, I just don't like people -Tommy Holden

    Comment


    • #3
      Re: Page at a time subfile with SQL

      I have a few suggestions:
      1. Change the multiple occurance DS to a Data Structure Array
      Code:
      d  mysqldata    e ds                  extname(ii) Dim( 10 ) qualified
      2. Define a data structure for your subfile screen
      Code:
      d ScreenDataDS    ds                  LikeRec( RecordFormatName : *All )
      Now, you can change your subfile load from moving each field from the data structure into a subfile field, to a one line eval-corr.
      Code:
      eval-corr ScreenDataDS = MySqlData( Count );
      write Sub01 ScreenDataDS;


      The beauty of this is that if you perform a "select *" with your SQL, then you can add any field from the file to your data structure, and it will automagically be picked up in the load by the eval-corr by a simple recompile. (As long as the field name on the screen is the same as the field name in the file). And you can load all the subfile fields with one line of code, whether you have one field or 1000.

      Also, if your subfile fields are coming from multiple files via an SQL join, then you can place each of the files data structure on the fetch.

      Then, you will eval-corr the data structure for each file into the ScreenDataDS. It's really a quick and clean way to bring a whole lot of data from different places into a screen program.
      Michael Catalani
      IS Director, eCommerce & Web Development
      Acceptance Insurance Corporation
      www.AcceptanceInsurance.com
      www.ProvatoSys.com

      Comment


      • #4
        Re: Page at a time subfile with SQL

        Thanks for taking the time Michael to help me out....

        I'm on v5r3 --- I dont think I can use a datastructure... Can I?

        I also have used Likerec and eval-corr many times in the past. Thank you again..

        But ... I am only interested in the page up portion of the code....
        I am looking for a position pointer rather than re-reading the records.
        Is there such a beast?


        thank you
        Jamie
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: Page at a time subfile with SQL

          Originally posted by jamief View Post
          Thanks for taking the time Michael to help me out....

          I'm on v5r3 --- I dont think I can use a datastructure... Can I?
          hmmmm. I cant remember. I'm pretty sure data structure arrays were v5r3 or v5r4.

          I also have used Likerec and eval-corr many times in the past. Thank you again..

          But ... I am only interested in the page up portion of the code....
          I am looking for a position pointer rather than re-reading the records.
          Is there such a beast?
          If you can use data structure arrays, then you could have one DSarray in which to load the data from the fetch, and another DS Array that contains all of the records loaded so far. ( Like a cache) Then you can position the DSarray cache backwards and reload the subfile from this when you page up. You would also do this on a page down unless the current position in the array would require you to perform another load. (In which case you would do the fetch, then load the fetch Data Structure into the Cache data structure. Then reposition the cache and load. )

          You can do this with multiple occurance data structures as well. Its just not as slick, especially when you can couple it with the more powerful techniques such as using the data structures on file IO and utilizing the *ALL option on the likerec keyword.
          Michael Catalani
          IS Director, eCommerce & Web Development
          Acceptance Insurance Corporation
          www.AcceptanceInsurance.com
          www.ProvatoSys.com

          Comment


          • #6
            Re: Page at a time subfile with SQL

            You can update, insert using them, but no compile with fetch....

            Thank you again, because I have defined my cursor as scrolling, I would think there would be a way to
            set the cursor up (in my example 20 records) there are keywords like ABSOLUTE & RELATIVE that look like
            they might work, but I can't seem to make them.

            jamie
            All my answers were extracted from the "Big Dummy's Guide to the As400"
            and I take no responsibility for any of them.

            www.code400.com

            Comment


            • #7
              Re: Page at a time subfile with SQL

              Originally posted by jamief View Post
              Thanks for taking the time Michael to help me out....

              I'm on v5r3 --- I dont think I can use a datastructure... Can I?

              ...
              Yes, you can use an ADS (instead of a MODS) as of v5r3.
              Regards

              Kit
              http://www.ecofitonline.com
              DeskfIT - ChangefIT - XrefIT
              ___________________________________
              There are only 3 kinds of people -
              Those that can count and those that can't.

              Comment


              • #8
                Re: Page at a time subfile with SQL

                Kit so you have a v5r3 box ?
                I should be more clear I can use a defined DS, but once I hang "DIM" on it.. It no compile!
                If you have v5r3 box would you test this?

                thank you
                Jamie
                All my answers were extracted from the "Big Dummy's Guide to the As400"
                and I take no responsibility for any of them.

                www.code400.com

                Comment


                • #9
                  Re: Page at a time subfile with SQL

                  Yes... I'll send you something later when I get home.
                  Regards

                  Kit
                  http://www.ecofitonline.com
                  DeskfIT - ChangefIT - XrefIT
                  ___________________________________
                  There are only 3 kinds of people -
                  Those that can count and those that can't.

                  Comment


                  • #10
                    Re: Page at a time subfile with SQL

                    I just retested and it still not working here.
                    with only changing from dim to occurs and back.
                    compiles each time with occurs, fails with dim.

                    Thank you

                    jamie
                    All my answers were extracted from the "Big Dummy's Guide to the As400"
                    and I take no responsibility for any of them.

                    www.code400.com

                    Comment


                    • #11
                      Re: Page at a time subfile with SQL

                      Hi Jamie:

                      Check this out with fetch relative:


                      Best of Luck
                      GLS
                      The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

                      Comment


                      • #12
                        Re: Page at a time subfile with SQL

                        That would work much cleaner than mine..
                        but doesnt it seem odd..you have to fetch records to position the cursor.

                        It would make sense (to me) that there would be something like SETLL that allows
                        you to move the cursor but not retrieve the data with a fetch.

                        jamie
                        All my answers were extracted from the "Big Dummy's Guide to the As400"
                        and I take no responsibility for any of them.

                        www.code400.com

                        Comment


                        • #13
                          Re: Page at a time subfile with SQL

                          Are you sorted now Jmaie?
                          Regards

                          Kit
                          http://www.ecofitonline.com
                          DeskfIT - ChangefIT - XrefIT
                          ___________________________________
                          There are only 3 kinds of people -
                          Those that can count and those that can't.

                          Comment


                          • #14
                            Re: Page at a time subfile with SQL

                            This has been a long thread....Tons of good info --Thank you ALL for contributing.

                            But my original question is still here..

                            It would make sense (to me) that there would be something like SETLL that allows
                            you to move the cursor but not retrieve the data with a fetch.

                            Thanks
                            Jamie
                            All my answers were extracted from the "Big Dummy's Guide to the As400"
                            and I take no responsibility for any of them.

                            www.code400.com

                            Comment

                            Working...
                            X