Noobie Question Again - Update / Insert SQL

Classic Servoy
1

Hi Folks - still at the evaluation stage of the whole Servoy System.

Looking at the FAQ on this forum I noticed that Update and Insert is not supported in Servoy SQL because of the data broadcasting tools built in.

Is this the case? If so, how does one go about the global update of an entire database table?

EG:

UPDATE activity_costs SET activity_costs.fa_fabric_activities_code = “22”
WHERE (((activity_costs.fa_fabric_activities_code)=“33”));

This is the type of query I use all the time and is the basis for all my apps data updates. Very little of the data changing is done through a form and simple column edits.

Have I got it wrong and this is easily possible?

Appreciate any feedback.

2

Hi Kahuna,

Yes you can do updates on a whole table at once.
You use the rawSQL plugin for that.
You can execute any SQL and at the end you can flush all the clients cache so that the other clients will see the changes made.

Look under the plugins node in Servoy Editor.

Hope this helps.

3

Hi Kahuna,

Have also a look at databaseManager.getFoundSetUpdater(foundset)

from its “move sample”

//There are 3 types of possible use with the foundset updater
//1) update entire foundset
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('customer_type',1)
fsUpdater.setColumn('my_flag',0)
fsUpdater.performUpdate()

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

//3) safely loop through foundset (starts with selected row)
controller.recordIndex = 1
var count = 0
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
while(fsUpdater.next())
{
	fsUpdater.setColumn('my_flag',count++)
}

should be easy and is databroadcasting compatible

in your case:

controller.find();
a_fabric_activities_code ="33" ;
controller.search(true,true);
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('fa_fabric_activities_code',22)
fsUpdater.performUpdate()
4

Brilliant - thanks guys - helpful as ever!