How to use SQL to query tables from different database from the same server

Question

Question

Cache Newbie · Jul 17, 2021

#Caché

Hi guys,

need some help here. How do i use sql to query tables from different database which reside in same server?

Product version: Caché 2017.1

Discussion (2)1

Log in or sign up to continue

Nigel Salm · Jul 17, 2021

Hi

Just one additional comment. If the databases/namespaces are in different instances on the same or different servers, then you need to create a remote database connection to the databases on the secondary server (the server instance which contains the tables you want to use in queries in your primary server instance). Once you have made the secondary databases available to your primary server then you can create a namespace on the primary server to include the database or databases (if you split your globals into one database and your routines into another database) and then you can use Package Mapping on the primary Server Namespace to map the Class/Table Packages into the Primary server Instance. What you don't want to do is map the globals associated with the secondary server namespace into the primary server namespace as this will cause the globals to be created in the primary server namespace database which means that if you ever disconnect the servers then the data that rightfully belongs to the secondary server will be orphaned in the databases of the primary server.

Nigel

Yours

Nigel

0 0

score 0 · Answer 1 · 2021-07-17T05:53:36-04:00

There are two ways to do that:

1. Mappings. Map classes and globals into the namespace you're querying from. This would work if there's no global/class collisions of course.

2. Custom queries. Here's an article on the topic. And here's an example.

3. Just set $namespace before running your query. Works only if you need to query one other namespace at a time. Example.