1. Gregory Rusak
  2. PowerBuilder
  3. Tuesday, 16 April 2024 16:45 PM UTC

Hello,

Using PowerBuilder 2021 build 1288 against the PBDemoDB2021 SQLAnywhere 17 (32-bit) database, (examples table) we can demonstrate that a datawindow with just 2 columns (example_id integer and technique long varchar) where we have set the Update Properties to be "Key and Updatable Columns", the Rows Change Between Retrieve and Update DOES NOT happen if the data in the technique column is updated in datawindow A, committed, then Updated in datawindow B and committed.

We would like to know if this is a known defect, expected behaviour (that we would find very odd) and if there is any workaround for this issue.

We've attached a demo SDI application that connects to the PBDemoDB2021 database and has 2 same datawindows on it. If you retrieve both datawindows, then update the technique column in the first datawindow and commit, then change the data and update and commit in the second datawindow, the data that was entered in the 2nd datawindow overwrites whatever was entered in the first datawindow (without having the Rows changed between retrieve and update occurring).

Here are a few screenshots of what the demo application demonstrates:

Datawindow Update Properties:

Retrieve both datawindows where initial data of "technique" for example_id 0 is "No Data":

Update the data in the left datawindow and click the Update button to Update and Commit:

Update the data in the right datawindow and click the Update button to Update and Commit:

Check in ISQL what data has been committed after NOT getting a Rows changed between retrieve and update error:

As always, any insight is much appreciated.

Warm Regards,

Greg

Attachments (6)
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 18 April 2024 14:32 PM UTC
  2. PowerBuilder
  3. # 1

Hi Arthur;

  FYI: I have now tested Greg's test case with ASE, SQL Server &  PostGreSQL and both respond correctly in capturing the WHERE check on the Long VarChar column in the PB Demo DB I installed on both DBMS servers.So PB seems to be working properly in that regard. The anomaly only seems to be with SA.

Regards .. Chris

Comment
There are no comments made yet.
Gregory Rusak Accepted Answer Pending Moderation
  1. Thursday, 18 April 2024 14:27 PM UTC
  2. PowerBuilder
  3. # 2

Well Arthur,

You were absolutely right. After editing the source code and setting the updatewhereclause=yes for the technique long varchar column, the update acted as expected as follows:

Now I suppose the questions I have for Appeon is why would this have been done and not documented anywhere where the end result of this oversight results in data being over written without the end-users knowledge. And secondly, are there any other datatypes where this may also apply?

Again Arthur, thank you so much for your attention to this.

Kind Regards,

Greg

 

Comment
There are no comments made yet.
Arthur Hefti Accepted Answer Pending Moderation
  1. Thursday, 18 April 2024 03:52 AM UTC
  2. PowerBuilder
  3. # 3

Hi

