1. Michał Misijuk
  2. PowerBuilder
  3. Tuesday, 10 September 2019 12:37 PM UTC

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
  1. Wednesday, 11 September 2019 12:37 PM UTC
  2. PowerBuilder
  3. # Permalink

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
  1. Michał Misijuk
  2. Thursday, 12 September 2019 08:37 AM UTC
Michael,

You! Are! The! BEST!

Thank You!

bow.gif
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 18:33 PM UTC
  2. PowerBuilder
  3. # 1

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
  1. Michał Misijuk
  2. Wednesday, 11 September 2019 05:56 AM UTC


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. Helpful
  1. Michael Kramer
  2. Wednesday, 11 September 2019 12:39 PM UTC
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 16:57 PM UTC
  2. PowerBuilder
  3. # 2

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
  1. Michał Misijuk
  2. Wednesday, 11 September 2019 05:40 AM UTC
Hi Chris,

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

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

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
  1. Michał Misijuk
  2. Wednesday, 11 September 2019 05:22 AM UTC
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. Helpful
  1. Olan Knight
  2. Wednesday, 11 September 2019 20:59 PM UTC
>>> 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. 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.