The model of EDB seems to be:

Session -> Databases.

The "session" really represents a pointer to the EDB Server engine, which might be local (i.e. just a folder) or Remote.

A SQL Query is written against a Database, but the USE clause can be added to allow different parts of a script to query different databases within the session.

i.e. if I have a session: "MainOffice" with databases "LiveData" and "Summary" I can write 1 query which compares the data in both "LiveData" and "Summary".

--

However, I would _like_ to be able to run a query against a reference database which is on a totally different machine (IP Address 46.XXX.XXX.001), and therefore has to be in a different SESSION, and compare this with data in another database (IP Address 10.0.0.254) ...

Can this be done?

Adam


Basically no. I am not aware (but the way my memory's been recently that's no guarantee) of any mechanism to allow the session boundary to be bypassed.

I don't think you can do as would have worked in DBISAM and stream the table into the local database.

Two ideas:

1. nick Tim's reverse engineering code, use it to reverse engineer the table you want, use that to create a copy of the table in the local database
2. I'm guessing its a sort of "does this exist" type query. If so you could create a query into a stringlist using information from one database and use that against the other.

A bit more information may prompt some more ideas. Or better still someone may say its easy and here's how.....

Roy Lambert [Team Elevate]

I can't see a way round it in "raw" EDB, but I am such a beginner I may just be missing some key knowledge.

My ideas for a work-around (assuming it isn't possible directly in EDB) included:

* A JOB on the REMOTE DB which saves a single table to a STORE, then the LOCAL machine copies down this file & perhaps deletes it (??).

* Write an external MODULE for EDB which calls a hard-coded REMOTE db (doing this is actually beyond my knowledge right now ... but I think I could figure it out!)

* transfer control of the process to the DELPHI application ... in that application have 2 DB connections & manually pass the new value across there.

--

Of the above the third is actually probably the easiest, but I was hoping to do the whole thing in EDB (i.e. within the Database Definition) so that the framework could be independent of the GUI / programming, to make it more adaptable.

To keep it "in EDB" I _think_ I have to use one of the first 2 options, but I am still crossing my fingers and hoping there is an easier way.

--

All I am trying to do is store an ID range locally (i.e. numbers 10,000 - 20,000) so that all new records in the DB use this ID range for their primary keys. Once the local DB reaches ID 20,000 it should connect to the remote DB to get the next available set of IDs (perhaps 40,000 - 50,000) ...

In this way a large number of separate offices can create records & then merge these, maintaining unique primary keys which are integers, and this key keeps an element of Ordinality / Traceability.

Using a REMOTE db is actually very heavy handed ... all I really need is a file with a single number in it on the SERVER & code to grab and increment this number from the LOCAL DB Session ... again, a MODULE written in DELPHI to do that job added to EDB's external modules might be just the job ... but I don't yet know how to write it!

You could use replication with one store and rare calls to SAVE UPDATES /
LOAD UPDATES specifying a specific table.  Or, just write your own values to
a text file and put it in a remote store that is checked by the other
database.  You're not limited to EDB tables.


David Cornelius
Cornelius Concepts

Thanks Roy & David,

I have made a decent step towards getting this working & thought I would share the work.

--

1. I have a very simple & rather lonely EDB table on a Cloud Server:

CREATE TABLE "UIDCloud"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"UID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 10000) NOT NULL,
"DateCreated" TIMESTAMP,
CONSTRAINT "ID" PRIMARY KEY ("ID")
)

Simply inserting a new record into this table automatically generates a non-repeating reference INTEGER which is always 10k above the previous one.

--

2. I have an external module (written in DELPHI). This just uses the TEDBExtrnalModule class provided by Elevate and overrides 1 method "DoRoutine":


procedure TEDBExternalModule.DoRoutine(const RoutineName: TEDBString);
begin
 ParamValues.FindByName('Result').AsInteger := ReturnNewMaxID;
end;

Calling 1 new method, which establishes a separate REMOTE DB connection, inserts 1 new record into the cloud table & retrieves the new ID:

