ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

dynamic sql - receive fetched row values into host variables

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

  • dynamic sql - receive fetched row values into host variables

    attached is a small standalone pgm that anyone could run to help me out with this. ( you would need the file object however for actual execution).
    but will compile and allow you to see what I'm attempting.

    I'm trying my best to build a cursor over a pre-constructed sql statement (file/columns) unknown... but for sake of sample pgm, i have hardcoded a statement in the host variable.
    I then want to fetch the cursor for that sql statement and process the fetched row (does this come from sqlda/sqlvar's?) and populate a host variable ds. if so, how do they get processed? i've read and attempted various methods, none with successful results, so hoping for some code specific for my processing.

    And one other piece of confusion regarding my current code in attachment, when the fetch occurs, on my system, the record does indeed exist... sqlcode is set to 0, BUT if I look a the joblog, it says 0 records fetched.

    I'm confused... can someone please help me with this piece of a much bigger application?
    Attached Files

  • #2
    Here is an example with that works very well. Depending on the parameter value different columns are selected from the same page and the result will be be ordered in different sequences.
    Since the table (as in your example) is known, it is possible to associate the data pointers directly with the output host variables (FOR Loop), no need to check the data types.
    Code:
         /If Defined (*CRTBNDRPG)                                                                                          
            Ctl-Opt DftActGrp(*No) ActGrp('EMBDYNCDA');                                                                    
          /EndIf                                                                                                            
           //*********************************************************************************************                
            DCL-S DsplyText   Char(50);                                                                                    
    
            DCL-DS PGMSDS PSDS;                                                                                            
              SDSMsgTextK     Char(50) Pos(91);                                                                            
            End-Ds;                                                                                                        
    
            DCL-C SQL_NUM Const(10);                                                                                        
            Exec SQL  Include SQLDA;                                                                                        
            //*********************************************************************************************                
            EXEC SQL Set Option DatFmt = *ISO, Commit = *NONE,                                                              
                                CloSQLCsr = *ENDACTGRP;                                                                    
             Monitor;                                                                                                      
    
                DynSalesCustItemYearSort('A');                                                                                
                DynSalesCustItemYearSort('J');                                                                                
                DynSalesCustItemYearSort('K');                                                                                
    
             On-Error;                                                                                                      
                Dsply SDSMsgTextK;                                                                                          
             EndMon;                                                                                                        
    
             *InLR = *On;                                                                                                  
            //*********************************************************************************************                
            // Determine Sales per Customer / ItemNo, Year  --> Variable Columns and Sorts                                      
            //*********************************************************************************************                
            DCL-Proc DynSalesCustItemYearSort;                                                                                
    
              DCL-PI    *N;                                                                                                
                ParSort Char(1) Const;                                                                                      
              End-Pi;                                                                                                      
    
              DCL-S CmdSQL     Varchar(1024);                                                                              
              DCL-S Index      Uns(3);                                                                                      
              DCL-S DsplyText  Char(50);                                                                                    
    
              DCL-DS DSC1      Inz;                                                                                        
                C1Customer     Char(15);                                                                                    
                C1Item         Char(22);                                                                                    
                C1Year         Int(10);                                                                                    
                C1Total        Packed(11: 2);                                                                              
              End-Ds;                                                                                                      
             //---------------------------------------------------------------------------------------------                
             DsplyText = 'OrderBy : ' + ParSort;                                                                        
             Dsply DsplyText;                                                                                              
    
             Select;                                                                                                        
             When ParSort = 'A';                                                                                            
                  CmdSQL = 'Select ItemNo, Year(SalesDate) as SalesYear,         +                                          
                                   Cast(Sum(Amount) as Dec(11, 2)) as Total      +                                          
                              From Sales                                         +                                          
                              Group By ItemNo, Year(SalesDate)                   +                                          
                              Order By ItemNo, Year(SalesDate)';                                                        
    
             When ParSort = 'J' or ParSort = *Blanks;                                                                      
                  CmdSQL = 'Select Year(SalesDate) as SalesYear,                 +                                          
                                   Cast(Sum(Amount) as Dec(11, 2)) as Total      +                                          
                              From Sales                                         +                                          
                              Group By Year(SalesDate)                           +                                          
                              Order By Year(SalesDate)';                                                                    
    
             When ParSort = 'K';                                                                                            
                  CmdSQL = 'Select CustNo, ItemNo, Year(SalesDate) as SalesYear, +                                          
                                   Cast(Sum(Amount) as Dec(11, 2)) as Total      +                                          
                              From Sales                                         +                                          
                              Group By CustNo, ItemNo, Year(SalesDate)           +                                          
                              Order By CustNo, ItemNo, Year(SalesDate)';                                                
             EndSL;                                                                                                        
    
             DsplyText = CmdSQL;                                                                                            
             Dsply DsplyText;                                                                                              
    
             EXEC SQL  Close C1;                                                                                            
             Exec SQL  Prepare DynC1 From :CmdSQL;                                                                          
             If SQLCODE < *Zeros;                                                                                          
                //Error Handling                                                                                        
                Return;                                                                                                    
             EndIf;                                                                                                        
    
             Exec SQL  Describe DynC1 into :SQLDA using System Names;                                                      
             If SQLCODE < *Zeros;                                                                                          
                //Error Handling                                                                                          
                Return;                                                                                                    
             EndIf;                                                                                                        
    
            For Index = 1 to SQL_NUM;                                                                                      
                SQLVAR = SQL_VAR(Index);                                                                                    
                Select;                                                                                                    
                When SQLName = 'CUSTNO';                                                                                  
                     SQLData = %Addr(C1Customer);                                                                              
                When SQLName = 'ITEMNO';                                                                                
                     SQLData = %Addr(C1Item);                                                                            
                When SQLName = 'SALESYEAR';                                                                                      
                     SQLData = %Addr(C1Year);                                                                              
                When SQLName = 'TOTAL';                                                                                    
                     SQLData = %Addr(C1TOTAL);                                                                            
                Other;                                                                                                      
                     Clear SQLVAR;                                                                                          
                EndSL;                                                                                                      
                SQLIND = %Addr(SQLCODE);                                                                                    
                SQL_VAR(Index) = SQLVAR;                                                                                    
            EndFor;                                                                                                        
    
             Exec SQL  Declare C1 Cursor For DynC1;                                                                        
    
             EXEC SQL  Open  C1;                                                                                            
             If SQLCODE < *Zeros;                                                                                          
                //Error Handling                                                                                        
                Return;                                                                                                    
             EndIf;                                                                                                        
    
            DoU 1=0;                                                                                                        
    
                EXEC SQL Fetch Next From C1 using Descriptor :SQLDA;                                                        
                If SQLCODE = 100;                                                                                          
                   Leave;                                                                                                  
                ElseIf SQLCODE < *Zeros;                                                                                    
                   //Error Handling                                                                                      
                   leave;                                                                                                  
                EndIf;                                                                                                      
    
                DsplyText = (%Char(C1Year) + ' / ' +  %Trim(C1Customer)   + ' / ' +                                        
                             %Trim(C1Item) + ' / ' +  %Char(C1Total));                                                  
                Dsply DsplyText;                                                                                            
    
             EndDo;                                                                                                        
    
             EXEC SQL  Close C1;                                                                                            
             If SQLCODE < *Zeros;                                                                                          
                //Error Handling                                                                                        
                Return;                                                                                                    
             EndIf;                                                                                                        
             Return;                                                                                                        
           End-Proc;
    BTW if the table/view is known from which is read and the sequence of the columns will be always the same, using the SQLDA is not necessary, i.e. the information can be directly fetched into the host structure.
    Something like this.
    Code:
      CMDSQL = 'SELECT COL1, COL2, ... COLN From ....'
      Exec SQL Pepare DynSQL1 From :CMDSQL;
      Exec SQL Declare C1 Cursor For DynSQL1;
      Exec SQL Open C1;
      Dou 1=0;
        Exec SQL Fetch Next From C1 into :HostDS;
        If SQLCODE = 100 or SQLCODE < *Zeros;
           Leave;
        EndIf;
        //Do whatever you want
      EndDo;
      Exec SQL Close C1;
    Birgitta
    Last edited by B.Hauser; October 16, 2017, 11:01 PM.

    Comment

    Working...
    X