Rephrased: Manipulating Data Columns In Code?

Classic Servoy
1

Hi Folks, Please forgive me rephrasing my question in a new thread - Patrick was good enough to comment on my last thread

“Access Columns by name in getDataSetByQuery?” viewtopic.php?f=22&t=13004

However I think I’ve taken a wrong turn completely and so think a new thread and question will make things clearer (at least for me)?

I need to manipulate several data columns in a table, in code, based on a SQL query selection. Originally I figured the getDataSetByQuery was the sensible route, and using the SQL to allow me to loop through them, and with each loop update several columns of data.

It seems that’s not going to work (as per Patricks comments). My next thought is to use a foundset, loop through that and change the column data as necessary. However I cant see how I can limit the foundset returned by a SQL string (must be a SQL string since this is supplied and pre-built).

Q. Is this the best way to manipulate the column data in code in Servoy?
Q. If so, how can I limit the the rows returned in the foundset to those in the SQL String

SQL String Example: SELECT * FROM abc WHERE col1 = ‘xyz’;
Foundset Table ‘abc’

Appreciate any feedback - or sample code!

2

Hi Ian,

Kahuna:
Q. Is this the best way to manipulate the column data in code in Servoy?

If they are bulk updates I would use plain SQL and the rawSQL plugin. But if you need to make specific changes per record then a foundset might be the easiest way. But in either way you could also use the foundsetUpdater.

Kahuna:
Q. If so, how can I limit the the rows returned in the foundset to those in the SQL String

SQL String Example: SELECT * FROM abc WHERE col1 = ‘xyz’;

You mean how to use a SQL query to load a foundset ?

controller.loadRecords("SELECT idColumn FROM abc WHERE col1 = 'xyz'")

Or more flexible (when needed) using a prepared statement:

controller.loadRecords("SELECT idColumn FROM abc WHERE col1 = ?", ['xyz'])

Hope this helps.

3

ROCLASI:
Hi Ian,
You mean how to use a SQL query to load a foundset ?

controller.loadRecords("SELECT idColumn FROM abc WHERE col1 = 'xyz'")

Or more flexible (when needed) using a prepared statement:

controller.loadRecords("SELECT idColumn FROM abc WHERE col1 = ?", ['xyz'])

Hope this helps.

Thanks for that Robert - I see better the tack to take - would you mind showing an example of loading that data and how to refer to it in a loop - similar to my getDataSetByQuery example??

var query = sqlToRun // Query MUST be well formed - Taken from Year Setting Form SQL field
            var dataset = ''
            dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
            //Step through rows and do work
            for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
            {
               dataset.rowIndex = i;
                  dataset.setValue(1,40,yearToSet)

I’m felling a little lost in using controller!

4

Hi Ian,

You can use the FoundSetUpdater for this:

controller.loadRecords(sqlToRun);
var _oFsUpdater = databaseManager.getFoundSetUpdater(foundset);
while( _oFsUpdater.next() )
{
    _oFsUpdater.setColumn("yearColumn",yearToSet);
}

But if you just want to set the whole foundset to the same value then you don’t really have to iterate the foundset. Just update it all in one go:

controller.loadRecords(sqlToRun);
var _oFsUpdater = databaseManager.getFoundSetUpdater(foundset);
_oFsUpdater.setColumn("yearColumn",yearToSet);
if ( !_oFsUpdater.performUpdate() ) {
    // something went wrong
}

Hope this helps.

5

Fantastic feedback thanks Robert.

I’m going to experiement with the following:

var dataCont = forms.FormB.controller.loadRecords(sqlToRun);
var _oFsUpdater = databaseManager.getFoundSetUpdater(dataCont);
while( _oFsUpdater.next() )
{
    _oFsUpdater.setColumn("yearColumn",yearToSet);
}

I need the data from a different form and I’m assuming this is the way to get it? Your first sample is my option Robert - I have a lot of data checking and manipulation to do in the code with comparisons and updates etc. One more question please Robert:

Q. How do I get a value from from this foundset? i.e. If I want to compare the current ‘yearColumn’ to the update value, how do I see the current value? Is it:

dataCont.yearColumn< ?

Patrick posted a different approach on the other thread - but I like this one for this particular purpose, as I can run the update on FormB and take advantage of any filtering the user has applied on that form / table.

I think using Patrick’s code I’m doing the data changes on the table directly - and that will be increadibly usefull for future coding I need to do. Just for completeness here is Patrick’s code too (saves looking in the other thread if you’re interested in the same process):

         //Get Dataset of SQL
            var query = sqlToRun // Query MUST be well formed - Taken from Year Setting Form SQL field
            var myfoundset = databaseManager.getFoundSet(controller.getServerName(), tableName);
         myfoundset.loadRecords(query);
            //Step through rows and do work
            for( var i = 1 ; i <= myfoundset.getSize() ; i++ )
            {
            var myrecord = myfoundset[i];
                myrecord[columnName] = yearToSet; 
            }
         databaseManager.saveData();