inserts and updates via SQL

[xtsr]

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

[Jan Blok]

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)

[youseful]

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?

[jcompagner]

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

that would be a huge drain...

[Jan Blok]

Servoy 2.0 rc5 will support updates like this:

Code: Select all

//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

Code: Select all

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