Product Srch Locs table using RPGSQL loading Array

Discussions relating to writing software in ILE RPG (RPG IV). This includes both fixed and free format RPG.
Post Reply
mrossRpg
Posts: 3
Joined: Wed Oct 27, 2021 10:06 pm

Product Srch Locs table using RPGSQL loading Array

Post by mrossRpg »

Scott, I'v searched and exhausted resources I'm urgently in need of help to resolve getting data from a table load data into a data structure using Search Criteria filters String there are 4 2 data structure (PDDTA and Vparts) arrays never get loaded with data from table. If I run a regular SQL Select I get records. also I run the program in DEBUG with service point entry in RDI with breaks in code to check values

Code: Select all

     Dcl-F EDIMIKPRD Disk Usage(*Input:*Output) Keyed Qualified UsrOpn;

Data Structure:
        Dcl-DS  PRDDTA  DIM(100) LEN(150) Qualified;
               Itmid   Char(18);
               Upccd   Char(12);
               Itmdesc Char(40);
               Altdesc Char(40);
               Mikmino Char(10);
               MIKSKU  Char(10);
               Onhand  Zoned(9:2);
               Price   Zoned(9:2);
        END-DS;

        Dcl-ds VParts  Dim(100) LEN(148) qualified;
               Itmid   Char(18);
               Upccd   Char(12);
               Itmdesc Char(40);
               Altdesc Char(40);
               Mikmino Char(10);
               MikSKU  Char(10);
               Onhand  Zoned(9:2);
               Price   Zoned(9:2);
        End-Ds VParts;


1.           if  SavRFilter = 'B';  
// Begins with 
             Eval  SrchDesc = '%' + TRIM(SrchDesc) + '%';

              EXEC Sql
              Declare ProdsReq cursor for

                Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
                        Miksku, Onhand, Price
                  From MIKPOC/EDIMikPRD Where
                        CONTAINS(Itmdesc, Trim(:SrchDesc)) = 1;

               Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
               EXEC SQL OPEN ProdsReq;

               EXEC SQL
               FETCH FROM ProdsReq for :RowCnt rows INTO :PRDDTA;
                      if (Ctr <= SavRMaxrtn) and (PRDDTA(x).Itmid > *blanks);
                        Eval VParts(X).Itmid= PRDDTA(x).Itmid;
                        Eval VParts(X).Upccd = PRDDTA(x).Upccd;
                        Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
                        Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
                        Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
                        Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
                        Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
                        Eval Vparts(X).Price = PRDDTA(x).Price;
                        Eval RtnRows = RtnRows + 1;
                        Eval Matched = Matched + 1;
                        Eval Ctr = Ctr +1;
                        Eval X = X + 1;
                      Endif;
          Endif;

