Use of foundset node to move data from one table to another

Classic Servoy
1

Hello all,

I am in the midst of learning the databaseManager node and having some difficulty with direct reads and writes of foundsets. Code snippet follows using a limited loop (5 records):

var fs_in = databaseManager.getFoundSet('Production','tr_notes')
var fs_out = databaseManager.getFoundSet('cluster','dcm_notes')
var fs_cnt = databaseManager.getFoundSetCount(fs_in);

for (var i = 1; i <= 5; i++)
{
	var rec_in = fs_in.getRecord(i);  // load a record from the foundset
	fs_out.newRecord();  // write a new blank record
	var work_val = fs_in.getDataProviderValue('subject');
	fs_out.topic = work_val;
	globals.dcm_debug(work_val)
}

databaseManager.saveData();

globals.dcm_debug('Done');

Globals.dcm_debug is a simple dialog I use when testing routines. The variable work_val always returns a null and valid string data is in the database. fs_cnt evaluates properly to the inbound table row count.

Am I using the above methods properly or should I be going through another function/object (controller)?

I am trying to learn how to interact directly with the database (bypassing a form) for performance/speed reasons. Basic goal is to move data from one table to another (but data will need some custom massaging on a record by record basis on the way through so foundsetUpdater is not generally appropriate … I think).

Thanks, Michael

2

Without looking really close at your code, I can say that your foundsets are empty. You will have to do something like

var fs_in = databaseManager.getFoundSet('Production','tr_notes')
fs_in.loadAllRecords()
var fs_out = databaseManager.getFoundSet('cluster','dcm_notes') 
fs_out.loadRecords(query, [queryArgs]);

Hope this helps.

3

uhhm,

for (var i = 1; i <= 5; i++) 
```Would never do that. You should check the size of the foundset to be sure it is not smaller (or even null).

var work_val = fs_in.getDataProviderValue(‘subject’)

It is a way to find the value of a dataProvider per form element (see documentation)...
4

Patrick - thanks, - will give it a try.

… the for-loop test for <=5 is not production code (just trying to get the hang of direct foundset interaction in this test snippet). Appreciate that there are no boundary tests (e.g. - null foundset) conditions. Thanks for the tip on getDataProviderValue … I was wondering if I was working with the right methods!

5

Hi Patrick and Marcel, others:

I think I am starting to understand some of the basics here (300 rows/second load with fairly large Notes fields).

The part I don’t understand in your response yet is why one should use loadRecords(sqlstring) and bring in the primary keys (I just used newRecord()). Is loadRecords a more efficient loading mechanism with the SQL clause (I have shown my PK string - presently commented out)? Timediff code is only temporary (a bit cludgy!).

I am wondering if it might be better to move the inbound foundset over via a sql clause (and so your earlier response on loadRecords(,) on the outbound foundset). I am thinking that perhaps my fs_out.newRecord() approach is not performance optimal.

Michael

var time_open = application.getTimeStamp()
var fs_in = databaseManager.getFoundSet('production','tr_notes')
var fs_out = databaseManager.getFoundSet('cluster','dcm_notes')
var fs_cnt = databaseManager.getFoundSetCount(fs_in); // boundary tests not in place yet
fs_in.loadAllRecords();

for (var i = 1; i <= fs_cnt; i++)
{
	var rec_in = fs_in.getRecord(i);  // load a record from the foundset
	fs_out.newRecord(false, true); // add record to the output foundset and move the pointer

//	fs_out.loadRecords('select dcm_notes.dcm_note_id from dcm_notes order by dcm_notes.dcm_note_id');

//	load the target fields from source inbound record
	fs_out.topic = rec_in.subject;
	fs_out.note = rec_in.notes;
}

databaseManager.saveData();
var time_close = application.getTimeStamp()
var time_diff = time_close.getSeconds() - time_open.getSeconds();

globals.dcm_debug('Done in ' + time_diff + ' seconds')
6

To be quite honest I think Patrick is the one to answer here, he gave you the suggestions.
I don’t see why you should use a sql statement.

7

I was just giving examples on how you can fill a foundset in general. Since I don’t know what data you actually want (all records, some records), I couldn’t be more precise.

8

Thanks, Patrick! Your input appreciated,

Michael