I have a table event, onRecordUpdate(), that is not being invoked when databaseManager.getFoundSetUpdater()/fs_updater.performUpdate() is used on the same table. Is this normal? I was expecting the foundset updater to trigger onRecordUpdate() for each recorded visited during the update. Would someone please enlighten me? Also, does anyone know of a work-around?

What is the underlying reason for this? It seems to me, for validation purposes, that onRecordUpdate() should trigger whether the record update is done by looping through the foundset or by using a foundset updater. I am obviously not understanding what the difference is...sorry to be so obtuse.

To understand why the foundsetUpdater doesn't trigger the Table events you need to understand how the foundset updater and the Servoy Table Event works.For one the Servoy Table Events are completely handled on the application layer so if you would use a third party tool and edit data from outside Servoy these events will not trigger (unlike database triggers).FoundsetUpdater has 2 ways of working. The first way it actually loops through the foundset and updates one record at a time using the next() method. This will indeed trigger the Servoy Table Events (if not you should consider this a bug).The other way is to update the foundset in one fell swoop using the performUpdate() method. What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.

What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.

Don't forget to mention the above is not so when Servoy logging is enabled on that table. In that case the update is always record by record.

True, almost forgot about that. The FoundSetUpdater will then default back to the looping.I guess one could argue that if the table has Table Events attached it should do the same thing.Kim, perhaps you should add a feature request for this.

ROCLASI wrote:Hi Kim,The other way is to update the foundset in one fell swoop using the performUpdate() method. What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.

var fsu = databaseManager.getFoundSetUpdater(foundset);fsu.setColumn(columnName, arrayOfValues); //arrayOfValues contains one value per record to be updatedfsu.performUpdate();

Since two of three methods of using the same object trigger Table Events, I would hesitate to call this a feature request and personally view it more as a bug. I believe that the general expectation, unless otherwise noted, is that when you work directly with Servoy provided objects (JSFoundSet, JSRecord, JSFoundSetUpdater) that you are in fact working within the provided application layer that would trigger your events. Only when you fall back to manual/raw methods (rawSQL, outside applications) would I lose the expectation of attached events triggering.

This seems to essentially mean that for any type of record that could ever trigger a record event, you cannot use the "Set Column With Single Value" option, or risk introducing bugs into your software down the line when adding a table event (maybe even in a different module that your original work has no knowledge of!). You could perhaps try and mitigate this by providing some sort of "per table, single value, column batch update" method that handles this edge case for you so that if you need to change the behavior in the future, you've only got one place to edit, but it certainly feels like unnecessary complexity and something that Servoy should be handling for you given their capacity to provide the most efficient solution possible.

TL;DRThis is preferably a bug, and, at minimum if not fixed, should be noted in the documentation as a "feature"

jgarfield wrote:I tested this locally with all three ways (you forgot one Robert ) to use the JSFoundSetUpdater

You got me. To be honest I don't use the JSFoundSetUpdater, at all.I know it when it was introduced (2.x). To me it's just a simple wrapper around a foundset OR a pure SQL query. I tend to do either one myself in code, it makes it all very clear what's happening.In fact part of the issue is the hiding of SQL from the developer while SQL is in fact the developers friend. Sure there is the issue of the 'standard' SQL (which is not all that standard in practice) but an ORM like Servoy uses add an extra layer and adds more unexpected behavior than the SQL issues alone. Sure it makes a lot of coding simple which is AWESOME, but when you really get your hands dirty with code you get a lot of unexpected behavior when using ORM's (value-lists anyone ?). It's therefor important to know SQL and how Servoy translates code/objects into SQL because in the end it ALL ends up as SQL queries to the database.

Something I will talk about in an upcoming ServoyWorld 2014 session.I would say to all who are reading this: SQL is a programming language, learn it.

ROCLASI wrote:It's therefor important to know SQL and how Servoy translates code/objects into SQL because in the end it ALL ends up as SQL queries to the database.

I agree whole-heartedly. We have been known to apply raw SQL fairly liberally throughout our code base so I'm no stranger to the convolutions you need to go through (and the bugs this can cause..) to make sure that you manually databroadcast, or force refresh records from the database when needed.

That's why I think it's important to have clear boundaries of where you can work within the Servoy framework (and get all the associated events, side effects, conversions, etc) and when you are working outside of it. personally, I think the best way to deal with these boundaries is to have certain classes of abstraction (JSFoundSet, JSFoundSetUpdater) always work within the framework (to it's entirety). If the JSFoundSetUpdater didn't trigger a databroadcast event, everyone would agree that would be a bug. Personally I feel the same way about Table Events.

That being said, along with submitting a bug report, I've also submitted a documentation change request that would make this edge case clear within the documentation (in the event that the behaviour doesn't change). As you said, it's important to be able to know how working with particular objects translates to SQL and any side effects of this, especially when the abstraction is not consistent.

update entire foundset by a single sql statement; that is not possible when the table of the foundset has tracking enabled then it will loop over the whole foundset. When a single sql statement is done, modification columns will not be updated, because it does the update directly in the database, without getting the records.

(also this page is under construction as far as i can see so it is then made a bit more clear)

But yes i guess if we detect table events we could just always start looping, but the performance case that JSFoundsetUpdater does bring is then gone.(you could do that in your own code with a very simple loop anyway)

I'll agree that it says that modification columns will not be updated, and that in retrospect one could draw conclusions that Table Events would not fire, but you must admit that drawing conclusions is not quite the same as actual documentation

(my edits to make this more clear might be what you see as under construction)

If you do detect Table Events and start looping, would all the performance benefits really be gone? Does that mean that the other two methods of using the foundset updater (Set by Iterating and Set By Array) provide no performance benefits? I'm of the opinion that even if that is the case, I would personally prefer a consistent experience (i.e. using FoundSet related functions trigger all the appropriate framework events/side effects).

In the same way that you say we can perform that action on our own with loops, we also have the facilities to issue our own "UPDATE table SET column = ? WHERE id IN ()" statements if we are looking to eek out the performance and resolve events on our own.

By leaving the behaviour as it is, it is possible to introduce bugs into your system merely by adding a Table Event to a table that did not previously have one if you have used the Set Column with Single Value method anywhere in code. Conversely, if the JSFoundSetUpdater were to detect TableEvents and loop if necessary, then you are merely left with a performance hit, rather than buggy code, when you add a Table Event.

i was more pointing the the line " update entire foundset by a single sql statement"that should already say to everybody that just one update sql statement is send that can alter 100K rows in the db... so all records don't have to be on the client at all, so not all records are not loaded in, so no table events can be fired because the record itself is not even on the client.

If it starts looping then no there is no big performance gain at all, If you write a generic javascript function that does the same (so looping over records setting some value) then that would be quite the same.