function TEDBExternalModule.ReturnNewMaxID: Integer;
begin
 Result := 0;
 RemoteSession:= TEDBSession.Create(nil);
 RemoteSession.SessionType:= stRemote;
 RemoteSession.RemoteAddress:= '46.xxx.xxx.xxx'; //PUT YOUR OWN REMOTE IP HERE
 RemoteSession.SessionName:= 'CloudConnection';
 RemoteSession.LogInPassword:= '*******';
 RemoteSession.LoginUser:= 'Administrator';
 RemoteDatabase:= TEDBDatabase.Create(nil);
 RemoteDatabase.SessionName:= 'CloudConnection';
 RemoteDatabase.Database:= 'CloudReference';
 RemoteDatabase.DatabaseName:= 'CloudReference';
 RemoteDatabase.Connected:= true;
 RemoteQuery:= TEDBQuery.Create(nil);
 RemoteQuery.DatabaseName:= 'CloudReference';
 RemoteQuery.SessionName:= 'CloudConnection';
 try
   RemoteQuery.SQL.Text:= ' INSERT INTO UIDCloud (DateCreated) VALUES (Current_Timestamp) ';
   RemoteQuery.ExecSQL;
   RemoteQuery.Unprepare;
   RemoteQuery.SQL.Text:= ' SELECT UID FROM UIDCloud WHERE ID = SELECT MAX(ID) FROM UIDCloud ';
   RemoteQuery.Open;
   Result:= RemoteQuery.FieldByName('UID').asInteger;
   RemoteQuery.Close;
   RemoteQuery.Unprepare;
 finally
   FreeAndNil(RemoteSession);
   FreeAndNil(RemoteDatabase);
   FreeAndNil(RemoteQuery);
 end;
end;

3. In EDB, on my main server I have 2 functions. The first "UID()" returns a new INTEGER value from a local UIDGenerator table. The second function references the external module.

Here is the code:

CREATE FUNCTION "UID" ()
RETURNS INTEGER
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result INTEGER DEFAULT 1;
 DECLARE UID INTEGER;
 DECLARE NextMaxUID INTEGER;
 DECLARE iReopen BOOLEAN;

SET iReopen = false;
PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
OPEN Crsr;     

IF (ROWCOUNT(Crsr) < 1) THEN
     SET NextMaxUID = NextCloudUID();
     PREPARE Stmt FROM
       'INSERT INTO "UIDGenerator" (UID, NextMaxUID) VALUES (?, ?) ';
     EXECUTE Stmt USING CAST(NextMaxUID AS VARCHAR(14)), CAST(NextMaxUID+10000 AS VARCHAR(14));
     SET iReopen = true;
   END IF;
IF (ROWCOUNT(Crsr) < 2) THEN
     SET NextMaxUID = NextCloudUID();
     PREPARE Stmt FROM
       'INSERT INTO "UIDGenerator" (UID, NextMaxUID) VALUES (?, ?) ';
     EXECUTE Stmt USING CAST(NextMaxUID AS VARCHAR(14)), CAST(NextMaxUID+10000 AS VARCHAR(14));
     SET iReopen = true;
   END IF;

IF iReopen = true then
   CLOSE Crsr;
   PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator WHERE ID = SELECT MIN(ID) FROM UIDGenerator ';
   OPEN Crsr;
 END IF;

FETCH FIRST FROM Crsr ('UID') INTO UID;
FETCH FROM Crsr ('UID') INTO Result;
FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;   
CLOSE Crsr;

IF (UID = NextMaxUID) THEN
   EXECUTE IMMEDIATE
   ' DELETE FROM UIDGenerator WHERE ID = SELECT MIN(ID) FROM UIDGenerator ';
   PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator WHERE ID = SELECT MIN(ID) FROM UIDGenerator ';
   OPEN Crsr;
   FETCH FIRST FROM Crsr ('UID') INTO UID;
   FETCH FROM Crsr ('UID') INTO Result;
   FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;
   CLOSE Crsr;
 END IF;

