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 9.5.0.2 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,

DCLPRCOPT  DFTACTGRP(*NO) ACTGRP(*CALLER)  BNDDIR(R50MODS/CAPITOL)
             CALLPRC    PRC(GETPRVQTR) PARM(' ') RTNVAL(&RTNQTR)
             CALLPRC    PRC(GETTODAY) PARM(' ') RTNVAL(&TODAY)

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);
       end-pr;

       dcl-ds quarterdt qualified;
         lbegqtr char(8);
         lendqtr char(8);
       End-Ds;
                 
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);
         End-Pi;

       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;
         else;
           last_quarter = current_quarter - 1;
       EndIf;

       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'
           end;

       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'
           end;

       return quarterdt;

       End-Proc;                                   

Any assistance with this will be greatly appreciated.

Thanks,


Richard

Saturday, January 30, 2016

DB2 IBM i SQLRPGLE Free and Date conversion

One annoying capability missing from RUNSQL in CLLE is the ability to use Select into a variable. Sure you can create temporary file with create table as and use RCVF to retrieve into our variable but that can be a pain if QTEMP is not where I believe it should be. The QTEMP story is for another day.

I am currently automating month end and daily sales reporting and wanted a better way than CEEDATE and wanted to use SQL. I decided to create a little SQLRPGLE program, when called, would return dates based on current date.

My CLLE programs need last month end date, last beginning quarter, last ending quarter, yesterday, and last month name for report selection criteria.

I took the opportunity to code completely in SQLRPGLE Free. This is my first totally Free program.

There is some default code that I copy from program to program and not used. I wanted to have examples of Free code for future programs.

Feel free (no pun intended) to critic my code. I never claimed to be a programmer but for some reason find myself programming. Go figure.


 ctl-opt option(*nodebugio) dftactgrp(*no) bnddir('QC2LE');
      //********************** M O D I F I C A T I O N S *************************
           //                                                                      **
      //                 Jacksonville, Florida 32223                          **
      //                                                                      **
      // This program returns last month end date, last month name, yesterday,**
      // last beginning quarter and last ending quarter.                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //******************** M O D I F I C A T I O N S *************************
      // Date       Programmer   Description                                  **
      // ---------- ------------ -------------------------------------------- **
      // 01/11/16   Richard Bryant                                            **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //*************************************************************************
      // ------------------------------------- Prototypes
       dcl-pr rtvdate ExtPgm;
         *n char(8);
         *n char(10);
         *n char(9);
         *n char(8);
         *n char(8);
         *n char(8);
       End-Pr;

       dcl-pi *n;
         medate char(8);     // YYYYMMDD Prior month end date
         medatef char(10);   // YYYY-MM-DD Prior month end date
         mename char(9);     // Month name Prior month end name
         lbegqtr char(8);    // YYYYMMDD last beginning quarter date
         lendqtr char(8);    // YYYYMMDD last ending quarter date
         yesterday char(8);  // YYYYMMDD yesterday
       End-Pi;


      // Program Status Data Structure
       dcl-ds PgmDs PSDS;
         pgmnam *PROC;
         prmnbr *PARMS;
         job char(10) pos(244);
         user char(10) pos(254);
       End-Ds;

      // Data Structures

      // Program Constants
       dcl-c crlf x'0d25';  // Carridge return / Line feed
       dcl-c s ' ';         // Space
       dcl-c d '-';         // Dash
       dcl-c @Apostr x'7d'; // '
       dcl-c @Dblqt x'7f';  // "
       dcl-c @Gt x'6e';     // >
       dcl-c @slgt x'616e'; // />

      // Date conversion fields
       dcl-s @numA zoned(6:0) inz(041205);
       dcl-s @dateA date(*YMD) inz(D'2004-12-04');

      // Standalone Variables
       dcl-s fd int(10);
       dcl-s $xml char(512);

       dcl-s @CmdStr char(512) inz;
       dcl-s message char(52) inz;
      *
       dcl-s sdays zoned(3:0);
       dcl-s ts timestamp;
       dcl-s date date;
       dcl-s sdate date;          // Sales date
       dcl-s currdt zoned(8:0);
       dcl-s currtm zoned(6:0);
       dcl-s current_quarter int(10);
       dcl-s last_quarter int(10);

      //************************************************************************
      //    MainLine
      //


       // Set SQL options
       Exec Sql Set Option Datfmt=*Iso, Commit=*None, Closqlcsr=*Endmod;

       ts = %timestamp();        // Get current date and time
       date = %date(ts);         // Load current date field
       currdt = %dec(%date: *ISO); // Current date yyyymmdd
       currtm = %dec(%time: *HMS); // Currrent time

       // Formated last month end character date YYYY_MM_DD
       Exec SQL Set :medatef = replace(char((current_date + 1 Month - DayofMonth
                   (current_date + 1 Month) Days - 1 month), iso), '-', '_');

       // Last month character date YYYYMMDD
       Exec SQL Set :medate = replace(char((current_date + 1 Month - DayOfMonth
                 (current_date + 1 Month) Days - 1 month), iso), '-', '');

       // Last month name
       Exec SQL Set :mename = monthname(current_date + 1 Month - DayOfMonth
                     (current_date + 1 Month) Days - 1 month);

       // Yesterday YYYYMMDD
       Exec SQL Set :yesterday = replace(char((current_date - 1 day),
                    iso), '-', '');

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

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

       Exec SQL Set :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'
           end;

       Exec SQL Set :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'
           end;


       *inlr = *on ;    

