Synopse Open Source

Note that the Execute() method returns a RawJSON kind of variable, which is in fact a sub-type of RawUTF8. Its purpose is to transmit the UTF-8 encoded content directly, with no translation to a JSON string, as would be the case with a RawUTF8 variable. In fact, escaping some JSON array within a JSON string is quite verbose. Using RawJSON in this case ensure the best client-side and server-side speed, and also reduce the transmission bandwidth.

The server part is quite easy to follow:

type
  TServiceRemoteSQL = class(TInterfacedObject, IRemoteSQL)
  protected
    fProps: TSQLDBConnectionProperties;
  public
    destructor Destroy; override;
  public // implements IRemoteSQL methods
    procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName,
      aUserID, aPassWord: RawUTF8);
    function GetTableNames: TRawUTF8DynArray;
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

 { TServiceRemoteSQL }
procedure TServiceRemoteSQL.Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const // rseOleDB, rseODBC, rseOracle, rseSQlite3, rseJet, rseMSSQL
  TYPES: array[TRemoteSQLEngine] of TSQLDBConnectionPropertiesClass = (
     TOleDBConnectionProperties, TODBCConnectionProperties,
     TSQLDBOracleConnectionProperties, TSQLDBSQLite3ConnectionProperties,
     TOleDBJetConnectionProperties, TOleDBMSSQL2008ConnectionProperties);
begin
  if fProps<>nil then
    raise Exception.Create('Connect called more than once');
  fProps := TYPES[aEngine].Create(aServerName,aDatabaseName,aUserID,aPassWord);
end;

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var res: ISQLDBRows;
begin
  if fProps=nil then
    raise Exception.Create('Connect call required before Execute');
  res := fProps.ExecuteInlined(aSQL,aExpectResults);
  if res=nil then
    result := '' else
    result := res.FetchAllAsJSON(aExpanded);
end;

function TServiceRemoteSQL.GetTableNames: TRawUTF8DynArray;
begin
  if fProps=nil then
    raise Exception.Create('Connect call required before GetTableNames');
  fProps.GetTableNames(result);
end;

destructor TServiceRemoteSQL.Destroy;
begin
  FreeAndNil(fProps);
  inherited;
end;

Any exception during SynDB process, or raised manually in case of wrong use case will be transmitted to the client, just as expected. The fProps instance life-time is handled by the client, so all we need is to release its pointer in the service implementation destructor.

From the client point of view, it will be consumed as such:

procedure TMainForm.FormShow(Sender: TObject);
  (...)
  fModel := TSQLModel.Create([],ROOT_NAME);
  fClient := TSQLHttpClient.Create('localhost','888',fModel);
  if not fClient.ServerTimeStampSynchronize then begin
    ShowLastClientError(fClient,'Please run Project16ServerHttp.exe');
    Close;
    exit;
  end;
  if (not fClient.SetUser('User','synopse'))  or
     (not fClient.ServiceRegisterClientDriven(TypeInfo(IRemoteSQL),fService)) then begin
    ShowLastClientError(fClient,'Remote service not available on server');
    Close;
    exit;
  end;
end;

Note the use of ShowLastClientError() function of mORMotUILogin unit, which is able to use our SynTaskDialog unit to report standard and detailed information about the latest error.

Note that in this sample, no table has been defined within the ORM model. It is not necessary, since all external process will take place at the SQL level. As we need authentication (see the call to fClient.SetUser method), the ORM core will by itself add the TSQLAuthUser and TSQLAuthGroup tables to the model - no need to add them explicitly.

From now on, we have a fService: IRemoteSQL instance available to connect and process any remote SQL request.

procedure TMainForm.btnOpenClick(Sender: TObject);
var TableNames: TRawUTF8DynArray;
  (...)
  with fSettings do
    fService.Connect(Engine,ServerName,DatabaseName,UserID,PassWord);
  TableNames := fService.GetTableNames;
  cbbTableNames.Items.Text := UTF8ToString(RawUTF8ArrayToCSV(TableNames,#13#10));
  (...)

Now we are connected to the database via the remote service, and we retrieved the table names in a TComboBox.

Then a particular SQL statement can be executed as such:

procedure TMainForm.btnExecuteClick(Sender: TObject);
var SQL: RawUTF8;
begin
  SQL := trim(StringToUTF8(mmoQuery.Text));
  Screen.Cursor := crHourGlass;
  try
    try
      if isSelect(pointer(SQL)) then begin
        fTableJSON := fService.Execute(SQL,True,False);
        TSQLTableToGrid.Create(drwgrdData,
          TSQLTableJSON.Create([],SQL,pointer(fTableJSON),Length(fTableJSON)),fClient);
      end else
        fService.Execute(SQL,False,False);
    except
      on E: Exception do
        ShowException(E);
    end;
  finally
    Screen.Cursor := crDefault;
  end;
end;

Here, TSQLTableToGrid.Create(), from the mORMotUI unit, will "inject" the returned data to a standard TDrawGrid, using a TSQLTableJSON instance to unserialize the returned JSON content.

Note that in case of any exception (connection failure, or server side error, e.g. wrong SQL statement), the ShowExecption() method is used to notify the user with appropriate information.

Feedback is welcome on our forum.