EXECUTE IMMEDIATE
 ' UPDATE UIDGenerator SET UID = UID + 1 WHERE ID = SELECT MIN(ID) FROM UIDGenerator ';

RETURN Result;

END

This is a bit long because I have chosen to have the local UIDGenerator table work with 2 rows, so it has the current set of INTEGERs it is calling down, but also has a "spare", this is just in case the user is disconnected at the moment that they run out of new UIDs. It means that once the function goes down to a single row each insert will try to find the CLOUD & add a new row to the UIDGenerator table. I have put this in for a safety factor ...

3. Linking the "NextCloudUID()" Function to its external MODULE has only a couple of steps:

- Compile the DLL in Delphi & copy it into the required folder somewhere the server can see.
- Use the following SQL to register it:

CREATE MODULE UIDModule
PATH D:\EDB\Modules

- Then you can create the function:

CREATE FUNCTION "NextCloudUID" ()
RETURNS INTEGER
EXTERNAL NAME "UIDModule"

--

I hope people find this useful, its only a first step really, now I have this working I can get on with the replication sections, saving UPDATEs etc.

Adam,

<< Calling 1 new method, which establishes a separate REMOTE DB connection,
inserts 1 new record into the cloud table & retrieves the new ID: >>

This can be streamlined a bit:

function TEDBExternalModule.ReturnNewMaxID: Integer;
begin
 Result := 0;
 RemoteSession:= TEDBSession.Create(nil);
 RemoteSession.SessionType:= stRemote;
 RemoteSession.RemoteAddress:= '46.xxx.xxx.xxx'; //PUT YOUR OWN REMOTE IP
HERE
 RemoteSession.SessionName:= 'CloudConnection';
 RemoteSession.LogInPassword:= '*******';
 RemoteSession.LoginUser:= 'Administrator';
 RemoteDatabase:= TEDBDatabase.Create(nil);
 RemoteDatabase.SessionName:= 'CloudConnection';
 RemoteDatabase.Database:= 'CloudReference';
 RemoteDatabase.DatabaseName:= 'CloudReference';
 RemoteDatabase.Connected:= true;
 RemoteQuery:= TEDBQuery.Create(nil);
 RemoteQuery.DatabaseName:= 'CloudReference';
 RemoteQuery.SessionName:= 'CloudConnection';
 try
   RemoteQuery.SQL.Text:= ' INSERT INTO UIDCloud (UID, DateCreated) VALUES
(:UID, Current_Timestamp) ';
   RemoteQuery.ExecSQL;
   Result:= RemoteQuery.ParamByName('UID').asInteger;
   RemoteQuery.Close;
   RemoteQuery.Unprepare;
 finally
   FreeAndNil(RemoteSession);
   FreeAndNil(RemoteDatabase);
   FreeAndNil(RemoteQuery);
 end;
end;

This is safer also because it returns the exact value that was set during
the actual INSERT.

<< I hope people find this useful, its only a first step really, now I have
this working I can get on with the replication sections, saving UPDATEs etc.
>>

Hell of a first step there.  Very well done, indeed.

--
Tim Young
Elevate Software
www.elevatesoft.com

>>RemoteQuery.SQL.Text:= ' INSERT INTO UIDCloud (UID, DateCreated) VALUES (:UID, Current_Timestamp) ';
>>RemoteQuery.ExecSQL;
>>Result:= RemoteQuery.ParamByName('UID').asInteger;

... that last line is brilliant. I haven't got my head around several of the EDB features which are similar to this, such as the ability to call "Execute" on a session variable ... really useful short-cuts.

Adam,

<< .. that last line is brilliant. I haven't got my head around several of
the EDB features which are similar to this, such as the ability to call
"Execute" on a session variable ... really useful short-cuts. >>

The parameter handling is greatly-improved in EDB with the introduction of
in-out and out parameters.

--
Tim Young
Elevate Software
www.elevatesoft.com