• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Specifying the WHERE clause for update/delete not working as described

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Specifying the WHERE clause for update/delete not working as described

  1. Issue
0
Votes
Undo
  1. Arthur Hefti
    Arthur Hefti
  2. PowerBuilder
  3. Thursday, 6 February 2020 14:47 PM UTC

Hi

according to the PB Help when choosing "Key and Updatable Columns" or "Key and Modified Columns" in the "Specify Update Properties" dialog the columns marked as updatable should be included in the where clause (either all or the changed), but...

There's an additional property "updatewhereclause" in the datawindow which is visible in the source only. When this is set equals "no" the column is never included in the where clause. For columns with type char(32766) this is always set equals "no" you can't change this by unselecting/selecting the column in the dialog.

When I look at my datawindows that are changed over the years it used to be possible to have columns of type char(32766) in the where clause, they have updatewhereclause="yes". No idea when this changed.

From my point of view this is quite bad and has big impact on the behavior of an application.

Regards
Arthur

Datawindow Select Property Returning Empty String
right click copy and paste datawindow
Responses (3)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 6 February 2020 18:19 PM UTC
  2. PowerBuilder
  3. # 1

Hi Arthur, I see similar behavior using both ODBC (SQLAny 17) and SNC (MSSQL 2019)

 

The 32766 limit is the limit where data in a single column surpasses 64 kB.

I presume root cause is DataWindow's WHERE clause construction. Perhaps 64+ kB in single string value causes buffer overflow. Hence, UpdateWhereClause locks to value = "no".

If that's the case you could calculate some checksum like CRC-32/CRC-64/SHA-1 to check for changes in the large text object within the WHERE clause.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 6 February 2020 19:20 PM UTC
  2. PowerBuilder
  3. # 2

Hi Aurther;

  I seem to remember Sybase adding that restriction a while back. I believe that the reasoning was to ensure proper performance and also, why would someone be querying a large free form text column on an update. It just makes no logical sense.

  What I am surprised though is that the DW Painter does not allow you manually "toggle" that setting. Hmmm ... I smell an enhancement request coming.  ;-)

Regards ... Chris

Comment
Michael Kramer
  1. Michael Kramer
  2. Thursday, 6 February 2020 19:33 PM UTC
That property was always undocumented albeit hiding in plain sight when editing source code. Very useful when updating multiple tables from one DataWindow within a single DB transaction.

I found that I can still set it to YES when editing source. Then DW painter retains the new YES value. At least when DBMS = SNC.
  1. Helpful
mike S
  1. mike S
  2. Thursday, 6 February 2020 21:22 PM UTC
i had reported a related issue/enhancement - https://www.appeon.com/standardsupport/track/view?id=2091

basically asking for better updatewhere options in general. using the UpdateWhereClause would allow you to use the key and modified option and have it actually work
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 7 February 2020 16:40 PM UTC
Hi Mike. FYI: Ticket #2091 ... Last response - "We have consulted our development team and we are sorry to let you know that currently we still don’t have a plan to fix this issue "
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 04:51 AM UTC
  2. PowerBuilder
  3. # 3

As a remark, not really solving the described problem, I'd like to state that if you use big texts like this you have to be careful with the "edit.limit" parameter.

It should be "less" than the length allowed by the column: When you start using utf characters with accents or like the Spanish "ñ" or £, then these characters, occupying more than one byte, will cause your update to fail.

How much "less"? That's a good question.

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.