2. If SavRFilter = 'C';   //Contains
    Eval    SrchDesc = '_' + %TRIM(SrchDesc) + '_';
              EXEC Sql
              Declare ProdsReq2 cursor for
                Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
                        Miksku, Onhand, Price
                  From MIKPOC/EDIMikPRD Where Itmdesc
                  Like Trim(:SrchDesc);

               Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
              EXEC SQL OPEN ProdsReq2;

               EXEC SQL
               FETCH FROM ProdsReq2 for :RowCnt rows INTO :PRDDTA;
                      if Ctr <= SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
                        Eval VParts(X).Itmid= PRDDTA(x).Itmid;
                        Eval VParts(X).Upccd = PRDDTA(x).Upccd;
                        Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
                        Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
                        Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
                        Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
                        Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
                        Eval Vparts(X).Price = PRDDTA(x).Price;
                        Eval RtnRows = RtnRows + 1;
                        Eval Matched = Matched + 1;
                        eval Ctr = Ctr +1;
                        Eval X = X + 1;
                      Endif;
          Endif;

          If SavRFilter = 'C';   //Contains
             EXEC SQL  Close ProdsReq2;
          Endif;


          If SavRFilter = 'E';  // Exact
              eval  NbrOfRows = 50;
              eval    Matched = 0;
              eval    RtnRows = 0;
              eval    X = 1;

              EXEC Sql
              Declare ProdsReq3 cursor for
                 Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
                        Miksku, Onhand, Price
                  From MIKPOC/EDIMikPRD Where Itmdesc
                       Like :SrchDesc;

               Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
                EXEC SQL  OPEN ProdsReq3;

               EXEC SQL
               FETCH FROM ProdsReq3 for :RowCnt rows INTO :PRDDTA;
                    // verify beigins with
                      if Ctr <= SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
                        Eval Matched = Matched + 1;
                        Eval VParts(X).Itmid= PRDDTA(x).Itmid;
                        Eval VParts(X).Upccd = PRDDTA(x).Upccd;
                        Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
                        Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
                        Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
                        Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
                        Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
                        Eval Vparts(X).Price = PRDDTA(x).Price;
                        Eval RtnRows = RtnRows + 1;
                        Eval Matched = Matched + 1;
                        Eval Ctr = Ctr +1;
                        Eval X = X + 1;
                      Endif;
          Endif;


          If SavRFilter = 'E';  // Exact
          EXEC SQL  Close ProdsReq3;
          Endif;

          If SavRFilter = 'K';  // Keyword
              eval  Matched = 0;
              eval  RtnRows = 0;
                  X = 1;

                //  Read EDIMikPRD VParts;  //SrchDesc Like Trim(:SrchDesc);
              EXEC Sql
              Declare ProdsReq4 cursor for
                Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
                        Miksku, Onhand, Price
                  From MIKPOC/EDIMikPRD Where Itmdesc
               Like Trim(:SavRString) or
                          AltDesc LIKE Trim(:SavRString);

               Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
               EXEC SQL OPEN ProdsReq4;

               EXEC SQL
               FETCH FROM ProdsReq4 for :RowCnt rows INTO :PRDDTA;

                      if Ctr < SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
                        Eval Matched = Matched + 1;
                        Eval VParts(X).Itmid= PRDDTA(x).Itmid;
                        Eval VParts(X).Upccd = PRDDTA(x).Upccd;
                        Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
                        Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
                        Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
                        Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
                        Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
                        Eval Vparts(X).Price = PRDDTA(x).Price;
                        Eval RtnRows = RtnRows + 1;
                        Eval Matched = Matched + 1;
                        Eval X = X + 1;
                        Eval Ctr = Ctr +1;
                        Eval PassFail = 'P';
                      Endif;
          Endif;

          If SavRFilter = 'K';  // Keyword
          EXEC SQL  Close ProdsReq4;
          Endif;
Scott then in the end I'm working with the arrays stored values from the select and Vparts vaule are always blank because PRDDTA is blank

Code: Select all

          For i = 1 to %ELEM(VParts);
              if (PRDDTA(I).MOTSKU <> *blank) or (VParts(i).ITMID <> *blank);
               RXS_ComposeVariable( V.VENDOR_PART : %Trim(VParts(i).MikSKU) );
               RXS_ComposeVariable( V.VENDOR_ITEM : %Trim(VParts(i).ITMID) );
               RXS_ComposeVariable( V.VENDOR_UPC_NO : %Char(VParts(i).UPCCD) );
              RXS_ComposeVariable( V.MINO : %Trim(VParts(i).MikMINO) );
               RXS_ComposeVariable( V.ITEM_NO : %Trim(VParts(i).ITMID) );
            If PassFail = 'P';
               RXS_ComposeVariable( V.UNIT_PRICE : %Char(VParts(i).PRICE) );
               RXS_ComposeVariable( V.QTY_AVL : %Char(VParts(i).ONHAND) );
            endif;
               RXS_ComposeVariable( V.VENDOR_DESC : %Trim(VParts(i).ITMDESC) );
               RXS_ComposeVariable( V.POR : %Trim(POR) );
               RXS_ComposeVariable( V.PHONE_FLAG : %Trim(PHONE_FLAG) );
               RXS_ComposeVariable( V.MESSAGE : %Trim(MESSAGE) );
               RXS_ComposeSection( S.PROD_SRCHR_ITMDTL );
              endif;
          EndFor;
               RXS_ComposeSection( S.PROD_SRCHR_ITMDTL_FOOTER );

              //Close the cursor
               RXS_ComposeSection( S.PROD_SRCHR_ITMHDR_FOOTER );
              RXS_ComposeVariable( V.NO_MATCHES : %Char(Matched) );
              RXS_ComposeVariable( V.ROWS_RETURN : %Char(Ctr) );
              RXS_ComposeSection( S.PROD_SRCHR_FOOTER );

              XMLResponse = RXS_GetComposeBuffer();

              // Write the response XML to the IFS
              RXS_ResetDS( PutStmfDS : RXS_DS_TYPE_PUTSTMF );
              PutStmfDS.Stmf = %Trim(pXMLResponseFile);
              RXS_PutStmf( XMLResponse : PutStmfDS );
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Product Srch Locs table using RPGSQL loading Array

