inserts and updates via SQL

Classic Servoy
1

How can I do “inserts into” and “updates” on a table using SQL queries? I tried “getDataSetByQuery”, which didn’t work (could be another reason, but judging from the name wouldn’t surprise me if it wasn’t supposed to work).

BTW, I’m using MySQL on OS X as db.

I’m new to Servoy, so please forgive me if this is a beginner’s question, but i couldn’t find an answer in the forum or in help…

Thanks!
Reto

2

If you edit records, Servoy will make the insert/updates for you, we do not allow SQL update/inserts because it breaks the data notification between clients.
(Servoy must be aware of all database changes, if update is done directly on database outside Servoy you must flush the client caches via the admin page)

3

Here in pseudo code

Do_SQL_Insert();
Notify_Server();

In Notify_Server() you notify the server that clients must flush cache.
On the client side there a Cache_flush event that can be Triggered.

What kind of perfermance hit would this be?

4

yes but what to flush?
Everything over ALL the clients??

that would be a huge drain…

5

Servoy 2.0 rc5 will support updates like this:

//1) update entire foundset
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('customer_type',1)
fsUpdater.setColumn('my_flag',0)
fsUpdater.performUpdate()

So when your foundset is as big as the entire table, the entire table will be updated

//2) update part of foundset, for example the first 4 row (starts with selected row)
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('customer_type',new Array(1,2,3,4))
fsUpdater.setColumn('my_flag',new Array(1,0,1,0))
fsUpdater.performUpdate()

And will notify the other clients about the update.