1. Michał Misijuk
  2. PowerBuilder
  3. Tuesday, 10 September 2019

I have to modify exisiting data import function which works IN LOOP like:
get data from text>  put into datastore  (via INSERT ROW(0) > update ( which causing insert into table....).

my goal is to modify it to work like:
add, new column - operation

if operation = 'new' then do as above,
if operation = 'delete' then update column active
if operation = 'update' then update multiple columns

problem is that once a record appear in datastore by insert row command, powerbuilder always generates insert, but i need update.

is there a way, to i don't know - create another datastore, and in every loop iteration add this record there, and then one datastore will be shooting inserts in DB, and the other one - updates? 


PS in very tough situation i thought about some temparary table which powerbuilder will be filling and run some procedure on DB which will be working on records from this table.

Accepted Answer
Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

Hi Michal,

DataWindow WHERE clause = "KEY and modified" or "KEY and updateable" requires you specify CURRENT and ORIGINAL value (original requires dot-notation).

Dot notation is actually faster than GetItem/SetItem since PB 11.5. Drawbacks:

  1. Column names are static (workaround: use column numbers)
  2. Mismatched column names throw DWRuntimeError exceptions.

NOTE: Row status must be NotModified! or DataModified! before DataWindow engine distinguishes between CURRENT and ORIGINAL.

Code like this is needed

row = ids_data.InsertRow(0)

// KEY value(s)
ids_data.object.id[row] = ll_id

// Turn on "UPDATE" handling
ids_data.SetItemStatus(row, 0, Primary!, DataModified!)

// Data values
// NOTE: Somehow you need to "find original values" for all columns to be modified
// So you end up having to retrieve such data from DB!
ids_data.object.status[row] = 1
ids_data.object.status.original[row] = 0

ids_data.object.MyData[row] = ls_dataImported
ids_data.object.MyData.original[row] = ls_dataKnownToBeInDB

. . .

 

Alternative

  1. Create separate DataStore to manage rows to update.
  2. Retrieve argument in DW object are KEY values so each ids_data.Retrieve( id ) retrieves just that one row
  3. RetrieveStart event => RETURN 2 -- Retrieve appends row read from DB
  4. No row retrieved => InsertRow(0) -- which appends row new to DB
  5. Update imported data in the newly appended row
    No need to set original values since they have been read from DB when row exists
    1. Row read from DB becomes DataModified! => UPDATE
    2. Row new to DB becomes NewModified! => INSERT
  6. Repeat for each imported row
  7. When import completed, perform single ids_data.Update( )

HTH /Michael

Comment
Michael,

You! Are! The! BEST!

Thank You!

bow.gif
  1. Michał Misijuk
  2. Thursday, 12 September 2019
There are no comments made yet.
  1. Wednesday, 11 September 2019
  2. PowerBuilder
  3. # Permalink
Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

Chris example shows GetItemStatus/SetItemStatus functions. F1-Help has details.

Row status governs SQL verb

  • NewModified! => INSERT
  • DataModified! => UPDATE
  • Other statusses => No changes

You can fine tune with column statuses if need be.

When you convert to UPDATE you must decide what type of WHERE clause you want ("key only" is easy but potentially risky).

HTH /Michael

Comment


Hi Michael,



I got progress, but still missing something:



ll_row = ids_data.InsertRow(0)

ids_data.SetItem(ll_row, 'status',1)

ids_data.SetItem(ll_row, 'id',l_id)



and it gives me:





Row 1

Sql -3

Text Row changed between retrieve and update.



No changes made to database.



UPDATE table SET ha = 0 WHERE pm = 2090751 AND status = 1 --ithis update affect no row on DB, since this pm at the moment is in with the status = 0, and i want it update to 1



It should looks like:



UPDATE table SET status = 1 WHERE pm = 2090751 AND status = 0



any ideas? mayby i misunderstood something?
  1. Michał Misijuk
  2. Wednesday, 11 September 2019
There are no comments made yet.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 1
Chris Pollach Accepted Answer Pending Moderation
0
Votes
Undo

Hi Michal;

   Have a look at the SetItemStatus () command. It can change DWO's Row & Column flags from an "Insert" into a "Update" (aka Modify). For example ...

dwItemStatus l_status
l_status = dw_rpt.GetItemStatus(5, 0, Primary!)
IF l_status = NewModified! THEN
      dw_rpt.SetItemStatus (5, 0, Primary!, DataModified!)
END IF

HTH

Regards ... Chris

 

Comment
Hi Chris,

You answer helped me but i still got something wrong. Could you look at my answer to Michael K.?

Thank You :)
  1. Michał Misijuk
  2. Wednesday, 11 September 2019
That is because after the insertrow(), your code did not issue any SetItemStatus() commands to change the "Insert" into an "Update" DML command.
  1. Chris Pollach
  2. Wednesday, 11 September 2019
There are no comments made yet.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 2
Olan Knight Accepted Answer Pending Moderation
0
Votes
Undo

I'm a bit confused by this. Do you have code like this:

CHOOSE CASE (operation)
   CASE "NEW"
            ll_row = lds.InsertRow (0)

   CASE "DELETE"
            ll_rc = lds.SetItem (al_row_passed_in, "active", 0)

   CASE "UPDATE"
            ll_rc = lds.SetItem ()

   CASE ELSE
END CHOOSE

 

   If so, it does not matter how the updates are defined in the datawindow object because you code controls the action to be performed.

Olan

Comment
in a big short - yes.

If new i want to insert into DS, and DS should generate insert (which does)

if delete then insert indo DS, but DS should generate update

if update then insert into DS, but DS should generate update
  1. Michał Misijuk
  2. Wednesday, 11 September 2019
>>> if delete then insert indo DS, but DS should generate update

What does this mean? Do you mean 1) delete the specified row, then 2) insert a new row?



>>> if update then insert into DS, but DS should generate update

What does this mean? Do you mean 1) update the specified row, then 2) insert a new row?



If you have code as listed above, YOU control what occurs in the DS and thus YOU control what happens in the database.



Olan

  1. Olan Knight
  2. Wednesday, 11 September 2019
There are no comments made yet.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 3
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.