Where Clause for Update/Delete

Hi All,

Recently, I changed my practice of using "Key and Updatable Columns" to "Key and Modified Columns" in the "Where Clause for Update/Delete" of data entry DataWindows. My reasoning is that it is not necessary to include all updatable columns in the where clause, just the ones that got modified. This approach allows other users to modify other fields of the same record without getting concurrency errors.

But since my Data Source query includes additional tables, PowerBuilder includes the columns from the other tables in the update where clause, if they where also modified. The result is an error from the database indicating that such columns are invalid. PowerBuilder should not have included those columns since they don't belong to the table that will get updated even though they were modified.

The reason I am including more tables in the data source and modifying columns from those tables is because I use those tables as reference tables to get values like customer name, etc. This way I can use the same datawindow to add/update records and also to read existing ones. The only drawback to this approach is that when a new record is added I must write code to look up the information on all reference tables and place them manually. By doing so, PowerBuilder detects them as modified and includes them in the update where clause for the table I want to insert a new record to.

Is there a way that only the modified columns of the updatable table be included in the where clause?

TIA,

Ricardo Jasso

Kevin Ridley Accepted Answer Pending Moderation

Tuesday, 12 March 2019 15:20 PM UTC
PowerBuilder
# 1

Just a quick aside, you say you are only joining to the other tables to get reference values for id/codes in the main table, why not just use dddw's for those? Then you won't have to join at all, and problem is solved (for this case). Sometimes the simplest solution is the best.

Comment

Ricardo Jasso
Tuesday, 12 March 2019 16:41 PM UTC

Using dddw's causes the application to load all records from the reference table and for some catalogs there might be hundreds or thousands of records. I began coding in PB when WAN connections where 1 or 2 mbps at most and we had to be very cautious of the amount of data being read by the application. So we chose search windows for large reference tables and dddw's for small ones. My issue is that I use the same capture window for both new records and existing ones so I developed this technique which works quite well. And I have no problem using the updatable columns options, it's just that I've always been uncomportable having all updated columns included in the where clause when only modified columns will suffice to protect from concurrent updates on the same table and the same columns. Anyway, I think this should be addressed by the development team as the behaviour is not correct.

Helpful 0

Kevin Ridley
Tuesday, 12 March 2019 17:23 PM UTC

I understand you, and I agree it should work the way you are expecting it to work, I was just suggesting an alternative. With todays more powerful computers with lots more memory, a few thousand records with only a couple of columns (typical dddw) shouldn't be a big deal. You can even cache them similar to how the pfc caches datawindows to avoid re-retrieving all the time.

Helpful 0

There are no comments made yet.

mike S Accepted Answer Pending Moderation

Tuesday, 12 March 2019 01:48 AM UTC
PowerBuilder
# 2

Ricardo,

i agree - that is a fairly idiotic setting. I think there is a very narrow use case for this where it could make sense, but it causes way more problems then it is solves. you can code around this bad implementation by changing the status during updates.

put in a bug/enhancement request for an update option that only adds modified columns to the update statement for the updateable table.

Comment

Roland Smith
Tuesday, 12 March 2019 12:29 PM UTC

It has to keep track of all columns that changed, otherwise the multi-table update service in PFC wouldn't work. It does seem that it should limit the where clause to columns in the table currently assigned as the updateable table.

Helpful 0

mike S
Tuesday, 12 March 2019 13:45 PM UTC

I don't think the existing setting can change since there are probably some applications that require it to work that way; i do think a new setting would be immensely useful to a lot of people.

for history:

https://groups.google.com/forum/#!topic/sybase.public.powerbuilder.datawindow/xa25lqlflYU

and i had reported this same exact thing myself a couple of months ago. because i forget that it works this way every couple of years....

https://www.appeon.com/standardsupport/track/view?id=2091#

Helpful 0

Ricardo Jasso
Wednesday, 13 March 2019 20:20 PM UTC

