Servoy Forum

Hi,

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?

Kind regards,

Tony

why are you extracting some records by converting it to a dataset?
why not directly use the records and update when needed?

Hmmm....lack of knowledge...I've only gotten this far by playing around with samples. I couldn't see how to access the data directly.

How would I do this? Can you point me in the right direction?

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.

Kind regards,

Tony

Hi Tony.

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

Code: Select all

/** @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);

Code: Select all

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)

when loading foundsets, query for the primary key:

Code: Select all

var lvSQLQuery = 'Select incoming_id From incoming where callid=?';

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.

Yes but querying only on the database pk is no good for what I'm trying to do.

Kind regards,

Tony

with a foundset you can query on anything you want from that record, just look at the documentation/sample of Foundset.find()/Foundset.search()

Ok Tony, then you can create a dataset with the SQL you need and load the foundset based on that dataset.

Code: Select all

var _dataSet=databaseManager.getataSetByQuery('myserver','SELECT .......',Arguments,-1);
fs.loadRecords(_dataSet);

fs is the foundset you initialized before, like I did in my code. After that you can loop the foundset.

Thanks everyone, this has been an invaluable lesson and I have finally acheived what I was trying to do.

Kind regards,

Tony