Hi,
look at my example that I'm using for preparing two DB fou join into one
big.
My be it will help you.
-- Prepare data table for replication
-- Ensure that data in both databases are unique
-- No overlap during replication will occur
CREATE OR ALTER PROCEDURE Repl$SynchronizeDataTable(
MasterDB VARCHAR(200),
MasterUser VARCHAR(20),
MasterPass VARCHAR(20),
RelationName RDB$RELATION_NAME, -- Table that exists in
both databases and have to be synchronized
PrimaryKeyF RDB$FIELD_NAME, -- Primary key name
GenName RDB$RELATION_NAME, -- Used te retrive new id
if needed during synchronization
MasterIgnoreWhere VARCHAR(200) DEFAULT NULL --
) RETURNS (
Msg VARCHAR(70),
PrimaryKeyMaster INTEGER,
PrimaryKeyTarget INTEGER
)
AS
DECLARE ds VARCHAR(500);
DECLARE ds2 VARCHAR(500);
DECLARE dsu VARCHAR(500);
DECLARE ds_l VARCHAR(500);
DECLARE M_id INTEGER;
DECLARE GM_id INTEGER;
DECLARE cnt_total INTEGER;
DECLARE cnt INTEGER;
DECLARE MaxL_id INTEGER;
BEGIN
ds = 'SELECT '||TRIM(PrimaryKeyF)||' FROM '||RelationName;
IF(MasterIgnoreWhere IS NOT NULL)THEN
ds = ds ||' WHERE '||MasterIgnoreWhere;
cnt_total = 0;
cnt = 0;
ds_l = 'SELECT '||PrimaryKeyF||' FROM ' || RelationName || ' WHERE
'||PrimaryKeyF||' = :M_id';
-- Update statement for local DB
dsu = 'UPDATE '||TRIM(RelationName)||' SET '||PrimaryKeyF||'=:GM_id
WHERE '||PrimaryKeyF||'=:id';
Msg = dsu; SUSPEND;
-- Adjust sequence
ds2 = 'SELECT MAX('||TRIM(PrimaryKeyF)||') FROM ' || RelationName;
EXECUTE STATEMENT (:ds2) INTO :MaxL_id;
IF(MaxL_id IS NULL) THEN MaxL_id = 0;
Msg = 'Max local id:'||MaxL_id;
SUSPEND;
ds2 = 'EXECUTE BLOCK AS
DECLARE g_id INTEGER;
BEGIN
IF('||MaxL_id||'>GEN_ID('||GenName||',0)) THEN BEGIN
g_id =
GEN_ID('||GenName||',MAXVALUE('||MaxL_id||'-GEN_ID('||GenName||',0),0));
-- be paranoid, another transaction can change generator
END
END';
EXECUTE STATEMENT ds2
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass;
-- Check conflict and resolve it
FOR EXECUTE STATEMENT ds
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass
INTO :M_id DO BEGIN
cnt_total = cnt_total + 1;
PrimaryKeyTarget = NULL;
EXECUTE STATEMENT (:ds_l) (M_id := :M_id) INTO :PrimaryKeyTarget;
IF(PrimaryKeyTarget IS NOT NULL)THEN BEGIN
cnt = cnt + 1;
-- We expect that generator are always created without ""
ds2 = 'EXECUTE BLOCK RETURNS(id INTEGER) AS BEGIN '||
'EXECUTE PROCEDURE
MASA$Sequence_Check_Pool(UPPER('''||GenName||'''));'||
'id = NEXT VALUE FOR '||GenName||';'||
'SUSPEND;'||
'END';
EXECUTE STATEMENT ds2
ON EXTERNAL DATA SOURCE MasterDB
AS USER MasterUser
PASSWORD MasterPass
INTO :GM_id;
PrimaryKeyMaster = M_id;
EXECUTE STATEMENT (:dsU) (GM_id := :GM_id, id:=:PrimaryKeyTarget);
Msg = 'New id:'||GM_id;
SUSPEND;
END
END
Msg = 'Total:'||cnt_total;
SUSPEND;
Msg = 'Conflicts:'||cnt;
SUSPEND;
END
^
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:[email protected]
http://www.elektlabs.cz
On 12.1.2017 21:44, [email protected] [firebird-support] wrote:
>
> Hi all,
> What is the way to make a join from 2 tables in two respective database (in
> same server) ?
>
>
> ex :
> Db1
> TableA
>
>
> Db2
> TableB
>
>
> how can i make somethink like this :
>
>
> SELECT tA.MATRICRS, tB.name, tB.age
> FROM TableA tA
> JOIN TABLEB tB ON ( tA.ID = tB.ID)
>
>
>
>
>
>
> ps: I use FlameRobin to write sql statement.
>
>
