Multiple databases per connection


With servers, let’s say MySQL, whenever you want to connect to them, you have to specify the host, the port, the user and the database. How is it possible to connect to multiple databases, so that a statement like SELECT * FROM database2.table1 can work? Hence you could connect to database1 and database2 with the same connection.

While making the connection only specify type of database-engine (MySQL), the host, the port and the user and forget the database. Access to the databases is controled by the rights of the specified user. At least that is what allways worked for me.

Not sure why you would want to connect to different db instances with the same connection. Just use one connection instance per database.

To run cross-database SQL queries, which can’t be done across two connections.

IF the DB’s are all on the same db server then with the appropriate rights you can, or should be able to, query across those two DB’s
MOST db server permit this

IF the DB’s are on two different servers (ie/ different IP addresses) then you need something else entirely that makes those two different servers appear to be on the same single IP so you can connect to that one server that “wraps” the others
Different db vendors have different ways of doing this

In mysql you probably want to look at FEDERATED tables (which assumes the other server is also mySQL)

If you are using SQLite… you can ATTACH multiple databases into a “virtual volume”… previous versions supported up to 10, and I think the latest version in Xojo supports even more.

The first database (to which you make the main connection is called “MAIN” but does not need to be specifally referred in querys) each addtional attachment you give a name to and use that name along with the table in any queries

SELECT a.field, b.field2 FROM main.table a,otherdatabase.table where some condion

I do not know what other database engines may or may not support this or similar structure.