Post by Scott Klement »

Hi,

What do you mean when you say "If I run a regular SQL Select I get records."? These look pretty "regular"? I'm trying to think about what the difference might be, but not sure what you're doing when it works.

You say that you're debugging it with RDi. When it runs and you step through the code, what do you notice being wrong?

You include a whole bunch of subprocedure calls like RXS_ComposeSection() and other RXS_xxx procedures. Are these relevant to the problem you're having? I've never seen these before, and don't have any idea what they do.
mrossRpg
Posts: 3
Joined: Wed Oct 27, 2021 10:06 pm

Re: Product Srch Locs table using RPGSQL loading Array

Post by mrossRpg »

Question 1: What do you mean when you say "If I run a regular SQL Select I get records."? These look pretty "regular"? I'm trying to think about what the difference might be, but not sure what you're doing when it works

Answer 1: If I run a SQL Select on the Ibm i database Tool like Surveyor 400 but replace Trim(:SrchDesc) with this liter %CLK% in RDI Debug I've add Break just before this select to see what vaule is in :SrchDesc and it contains %CLK% Surveyor DB tool returns several rows of records CLK is in Itemdesc.

Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino, Miksku, Onhand, Price From MIKPOC/EDIMikPRD Where CONTAINS(Itmdesc, Trim(:SrchDesc)

Question 2: You say that you're debugging it with RDi. When it runs and you step through the code, what do you notice being wrong?

Answer 5. When Stepping through the code on the break line after the Select statement
Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ; mouseing over RowCnt to see the value of returned records it equals 0 so stepping through to
if (Ctr <= SavRMaxrtn) and (PRDDTA(x).Itmid > *blanks); mouseing over (PRDDTA(x).Itmid it is blank therefore nothing is being populated

Question 3. You include a whole bunch of subprocedure calls like RXS_ComposeSection() and other RXS_xxx procedures. Are these relevant to the problem you're having? I've never seen these before, and don't have any idea what they do.

Answer: Those lines of code never get executed they are not relevant to no data being retrieved via EXEC SQL's

ex. if I hover over (PRDDTA(x).Itmid on RDI line Break it is blank and if I hover over RowCnt it is 0 RowCnt should be 25 rows ItmDesc according to running
EXEC SQL
FETCH FROM ProdsReq for :RowCnt rows INTO :PRDDTA;
if (Ctr <= SavRMaxrtn) and (PRDDTA(x).Itmid > *blanks);
Eval VParts(X).Itmid= PRDDTA(x).Itmid;

I'm available to do gotomeeting screen share if need be just email me if need be. Thanks In Advance.
rogersonra
Posts: 1
Joined: Thu Jul 29, 2021 1:08 am

Re: Product Srch Locs table using RPGSQL loading Array

Post by rogersonra »

GET DIAGNOSTICS to get the row count should be after the FETCH and not after the DECLARE CURSOR should it not?
And also, have you checked the sqlstate immediately following the FETCH? An sqlstate other than '00000' may indicate an error in your cursor declaration.

Rob
mrossRpg
Posts: 3
Joined: Wed Oct 27, 2021 10:06 pm

Re: Product Srch Locs table using RPGSQL loading Array

Post by mrossRpg »

Thanks I will check that..
Post Reply