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.