Base On Login User to select the Database Connection

Hi,

I want to base on logined user to select the database connection.

As I know that the service is pooling, what is suggested way to achieve this?

For example : if it is public function i can setup the connection before data call ?
how about TDamemtable access the export data or stored procedure?

is it any way to setup the DB connection base on user ?

any suggestion?

Joe

Hi,

you can use MultiDbLoginServiceV5 service as an ancestor for your Login service:

it contains these methods:

    function Login(const aUserID: ROUTF8String; const aPassword: ROUTF8String; const aConnectionName: ROUTF8String; out aUserInfo: UserInfo): Boolean;
    function GetConnectionNames: StringArray;
    function GetDefaultConnectionName: ROUTF8String;

so you can get list of connections and pass required connection name in the Login method

Yep, After login, if I required other data service , will it keep the same database connection?

After Login, I will consume other Dataservices, is it ensure the correct database connecton or keep the connection for it?
As I know all service call or export database it is pooling , if user A use connection A, User B use Connection B, however User C i want to use back Connection A. For such case , what is the suggested way to achieve ?

joe

Hi,

You can create a custom method like SetConnectionName or create custom method like MyGetData that can contain connection name parameter. Implementation of MyGetData can be: set connection name and call default GetData method

Take a example:

Server Side

DataService 1 :
Procedure SetConnectionA;
Procedure SetConnectionB;

At Client Side:

Dataservice 1. SetConnectionA;

Then TDaememtable.open, it is separate function call , can it ensure same incident of the DataService1?

As from my understand, different function call to DataService1 , cannot confirm it is same incident as you consume before.

Am i Correct?

joe

Hi,

it depends on your implementation of SetConnectionA.
I can suggest to store connection name to Session and restore it in events of DataService like

procedure TDataService.DataAbstractServiceActivate(const aClientID: TGUID;
  aSession: TROSession; const aMessage: IROMessage);
begin
  ConnectionName := Session['connectionname'];
end;

Great let me try.

joe

I tried your direction but I change DASchema.ConnectionManager instead of connectioname.

As I only have one connectioname in my schema now, if I have 10 connection , I need refine all existing statement to add back 10 connection for each statement. am I correct ?

Now I create say 10 connectionmanager in servermodule, base on the login user set the

DASchema.ConnectionManager in above mentioned DataAbstractServiceActivate event.

I have the following question on this Usage:

  1. if i use ConnectionManager instead of connectionname, is it a suitable approach?
    will it draw huge resource on server? as I may going to set 20 -30 connection manager.

  2. for serviceactivate when will it be triggered? I tested seems every function call to that service, it will trigger this event. Am I correct? ( any call to service , can skip this activate event?)

Please advise

Hi,

You can do anything if it is required. but why you want to do it via ConnectionManager?

this event is always triggered. you can’t skip it

why I need use ConnectionManager?
→ As I only have one connectionname on each service, if say I need addd 10 connectioname, I need each table or sql statement , I need create 10 more table or sql statement on that .
for example, I have Table Name ( MyClientTable) , I need create a table for each connectionname , Am I correct( as all DB is same just different path so each table or statement is exactly the same)?

But if I switch ConnectionManager onactivate, I no need create so many dummy object for different connectionname , is it correct?

Hi,

If you have the same DB struct on all DB servers , you can use non-empty ConnectionType in Schema Modeller, like:

this means, that this table will correspond to all connections that have SQLite as Connection Type.
for example, you can have several connections like

Untitled

if you change DataAbstractService.ConnectionName and set DataAbstractService.Conenction to nil then new connection will be created according to specified DataAbstractService.ConnectionName.

Thanks for your reply and sorry for get back you late. I will take test on your way.

As now if follow your way, I need change all connectionname to empty first. if I use connectionManager way, it seems easier for me to implement the case.

By comparing the Changing connectionname and Changing Connection Manager, wil connection Manager use more resource? or any performance difference on it?

joe

Hi,

ofc, usage of X instances of some class will require more resources in comparing with usage of one instance. also you need time for creating X instances