Wednesday, February 17, 2016

Stumped, SQLRPGLE nomain module with data structure

I am stumped and have exhausted all clues provided by searching with Google high and low. My current challenge is a big one. Create an SQL nomain module, copybook member, service program that will enable to manage my backend ERP date fields that are eight digit numeric and not true date field. 

I do a lot of CLLE to automate the ERP reports which usually require date ranges. I also have the need to create CLLE programs that can do some date math.

I currently use RDI exclusively and IBMi OS 7.1. I could do all of this with CEEDATE and play with Lillian but where the fun in that?  

All of this could be resolved by the good folks at IBM by giving us the option to use SQL Select or Set to populate a CLLE variable. ;-)

I successfully figured out how to create and compile SQLRPGLE Nomain module. The trick was the compile type option OBJTYPE, this needs to be *module when using the CRTSQLRPGI. The other stumbling point is RPGPOPT type needs to be set to *LVL1 if Data Structure is in your Prototype member.

This is where I am failing, I can make it work until got to the data structure. I am being a little hard headed and I could just fall back to what works passing one parameter per procedure call. But I believe I need to learn this technique for future use.

I managed to create module, prototype, service program, binding directory. I learned I could add DCLPRCOPT to my CLLE to set the binding directory.
So my main CLLE program DYSLSRPTC that needs dates looks like this,


I have GETTODAY working. I believe I can enhance this with *nopass and eliminate the PARM. I do not need to send the procedure data, just need to get back today's date.

My problem arose when I needed to get last quarter beginning and ending dates. I just want to call the procedure and have it return the two dates in one 16 alpha field as yyyymmddyyyymmdd. This seems like a perfect use for prototyping with data structure and pass back as one field.
I have tried many different permeation's with no luck. Currently getting pointer not referenced when debugging. Here is my current prototype source used for /copy.
      * Get previous beginning and ending quarter date yyyymmdd
       dcl-pr getPrvqtr char(16);
        *n likeds(quarterdt);

       dcl-ds quarterdt qualified;
         lbegqtr char(8);
         lendqtr char(8);
Here is my procedure in my nomain module,
       // getPrvqtr returns previous beginning and ending quarter yyyymmdd
       dcl-proc getPrvqtr export;
         dcl-pi *n char(16);
           qtrdt likeds(quarterdt);

       dcl-s last_quarter int(10);
       dcl-s current_quarter int(10);
       dcl-s medate char(8);

       Exec SQL Set :medate = replace(char((current_date -
                       day(current_date) days), iso), '-', '');

        // Last beginning and ending quarter
       Exec SQL Set :current_quarter = quarter(current_date);

       if current_quarter = 1;
         last_quarter = 4;
           last_quarter = current_quarter - 1;

       Exec SQL Set :quarterdt.lbegqtr =
           case :last_quarter
             when 1 then substring(:medate,1,4) concat '0101'
             when 2 then substring(:medate,1,4) concat '0401'
             when 3 then substring(:medate,1,4) concat '0701'
             when 4 then substring(:medate,1,4) concat '1001'

       Exec SQL Set :quarterdt.lendqtr =
           case :last_quarter
             when 1 then substring(:medate,1,4) concat '0331'
             when 2 then substring(:medate,1,4) concat '0630'
             when 3 then substring(:medate,1,4) concat '0930'
             when 4 then substring(:medate,1,4) concat '1231'

       return quarterdt;


Any assistance with this will be greatly appreciated.



1 comment:

  1. Turns out I missed one little thing, you have to recreate the service program every time change the module. I think I have to look into the export(*all) option in the create service program and binder source file.....