Query two databases (both SQL Anywhere 12)

[huber]

How can I query two databases? In my case two SQL Anywhere 12 databases. They use the same database server name.

I would like to do something like:

Code: Select all

SELECT onedb.*, otherdb.*
FROM server1.Database1.dbowner.myTable AS onedb
INNER JOIN server2.Database2.dbowner.myTable AS otherdb
   ON onedb.id = otherdb.id

[rgansevles]

Robert,

Check on sql anywhere forums if this is possible.

Servoy will only join across databases when they are in the same jdbc connection.
If you want to use server2.Database2.dbowner.myTable in server1 you would need to expose it in server1, possibly as a view.

Rob

[huber]

Rob

I checked in the SQL Anywhere forum, unfortunatly it's down at the moment. I will try later on of course.
I didn't know two databases can be in the same jdbc connection. Is this possible to use twice the same database server name?
In distributed database environment my asking should be a "normal" requirement, shoudn't it?
Thanks for the hint about the view.

Robert,

Check on sql anywhere forums if this is possible.

Servoy will only join across databases when they are in the same jdbc connection.
If you want to use server2.Database2.dbowner.myTable in server1 you would need to expose it in server1, possibly as a view.

Rob

[sbutler]

you can do that in MySQL without any changes. For SQL Anywhere, I think your looking for is called proxy tables, where you create a table in your otherdb that is a proxy to onedb, and then you can join them with a sql query.

[mboegem]

For SQL Anywhere, I think your looking for is called proxy tables, where you create a table in your otherdb that is a proxy to onedb, and then you can join them with a sql query.

Pretty sure this is the case or at least was back in versions 10/11, don't know if Sybase changed the game in 12.