ManyToMany SQL in a query component

[nokib8]

Hello.

I try to manage a many to many SQL and this works as long as the three table involved is in the same database in my test project.

In my real project unfortunately the tables involved is in different database,
thus with different connection components.
The problem is that the query component allows only one connection component to be registered.

How do I solve this?

(The two tables is Person and Pictures linked in a junction table, where one person can be registered with several pictures and one picture with several persons.)

Thanks in advance.

Kai Inge

[azyk]

Please specify the database you are working with.

[nokib8]

Hello.

My environment is as follows:
Win7
Delphi 2009E CodeGear™ RAD Studio 2009 Version 12.0.3420.21218 Copyright © 2009 Embarcadero Technologies, Inc.
madExcept 4.0.9
Character Set: UTF8
Raize components 6.1.2.0
UniDac components 61d12pro, like: TInterbaseUniProvider->TUniConnection->TUniQuery->TUniDataSource->TUniDBGrid.
Firebird 2.5.0.26074
Database Workbench 5 pro

The Code I try to manage in a TUnyQuery is like this:
procedure TfrmMtMd.qryPersAfterScroll(DataSet: TDataSet);
begin
qryPictureR.Active := False;
qryPictureR.SQL.Clear;
qryPictureR.SQL.Add('SELECT * FROM BILDER WHERE ID IN (SELECT BILDEID FROM PERSONS_PICTURES WHERE PERSONID = ' + QuotedStr(qryPers.FieldByName('ID').AsString) + ')' );
qryPictureR.Active := True;
end;

The problem is that the TUniQuery component can only be connected to one of the two databases at a time.
'PERSON' and 'PERSONS_PICTURES' is in one database and 'BILDER' is in another database.
Therefore one of the conditions will have an unknown 'ID' field, depending of which database connection is missing.

Where can I put the code so both databases is 'known' by the component?


Kai Inge

[ViktorV]

This issue belongs to Firebird, not to UniDAC. Firebird does not allow to use different databases in the same SQL statement. Firebird 2.5 introduces a way to request external data sources in PSQL. EXECUTE STATEMENT has been extended with a special clause: ON EXTERNAL DATA SOURCE, and you can supply a different Firebird database as a data source. See more details in the Firebird documentation.