How To Specify The Database In Sql Query

Data Abstract
1

Just ran into this as a production problem. We need to pull data from one database table into another table on a completely different database. Two separate databases but this could be from any of the 7 that are operating.

What would be the best way for the client application to pull from one and then push into another? I read somewhere about a multi database login. Is that the course to take? Are there any sample code of this anywhere?

Thanks.
Bill Brittain

2

Hi,

Standard TMultiDbLoginService supports IMultiDbLoginServiceV5 .

this interface allows to receive list of server’s connections with GetConnectionNames method.

Later you can pass connection’s name in Login method.

You can store passed connection name into Session variable

  Session['DefaultConnection'] := aConnectionName;

and later initialize DataAbstractService.ConnectionName with this value:

procedure TDataService.DataAbstractServiceActivate(const aClientID: TGUID;
  aSession: TROSession; const aMessage: IROMessage);
begin
  if Session['DefaultConnection'] <> '' then 
    ConnectionName := Session['DefaultConnection'];
end;

Also you can create a custom server’s method like

procedure TDataService.SetConnectionName(const ConnectionName: ROUTF8String);
begin
  Session['DefaultConnection'] := ConnectionName;
end;

and change default connection on fly.

3

Thank you Evgeny.

To extend that TDataService SetConnectionName, what would be the proper way to also take that SetConnectionName and then pass and execute the sql query. ( const ConnectionName: ROUTF8String, const SqlQuery: String ) so that we can execute from the client.

I think that would solve our problem. We are going back and forth with the databases when we are trying to pull a work order from one database and then create into another. Very typical master / detail record pull and inserts.

I was working on another completely different case yesterday where we cannot use the above for a similar case. But that one will require two separate logins because they exist on two different servers with two completely different ip addresses. Real life is complicated.

This one involves a catering operation that interacts directly with some of their customers where they are pulling data directly from the customer ( which also are using our system, convenient ). These are not just once in while pulls. They are pulling multiple big events every day.
And we have the same problem with another where they oversee multiple event centers each with their own calendars and event work orders. When we figure these out, I will try to share with everyone our solutions and then maybe those can be used by others and expanded.

4

Hi,

You can do it and it will work.

You can use 2 connections simultaneously on server-side. this is acceptable but requires manual coding.

also it is possible to combine several tables from different DB servers and use combined result set as one table … check more at Union Tables and Managing Union Tables