0

Is attaching the database supported only by SQLite and MS SQL Server?

All other DBMSes can only have one database per connection?

Scenario:

Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period.

The database in use is not the two mentioned above.

How do people solve that?

EDIT:

The plain SQL supports the syntax of catalog.schema.table which produces fully qualified table name to the query.

However I believe that in order to use that syntax I have to have a connection to the second database.

Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1 and then just use the appropriate syntax.

However if those 2 databases are physically located on 2 different servers, I think I need to create a connection to the second database in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s).

Now, my understanding is that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes.

Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2.

TIA for confirming or deniying my suspicions.

1 Answer 1

0

all rdms support

Db.table

SQL SERVER

[db].[dbo].[Table]

So you can make a query

SELECT a.col1,b.cl2 FROM db1.table1 a INNER JOIN db1.table1 b ON a.id= b.id

That would get you columns from 2 databases, on one connection.

2 connections make the thing more complicted

MYsql can establish federated tables http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html so that you have scenario 1 again. (that is possible for almost every rdms)

For Postgreslq exists something similar see https://www.postgresql.org/docs/9.3/postgres-fdw.html

Else you can only get the data from connection/Server 1 and compare it to the data received by server 2, by means of a variables

(the syntax differs somewaht from rdms to rdms)

5
  • you are talikng abut twi servers, with seperated connections, i expanded my answer
    – nbk
    Commented Oct 10, 2020 at 17:58
  • IIUC, when I estabish a connection to the DB server I either have to supply a db name or issue use <db_name>, therefore all queries will be going into this specific database. What I'm looking for is get the information from another DB without disconnecting. One connection handle - 2 DB. AFAIK, only SQLite (ATTACH command) and MS SQL Server (CREATE DATABASE...FOR ATTACH command). Then I can use the appropriate syntax. Are you saying all other DBMSes support something like this? If yes - how? I know PostgreSQL didn't have a notion of attached db - didn't support it.
    – Igor
    Commented Oct 10, 2020 at 20:05
  • mysql has fedrated tables postgresql postgresql.org/docs/9.3/postgres-fdw.html, this makes an external datasource available in the host system, so that yu can use it. directly, but as i explained before, when you want to mix tow dofferent connections, you make a select in one and on the other connection you make alo a select and then compare them, or update them
    – nbk
    Commented Oct 10, 2020 at 20:14
  • from that link The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external PostgreSQL servers.. I presume federated tables for mySQL is also a remote server thing. Does this mean that I can't access second db on the same server? And what about Sybase/Oracle?
    – Igor
    Commented Oct 11, 2020 at 16:17
  • as always give it a try, federated tables are tables, that are mirrored local copies of tables from other servers and you can access it like it was on your server and use them in selct views or what you need
    – nbk
    Commented Oct 11, 2020 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.