dc03kks dc03kks - 16 days ago 6
SQL Question

PowerBuilder 12.5 sql cursors transaction size error

i have a major problem and trying to find a workaround. I have an application in PB12.5 that works on both sql and oracle dbs.. (with a lot of data)

and i m using CURSOR at a point,, but the aplications crashes only in sql. Using debuging in PB i found that the sql connection returs -1 due to huge transaction size. But i want to fetch row by row my data.. is any work around to fetch data like paging?? i mean lets fetch the first 1000 rows next the other 1000 and so on.. i hope that you understand what i want to achieve (to break the fetch process and so to reduce the transaction size if possible) , here is my code

DECLARE trans_Curs CURSOR FOR
SELECT associate_trans.trans_code
FROM associate_trans
WHERE associate_trans.usage_code = :ggs_vars.usage ORDER BY associate_trans.trans_code ;

OPEN trans_Curs;
FETCH trans_Curs INTO :ll_transId;

DO WHILE sqlca.sqlcode = 0
ll_index += 1
hpb_1.Position = ll_index
if not guo_associates.of_asstrans_updatemaster( ll_transId, ls_error) then
ROLLBACK;
CLOSE trans_Curs;
SetPointer(Arrow!)
MessageBox("Update Process", "Problem with the update process on~r~n" + sqlca.sqlerrtext)
cb_2.Enabled = TRUE
return
end if
FETCH trans_Curs INTO :ll_transId;
LOOP
CLOSE trans_Curs;

Answer

First of all thank you FDavidov for your effort, so i managed to do it using dynamic datastore instead of cursor,, so here is my solution in case someone else need this.

    String ls_sql, ls_syntax, ls_err
    Long ll_row
    DataStore lds_info      

    ls_sql = "SELECT associate_trans.trans_code  "  &
                + " FROM associate_trans " &
                + "  WHERE  associate_trans.usage_code = '" + ggs_vars.usage +"' "&
                + " ORDER BY associate_trans.trans_code"
    ls_syntax = SQLCA.SyntaxFromSQL( ls_sql, "", ls_err )

    IF ls_err <> '' THEN
    MessageBox( 'Error...', ls_err )
    RETURN
    END IF

    lds_info = CREATE DataStore
    lds_info.Create( ls_syntax, ls_err )
    lds_info.SetTransObject( SQLCA )
    lds_info.Retrieve( )        

        DO WHILE sqlca.sqlcode = 0 and ll_row <= ll_count               
                FOR ll_row = 1 TO ll_count
                    ll_transId = lds_info.GetItemNumber( ll_row, 'trans_code' )
                        st_process.Text = 'Κίνηση :' + string(ll_transId) + "  (" + string(ll_index) + "/" + string(ll_count) + ")"
                        ll_index += 1
                        hpb_1.Position = ll_index
                        do while yield(); loop
                        if not guo_associates.of_asstrans_updatemaster( ll_transId, ls_error) then
                            ROLLBACK;
                            DESTROY lds_info
                            SetPointer(Arrow!)
                            MessageBox("Update Process", "Problem with the update process on~r~n" + sqlca.sqlerrtext)
                            cb_2.Enabled = TRUE
                            return
                        end if              
                    NEXT
                    DESTROY lds_info                    
        LOOP