2/1/16 - Whoops, my SQL is flawed. Today, 2/1/16, returned previous month end 1/29/16. I found a blog written by Sam Lennon, programmer who opened my eyes to SQL, to figure out where I went wrong. Here is the corrected code...

 // Formated last month end character date YYYY_MM_DD                                         
       Exec SQL Set :medatef = replace(char((current_date -                                         
                               day(current_date) days), iso), '-', '_');                            
                                                                                                    
       // Last month character date YYYYMMDD                                                        
       Exec SQL Set :medate = replace(char((current_date -                                          
                              day(current_date) days), iso), '-', '');                              
                                                                                                    
       // Last month name                                                                           
       Exec SQL Set :mename = monthname(current_date -                                              
                              day(current_date) days);                                              
                                                                                                    
       // Yesterday YYYYMMDD                                                                        
       Exec SQL Set :yesterday = replace(char((current_date - 1 day),                               
                    iso), '-', '');                                                                 
                                                                                                    
       // Last beginning and ending quarter                                                         
       Exec SQL Set :current_quarter = quarter(current_date);                                       
                                                                                                    
       if current_quarter = 1;                                                                      
         last_quarter = 4;                                                                          
         else;                                                                                      
           last_quarter = current_quarter - 1;                                                      
       EndIf;                                                                                       
                                                                                                    
       Exec SQL Set :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'                                       
           end;                                                                                     
                                                                                                    
       Exec SQL Set :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'                                       
           end;                                           


~Richard 


“f u cn rd ths, u cn gt a gd jb n sftwr tstng.” (Anonymous) 
                    

Friday, January 29, 2016

IBM i Access Client Solutions, AWESOME!

I have been running Client Solutions since October and have found it is now a full replacement for Iseries Access and Ops Navigator. The last update added the last missing piece, Run SQL scripts.

For the most part I use Linoma's Surveyor/400 software which is awesome and I really miss it when I have to work on machine it is not licensed on. That's where Client Solutions comes to play, and play well, it now does.


I have to say even over remote VPN client it all works very well. I am starting to use Navigator i for all monitoring replacing green screen.

One gotcha to be aware of. I went to delete 125mb save file from IFS folder and the machine ground to a halt. Not a good thing in the middle of the day. Had to sweat it out for 20 minutes before the machine would respond.

I knew there was a December update that I had not loaded so I downloaded and installed. Install was painless and now have run SQL option.

Planning to wait until off hours on Sunday to try deleting from IFS and see if issue was resolved in last update.

Have a great day!

~Richard

Friday, August 28, 2015

SQL is cool!

SQL is cool!



~ Richard

I really hate this darn machine;
I wish that they would sell it.
It won't do what I want it to,
but only what I tell it.
~Author Unknown

Wednesday, December 17, 2014

Web service call from SQLRPGLE

Yesterdays challenge is to replace a scheduled timed event with a dynamic process. Give's us an opportunity to try REST from RPGLE using new SYSTOOLS HTTPGETCLOB.

The program is simple and just prompts the Web programming to capture funds and invoice order in ERP backend. Responds with OK and if not send me an E-mail.

Very cool and once I managed to get all the special chatters in the right place works great. This can also be done in a CLLE with RUNSQL command and CREATE TABLE AS. I initially blew it in CLLE and reported to IBM could not get it working. They came back a few days later, right after I got RPGLE version working, and informed me that '+' continuation line character had a space before and that was causing SQL to fail with SQLSTT 38000. DUH!

The next step is a SOAP message so we can trade data between web and back end ERP.

I wish I had the time to learn PowerRuby. I believe I can do this with Ruby as well.

My next endeavor is JSON to link back end VAI ERP to DESK.COM. Again I think PowerRuby can help and really need to figure out how to get there.

//******************Begin sub-routines*******************************
       //********************************************************************
       // Run web service and check response
       //********************************************************************

       begsr xxxx;

       // Change Job CCSID to 37
       CmdStr = 'CHGJOB CCSID(37)';
       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       // Call Web Service Action=doInvoicing
       Exec SQL
            SELECT data into :m_data FROM
                   (VALUES(SYSTOOLS.HTTPGETCLOB
                   ('http://xxxxxx.xxxxxxxx.com/xxx/+
                   xxxxxxx.cfm?action=doinvoicing',+
                   '<httpHeader connectTimeout="10000"> +
                   </httpHeader>'))) WS(data);

       // Change Job CCSID to system default
       CmdStr = 'CHGJOB CCSID(65535)';
       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       // If Web service response not OK - send Email
       if m_data <> 'OK';
        exsr email_err;
       EndIf;

       endsr;

        //********************************************************************
        // Send email if failed to get response from Web
        //********************************************************************

       begsr email_err;
        // Build send email command                                                 
         CmdStr = 'SNDMAIL RECIPIENT((RICHARD xxxxxxxx@xxxxxxxx.COM))';
         CmdStr = %trim(CmdStr) + ' SENDER(QCAPIBMI xxxxxx@xxxxxxxxx.COM)';
         CmdStr = %trim(CmdStr) + ' SUBJECT(' + Quote + 'PO Receipt processed';
         CmdStr = %trim(CmdStr) + ' ***Web Capture funds not processed ***';
         CmdStr = %trim(CmdStr) + Quote + ') MESSAGE(' + Quote;
         CmdStr = %trim(CmdStr) + 'EDIARDSCF failed to receive response OK';
         CmdStr = %trim(CmdStr) + ' *** Contact WEB ***';
         CmdStr = %trim(CmdStr) + Quote + ')';

       // Send email                                                               

       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       EndSr;




~Richard

One man's crappy software is another man's full time job. ~Jessica Gaston, 2008