mORMot Open Source

Hmdsadeghian

Member

Registered: 2017-11-25

Posts: 31

How to implement VirtualTableExternalMap

Hi,

I create a http service application and create a class for connecting to the SQL Server from client.
My connection class is :

 
type
  TConnection = class(TInterfacedObject, IConnection)
  protected
    fProps: TOleDBMSSQL2012ConnectionProperties;
  public
    destructor Destroy; override;
  public // implements IRemoteSQL methods
    procedure Connect(const aServerName, aDatabaseName,
      aUserID, aPassWord: RawUTF8);
  end;

implementation

{ TServiceRemoteSQL }

procedure TConnection.Connect(const aServerName, aDatabaseName, aUserID,
  aPassWord: RawUTF8);
begin
  if fProps<>nil then
    raise Exception.Create('Connect called more than once');
    fProps := TOleDBMSSQL2012ConnectionProperties.Create(aServerName,
              aDatabaseName,aUserID,aPassWord);
 end;

and my service code is :

procedure TFaezERPHttpService.DoStart(Sender: TService);
var
  aModel: TSQLModel;
  aServer: TSQLRestServerFullMemory;
  aHTTPServer: TSQLHttpServer;
begin
  // define the log level
  with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE; // log all events to the console
    PerThreadLog := ptIdentifiedInOnFile;
  end;
  AllocConsole;
  TextColor(ccLightGray); // needed to notify previous AllocConsole
  TModel.CreateModel(aModel);
  try
    aServer := TSQLRestServerFullMemory.Create(aModel,'users.json',false,true);
    try
      TregisterService.RegisterAllServices(AServer);
      aHTTPServer := TSQLHttpServer.Create(PORT_NAME,[aServer],'+',HTTP_DEFAULT_MODE);
      try
        aHTTPServer.AccessControlAllowOrigin := '*'; // for AJAX requests to work
        Sleep(200); // allow all HTTP threads to be launched and logged
        writeln(#10'Background server is running.'#10);
        writeln('Press [Enter] to close the server.'#10);
        ConsoleWaitForEnterKey;
      finally
        aHTTPServer.Free;
      end;
    finally
      aServer.Free;
    end;
  finally
    aModel.Free;
  end;
end;

Also I create a class for registering services and create models and mapping virtual tables that code is :

Type
  TRegisterService=class

  public
   class  procedure RegisterAllServices(var server:TSQLRestServerFullMemory);
   class  procedure RegisterClientServices(var server:TSQLRestClientURI);
  end;

  TModel= Class
  public
   class  Procedure CreateModel(Var Model:TSQLModel);
  End;

  TDatabaseConfiguration=class
  public
   class  procedure RegisterVirtualTable(var model:TSQLModel;
          var Props:TOleDBMSSQL2012ConnectionProperties);
  end;

const
  ROOT_NAME = 'root';
  PORT_NAME = '8888';
  APPLICATION_NAME = 'RestService';

implementation

uses UntConnection,UntIConnection,UntUsers;

{ TRegisterService }

class procedure TRegisterService.RegisterAllServices(
  var server: TSQLRestServerFullMemory);
begin
    Server.ServiceDefine(TConnection,[IConnection],sicClientDriven);
end;

class procedure TRegisterService.RegisterClientServices(
  var server: TSQLRestClientURI);
begin
   Server.ServiceDefine([IConnection],sicClientDriven);
end;

{ TModel }

class procedure TModel.CreateModel(var Model: TSQLModel);
begin
   Model := TSQLModel.Create([TSQLSampleRecord],Root_Name);

end;

{ TDatabaseConfiguration }

class procedure TDatabaseConfiguration.RegisterVirtualTable(
  var model: TSQLModel; var Props: TOleDBMSSQL2012ConnectionProperties);
begin
  VirtualTableExternalMap(Model,TSQLSampleRecord,Props,'TestTable');
end;

My problem is that in Mormot documentation said for using virtualTableExternalMap must use before server.create . But in my code , I want to create server and client send username,password and server name to connect to SQL Server. I don't know with this approach, how to using this function and create external tables on SQL Server.

Thanks a lot

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,260

Website

Re: How to implement VirtualTableExternalMap

Use separated TSQLRestServer instances: one for the Services (with no direct access to the DB), and several for the per-user access to the remote DB.
I would make a "repository service" with an interface for all per-user access to the remote DB.
Each sicPerUser instance will maintain its own connection.

It will also be safer, since only the main TSQLRestServer will be published over HTTP, so the per-user instances will be available only through services.

Hmdsadeghian

Member

Registered: 2017-11-25

Posts: 31

Re: How to implement VirtualTableExternalMap

Thank you .
Do you have any example for this approach?

In your solution, can I host my service on dedicate server on the internet and connect through the internet to the service application?

If I understand correctly, I don't need change the DoStart procedure. also need to create an instance on TSQLServerServer on the TConnection Class when the Connect function called. is it true?

Last edited by Hmdsadeghian (2018-07-24 03:36:57)

Hmdsadeghian

Member

Registered: 2017-11-25

Posts: 31

Re: How to implement VirtualTableExternalMap

would you help me please?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,260

Website

Re: How to implement VirtualTableExternalMap

There is no such direct sample on it.

Define a sicPerUser interface-based service, and connect to the proper database during the implementation class constructor.

Hmdsadeghian

Member

Registered: 2017-11-25

Posts: 31

Re: How to implement VirtualTableExternalMap

thanks a lot