Schema Datatable/Command Connection Name?

stuartclennett · April 3, 2013, 1:15pm

Hi,

I’m trying to implement multiple database connections within a single custom Delphi DA server. I need a table in the database to be (optionally) stored in another database (it’s an audit trail table).

I can get it working at design time (using the Preview tab in the Schema Modeller) as follows:

Create two connections in the ConnectionManager called “DefaultConnection” (default) and “LogConnection” which both point to different databases (on the same server in this case).
Go to the LOG datatable in the schema & set the statement Connection property to “LogConnection”.
Leave the Connection property blank for all other datatables’ statements.

In the preview tab, I can see data from DefaultConnection in all data tables except for the LOG table which shows data from LogConnection.

At runtime the ConnectionStrings are read from an INI file. In ServerDataModule.DataModuleCreate I delete all the designtime connections then create two connections named as above. But opening the LOG table on the client shows data from the DefaultConnection not from LogConnection.

Where could I be going wrong here?

Thanks,
Stuart

vovanl · April 4, 2013, 12:27pm

Hello,
To work with some connection you can use two ways:

You can switch connections inside BeforeAcquireConnection event on your datamodule.

procedure TMyService.DataAbstractServiceBeforeAcquireConnection(
aSender: TObject; var aConnectionName: string);
begin
aConnectionName := ‘MyNewConnection’;
end;

You can use heterogeneous connections:

Open you schema and make the following:

Add the fist connection (DefaultConnection)
Add the second connection (LogConnection)
Add a table of the first connection (table1)
Add a table of the second connection (LOG)

In your service module add lHet:TDAHETConnection object.

procedure TNewService.DataAbstractServiceCreate(Sender: TObject);
begin
  lHet := TDAHETConnection.Create(Schema.ConnectionManager.Connections);
  lHet.Name := 'HetConnection';
  lHet.ObjectMappings.Add('LOG=LogConnection');
  lHet.ObjectMappings.Add('table1=DefaultConnection');
  lHet.Default := True;
end;

procedure TNewService.DataAbstractServiceDestroy(Sender: TObject);
begin
  lHet.Free();
end;

Drop TDAHETDriver component on your server data module.

On the client side when you use “LOG” or "table1"in the memtable.LogicalName property you
will connect to different databases.

Read about it in wiki:
http://wiki.remobjects.com/wiki/TDAHETConnection_Class

stuartclennett · April 4, 2013, 2:50pm

Thanks for your reply. Couple of questions if I may?

I’m still not sure why the two different connections are working as expected as design time & yet it doesn’t work at runtime? Are you saying that setting the connection property of the statement to the name of a connection won’t retrieve data from that connection?
If that;s the case, what is the purpose of the statements Connection property?
Not sure how I would use the BeforeAcquireConnection event to swap connections for specific datatables in the schema?
Does TDAHETConnection require the name of the schema table or the name of the database table. Exmaple: I have a schema table called ILOG_ORDERED that is simply a select…order by on the ILOG database table. Do I use ILOG_ORDERED or ILOG in the TDAHETConnection?
Does the TDAHETConnection also work with commands as well as tables? If so do you have an example please?

Sorry for many questions, not sure I fully understand the role of connections vs statements.

Thanks,
Stuart

vovanl · April 5, 2013, 9:04am

Hello,
You wrote:

If that;s the case, what is the purpose of the statements Connection property?

Purpose of Connection propery:
Depending on default connection the datatables get SQL,StatementType, etc from the corresponded statement.
Example:

Create two connections: IBDAC + SQLite
Create databable TABLE1 which has two statements
Statement1.Connection=IBDAC (set sql syntax, etc for Interbase)
Statement2.Connection=SQLite (set sql syntax, etc for SQLite)
Set IBDAC to Default. In this case TABLE1 will get SQL,StatementType, etc from Statement1
Set SQLite to Default. In this case TABLE1 wil get SQL,StatementType, etc from Statement2

You use two connection immideatly, so you should use a heterogeneous connection.

Does TDAHETConnection require the name of the schema table or the name of the database table

TDAHETConnection requires the name of the schema table.

Does the TDAHETConnection also work with commands as well as tables?
If so do you have an example please?

TDAHETConnection also works with commands.

procedure TNewService.DataAbstractServiceCreate(Sender: TObject);
begin
  lHet := TDAHETConnection.Create(Schema.ConnectionManager.Connections);
  lHet.Name := 'HetConnection';
  lHet.ObjectMappings.Add('Command1=Connection1');
  lHet.ObjectMappings.Add('Command2=Connection2');
  lHet.Default := True;
end;

stuartclennett · April 8, 2013, 11:04am

Thanks for taking the time to explain it.

I tried implementing the TDAHetConnection. I discovered that all tables & commands in the schema need an entry in the ObjectMappings - not too difficult in a loop

