Access both Aurelius and SQL data

Hello,

Currently I am using the Aurelius (Entities) format to get data from the XData server. But when I watched the video "Accessing any Delphi compatible database through a REST API using SQL" (cf https://www.tmssoftware.com/site/blog.asp?post=544), I found that very interesting, because it could solve some problems of slowness on long requests (several tens of thousands of rows with joints). Response time can by divided by more than 10 ! (I made a small comparison test)

My question is: Is it possible to add a FDQuery component to the FireDac connection (part of IDBConnection), or to use ExecuteQuery (IDBStatement) as a TFDQuery type, to have a stream (FDQuery.SaveToStream(AStream, sfJSON) ) ?

The goal is to avoid creating a second FDConnection (FireDac) for the same database

Thank you,

From any IDBConnection (one that you get from the pool, for example), you can retrieve the underlying connection component used - in your case TFDConnection if you are using FireDac.

It's described here:
http://www.tmssoftware.biz/business/aurelius/doc/web/component_adapters.html

More specifically:

Referencing original component

 

If the component adapter also implements the IDBConnectionAdapter interface, you can retrieve the original adapted component. For example, given an IDBConnection that you know was created from a TFireDacConnectionAdapter, you can retrieve the TFDConnection object using the following code:

 

var
  MyConnection: IDBConnection;
  FDConnection: TFDConnection;
{...}
  FDConnection := (MyConnection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;

 

 

Hello Wagner,

It works! Thank you.

Here is the code I use, if it could help:

function TMyService.GetMyViewData(const AJrnID, APerID1, APerID2: integer): TStream;
const
  SQL_TXT = 'SELECT * FROM MyView ..........';
var
  MyConnection: IDBConnection;
  FDConnection: TFDConnection;
begin
  MyConnection := TXDataOperationContext.Current.GetConnectionPool.GetConnection;
  FDConnection := (MyConnection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;
  result := TMemoryStream.Create;
  try
    with TFDQuery.Create(nil) do
      try
        Connection := FDConnection;
        SQL.Text := SQL_TXT;
        Prepare;
        ParamByName('JRNID').AsInteger := AJrnID;
        ParamByName('PERID1').AsInteger := APerID1;
        ParamByName('PERID2').AsInteger := APerID2;
        Open;
        SaveToStream(TMemoryStream(result), sfJSON);
      finally
        Free;
      end;
  except
    TMemoryStream(result).Free;
    result := nil;
  end;



Of course, a TFDStanStorageJSONLink component is dropped anywhere on both server and client projects to be able to use JSON format.

Kind Regards,