IBM i Global

 View Only
Expand all | Collapse all

Sql External table function

  • 1.  Sql External table function

    Posted Fri September 30, 2022 06:10 AM
    Edited by Paolo Salvatore Fri September 30, 2022 06:11 AM
    Hi, 

    I need a table with one row per day for one year, I try with an external function that call an RPG and return a DS dim(365). But something doesn't work, I create a sample:

    Module:

    **free
    Ctl-Opt
      NoMain
      Expropts(*AlwBlankNum)
      Option(*NoUnRef :*SrcStmt :*NoDebugIo)
      DatFmt(*Iso) TimFmt(*Iso)
      Debug(*Constants)
      AlwNull(*UsrCtl)
      DftName(TESTSRVPGM)
      Text('Modulo per service program TESTSRVPGM');
    // ____________________________________________________________________________
    Dcl-Proc GetNextYear Export;
      Dcl-Pi GetNextYear LikeDs(NextYear_Template) Dim(365);
        StartDate       Packed(8 :0) Const;    // Data di Partenza
      End-Pi GetNextYear;
      // ____________________________________________________________________________
      Dcl-Ds NextYear LikeDs(NextYear_Template) Dim(365);
      Dcl-S Idx        Int(5);
      // ____________________________________________________________________________
      Dcl-Ds NextYear_Template Qualified Template Dim(365);
        Data           Packed(8);
      End-Ds NextYear_Template;
    
    
      Reset NextYear;
      NextYear(1).Data = StartDate;
    
      For Idx = 2 to 365;
        NextYear(Idx).Data =  %Dec(%Date(NextYear(Idx - 1).Data) + %Days(1));
      EndFor;
    
      Return NextYear(*);
    
    End-Proc GetNextYear;



    Compilation command:

    CRTRPGMOD MODULE(MYLIB/testsrvpgm) SRCSTMF('./qrpglesrc/testsrvpgm.rpgmod') OPTION(*EVENTF) TGTCCSID(*JOB)

    Then I create a service program:

    CRTSRVPGM SRVPGM(MYLIB/TESTSRVPGM) EXPORT(*ALL)

    Sql Function:

    Create Or Replace Function MYLIB/Getnextyear (
          Datapartenza decimal(8, 0)
      )
      Returns Table (
        Datanext Decimal(8, 0)
      )
      External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)'
      Language Rpgle
      PARAMETER STYLE SQL
      No Sql 
      DETERMINISTIC
      NO EXTERNAL ACTION
      NOT FENCED
      SCRATCHPAD
      FINAL CALL
      DISALLOW PARALLEL
      CARDINALITY 365;

    Then trying in strsql I, it return to me MCH3601

    Select *                                          
      From Table (                                    
         MYLIB/getnextyear(20220930)                 
        )                                             


    Any Idea? 
    Is it possible what I'm trying to do? 
    Do you have a better idea?

    Many thanks



    ------------------------------
    Paolo Salvatore
    ------------------------------


  • 2.  RE: Sql External table function

    Posted Fri September 30, 2022 09:15 AM

    Hi, 

    looking to Scott Klement site I found this:
    https://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%20&%20Table%20Functions.pdf

    So I change my procedure to this:

    **free
    Ctl-Opt
      NoMain
      Expropts(*AlwBlankNum)
      Option(*NoUnRef :*SrcStmt :*NoDebugIo)
      DatFmt(*Iso) TimFmt(*Iso)
      Debug(*Constants)
      AlwNull(*UsrCtl)
      DftName(TESTSRVPGM)
      Text('Modulo per service program TESTSRVPGM');
    // ____________________________________________________________________________
    Dcl-Proc GetNextYear Export;
      Dcl-Pi GetNextYear;
        StartDate       Date Const;       // Input Data
        ReturnDate      Date;             // Return Data
        n_StartDate     Int(5) Const;     // Input Data null
        n_ReturnDate    Int(5);           // Return Data null
        State           Char(5);
        Function        VarChar(517) Const;
        Specific        VarChar(128) Const;
        errorMsg        VarChar(1000);
        CallType        Int(10) Const;
      End-Pi GetNextYear;
      // ____________________________________________________________________________
      Dcl-C CALL_OPEN    -1;
      Dcl-C CALL_FETCH    0;
      Dcl-C CALL_CLOSE    1;
      Dcl-C PARM_NULL    -1;
      Dcl-C PARM_NOTNULL  0;
    
      Dcl-S Idx          Int(5);
    
      If (n_StartDate=PARM_NULL);
        State = '38999';
        errorMsg = 'StartDate is mandatory';
        return;
      EndIf;
    
    
      Select;
        When (CallType = CALL_OPEN);
          ReturnDate = StartDate;
          Idx = 0;
    
        When (CallType = CALL_FETCH);
          ReturnDate += %Days(1);
          Idx += 1;
          If (Idx = 365);
            State = '02000';
            Return;
          EndIf;
    
        When (CallType = CALL_CLOSE);
          State = '00000';
          Return;
    
      EndSl;
    
    End-Proc GetNextYear;


    Then I recreate the service program:

    CRTSRVPGM SRVPGM(MYLIB/TESTSRVPGM) EXPORT(*ALL)

    and the function:

    Create Or Replace Function MyLib/Getnextyear (
          Datapartenza Date
      )
      Returns Table (
        Datanext Date
      )
      External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)'
      Language Rpgle
      Program Type Sub
      Parameter Style Db2sql
      No Sql
      Not Deterministic
      Disallow Parallel;
      


    But something doesn't work, it never pass from  the statement:

    When (CallType = CALL_OPEN);
    ReturnDate = StartDate;
    Idx = 0;
    why?

    Many thanks



    ------------------------------
    Paolo Salvatore
    ------------------------------



  • 3.  RE: Sql External table function

    Posted Fri September 30, 2022 10:56 AM
    Edited by Paolo Salvatore Fri September 30, 2022 10:56 AM
    Hi, 

    I found a solutions, but I don't know if it is correct... 

    Here's the new code:

    **free
    Ctl-Opt
      NoMain
      Expropts(*AlwBlankNum)
      Option(*NoUnRef :*SrcStmt :*NoDebugIo)
      DatFmt(*Iso) TimFmt(*Iso)
      Debug(*Constants)
      AlwNull(*UsrCtl)
      DftName(TESTSRVPGM)
      Text('Modulo per service program TESTSRVPGM');
    
    Dcl-S GetNextYearIdx              Int(5);
    Dcl-S GetNextYearDate             Date;
    // ____________________________________________________________________________
    Dcl-Proc GetNextYear Export;
      Dcl-Pi GetNextYear;
        StartDate       Date;             // Input Data
        DaysToAdd       Int(10);           // Input Data
        ReturnDate      Date;             // Return Data
        n_StartDate     Int(5) Const;     // Input Data null
        n_DaysToAdd     Int(5);           // Input Data
        n_ReturnDate    Int(5);           // Return Data null
        State           Char(5);
        Function        VarChar(517) Const;
        Specific        VarChar(128) Const;
        errorMsg        VarChar(1000);
        CallType        Int(10) Const;
      End-Pi GetNextYear;
      // ____________________________________________________________________________
      Dcl-C CALL_OPEN    -1;
      Dcl-C CALL_FETCH    0;
      Dcl-C CALL_CLOSE    1;
      Dcl-C PARM_NULL    -1;
      Dcl-C PARM_NOTNULL  0;
    
      // ____________________________________________________________________________
    
      Select;
        When (CallType = CALL_OPEN);
          If (n_StartDate = PARM_NULL);
            StartDate = %Date();
          EndIf;
          If (n_DaysToAdd = PARM_NULL);
            DaysToAdd = 365;
          EndIf;
          GetNextYearDate = StartDate;
          GetNextYearIdx = 0;
    
        When (CallType = CALL_FETCH);
          ReturnDate = GetNextYearDate;
          ReturnDate += %Days(1);
          If (GetNextYearIdx >= DaysToAdd);
            State = '02000';
            Return;
          EndIf;
          GetNextYearIdx += 1;
          GetNextYearDate = ReturnDate;
    
        When (CallType = CALL_CLOSE);
          Reset GetNextYearDate;
          Reset GetNextYearIdx;
          State = '00000';
          Return;
    
      EndSl;
    
    End-Proc GetNextYear;​


    And the new function:

    Create Or Replace Function mylib/Getnextyear (
          Datapartenza Date, DaysToAdd Int
      )
      Returns Table (
        Datanext Date
      )
      External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)'
      Language Rpgle
      Program Type Sub
      Parameter Style sql
      No Sql
      Not Deterministic
      Disallow Parallel;

    With this review it works, but I have a problem with variabile GetNextYearDate, GetNextYearIdx.

    Is it correct to define for global scope? If I declare this variable in my subprocedure, every time the function is called those variables are initialized, so is there some trick?

    Many thanks



    ------------------------------
    Paolo Salvatore
    ------------------------------



  • 4.  RE: Sql External table function

    IBM Champion
    Posted Mon October 03, 2022 02:38 AM
    Hi Paolo.

    You can add the keyword STATIC to any variable inside a subprocedure, and the value will be kept between calls.

    So you can move GetNextYearDate and GetNextYearIdx into procedure GetNextYear and just add STATIC. They will be initialized on the first call.
    It is a good practice to avoid global variables if possible.

    Best regards,
    Christian

    ------------------------------
    Christian Jorgensen
    IT System Administrator
    Network of Music Partners A/S
    ------------------------------



  • 5.  RE: Sql External table function

    0