In memory FoundSet update a column

Questions and answers for designing and implementing forms in Servoy

In memory FoundSet update a column

Postby jeffrey.vandenhondel » Mon Aug 08, 2016 1:22 pm

Hello,

I want to update one column in my memory foundset without updating the whole foundset.
Is this possible?

Kind regards.
jeffrey.vandenhondel
 
Posts: 36
Joined: Thu Jul 28, 2016 9:05 am

Re: In memory FoundSet update a column

Postby Bernd.N » Tue Aug 09, 2016 4:42 pm

Hi Jeffrey,

as far as I know, a foundset is always something in memory, as it represents a (desired / filtered) subset of the physical database table.
(There can be a foundset without physical table, but I guess that is not something you had in mind.)

Maybe you want to say "I want to update one column in my memory foundset without updating the whole table." ?

In that case you would loop through the foundset, which would look like this.
Note that the getSize() function in the for loop will make sure that all records will be changed, and not only the first 200 (or 70 or whatever) in the foundset which will be loaded initially.

Code: Select all
   
for (i = 1; i <= fs.getSize(); i++) {
      
   rRec = fs.getRecord(i);

   rRec.yourFieldName = "yourNewValue";
}


My personal preference is to not use such a loop in cases where I have to update a lot of records (which happens seldom), as they are slower than SQL, but to use just plain SQL UPDATES inside Servoy, like this (sSQL contains the SQL-Query-String):

Code: Select all
plugins.rawSQL.executeSQL(scopes.utils.DB.SERVER, null, sSQL);
Last edited by Bernd.N on Tue Aug 09, 2016 4:49 pm, edited 1 time in total.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: In memory FoundSet update a column

Postby jeffrey.vandenhondel » Tue Aug 09, 2016 4:49 pm

Hi Bernd,

We are using a foundset without a physical table.
Tomorrow i will try the for loop i will let you know if it worked.
jeffrey.vandenhondel
 
Posts: 36
Joined: Thu Jul 28, 2016 9:05 am

Re: In memory FoundSet update a column

Postby Bernd.N » Tue Aug 09, 2016 4:57 pm

Ok, in that case you would have a specific IF-condition that you use inside the loop.
Only when that IF is true, a record of the foundset will get changed.
But you would loop through the complete foundset to inspect all records.
Last edited by Bernd.N on Wed Aug 10, 2016 7:12 pm, edited 1 time in total.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: In memory FoundSet update a column

Postby marco.rossi » Wed Aug 10, 2016 5:42 pm

Dear jeffrey,

Have you already tried with the foundsetUpdater?

It's very fast. Y
You can use it as follow:

Code: Select all
var updater = databaseManager.getFoundSetUpdater(foundsetYouWantToUpdate)
     updater.setColumn("columnName","valueYouWantToInser")
     updater.performUpdate()


I haven't used it to update a foundset not linked to any table but it should work anyway.

Regards

Marco
Marco Rossi
Senior Analyst Developer
Freelance Consultant

IT Manager @Mantho
Webmaster @Sitoliquido
marco.rossi
 
Posts: 110
Joined: Sun Apr 12, 2015 9:33 pm

Re: In memory FoundSet update a column

Postby jeffrey.vandenhondel » Thu Aug 11, 2016 9:15 am

Bernd.N wrote:Ok, in that case you would have a specific IF-condition that you use inside the loop.
Only when that IF is true, a record of the foundset will get changed.
But you would loop through the complete foundset to inspect all records.


Thank you very much!!
I fixed it with a foundset filter.

This is how i coded the function.
Code: Select all
fs.addFoundSetFilterParam('print_or_email', '=', 0, 'filterPrintOrEmail');
fs.loadAllRecords();


marco.rossi wrote:Dear jeffrey,

Have you already tried with the foundsetUpdater?

It's very fast. Y
You can use it as follow:

Code: Select all
var updater = databaseManager.getFoundSetUpdater(foundsetYouWantToUpdate)
     updater.setColumn("columnName","valueYouWantToInser")
     updater.performUpdate()


I haven't used it to update a foundset not linked to any table but it should work anyway.

Regards

Marco


I did try it like that but it didn't update as i wanted :)
jeffrey.vandenhondel
 
Posts: 36
Joined: Thu Jul 28, 2016 9:05 am

Re: In memory FoundSet update a column

Postby Bernd.N » Thu Aug 11, 2016 4:10 pm

There is an article from Gary regarding looping through foundsets that might be interesting for you:

Code: Select all
http://www.dotzlaw.com/tutorials/servoy-tutorials/servoy-tutorial-optimizing-code-performance/
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests