foundsetupdater question

Classic Servoy
1

I have a form with a lot of editable fields. Clients need to be able to edit fields on the form, and then either update the record, or update the foundset.

I want to place a button that will update a field in the entire foundset, as an option for the clients. I have looked at the getFoundSetUpdater function, but as I see it I need to specify which fields to update, and with what data. As this is left to the clients, I have no way of finding out which fileds they want to update.

Can anyone think of a workaround for this. Is there a way in servoy for me to find out which fields have been edited?

Thanks in advance
Simon.

2

you could attach a method to a onChange for youre inputfields.
And remember this for the update. use things like:

var field = element.yourelement.getDataProviderId()
controller.getDataProviderValue(field)

3

I took your advice and am now using globals to store data out of any fields that change.

Can you tell me if its possible to use the getFoundSetUpdater over a relationship?

var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn(A_to_B.my_field, globals.my_edited_field)
fsUpdater.performUpdate()

I have tried this and it doesnt appear to work.

4

Try this:

var fsUpdater = databaseManager.getFoundSetUpdater(A_to_B) 
fsUpdater.setColumn('my_field', globals.my_edited_field) 
fsUpdater.performUpdate()
5

Is this code known to work, because it doesnt appear to make any difference on my solution.

Thanks
Simon

6

hjk sample should work fine.

If you start with -DSTACKTRACE=TRUE what do you see in the log when you do performeUpdate. You have to see the sql update statement that changes youre field..

7

I started servoy using,

java -jar -DSTRACKTRACE=TRUE servoy_developer.jar

However, when I searched the directories for modified files after trying the update_set method, no logs were being written. Where can I see the sql servoy is generating?

I am using Servoy 2.0, mysql database on Red Hat Linux (Fedora 2).

[/code]

8

if you do that then you should see that in the console where you start the java command.

9

Sorry my mistake, typed TRACK instead of TACK! :oops:

Having looked at the log, I am still none the wiser. I can see the sql statement, which looks correct, and it appears to be doing something with the 9 PKs I am using to test the method.

However, the foundsetupdater is not actually updating the records in the database. Would you have time to have a quick look at the output if I post it on here?, I will only show the bit immediatly after I execute the method. You may be able to see something in there that might give some clues as to why the updater isnt working.

Simon

10

post it

11

Thanks for having a look.

used sql select nascode, seed_type_id, mutagen_id, background, pedigree, ploidy, transformation_method from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5290
sql update original set original.background = ?  where original.nascode = ?
questiondata[0] (test)Ler-0 (Landsberg erecta)
questiondata[1] 5290
used sql select original.nascode from original order by original.nascode
flushing relation 1325 size 1
flushing relation 1327 size 1
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5290
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5292
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5290
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select nascode, seed_type_id, mutagen_id, background, pedigree, ploidy, transformation_method from original where original.nascode = ?
questiondata[0]= 5290
queryForRelatedFoundSet: 1327 1 6226169 useCache: true
used sql select seed_type_id, seed_type_name from seed_type where seed_type.seed_type_id = ?  order by seed_type.seed_type_id
questiondata[0]= 1
-----------Data query for Cache not needed... (query done for pk)
queryForRelatedFoundSet: 1325 27 6226169 useCache: true
used sql select mutagen_id, mutagen_name from mutagen where mutagen.mutagen_id = ?  order by mutagen.mutagen_id
questiondata[0]= 27
-----------Data query for Cache not needed... (query done for pk)
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5294
flushing relation 1325 size 1
flushing relation 1327 size 1
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5295
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5297
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5293
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5298
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5296
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 5291
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select original.nascode from original where original.nascode = ?  order by original.nascode
questiondata[0]= 1
flushing relation 1325 size 0
flushing relation 1327 size 0
used sql select nascode, seed_type_id, mutagen_id, background, pedigree, ploidy, transformation_method from original where original.nascode = ?
questiondata[0]= 1
12

i see one update statement:

sql update original set original.background = ? where original.nascode = ?
questiondata[0] (test)Ler-0 (Landsberg erecta)
questiondata[1] 5290

is this the one?
Isn’t that stored in the db?

13

Yes that update works fine and is stored in the database, but the remaining 8 fields in the foundset are not updated with the following method.
You can see all their PKs in the log output, but I cant see if they are being used anywhere to update the original table.

var fsUpdater = databaseManager.getFoundSetUpdater(stock_to_original);
fsUpdater.setColumn('background',globals.background);
fsUpdater.performUpdate();
14

which 8 remaining fields??
is that piece of code you show me all the things you do? Because that is exactly what it does. As it is supposed to do.

15

I want to update every record in my foundset of 9 records with whatever is in the globals.background.

I am looking at a form with fields from table A, and fields from the related table B. If I change something in the A.field and use the getFoundSetUpdater, it updates all the fields in that foundset.
I want to be able to do the same thing on this form with the fields that are being displayed from table B, through the relation A_to_B. Background is in the B table.

This code is just a simple example of what I want to do. Sorry if it hasnt been clear, I hope you can see what I am after.

SImon

16

it does do that.

All the records that are in that related foundset (stock_to_original) that is based on original.nascode code column are updated.

So every row that has a nascode value of 5290 is updated to the global value. Isn’t that what you want?

How many records does stock_to_original have?

or do you mean that for every parent record (stock) you have in the parent foundset. You want the relation stock_to_origianl and then the original.background value set to the global value??

If that is the case then you have to first convert youre foundset.

var converted = databasemanager.convertFoundset(foundset,stock_to_original);

and then use that converted foundset in youre code:

var fsUpdater = databaseManager.getFoundSetUpdater(converted); 
fsUpdater.setColumn('background',globals.background); 
fsUpdater.performUpdate();
17

Thank you very much, it is working now! I needed to convert the foundset so I could update every record in original, that had a nascode in my stock foundset.

Hopefully I wont have anymore problems with this…

Cheers
Simon

19

Not sure if the last message was meant for me?

Anyway, I tried to update 150,000 records and the solution crashed and ran out of memory. I could see that it was creating and executing each update statement. Do you plan to implement a way to create custom update statements in the future?

20

Use a transaction if you want to make sure all records are done or none at all. (did you use Servoy 2.1 when running out of mem?)