many many years ago PowerBuilder changed the behavior (I don't know with SQL Anywhere only or all DBMS) how to include the long varchar columns in the where condition on updates, when creating the DataWindow by setting "updatewhereclause=no". See attached print screen from your datawindow.

Regards
Arthur

Attachments (1)
Comment
  1. Gregory Rusak
  2. Thursday, 18 April 2024 14:13 PM UTC
WoW!

Thanks for that Arthur. So I imagine (and I'll be sure to test this theory and will respond back with the findings) that if we manually Edit the datawindow source and change it to updatewhereclause=yes, then theoretically it should catch the rows changed between retrieve and update case.

Thank you very much for providing this very useful tidbit of information.

Warm Reagrds,

Greg
  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 18 April 2024 16:21 PM UTC
Greg / Arthur;

Ran an SQL Trace this AM on the SA 17 Demo DB and the trace conforms the missing WHERE clause column check.

Where as for the other DBMS I tested, even though the "updatewhereclause=no" DO source was set, it still sent the correct WHERE clause for the Long VarChar column to the DBMS server. I would like to suggest then that you open a Support Ticket for this issue against SA DBMS.

Regards .. Chris
  1. Helpful
There are no comments made yet.
Gregory Rusak Accepted Answer Pending Moderation
  1. Wednesday, 17 April 2024 22:34 PM UTC
  2. PowerBuilder
  3. # 4

Hello Chris,

If this were the case and in fact it was a SQLAnywhere defect, then one would suspect that the same behaviour would be exhibited in an ISQL Session. So I ran a test with 2 ISQL Windows where the initial data in the long varchar column was 'No Data', then in the left ISQL Window:

update examples set technique= 'Changed on left' where example_id = 0 and technique = 'No Data';

COMMIT;

And in the right ISQL Window:

update examples set technique= 'Changed on right' where example_id = 0 and technique = 'No Data';

COMMIT;

And as one would expect the data that ended up in the column was 'Changed on left' as no rows were actually updated in the right ISQL Window (for obvious reasons).

I have no idea of the inner workings of the datawindow engine that determines what constitutes a "Rows changed between retrieve and update", but understand that it is when no rows are updated when they should have been.

Any thoughts on that?

Thanks again,

Greg

 

 

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 17 April 2024 23:48 PM UTC
Hi Greg;

Hmm ... Another "twist" in this mystery!

I would now suggest running your Test Case with SA17 but this time, run it with an SQL Trace on.

SQLCA.DBMS = "TRACE ODB"

That might give us some more insight into what's going on in the handshake between PB's ODBC "interface" driver that then tald to the real ODBC driver (from SA).

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 17 April 2024 14:49 PM UTC
  2. PowerBuilder
  3. # 5

Hi Greg;

  FYI: This is not a PB bug. I just realized that this AM after pondering this issue last night as the DWO is depending on the WHERE clause processed by the DBMS to inform the DWO that another update has occurred in between. So this AM, I used the PB Demo DB running on my SQL Server 2022 DBMS and then ran your test case against that DB instance ...  and guess what - your test case now worked perfectly! So this is an SA  Bug!!!  :-(

So you will need a patch from SAP for SA v17 to get around this problem.

FYI: Demo DB for other DBMS:  https://github.com/Appeon/PowerBuilder-Project-Example-Database

HTH

Regards .. Chris

Comment
There are no comments made yet.
Gregory Rusak Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 20:45 PM UTC
  2. PowerBuilder
  3. # 6

Hello Chris,

Do you see any viable work around for this defect? It's causing a production issue of data being LOST, which in terms of defects is not goodly.

Warm Regards,

Greg

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 19:55 PM UTC
  2. PowerBuilder
  3. # 7

Hi Gregory;

  Thank you for the test case! This looks lie a product bug / limitation.

  I was also able to replicate this issue as well in PB 2022 R3 build 3356.

  It looks like any time you have a Long Varchar column with or without any other columns in the DWO, the update conflict is ignored by the DWO!  :-(

  However, if you remove the Long Varchar column and use any other combination of columns, then the update conflict is detected by the DWO.

  I would suggest that you open a Support Ticket for this problem and then attach your test case to that ticket.

Many thanks in advance!

Regards .. chris

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 18:34 PM UTC
  2. PowerBuilder
  3. # 8

Maybe you should go ahead and report a bug and attach all your screen shots & example code.

https://www.appeon.com/standardsupport/search

 

Comment
  1. Gregory Rusak
  2. Tuesday, 16 April 2024 18:39 PM UTC
Hello Roland,

Wanted to first make sure we weren't missing something obvious.
  1. Helpful
  1. Gregory Rusak
  2. Tuesday, 16 April 2024 18:44 PM UTC
... and we're planning on upgrading shortly to PB2022, so if it's fixed there, we can move on.
  1. Helpful
There are no comments made yet.
Gregory Rusak Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 18:12 PM UTC
  2. PowerBuilder
  3. # 9

Hello John,

You can find the datawindow in the .zip file of the demo application I attached.

That would be a char (32766):

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 18:06 PM UTC
  2. PowerBuilder
  3. # 10

What is the datatype definition of the "technique" column in the DataWindow? string of length ___?

Comment
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.