Cross database query engine with Oracle

[kasper360]

Hi,
I am developing an application where I want to query data from tables in 2 different databases at once. I am using Oracle as the database but my employer wants me to achieve this without making a database link between the 2 databases. So I thought of developing a cross database query engine using Qt sql module objects. But it should not be as complex as an sql parser. I just want query data from tables in two databases and display them in 1 view using a common interface. If there is anyone who has done this kind of programming before or someone who has an idea about this, your suggestions are much appreciated. Thank you very much.

[ChrisW67]

Is this for a particular small set of queries, or a general query tool? In the first case you just run two queries against the two databases and perform the join manually (hard-coded) at the client. In the second you have no option but to replicate a good chunk of SQL query logic either in a parser or some GUI query construction tool in order to know how to do the join at the client.

Another possibility would be to pull both sets of data back into a local Sqlite database (in-memory or on-disk) and performing the query against that. This is obviously not practical for millions of rows or very sensitive information.


Pulling the processing back to the client is nowhere near as efficient as doing it in Oracle, especially if the data sets are large. Is there a good reason to fork out big dollars for Oracle and then avoid using it?

[kasper360]

This is probably going to be a general tool since I may have to use it unknown number of times in different ways. (because this cross database query engine is only a small part of the application) So hard coding the queries is not an option.

And for your last question, think of it this way. The users of this application will log in using TNS aliases. And each alias has different privileges. These privileges will prohibit some users from accessing some tables. That's why I was asked not to use database links.

I'm using these aliases to connect to 2 database schemas called ATS and SER. And I want to query tables in both these schemas. What I have done so far is I have created 2 QSqlDatabase objects and connect to each schema using the TNS alias. But in order to make cross database queries, i have to do it manually each time, which is a very hectic task. so I want to develop an common interface (possibly an API) and call some methods to make these queries

[majorwoody]

And for your last question, think of it this way. The users of this application will log in using TNS aliases. And each alias has different privileges. These privileges will prohibit some users from accessing some tables. That's why I was asked not to use database links.

I don't understand the problem with database links. You have two schemes and different users. You can make a database link and give synonyms for tables in the linked database. And finally you can grant or revoke access to tables in the database and the linked database for every user.

But in order to make cross database queries, i have to do it manually each time, which is a very hectic task. so I want to develop an common interface (possibly an API) and call some methods to make these queries

You have to create your own QAbstractTableModel like QCrossSqlQueryModel, which gets two queries on two connections.

[kasper360]

I don't understand the problem with database links. You have two schemes and different users. You can make a database link and give synonyms for tables in the linked database. And finally you can grant or revoke access to tables in the database and the linked database for every user.

Thanx for this info. I'm not much familiar with database links because this is the 1st time I've even considered to use them in an application. I'll discuss this with my employer and see if there are any other causes why he wants me not to use it.