Apply SQL To a Forms 'Current' Foundset??

Classic Servoy
1

I been given a lot of support in applying a SQL string to a given forms foundset - using this type of code (suplied by Robert Ivens - Thanks to Robert and Patrick for their patience):

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

One of the attractions in using a forms foundset rather than going directly to the table was the potential to use whatever filtering was already applied to the form. i.e. If the user had already applied a controller.find() and controller.search(), the resultset could be iterated over and whatever work required done.

But, using loadRecords(sql) does not take the actual forms current foundset, with its applied filters and apply the sql to that - instead it appears to take the table the form is based on and apply the sql to that?

Q. Is there a way to apply a sql string to the ‘actual’ foundset - with its filters already applied?

I must apply a user provided sql string to the foundset (and yes, there are significant dangers in that from an injection view point that I’m aware of) but they need to be able to filter to a set of records to which they want that sql string to be applied!

This is exceptionally easy to do in other environments but it does’nt look that easy in Servoy! Any comment on the possibility of this, or how to structure it would be welcome.

2

Hi Ian,

So you want to search within the foundset using SQL. That would require to have the SQL that created the foundset in the first place.
You can get the SQL from the current foundset using the databaseManager.getSQL(foundset) but as we can read in this thread that is not always valid. Servoy could have used temporary tables to create the foundset and those tables won’t exist anymore.

But lets asume your foundset doesn’t use temp tables.
You can get the SQL from the foundset and add it to the WHERE clause of your SQL like so:

var _sQuery = sqlToRun + " AND pkColumn IN (" + databaseManager.getSQL(foundset) + ")";
controller.loadRecords(_sQuery);
var _oFsUpdater = databaseManager.getFoundSetUpdater(foundset);
while( _oFsUpdater.next() )
{
    _oFsUpdater.setColumn("yearColumn",yearToSet);
}

Just make sure your original SQL (in sqlToRun) does end with the WHERE clause and no semi-colon at the end.

Hope this helps.

3

ROCLASI:

var _sQuery = sqlToRun + " AND pkColumn IN (" + databaseManager.getSQL(foundset) + ")";

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



Just make sure your original SQL (in sqlToRun) does end with the WHERE clause and no semi-colon at the end.

Hope this helps.

Now that is NEAT Robert - and yes all these queries end in a Where without the ‘;’.

Just out of interest - are you planning to take a day or so off this year - or even get some sleep :lol: ???

You are an ever giving fountain of Servoy knowledge Robert - thanks a LOT for your help! Sheesh - cant remember how many beers or bottles of wine I owe you now :shock: ?

4

Kahuna:
Just out of interest - are you planning to take a day or so off this year - or even get some sleep :lol: ???

Planning ? Sure…now taking it is another matter. ;)

Kahuna:
You are an ever giving fountain of Servoy knowledge Robert - thanks a LOT for your help! Sheesh - cant remember how many beers or bottles of wine I owe you now :shock: ?

I stopped counting, just bring a case of each. :D

Just Kidding! :lol: