I have a global method in which I extract some records to an dataset using databaseManager.getDataSetByQuery(). I examine those records using various conditions because I’m really only testing to see if one of them meets certain criteria. However, if I find a record meeting the criteria I need to update it with a new value and this where I’m stuck. What do I need to do, to then locate and update the actual database records, either from the dataset or directly?
Your example would be great if I knew the indexes of the records I needed but I don’t.
Is there any documentation covering this topic in detail? because if I’m honest I do not fully understand foundsets and datasets.
When I looked at using .loadrecords() with a SQL query to filter the records I wanted to examine, it didn’t work and the sample info seemed to suggest that you could only do that when working with a form and I’m not, I’m writing a batch process in global method.
The sample Johan gave you is just the code inside the loop.
After loading the foundset with the SQL of data that you need, just do a loop to read all the records. The var index is the one incrementing the loop.
If you are not using a foundset of a form, just create it.
This code is version 6
/** @type {JSFoundset<db:/myserver/mytable>}*/
var fs=databaseManager.getFoundSet('myserver','mytable');
var cSQL="SELECT * FROM mytable WHERE status= ?";
var aArgs=new Array();
aArgs[0]=1;
fs.loadRecords(cSQL,aArgs);
for(var idx=1;idx<=fs.getSize(); idx++){
fs.setSelectedIndex(idx);
// Do your stuff here
}
I hope this helps.
If you still have problems with Servoy concepts I can advice you the Andy McGillys book http://www.mcgilly.com/
Thanks for the expanded example and book suggestion, I might well get it.
I’ve now created a test solution in which I have the following (the SQL argument would normally come from a global variable);
var lvSQLQuery = 'Select * From incoming where callid=?';
var lvSQLArgs = new Array();
lvSQLArgs[0]='1049674';
var lvIncFS = databaseManager.getFoundSet('logger','incoming');
lvIncFS.loadRecords(lvSQLQuery,lvSQLArgs);
var lvRecords = lvIncFS.getSize();
However, the query produces the followiong error. My table has 12 columns, is that too many?
ERROR: subquery has too many columns
Position: 35
Wrapped com.servoy.j2db.dataprocessing.DataException: ERROR: subquery has too many columns
Position: 35 (C:\Users\Tony\servoy6_workspace\incTest\globals.js#13)
at C:\Users\Tony\servoy6_workspace\incTest\globals.js:13 (onSolutionOpen)
But the primary key is just a unique number and I don’t know what range of id’s I would want. I’m getting really quite confused now.
All I want to do is query a database table, examine the records and if the right conditions are met I want to update one record.
It seems that with a foundset I can’t query on anything other than the primary key and with databaseManager.getDataSetByQuery(), I can’t update the actual records because all I’m doing is extracting them into an array/dataset.
Is there not a straight-forward method for this process?
Tony what Patrick means is that I made a mistake in the SQL sentence and he is right. The SQL sentence to load a foundset must select only the pk field of the table even the fiundset will contain all the fields. In fact you just need to use the SQL he sent you, that is the same that you had but selectin just the pk field.