1. René Ullrich
  2. PowerBuilder
  3. Friday, 31 August 2018 05:58 AM UTC

Hi,

I have a datawindow where the user can insert, update and delete rows.

My problem: If the user clicks the save button I won't use the update function because it updates all the changes in a single transaction. I need to update the datawindow row by row so each row is updated in a single transaction.

Background: On update of a single row I have to call external resources (a web service) the will update an external system. I have no control over this external update and it is not transactional.

So my idea:

FOR all rows to insert update or delete

    do the datawindow update for the single row

    do the external update

    IF external update was ok THEN

        COMMIT

        Resetupdate the single datawindow row

    ELSE

        ROLLBACK

        // let the row unupdated

    END IF

NEXT row for update

 

Any idea how to do that in a simple way?

Accepted Answer
Brad Mettee Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 13:53 PM UTC
  2. PowerBuilder
  3. # Permalink

It sounds like you need a full copy of the data so you can discard all rows except the one you want to update. This will eliminate all of the state changes to your current datawindow, and should provide the shortest code path (although copying large quantities of data into the blob & back out to datastore multiple times might be slower).

Conceptually I think this should work. Additional error checking is probably required.

blob lblob_fullstate
long ll_rows, ll_ctr, ll_b4rows, ll_afterrows
datastore ds

ds = create datastore
ds.settransobject(sqlca)

ll_rows = dw.getfullstate(lblob_fullstate)

for ll_ctr = 1 to dw.rowcount()
	// check row modified status, if it's not changed, ignore and go to next
	if dw.getitemstatus(ll_ctr, 0, primary!) = NotModified! then
		continue
	end if
	ds.SetFullState(lblob_fullstate)
	ll_b4rows = ll_ctr - 1
	ll_afterrows = ll_counter + 1

	if ll_afterrows < dw.rowcount() then
		ds.rowsdiscard(ll_afterrows, dw.rowcount(), Primary!)
	end if

	if ll_b4rows > 0 then
		ds.rowsdiscard(1, ll_b4rows, primary!)
	end if

	ds.update()
	// do external call
	if external success then
		dw.SetItemStatus(ll_ctr, 0, NotModified!)
		commit using sqlca;
	else
		rollback using sqlca;
	end if
end if



Comment
  1. René Ullrich
  2. Monday, 3 September 2018 05:25 AM UTC
Hi Brad,

this sounds it could work. I'll give it a try. I think I have to enhance it because of deletes.

Thank you!
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Saturday, 1 September 2018 02:18 AM UTC
  2. PowerBuilder
  3. # 1

Hi Rene;

  What about using a Web Service DW where you can map the Update/Delete/Insert actions to their respective Web Service? Then you would not need any special coding as the DWO will handle all of this internally.

Food for thought.

Regards ... Chris

Comment
  1. René Ullrich
  2. Monday, 3 September 2018 05:21 AM UTC
Hi Chis. This is not an option. Have the same problems and one more system.
  1. Helpful
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 11:43 AM UTC
  2. PowerBuilder
  3. # 2

Override the Update function (this would require creating an inherited datawindow for this type of use) for that dw.  Loop through and check the ItemStatus on each row to determine if it's an update, insert, delete or no change, then SetItemStatus on the row depending on whether the ws worked.  Optionally, you don't have to call Update, you can always make a new function and call that - ExternalUpdate() or something similar.

 

Kevin

Comment
  1. Kevin Ridley
  2. Friday, 31 August 2018 12:03 PM UTC
So either way, unless you use a new custom event, you will either need to create a new function on your current inherited dw, or create a new inherited dw and either override the Update or create a new function. A 3rd option, if it's something you think you might re-use, is to process it in a nvo using a datastore and populate the datastore using sharedata or getfullstate/setfullstate. Lots of options, good luck.
  1. Helpful
  1. René Ullrich
  2. Monday, 3 September 2018 05:23 AM UTC
Hi Kevin. The problem is the update I need to do row by row, not only the web service call.

Thanks.
  1. Helpful
  1. Kevin Ridley
  2. Tuesday, 4 September 2018 12:41 PM UTC
Ok, I thought the update was just the external. You could easily just create a ds and just copy the current row you are processing to it (not sure if the itemstatus goes with it on the rowscopy, so you may have to get the current and set it in the datastore). But anyway, then you call update on the ds and then ds.reset() before the next row is copied. Should be pretty easy. You actually don't even need to do the ds.reset() because the itemstatus will go to NotModified! after the update, but I'd probably rather just work with the 1 row every time.
  1. Helpful
There are no comments made yet.
Arthur Hefti Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 08:30 AM UTC
  2. PowerBuilder
  3. # 3

Hi

use the SQLPreview Event in the datawindow for this and return 2 (or 1 to cancel the whole update). There you have the row and buffer that generates the SQL Statement and you know if it's an update or insert.

Regards
Arthur

Comment
  1. René Ullrich
  2. Friday, 31 August 2018 09:10 AM UTC
Thank you for your response.

SQLPreview event will only work if I use the sqlsyntax parameter to do the Insert/Update/Delete myself using embedded sql with only works with DisableBind=1. Only in this case I have a chance to do the commit / rollback and a kind of ResetUpdate for the row in this event.

Will Powerbuilder get problems if I change the SetItemStatus or remove rows from delete buffer (= ResetUpdate for a single row) at this time?

  1. Helpful
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.