Can I do a multi table query (join) over more servers?

Classic Servoy
1

Is it possible to do a query over more than one server?

Working with joins works like a charm, working with aliases is also no issue but it looks like working with more than one server is not possible or do I mistake here?

2

can you give me an example how such a query should look?

Because that would be impossible to do.. To which server do you send the sql statement?

You just need to have 2 queries that will be send to server1 and server2

3

just for info:
A relation can go over 2 servers…
Then servoy will generated that query for you when viewing data.

4

I know relations can be used over more than one server but then I would have to build a lot of extra relations for a sometimes used query;

A query could look like:

var query = "SELECT I.invoicesid FROM invoices I, organization O WHERE I.invoicedate>='" + utils.dateFormat(globals.newDateTime, "yyyy-MM-dd 00:00:00.000") + "' AND I.invoicedate<='" + utils.dateFormat(globals.dueDateTime, "yyyy-MM-dd 23:59:59.999") +"' AND O.accountmanager='" + globals.Accountmanager + "' AND I.organizationid=O.organizationid AND (I.status=2 OR I.status=4)"

In this case invoices is on another dbserver than organization…

Or is this the moment for stored procedures?
And, if so, where can I pick up some good info on that for ASA?

5

Marcel,

I know some Databases support this feature you’re looking for. For example in Oracle, you can define DB link in on Database level. Once defined, you can say “select * from tableA a, tableB@db2 b where a.field1 = b.field1”

In this case, you send the query to DB1, and on DB level, the connection will be made to the second DB.

I do not know if other DB’s, like fireBird or Sybase, support this. If they do, i think it would solve your problem.

Paul

6

With ASA you can join multiple databases. A good starting point would be the documentation. How about this book/chapter:

ASA SQL User’s Guide
Accessing Remote Data
Joining remote tables

and

ASA SQL User’s Guide
Accessing Remote Data
Joining tables from multiple local databases