Copying data from one datasource to another

Classic Servoy
1

Hi,
I have a solution that has one main database server and a second one that links to a view updated by the hospital IT Dept. with a list of Consultants names etc.
Because this view sometimes ‘disappears’ I have made a local copy of the table I need in my main database server, but need to be able to regularly copy the data from the view into my local copy to make sure it is up to date.
I am trying to do this with the rawSQL plugin, but can’t seem to work out the correct syntax or statements I need
I first tried

var done = plugins.rawSQL.executeSQL("rnoh_orth","adds_consultant_table",DELETE FROM adds_consultant_table;") ; // my local copy
	if (done)
	{
		done = plugins.rawSQL.executeSQL("rnoh_consultants","adds_consultant_table","SELECT * INTO #mytemptable FROM adds_consultant_table;") ; // the view
		if (done)
		{
			done = plugins.rawSQL.executeSQL("rnoh_orth","adds_consultant_table","SELECT * INTO adds_consultant_table FROM #mytemptable;") ;

but I got errors that #mytemptable did not exist (I thought it was going to create a temp table in memory.
Then I thought I’d try using a single statement and a qualified name

var done = plugins.rawSQL.executeSQL("rnoh_orth",
"adds_consultant_table",
"DELETE FROM adds_consultant_table;\
SELECT * INTO adds_consultant_table FROM rnoh_consultants.DBA.adds_consultant_table;\
COMMIT;") ;

which didn’t generate an error, but didn’t actually do anything 8-(

I am using Sybase locally with Developer, but my client is using MS SQL Server 2005, so I want to try and use standard SQL that will work across any back-end (I think my first lot is Sybase specific…) and I don’t want to have to use any external files for temp storage if at all possible.

Maybe I can load an array with the contents of the ‘view’, but then how do I bulk insert that into my (empty) local copy?

Can anyone advise me, please?

Thanks,
Rafi

2

Rafi,

I think multiple statements with semicolon don’t work with rawsql, you probably need to do them separately.
Also a commit should not be done at servoy level, not using rawsql.

If this table is not very big, a more simpler approach could be to copy the data via 2 foundsets.

Rob

3

Hi Rob,

rgansevles:
Rafi,
I think multiple statements with semicolon don’t work with rawsql, you probably need to do them separately.
Also a commit should not be done at servoy level, not using rawsql.

OK

rgansevles:
If this table is not very big, a more simpler approach could be to copy the data via 2 foundsets.

There are only about 100 records in the view…
Is there a simple command that will set one foundset (an empty one…) to another foundset?
I know there is the ‘databaseManager.copyMatchingColumns’, but I don’t think that will let me do what I want.
Of course I could write a loop to go thru the view foundset and then create new records one by one, setting each column, but I really thought I would be able to do it with a couple of simple SQL statements :?
Thanks
Rafi