I am attached to two different SQL backends, an M$SQL 2000 and a Sybase.
I’m having an issue with a query this is supposed to look at either backend for answers…
Here is part of my SELECT statement:
query += " FROM iciwhs, sostrs, poptrs, sostrs_contact_mgmt, aritrs_contact_mgmt "
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows )
The first three tables are from M$SQL2000, the last two are from Sybase.
Is it possible to query two separate databases in one SQL query?
The reason why I don’t list the entire code is because the columns that are queried, both in M$SQL tables and the Sybase table, are dynamically selected by the user…
If I allow the user to select those columns from just one database or the other then everything works fine.
If they select columns from both databases, I run into trouble…
As you have noticed the problem lies in the getServerName().
You can’t have a single named connection to 2 databases. And the databaseManager only accepts 1 servername per query.
Servoy does offer joins and such over different named connections but only when you let Servoy handle the queries.
When you start using your own SQL you need to handle all that yourself.
In general it is not possible to query over two different databases. There has been a discussion about that on the forum. You might find a way to do this using linked tables in Sybase (that will allow you to look at MS SQL tables as if they were Sybase tables).
A quick a dirty way would be to run the query twice on 2 different forms, one linked to database A and the other to database B and dump the results into another table.