How to join tables in two Firebird databases?

Question

Currently I'm working on a simple library project using Embarcadero C++Builder 10.3 Community Edition, and Firebird and FlameRobin to create databases.

So far, I need only use simple queries, that were connected to a single database. Therefore, I used TFDConnection and TFDPhysFbDriverLink to connect to a .fdb file. Then, TFDQuery to create SQL commands and TDataSource. It works great.

Unfortunately, now I must join two tables. How do I write this command? I tried this:

SELECT * FROM users_books 
join books on
users_books.id_book = books.id

where users_books and books are databases.

I got an error:

SQL error code = -204
Table unknown
BOOKS.

So I think I must connect somehow to these two databases simultaneously. How to do that?

Answer 1

Firebird databases are isolated and don't know about other databases. As a result, it is not possible to join tables across databases with a normal select statement.

What you can do, is use PSQL (Procedural SQL), for example in an EXECUTE BLOCK. You can then use FOR EXECUTE STATEMENT ... ON EXTERNAL to loop over the table in the other database, and then 'manually' join the local table using FOR SELECT (or vice versa).

For example (assuming a table user_books in the remote database, and a table books in the current database):

execute block
  returns (book_id integer, book_title varchar(100), username varchar(50))
as
begin
  for execute statement 'select book_id, username from user_books'
    on external 'users_books' /* may need AS USER and PASSWORD clause as well */
    into book_id, username do
  begin
    for select book_title from books where id = :book_id
      into book_title do
    begin
      suspend;
    end
  end
end