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.