Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (1)

Resolved CURSOR Query

  1. How-to
0
Votes
Undo
  1. Olan Knight
    Olan Knight
  2. PowerBuilder
  3. Thursday, 14 July 2022 16:35 PM UTC

PowerBuilder 2019R#, b2703
Windows 64 bit platform


   Below in BROWN text is my code to call a PostgreSQL stored procedure using a CURSOR; the CURSOR code is in BLUE. This code is not working, I’m getting this on the OPEN call:


Research says this is most likely a SYNTAX error, however the stored procedure DOES work when you call it as follows in PGADMIN:
    exec sp_test_glextract_master('21-JUN-2022');

    

   Notice the output is a series of strings. I want to capture those lines of output and display them in a DW, thus the CURSOR.


   Here’s my code:
boolean    lb_connected
date        ldt_date
long        ll_rc, ll_new
string    ls_hdr, ls_err, ls_out, ls_date



lb_connected = sqlcG.of_isconnected( )
IF (NOT lb_connected) THEN
    ls_hdr = "Not Connected to xxxV5"
    ls_err = "We are not connected to the xxxV5 (PG) database."         +  &
           "Please connect to the xxxV5 (pg) database, and try again."    
    GOTO Exit_script
END IF


// Get the RUN DATE; the format required is 'dd-MON-YYYY'
// - here's the code in the SP:   vrun_date := to_date(vIn_Date, 'dd-MON-YYYY');
ls_date = sle_date.Text

// Convert from dd/mm/yyyy to dd-mmm-yyyy for the SP
ldt_date = Date (ls_date)
ls_date  = UPPER (String (ldt_date, 'DD-MMM-YYYY'))


// Start the dynamic call to run the stored procedure
DECLARE seq_cursor1 CURSOR FOR sp_test_glextract_master (:ls_date)
USING   sqlcG;

// Clear the dw of previous msgs
ll_rc = dw_msgs.Reset ()


// Open the cursor to start the stored procedure
OPEN  seq_cursor1;
ll_rc = sqlcG.sqlCode

DO WHILE (ll_rc >= 0)

    // Get the next line of output
    FETCH seq_cursor1 INTO :ls_out;
    
    // Check the status
    ll_rc = sqlcG.sqlCode
    IF (ll_rc >= 0) THEN
    
        // Write the output to the DW
        ll_new = dw_msgs.InsertRow (0)
        ll_rc  = dw_msgs.SetItem (ll_new, "msg", ls_out)
        
        ll_rc  = dw_msgs.ScrollToRow (ll_new)
    
    END IF
    
LOOP

// Close the cursor
CLOSE seq_cursor1;


// --------------------------------------


Thanks in advance, folks!   


64bit OCX does not work with a 64bit executables
How to calculate "Content-Length" while consuming ...
Responses (1)
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 14 July 2022 18:12 PM UTC
  2. PowerBuilder
  3. # 1

By the way, here's the stored procedure. Notice that the output is created entirely with DBMS_OUTPUT.PUT_LINE('') statements!


        vthe_year       varchar2(4);
        vthe_month      varchar2(3);
        vthe_day        varchar2(2);
        vrun_date       date;
        vrun_date_prev  date;
        vswa_date       date;
        vfa_date        date;
        vpmt_start      date;
        vpmt_end        date;
        vacc_swa_date   date;
        vpost_date      char(11);
        vbill_month     varchar(7);
        vpmt_month      varchar(7);

BEGIN

    -- Check if the user wants to use the current month/date
    IF (LENGTH(vIn_date) < 11) THEN
        vrun_date := cast(SYSTIMESTAMP AS DATE);
        dbms_output.put_line('Using Current : ' || to_char(vrun_date, 'dd-MON-YYYY'));
    ELSE
        vrun_date := to_date(vIn_Date, 'dd-MON-YYYY');
        dbms_output.put_line('Using VIn_Date: ' ||to_char(vrun_date, 'dd-MON-YYYY'));
    END IF;


    -- Get the previous months date (Most dates are the previous month)
        vrun_date_prev :=   ADD_MONTHS(vrun_date, -1);


    -- Pick out the parts of the date to make this more readable
        vthe_year       :=  TO_CHAR(vrun_date_prev, 'YYYY');
        vthe_month      :=  TO_CHAR(vrun_date_prev, 'MON');
        vthe_day        :=  TO_CHAR(vrun_date_prev, 'DD');

    dbms_output.put_line('vrun_date     : ' || to_char(vrun_date, 'dd-MON-YYYY'));
    dbms_output.put_line('vrun_date_prev: ' || to_char(vrun_date_prev , 'dd-MON-YYYY'));
    dbms_output.put_line('vthe_year     : ' || vthe_Year);
    dbms_output.put_line('vthe_month    : ' || vthe_month);
    dbms_output.put_line('vthe_day      : ' || vthe_day);



    --21-mmm-yyyy (Previous month)
        vswa_date       :=  TO_DATE(TO_CHAR( vrun_date_prev, 'yyyymm') || '21', 'yyyymmdd');
        dbms_output.put_line('vswa_date     : ' || to_char(vswa_date , 'dd-MON-YYYY'));


    --21-mmm-yyyy (Current month)
        vacc_swa_date   :=  TO_DATE(TO_CHAR(vrun_date, 'yyyymm') || '21', 'yyyymmdd');
        dbms_output.put_line('vacc_swa_date : ' || to_char(vacc_swa_date , 'dd-MON-YYYY'));

    --1-mmm-yyyy (Previous month)
        vfa_date        :=  TO_DATE(TO_CHAR(vrun_date_prev, 'yyyymm') || '01', 'yyyymmdd');
        vpmt_start      :=  vfa_date;
        dbms_output.put_line('vfa_date      : ' || to_char(vfa_date , 'dd-MON-YYYY'));
        dbms_output.put_line('vpmt_start    : ' || to_char(vpmt_start, 'dd-MON-YYYY'));

    --31-mmm-yyyy (Last day of previous month)
        vpost_date      := TO_CHAR(LAST_DAY(vrun_date_prev), 'dd-MON-YYYY');
        vpmt_end        := LAST_DAY(vrun_date_prev);
        dbms_output.put_line('vpost_date    : ' || vpost_date);
        dbms_output.put_line('vpmt_end      : ' || to_char(vpmt_end, 'dd-MON-YYYY'));

    --m-yyyy (Previous month)
        vbill_Month     :=  TO_CHAR(vrun_date_prev, 'MM-YYYY');
        vpmt_month      :=  vbill_Month;
        dbms_output.put_line('vbill_Month   : ' || vbill_month);
        dbms_output.put_line('vpmt_month    : ' || vpmt_month);


    -- Call the other Procedures

       sp_TEST_GLExtract_Rev(vswa_date, vfa_date, vbill_month);       
       sp_TEST_GLExtract_Rev_Ban(vswa_date, vfa_date, vbill_month);       

       sp_GCI_GLExtract_Pay(vpmt_start, vpmt_end, vpmt_month);
       sp_GCI_GLExtract_Pay_Ban(vpmt_start, vpmt_end, vpmt_month);

       sp_GCI_GLExtract_Accrual(vacc_swa_date, vpost_date, vbill_month);
       sp_GCI_GLExtract_Accrual_Ban(vacc_swa_date, vpost_date, vbill_month);

       sp_GCI_GLExtract_All();   
       commit;


END

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.