1. Pete Yankovich
  2. PowerBuilder
  3. Wednesday, 2 June 2021 14:11 PM UTC

For PB 2017R2, when a DW Update() is executed and there are inserts, updates and deletes being performed, is there a predefined sequence of how the SQL statements will be generated and executed?  Or, is there a way to order the SQL?

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 2 June 2021 15:43 PM UTC
  2. PowerBuilder
  3. # 1

Hi Pete;

  The DWO will process the various Update/Delete/Insert SQL in the order that the changes to the DWO's buffers were made (in date/time order).

Regards ... Chris

Comment
There are no comments made yet.
Pete Yankovich Accepted Answer Pending Moderation
  1. Wednesday, 2 June 2021 16:56 PM UTC
  2. PowerBuilder
  3. # 2

Thanks Chris, that's what I would guess.  But, I'm having an issue, described briefly.  I'm inserting rows into a datastore, populating values from a datawindow.  For the first row, the row status is changed to DataModified! and the second row is NewModified!.  I view the SQL in the SQLPreview event of the datastore.  I never see an Update SQL for the first row, only the Insert for the second row.  This fires an exception in the table trigger, apparently because the Update didn't happen.

I've double checked my code to verify the source and destination of values.

Does anything come to mind?

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 2 June 2021 17:58 PM UTC
Hi Pete ... that sounds suspicious as the 1st row should stay as NewModified! until either A) an Update() command was performed and then a column in row #1 was modified after that or B) a SetItemStatus() command was executed that over-ride the NewModified! flag into a DataModified! before the Update() command. Very weird!
  1. Helpful
  1. John Fauss
  2. Wednesday, 2 June 2021 18:10 PM UTC
The DW/DS generates the syntax for an UPDATE based on the "Where Clause for Update/Delete" setting, the row status, the status of each column in the row, and comparison of the current value in the Primary buffer against the original (retrieved) value. If you are inserting a row, populating it and then changing the row status to DataModified!, there are no "original" values for that row. Check the Where Clause setting...If it is Key & Modified, change it to either Key or to Key and Updatable Columns and see if that works.
  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.