Roland: It doesn't matter if it keeps track of all modified columns, what matters is that the UPDATE statement generated shouldn't include columns from secondary tables in the WHERE clause. So you are right, it should only include columns from the table specified in the "Table to Update" field.

Or ... it should be intelligent enough to build an UPDATE statement with joint tables to include the modified fields from secondary tables in the WHERE clause:

UPDATE

Customer

SET

Name = 'John'

FROM

Customer a

JOIN City b ON

a.CityId = b.CityId AND

b.Name = 'San Francisco'

WHERE

a.Name = 'Jack'

But I don't really need this. I only need modified columns exclusively from the updatable table to be included in the WHERE clause.

Mike: I perceive a confusion between this requirement and the need for a multi-table update option in the discussion threads you mention. So I don't see a clear proposed solution to this problem. My question concerns only with single table updates.

Helpful 0

There are no comments made yet.

Chris Pollach @Appeon Accepted Answer Pending Moderation

Monday, 11 March 2019 20:58 PM UTC
PowerBuilder
# 3

Hi Ricardo;

FWIW: The "Key and Modified" column setting is my favourite default setting that I have used since the beginning of PB time! ;-)

As for the updating DWO's, I never use two or more tables for any DWO that does updating. Only 1 DWO per table. Then you never get into the issue(s) that you described and ... the column names are never prefixed either. DataStores are you friends for foreign key resolution.

Food for thought.

Regards ... Chris

Comment

Ricardo Jasso
Monday, 11 March 2019 23:32 PM UTC

Hi Chris,

Having column names prefixed is annoying but the source editor is always available to correct them quickly! And I guess that you prefer to have one datawindow for inserting records and another separate datawindow for retrieving records.

But besides that, there shouldn't be any problem using two or more tables. In fact, that's the reason there is a drop down list box with the title "Table to Update" where one can select the table that PB should take to construct the Update statement.

I think this is a bug that can be traced back to the early versions of PowerBuilder because as far as I remember this options have always been there. It's just that I've always used "Key and Updatable Columns".

Regards,

Ricardo

Helpful 0

There are no comments made yet.

Roland Smith Accepted Answer Pending Moderation

Monday, 11 March 2019 20:36 PM UTC
PowerBuilder
# 4

Why are columns from the secondary tables getting set to updated?

Comment

Ricardo Jasso
Monday, 11 March 2019 23:36 PM UTC

Only columns from the primary tables are set to updated. In fact, PB doesn't allow to select columns from secondary tables in the "Updatable Columns" list. It'll throw an error saying " Column 'x' is not in table 'y' ".

That's the strange thing. They are not selected but anyway PB wants to include them in the Update construct because it detected that they were modified.

Helpful 0

Stuart Macandrew
Tuesday, 12 March 2019 00:13 AM UTC

PB detects they are modified because your application has allowed your end-user to modify them. Like... this is an observation that you may find curious, but is working exactly as designed. If you check "modified columns" then that is what you get. Either use another option (of which PB has a multitude to choose from - updateable columns, timestamp, stored procedure updating) to get what you want, or stop the end-user modifying values that cannot be updated.

Helpful 0

Ricardo Jasso
Tuesday, 12 March 2019 00:29 AM UTC

This option has worked for me for the past 15+ years. I don't see any reason to change it as it has always worked using "Key and Updatable Columns" or just "Key Columns". The user doesn't directly modify those columns. They are modified by the program to show product descriptions, customer names, quantity units, etc., etc., etc. Adding these columns from the secondary tables in the source editor has the great advantage that they are added to the datawindow where the user will input the data. This way, I don't have to add them as text objects. They are already added and ready to be used. This is not the problem. The problem is that when the "Key and Modified Colmns" option is selected PowerBuilder includes these columns which are not even part of the Updatable Columns. In fact, I think I just gave the solution to the development team... make sure that those modified columns are also in the Updatable Columns list, if not don't include them.

Helpful 0

There are no comments made yet.

Resolved Where Clause for Update/Delete