ConvertFoundSet between different databases

Classic Servoy
1

Hi,

For quite a while I have been doing things in Servoy between different databases where I will have a record in Sybase ASE or Oracle and look up related records in a completely different MS SQL database. However that has been taking one record and then using a common key to look up related records in the other database.

I now have a situation where I have a few thousand records in one database that I want to crosscheck in another database. Some of them will have related records and some will not. I want to simply find the ones that have related records and ‘mark’ them. I was planning on using the ConvertFoundSet method to do that. However I don’t think that works and thinking about it I’m not sure that it could work in any kind of efficient way. I notice also for instance that even though I have set up the relationship just fine and it displays fine on an individual record, I can’t for instance sort on related columns nor do a ‘find’ using related columns.

So my questions are:

  1. Am I correct and this will NOT work (ConvertFoundSet between different databases)?

  2. If that is true, do you think at any point in the future it could work? Perhaps even in a limited, batch-type mode (i.e. maybe ‘relate’ say 200 records at a time)?

  3. Right now one of the relations where I am trying to get this to work is between Sybase ASA and Oracle. If I move the Sybase ASA database to an Oracle Schema and then have a relation between one Oracle Schema and another will it then work? I’m very new to Oracle and don’t understand its basic set up that well which is why I created this new database in Sybase. I have a ‘development’ schema though that I could try this out on in Oracle but would that work any better in a relation to the ‘production’ schema in Oracle (both schemas run on the same Oracle server) or do both have to be in the same schema?

Even if that works (true relationships between different Oracle schemas on the same server) it won’t solve everything for me because I will also certainly have a relationship to MS SQL Server and that data will have to remain there. So I’d still appreciate any feedback on question 2 that anyone might have.

  1. Finally I guess I could loop through records looking for ones that are related and then mark those as Servoy ‘finds’ them. There aren’t that many records in either the Oracle or Sybase databases (a few thousand) so that might work. And after the first time, each week it will just be a few hundred. In finding records in Oracle or Sybase that relate to the MS SQL Server database there will be almost a million records in SQL Server. However I will still just be looping through a few hundred records at a time in Oracle or Sybase and not the million or so in SQL Server. Has anyone done much of that (looping through records where the count is maybe a thousand or more records)? Any advice/thoughts greatly appreciated. :)

John

2

1> convert foundset over a multi server relation will not work.

2> No conver foundset doesn’t use any data and nor will it in the future it just rewrites the sql. And that is not possible for a multi server relation

3> in 3.0 we have now better schema support but when this are still 2 server connections it still will be a multi server relation so the same things as in 2 applies.

But to summaries you only want to update a column on server X that has a field Y that is in Server Y?

so in the end the update statement looks something like:

update tableX set markcolumn= 1 wheren fieldY in (10,11,12,20)

?
If that is the case then don’t find in Server X but just harvest the field values form Server Y and do that update statement youreself through the SQL Plugin.

3

Hi Johan,

Thanks for the reply. I ended up just looping through the records one-by-one with surprisingly fast results:

The problem with using the RawSQLPlugin, I was thinking, is that I needed to involve two tables (without having a huge ‘IN’ statement). In other words I am really doing the update based on a relation:```
WHERE serverX.tableA.columnB = serverY.tableC.columnD

//collect a value if a ‘related’ record exists
SELECT serverY.tableC.columnX FROM serverY.tableC where serverY.tableC.columnZ = ‘valueFromCurrentControllerForm’


and then set that 'selected value' to my chosen dataprovider on the current form. The big advantage of doing it this way with the RawSQLplugin, I think, is that one can loop through the records without involving the GUI (i.e. with foundset.getRecord(i) instead of foundset.setSelectedIndex(i)). I'll try that. Does that sound like a better way to go about it than the way I way I did it earlier?