Now though, I’ve encountered an Access Violation with a technique I’ve been using in most of my service methods. Appears that SomeService.Connection" returns nil when using TDAHetConnection. For example, in my code I typically use something like the following:

procedure TMyService.GetSomeValue: string;
var
ids: IDADataset
begin
ids := ServiceSchema.NewDataset(connection, ‘SOME_TABLE_NAME’);
[… do stuff with the data table & return value …]
end;

The access violation occurs on the NewDataset call. I traced it to uDAEngine.pas at line 1108:

procedure TDAEConnection.GetDatasetClass
begin
result := nil;
end;

And so I get an Access Violation. Any advice on how to resolve this?

Thanks
Stuart

vovanl · April 8, 2013, 1:25pm

Hello,
Use the following construction:

aTableName := 'SOME_TABLE_NAME';
ds := ServiceSchema.NewDataset(GetConnectionForObject(aTableName), aTableName);

stuartclennett · April 8, 2013, 3:27pm

Thanks again. This is interesting stuff.

I have several Data services in my server and I was encountering a problem: “ConnectionManager does not have a default connection”.

I think I figured this out though - the Het connection was set as default, then free’d and so left no default connection.

Is my solution correct: In the DataAbstractServiceDestroy event, set a connection in the ConnectionManager to be the default.

(Note I tried to access the ServiceSchema.ConnectionManager, but in this even the ServiceSchema was nil so I referenced the ServerDataModule.ConnectionManager).

Am I on the right path here – is it okay to use TDAHetConnection on one service, but not on the other if there’s no need for it?

Thanks

vovanl · April 9, 2013, 1:40pm

Hello,
You wrote:

Is my solution correct: In the DataAbstractServiceDestroy event, set a connection in the ConnectionManager to be the default.
(Note I tried to access the ServiceSchema.ConnectionManager, but in this even the ServiceSchema was nil so I referenced the ServerDataModule.ConnectionManager).

You can also access to Schema component directly:

TDataService = class(TDataAbstractService, IDataService)
    Schema: TDASchema;
....

procedure TDataService.DataAbstractServiceDestroy(Sender: TObject);
begin
  FreeAndNil(lHet);
  //Schema.ConnectionManager
end;

You can set default connection for each service in BeforeAcquireConnection event:

procedure TDataServiceWithHetConnection.DataAbstractServiceBeforeAcquireConnection(
  aSender: TObject; var aConnectionName: string);
begin
  aConnectionName := 'HetConnection';
end;

procedure TDataServiceWithIBDACConnection.DataAbstractServiceBeforeAcquireConnection(
  aSender: TObject; var aConnectionName: string);
begin
  aConnectionName := 'IBDAC';
end;

Am I on the right path here – is it okay to use TDAHetConnection on one service, but
not on the other if there’s no need for it?

Yes, it’s ok

stuartclennett · April 9, 2013, 1:54pm

Thanks.

The server app has 5 services, only 2 of which need to use TDAHetConnection.

Should I be setting the connection name as you suggest in BeforeAcquireConnection event on the 3 services that don’t use TDAHetConnection?

I’m wondering if an instance of another service exists that has created a TDAHetConnection & made it default at the same time as accessing a service that just needs the “normal default” connection…? (Hope that makes sense)

Also, regarding the destroy event: the 5 service schemas are actually on the ServerDataModule or the WebModule depending on if I’m compiling for IIS or Windows. So I assign the ServiceSchema property directly in DataAbstractServiceCreate event:

{$IFDEF IIS}
ServiceSchema := WebModule.CoreSchema;
self.SessionManager := WebModule.DB_SessionManager;
{$ELSE}
ServiceSchema := ServerDataModule.CoreSchema;
self.SessionManager := ServerDataModule.DB_SessionManager;
{$ENDIF}

I normally reference “ServiceSchema” in other service methods, but this property seems to be set to Nil in the DataAbstractServiceDestroy event? It seems like this property should still be set in the destroy event…?

At the moment, I’m accessing the ConnectionManager on either ServerDataModule or WebModule.

Thanks again

vovanl · April 12, 2013, 5:22pm

Hello,
You wrote:

Should I be setting the connection name as you suggest in BeforeAcquireConnection
event on the 3 services that don’t use TDAHetConnection?

This way you will select the corresponded default connection for a service.

I normally reference “ServiceSchema” in other service methods, but this property seems to be set
to Nil in the DataAbstractServiceDestroy event? It seems like this
property should still be set in the destroy event…?

In DataAbstractServiceDestroy event the objects are cleaned and so ServiceSchema set to nil.

stuartclennett · April 15, 2013, 2:29pm

Thanks for the additional info - this has been a very informative discussion thanks