Methods to Copy / Insert Between Db's (NooB stuff again)

Classic Servoy
1

Hi Folks - with the transition from VB to the Servoy methods I’m struggling to get even basic (no pun intended) stuff straight in my head.

The basis of a lot of the coding I need to do is copying records from one source Db to another (the main) Db. And some of these data management processes really don’t use a form - other than having some place to put a button to explain and start the process.

For example, to copy a set of records from a flat file table in Db_1 to a Main & Related tables in Db_2 is simple in VB - with the following format:

Open recordset on Db_1 - table_a
Open recordset on Db_2 - table_b
Open recordset on Db_2 - table_c

recordsetDb_1.MoveFirst

Do While… etc

Edit recordsetDb_2_table_b
Add new_record to recordsetDb_2_table_b
… Add fields from Db_1 to equal fields in Db_2
i.e. table_b.column1 = table_a.column1 etc.
Save recordsetDb_2 (to create a PK)

Edit recordsetDb_2_table_c
Add new_record to recordsetDb_2_table_c
…table_c FK = table_b PK
table_b.column10 = table_a.column10 etc
Save recordsetDb_2

recordsetDb_1.MoveNext

Loop etc.

The plethora of methods in Servoy has me struggling to determine the direction I should head in to replicate this kind of functionality? Most of the methods relate to Servoy forms rather than the kind of ‘behind the scenes’ data manipulation we have used in the past.

Q’s

  1. Should I be using a FoundSet method for each of these recordsets and if so which foundset-method (getFoundSet)?

  2. Can I use a related-recordset to allow single-step input to the main and related tables?

  3. Using this kind of coding - will I need to flush the ClientCache, and if so, how will that impact on other clients connected to the same solution?

I’d appreciate some guidance from those ‘old-hand’s’ in Servoy, and some ‘air’ code would be a useful guide to get started.

TIA

2

Kahuna:

  1. Should I be using a FoundSet method for each of these recordsets and if so which foundset-method (getFoundSet)?

A foundset is an object of a form. When you use datasets for example you don’t have foundsets (the dataset is your ‘foundset’).
So your method of coding depends on what objects you are using.

Kahuna:
2. Can I use a related-recordset to allow single-step input to the main and related tables?

Yes you can ‘talk’ to a controller of a related foundset like so: forms.formName.relationToOtherTable.controller.newRecord().
And then assign data to those records like so: forms.formName.relationToOtherTable.columnName = myData;.

Kahuna:
3. Using this kind of coding - will I need to flush the ClientCache, and if so, how will that impact on other clients connected to the same solution?

You only want to flush the clients cache when you use a technique that doesn’t tell Servoy server to broadcast any changes made to the other connected clients. For instance when you use a third party tool outside of Servoy. Or when you use the rawSQL plugin.
Any other way of data entry in Servoy will/should broadcast the changes to the connected clients and therefore don’t need any flushing of caches.

Kahuna:
I’d appreciate some guidance from those ‘old-hand’s’ in Servoy, and some ‘air’ code would be a useful guide to get started.

You say you don’t use forms for all datasources other then having a button to start these import/conversion processes.
I assume that only counts for the external flat file databases you are trying to import, right?
So what you want is to get all the data from those files and then use existing Servoy forms to insert data into your new datamodel.
Then you code would look like this:

var sServer	= "connectionNameToFlatFile",
    sQuery 	= "SELECT * FROM myFlatFileTable;",
    ds		= datatabaseManager.getDataSetByQuery(sServer, sQuery, null , -1);

// Check if there were no errors
if ( !ds.getExceptionMsg() ) {

	// Loop through your result set
	for ( var i=1 ; i<ds.getMaxRowIndex() ; i++ )
	{

		forms.myParentForm.controller.newRecord(true);
		forms.myParentForm.myColomn1 = ds.getValue(i,1); // first column in the dataset
		forms.myParentForm.myColomn2 = ds.getValue(i,2); // second column in the dataset
		forms.myParentForm.myColomn3 = ds.getValue(i,3); // third column in the dataset
		// etc.

		// If you use database managed sequences you need this here to make sure you have a PK.
		forms.myParentForm.controller.saveData();

		// Now create any child records and fill them
		forms.myParentForm.myParentClientRelation.controller.newRecord(true,true);
		forms.myParentForm.myParentClientRelation.myRelColomn1 = ds.getValue(i,4);
		forms.myParentForm.myParentClientRelation.myRelColomn2 = ds.getValue(i,5);
		forms.myParentForm.myParentClientRelation.myRelColomn3 = ds.getValue(i,6);
		
		// REMARK: 	Maybe you actually need to make multiple child records.
		//			Then you simply put them in a for loop.
		//			And handle them accordingly.

	}

} else {
	application.output("ERROR:" + ds.getExceptionMsg()); 
	// Something went wrong
}

// Done

Hope this helps.

Edit: fixed a bug in the example code

3

WOW - thanks for that feedback Robert! Much appreciated.

This is really helping me see the form / data inter-structure. I hadn’t really realised the difference between a dataset and a foundset - nor how the form is actually acting as a Main_Table server link (if that makes sense ;-).

Thanks a bunch Robert.

4

Hi Kahuna,

Kahuna:
This is really helping me see the form / data inter-structure. I hadn’t really realised the difference between a dataset and a foundset - nor how the form is actually acting as a Main_Table server link (if that makes sense ;-).

Yes a form is always in the context of a database table. So a form represents a table.

Kahuna:
Thanks a bunch Robert.

Your totally welcome :)

5

hi,

there is also a function: copyMatchingColumns, which we use a lot when we migrate data, from one DB to an other DB.

Hope this helps.

6

Thanks for that feedback too Harjo.

Do you use that copyMatchingColumns only for flat file types of migration or can it be used relationaly too?

7

only for db’s that has the same columns

8

Cool - definately worth investigating since we have a number of dabase migrations to do.

Thanks Harjo