mORMot working as a DataSnap Replacement

Murat Ak · 2015-01-01 11:07:11

Hi,

I did some work on mORMot replacement to datasnap using ClientDataSet. It is working so good.
I did convert TSimpleDataSet and SynDBVCL.TSynSQLStatementDataSet.
It is working on SynDBRemote.

unit Pi.Air.DB.Client.Provider.Mormot;

interface

uses
  System.Classes,
  Datasnap.DBClient,
  Datasnap.Provider,
  SynCommons,
  SynDB,
  SynDBRemote,
  Data.DB,
  SynVirtualDataSet
;

type

  TInternalConnection = class(TSQLDBWinHTTPConnectionProperties)
  private
    function GetQuoteChar: string;
  end;

{ TInternalSQLDataSet }

  TInternalSQLDataSet = class(TSynVirtualDataSet)
  private
    FConnection: TInternalConnection;
    FCommandText: String;
    fDataAccess: TSQLDBProxyStatementRandomAccess;
    fTemp64: Int64;
    fData: RawByteString;
    procedure OpenDB;
  protected
    // IProvider
    procedure PSSetCommandText(const ACommandText: string); override;
    function PSGetTableName: string; override;
    function PSUpdateRecord(UpdateKind: TUpdateKind; Delta: TDataSet): Boolean; override;
    function PSIsSQLBased: Boolean; override;
    function PSIsSQLSupported: Boolean; override;
    function PSExecuteStatement(const ASQL: string; AParams: TParams): Integer; overload; override;
    // End of IProvider
    procedure InternalInitFieldDefs; override;
    function GetRecordCount: Integer; override;
    function GetRowFieldData(Field: TField; RowIndex: integer; out ResultLen: Integer;
      OnlyCheckNull: boolean): Pointer; override;

    procedure InternalOpen; override;
    procedure InternalClose; override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;

      /// read-only access to the internal binary buffer
    property Data: RawByteString read fData;
    /// read-only access to the internal SynDB data
    property DataAccess: TSQLDBProxyStatementRandomAccess read fDataAccess;
published
    property SQLConnection: TInternalConnection read FConnection write FConnection;
    property CommandText: String read FCommandText write FCommandText;
  end;

{ TSimpleDataSet }

  TPiCustomClientDataSet = class(TCustomClientDataSet)
  private
    FConnection: TInternalConnection;
    FInternalConnection: TInternalConnection; { Always points to internal if present }
    FDataSet: TInternalSQLDataSet;
    FProvider: TDataSetProvider;
  protected
    procedure AllocConnection; virtual;
    procedure AllocDataSet; virtual;
    procedure AllocProvider; virtual;
    procedure Loaded; override;
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    procedure OpenCursor(InfoQuery: Boolean); override;
    procedure SetConnection(Value: TInternalConnection); virtual;
    { IProviderSupport }
    function PSGetCommandText: string; override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure FetchParams;
  published
    property CommandText;
    property Active;
    property Aggregates;
    property AggregatesActive;
    property AutoCalcFields;
    property Connection: TInternalConnection read FConnection write SetConnection;
    property DataSet: TInternalSQLDataSet read FDataSet;
    property Constraints;
    property DisableStringTrim;
    property FileName;
    property Filter;
    property Filtered;
    property FilterOptions;
    property FieldDefs;
    property IndexDefs;
    property IndexFieldNames;
    property IndexName;
    property FetchOnDemand;
    property MasterFields;
    property MasterSource;
    property ObjectView;
    property PacketRecords;
    property Params;
    property ReadOnly;
    property StoreDefs;
    property BeforeOpen;
    property AfterOpen;
    property BeforeClose;
    property AfterClose;
    property BeforeInsert;
    property AfterInsert;
    property BeforeEdit;
    property AfterEdit;
    property BeforePost;
    property AfterPost;
    property BeforeCancel;
    property AfterCancel;
    property BeforeDelete;
    property AfterDelete;
    property BeforeScroll;
    property AfterScroll;
    property BeforeRefresh;
    property AfterRefresh;
    property OnCalcFields;
    property OnDeleteError;
    property OnEditError;
    property OnFilterRecord;
    property OnNewRecord;
    property OnPostError;
    property OnReconcileError;
    property BeforeApplyUpdates;
    property AfterApplyUpdates;
    property BeforeGetRecords;
    property AfterGetRecords;
    property BeforeRowRequest;
    property AfterRowRequest;
    property BeforeExecute;
    property AfterExecute;
    property BeforeGetParams;
    property AfterGetParams;
  end;


implementation

uses
  System.SysUtils,
  Data.SqlConst,
  Data.DBCommon
;

function NextPiece(Start: string; InLiteral: Boolean; QuoteChar: WideChar; EndParam: Boolean = False): Integer;
var
  P, Len: Integer;
  C, LQuoteChar: Char;
begin
  P := 1;
  Len := Start.Length - 1;
  Result := -1;
  LQuoteChar := QuoteChar;
  while (Result = -1) and (P <= Len) and (Start.Chars[P] <> #0) do
  begin
    C := Start.Chars[P];
    if (C = '''') or (C = LQuoteChar) then
      InLiteral := not InLiteral
    else if not InLiteral and ((C = ' ') or (C = ')') or (C = ',') or (C = '=')
      or (C = ':') or (C = '>') or (C = '<') or (C = #13) or (C = #10)) then
    begin
      if EndParam then
      begin
        if not ((C = '=') or (C = ':') or (C = '<') or (C = '>')) then
          Result := P;
      end
      else
      begin
        if (C = ':') then
        begin
          if ((Start.Chars[P-1] = ' ') or (Start.Chars[P-1] = ')') or (Start.Chars[P-1] = ',')
            or (Start.Chars[P-1] = '=') or (Start.Chars[P-1] = '(')) then
            Result := P - 1;
        end
        else if (P < Len) and (Start.Chars[P + 1] = ':') then
          Result := P;
      end;
    end;
    Inc(P);
  end;
end;

// SqlObjects does not support named params: convert to ?
// if not yet converted
function FixParams(SQL: string; Count: Integer; QuoteChar: string): string;
var
  Param, Start: string;
  Pos, EndPos: Integer;
  InLiteral: Boolean;
  Q: Char;
begin
  Q := #0;
  if QuoteChar.Length > 0 then
    Q := QuoteChar.Chars[0];
  if (Q = #0) or (Q = ' ') then Q := '''';
  InLiteral := False;
  Start := SQL;
  Pos := NextPiece(Start, InLiteral, Q);
  while Pos >= 0 do
  begin
    Start := Start.SubString(Pos + 1, Start.Length - Pos + 1);
    EndPos := NextPiece(Start, InLiteral, Q, True);
    if EndPos = -1 then
      Param := Start.SubString(0, Start.Length)
    else
      Param := Start.SubString(0, EndPos);
    SQL := SQL.Replace(Param, ' ? ', []);
    Pos := NextPiece(Start, InLiteral, Q);
  end;
  Result := SQL;
end;

{ TPiCustomClientDataSet }

constructor TPiCustomClientDataSet.Create(AOwner: TComponent);
begin
  inherited;
  AllocProvider;
  AllocDataSet;
  //AllocConnection;
end;

destructor TPiCustomClientDataSet.Destroy;
begin
  inherited; { Reserved }
end;

procedure TPiCustomClientDataSet.FetchParams;
begin
  if not HasAppServer and Assigned(FProvider) then
    SetProvider(FProvider);

  inherited FetchParams;
end;

procedure TPiCustomClientDataSet.Loaded;
begin
  inherited;
  { Internal connection can now be safely deleted if needed }
  if FInternalConnection <> FConnection then
    FreeAndNil(FInternalConnection);
end;

procedure TPiCustomClientDataSet.AllocConnection;
begin
  FConnection := TInternalConnection.Create('', '', '', '');
  FInternalConnection := FConnection;
  //FConnection.Name := 'InternalConnection';             { Do not localize }
  //FConnection.SetSubComponent(True);
  FDataSet.SQLConnection := FConnection;
end;

procedure TPiCustomClientDataSet.AllocDataSet;
begin
  FDataSet := TInternalSQLDataSet.Create(Self);
  FDataSet.Name := 'InternalDataSet';                   { Do not localize }
  FDataSet.SQLConnection := FConnection;
  FDataSet.SetSubComponent(True);
  FProvider.DataSet := FDataSet;
end;

procedure TPiCustomClientDataSet.AllocProvider;
begin
  FProvider := TDataSetProvider.Create(Self);
  FProvider.DataSet := FDataSet;
  FProvider.Name := 'InternalProvider';                 { Do not localize }
  FProvider.SetSubComponent(True);
  FProvider.Options := FProvider.Options + [poAllowCommandText];
  SetProvider(FProvider);
end;

procedure TPiCustomClientDataSet.Notification(AComponent: TComponent; Operation: TOperation);
begin
  inherited;
  {
  if not (csDestroying in ComponentState ) and (Operation = opRemove) and
     (AComponent = FConnection) and (AComponent.Owner <> Self) then
    AllocConnection;
  }
end;

procedure TPiCustomClientDataSet.OpenCursor(InfoQuery: Boolean);
begin
  if Assigned(FProvider) then
    SetProvider(FProvider);
  if FProvider.DataSet = Self then
    raise Exception.Create(SCircularProvider);
  inherited;
end;

procedure TPiCustomClientDataSet.SetConnection(Value: TInternalConnection);
begin
  { Assigning existing value or clearing internal connection is a NOP }
  if (Value = FConnection) or ((Value = nil) and Assigned(FInternalConnection)) then
    Exit;
  { Remove FreeNotification from existing external reference }
  {if FConnection <> FInternalConnection then
    FConnection.RemoveFreeNotification(Self);}
  { Reference to external connection was cleared, recreate internal }
  if (Value = nil) then
    AllocConnection
  else
  begin
    { Free the internal connection when assigning an external connection }
    if Assigned(FInternalConnection) then //and
       { but not if we are streaming in, then wait until loaded is called }
       //not (csLoading in FInternalConnection.ComponentState) then
      FreeAndNil(FInternalConnection);
    FConnection := Value;
    //FConnection.FreeNotification(Self);
    FDataSet.SQLConnection := FConnection;
  end;
end;

function TPiCustomClientDataSet.PSGetCommandText: string;
var
  IP: IProviderSupportNG;
begin
  if Supports(FDataSet, IProviderSupportNG, IP) then
    Result := IP.PSGetCommandText
  else
    Result := CommandText;
end;

{ TInternalSQLDataSet }

constructor TInternalSQLDataSet.Create(AOwner: TComponent);
begin
  inherited;
  //SetUniDirectional(True);
end;

destructor TInternalSQLDataSet.Destroy;
begin
  if Assigned(fDataAccess) then
    FreeAndNil(fDataAccess);
  inherited;
end;

function TInternalSQLDataSet.GetRecordCount: Integer;
begin
  result := fDataAccess.DataRowCount;
end;

function TInternalSQLDataSet.GetRowFieldData(Field: Data.DB.TField;
  RowIndex: integer; out ResultLen: Integer; OnlyCheckNull: boolean): Pointer;
var F: integer;
begin
  result := nil;
  F := Field.Index;
  if not fDataAccess.GotoRow(RowIndex) then
    exit;
  result := fDataAccess.ColumnData(F);
  if (result<>nil) and not OnlyCheckNull then
    case fDataAccess.Columns[F].ColumnType of
    SynCommons.ftInt64: begin
      fTemp64 := FromVarInt64(PByte(result));
      result := @fTemp64;
    end;
    SynCommons.ftCurrency: begin // ftFloat expects a DOUBLE value
      PDouble(@fTemp64)^ := PCurrency(result)^;
      result := @fTemp64;
    end;
    SynCommons.ftUTF8, SynCommons.ftBlob:
      resultLen := FromVarUInt32(PByte(result));
    end; // other ColumnTypes are already in the expected format
end;

procedure TInternalSQLDataSet.InternalClose;
begin
  inherited;
  if Assigned(fDataAccess) then
    FreeAndNil(fDataAccess);
  //fData := '';
end;

procedure TInternalSQLDataSet.InternalInitFieldDefs;
var F: integer;
    DBType: TFieldType;
begin
  FieldDefs.Clear;
  if fDataAccess=nil then
    exit;
  for F := 0 to fDataAccess.ColumnCount-1 do
    with fDataAccess.Columns[F] do begin
    case ColumnType of
    SynCommons.ftInt64: DBType := ftLargeint;
    SynCommons.ftDate:  DBType := ftDateTime;
    SynCommons.ftUTF8:  DBType := ftWideString; // means UnicodeString for Delphi 2009+
    SynCommons.ftBlob:  DBType := ftBlob;
    SynCommons.ftDouble, SynCommons.ftCurrency: DBType := ftFloat;
    else raise EDatabaseError.CreateFmt('GetFieldData ColumnType=%d',[ord(ColumnType)]);
    end;
    //FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnDataSize);
    if (DBType = ftWideString) and (ColumnValueDBSize = 0) then // blob
      FieldDefs.Add(UTF8ToString(ColumnName),ftMemo,ColumnValueDBSize) // Murat
    else
      FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnValueDBSize); // Murat
  end;
end;

procedure TInternalSQLDataSet.InternalOpen;
begin
  OpenDB;
  inherited;
end;

procedure TInternalSQLDataSet.OpenDB;
var
  IRows: ISQLDBRows;
  DataStream: TRawByteStringStream;
  DataRowPosition: TCardinalDynArray;
begin
  DataRowPosition := nil;
  IRows := FConnection.Execute(FCommandText, []);
  DataStream := TRawByteStringStream.Create;
  try
    IRows.Instance.FetchAllToBinary(DataStream, 0, @DataRowPosition);
    fData := DataStream.DataString;

    fDataAccess := TSQLDBProxyStatementRandomAccess.Create(
      pointer(fData), length(fData), @DataRowPosition);
  finally
    DataStream.Free;
  end;
  //IRows := nil;
end;

function TInternalSQLDataSet.PSExecuteStatement(const ASQL: string;
  AParams: TParams): Integer;
var
  SQLText: String;
  ParamCount: Integer;
  I: Integer;
  Stmt: ISQLDBStatement;
begin
  Result := 0;
  if (AParams <> nil) and (AParams.Count > 0) then
  begin
    SQLText := FixParams(ASQL, AParams.Count, FConnection.GetQuoteChar);
    ParamCount := AParams.Count;
  end
  else
  begin
    SQLText := ASQL.Substring(0, ASQL.Length);
    ParamCount := 0;
  end;
  if ParamCount = 0 then
    Result := FConnection.ExecuteNoResult(SQLText, [])
  else begin
    Stmt := FConnection.NewThreadSafeStatementPrepared(ASQL, false, true);
    FConnection.StoreVoidStringAsNull := False;
    for I := 0 to AParams.Count - 1 do
    begin
      case AParams[i].DataType of
        ftInteger, ftLargeInt:
          Stmt.Bind(I + 1, AParams[i].AsInteger);
        ftDateTime:
          Stmt.BindDateTime(I + 1, AParams[i].AsDateTime);
        ftFloat:
          Stmt.Bind(I + 1, AParams[i].AsFloat);
        ftString:
          Stmt.BindTextS(I + 1, AParams[i].AsString);
        ftWideString, ftMemo:
          //Stmt.BindTextW(I + 1, AParams[i].AsWideString);
          Stmt.BindTextS(I + 1, AParams[i].AsString);
        else
          raise Exception.Create('not defined!');
      end;
    end;
    //Result := FConnection.ExecuteNoResult(SQLText, Args);
    //Stmt.Bind(Params);
    Stmt.ExecutePrepared;
    try
      result := Stmt.UpdateCount;
    except // may occur e.g. for Firebird's CREATE DATABASE
      result := 0;
    end;
  end;
end;

function TInternalSQLDataSet.PSGetTableName: string;
begin
  Result := GetTableNameFromSQLEx(CommandText, idMixCase);
end;

function TInternalSQLDataSet.PSIsSQLBased: Boolean;
begin
  Result := True;
end;

function TInternalSQLDataSet.PSIsSQLSupported: Boolean;
begin
  Result := True;
end;

procedure TInternalSQLDataSet.PSSetCommandText(const ACommandText: string);
begin
  inherited;
  FCommandText := ACommandText;
end;

function TInternalSQLDataSet.PSUpdateRecord(UpdateKind: TUpdateKind;
  Delta: TDataSet): Boolean;
begin
  Result := False;
end;

{ TInternalConnection }

function TInternalConnection.GetQuoteChar: string;
begin
  {
  if Assigned(MetaData) then
    Result := MetaData.QuoteChar
  else
    Result := '"';
  FQuoteChar := Result;
  }
  Result := '"';
end;

end.

Memo and Blob fields working. Tested on MS SQL Server.

Need a some fix on TSynVirtualDataSet.

function TSynVirtualDataSet.CreateBlobStream(Field: TField; Mode: TBlobStreamMode): TStream;
begin
  if Mode<>bmRead then
    raise EDatabaseError.CreateFmt('%s BLOB should be ReadOnly',[ClassName]);
  result := GetBlobStream(Field,PRecInfo(ActiveBuffer).RowIndentifier);
  if result = nil then // Murat
    result := TMemoryStream.Create;
end;

How is working.
Create a TSQLDBWinHTTPConnectionProperties (TInternalConnection)
Create a RemoteDataSet (TPiCustomClientDataSet)

  AConnection := TInternalConnection.Create(EditHostName.Text + ':' + EditPort.Text, 'remote', 'username', 'password');
  ADataSet := TPiCustomClientDataSet.Create(nil);
  ADataSet.Connection := AConnection;
  ADataSet.Close;
  ADataSet.CommandText := Memo1.Text;
  ADataSet.Open;

ClientDataSet ApplyUpdates is working too.

FPiClientDataSet.ApplyUpdates(0);

I did that because use in my firemonkey projects, but i understand that it is not cross platform not working on ios, android.
I wish it can be work on as a cross platform and It will be perfect solution.

ab · 2015-01-01 15:53:13

I've fixed TSynVirtualDataSet.CreateBlobStream() to always return a TStream instance, even if the BLOB field is void.
See http://synopse.info/fossil/info/b6e28deb26

Your code is great, but there is a lot of duplicated code with TSynBinaryDataSet.
Why not just enhance the TSynBinaryDataSet class?
We just inherit from TSynBinaryDataSet, creating the new TSynDBSQLDataSet class.
AFAIK your PSExecuteStatement() implementation will be broken if the supplied Params[] order does not follow the order in the SQL statement itself.
So I propose another implementation, using TQuery for SQL parsing, and using any kind of TSQLDBConnectionProperties (not only SynDBRemote over WinHTTP).
See http://synopse.info/fossil/info/91e8b4c249

Now I need now to upgrade SynDBMidasVCL.pas to be able to add something similar to TPiCustomClientDataSet.
But I first, I would like to have your feedback.

About cross-platform, SynDBRemote.pas and SynDB.pas rely heavily on RawByteString structures and the optimized SynCommons.pas.
So it is not compatible with the NextGen compiler, and would be difficult to make it compatible.
It is not a problem of OS support in mORMot, since we support Linux via FPC without any problem, but it is a Delphi issue: the NextGen compilers did introduce abusive restrictions and breaking syntax changes.
For RAD user code, NextGen won't make a huge difference. But for low-level library code, NextGen did break the language. Just take a look how much $ifdef NEXTGEN conditional appeared in the Delphi RTL, and third party libraries.

Thanks a lot for sharing!

Murat Ak · 2015-01-02 08:09:29

Hi ab,

I like to see that you enhance the TSynBinaryDataSet and TSynDBSQLDataSet.
I am using TClientDataSet for a long time, really it has power, i did what i want all with TClientDataSet.
I think you can all replacement of datasnap, and many many people (and me) respect to you .

I understand you about crossplatform. But you can imagine how it is too much important.
Because ios and android is more important platform nowadays.
I test RemoteDB from TMS working crossplatform. But I have to say using database mORMot performance is much more.
But if i want to make a crossplatform application, i can not use mORMot .

Thanks for mORMot
Best Regards
Murat Ak

ab · 2015-01-02 10:49:42

If you want a true crossplatform application, do not use remote DB connection!
Do not put your SQL in your client application, but on a service, accessible via REST!
So use mORMot ORM/SOA instead of SynDBRemote, for crossplatform.
This is the much reliable option - see http://synopse.info/files/html/Synopse% … ml#TITL_86
SynDBRemote on mobile apps is a wrong design approach IMHO.

What is missing with the current version of SynCrossPlatform units is the direct support of TDataSet results.

Thanks for the feedback about speed difference between SynDBRemote and the TMS solution.
Open Source can rock!

itSDS · 2015-01-02 13:39:49

We also use FireMonkey for iOS and Android development of our software.
However, without ClientDataSet. Here I am of the same opinion as AB.

We write simple SOA interface functions or use REST to access TSQLRecord tables.
It's fast, safe and simple.

I think that you can implement the ClientDataSet with a simple SOA / interface.

ab · 2015-01-02 17:50:33

I've just introduced TSynDBDataSet as a TClientDataSet allowing to apply updates on a SynDB connection, via its internal TSynDBSQLDataSet: will be used now for overloaded ToClientDataSet() functions result.
See http://synopse.info/fossil/info/6c366e8491
and http://synopse.info/fossil/info/9b10fee7e7

ApplyUpdates() was not tested yet, but I tried to follow TPiCustomClientDataSet implementation.
Feedback is welcome!

Murat Ak · 2015-01-03 08:58:19

Hi,

I change some code on TSynBinaryDataSet.InternalInitFieldDefs.
You use ColumnDataSize, but i think it should be ColumnValueDBSize.
Otherwise if field has no value, or less chars you can not change data.

procedure TSynBinaryDataSet.InternalInitFieldDefs;
var F: integer;
    DBType: TFieldType;
begin
  FieldDefs.Clear;
  if fDataAccess=nil then
    exit;
  for F := 0 to fDataAccess.ColumnCount-1 do
    with fDataAccess.Columns[F] do begin
    case ColumnType of
    SynCommons.ftInt64: DBType := ftLargeint;
    SynCommons.ftDate:  DBType := ftDateTime;
    SynCommons.ftUTF8:
      //if ColumnDataSize=0 then
      if ColumnValueDBSize=0 then // Murat
        DBType := ftDefaultMemo else
        DBType := ftWideString; // means UnicodeString for Delphi 2009+
    SynCommons.ftBlob:  DBType := ftBlob;
    SynCommons.ftDouble, SynCommons.ftCurrency: DBType := ftFloat;
    else raise EDatabaseError.CreateFmt('GetFieldData ColumnType=%d',[ord(ColumnType)]);
    end;
    //FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnDataSize);
    FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnValueDBSize); // Murat
  end;
end;

And Applyupdates is not working. There is a error that "TOleDBConnection: The parameter is incorrect".
My codes was working on TPiClientDataSet.

About using TSQLRecord;
Do TSQLRecord have clonecursor or somethings like that?
Do TSQLRecord have indexfieldnames and setrange, findkey functions?
I know ORM is very usefull, but TSQLRecord is enough for me i dont know.
I have a ERP system and now i want to write that as a crossplatform.
I try to find a good solution access database, and on client side i need some functions give me TClientDataSet.
And for reporting i need a complex sql queries.
I have a ORM based on TDataSet.
and I have a SQL object that generating SQL Command Text, i dont use direct sql syntax.
and I have a Bussiness Rule system based on TDataSet.
I am working my ORM and SQL Object more than 10 years.

I dont understand why SynDBRemote on mobile apps is a wrong design approach IMHO?
It is perfect solution for me except not working crossplatform.
May be in future you can do that working on crossplatform .

I have to research more on TSQLRecord, or SOA interface.
but i dont want to convert json data, because of performance.
I need to work more about mORMot.

Best Regards
Murat Ak

ab · 2015-01-03 09:06:25

What is the error cause? what is the SQL?

About the speed of our SOA/ORM json format, just try it: you would be amazed!

Murat Ak · 2015-01-03 13:57:16

Hi ab,

Create a TSynDBDataSet.
Set Connection.
Connection is connected to northwind databases.

FSynDBDataSet.CommandText := 'SELECT * FROM Categories';
FSynDBDataSet.Open;
FSynDBDataSet.Edit;
FSynDBDataSet.FieldByName('CategoryName').AsString := FSynDBDataSet.FieldByName('CategoryName').AsString + 'x';
FSynDBDataSet.Post;
FSynDBDataSet.ApplyUpdates(0);

and you can see error that "TOleDBConnection: The parameter is incorrect".
TClientDataset generate update sql inself and send to connection with parameters.

About json i know about how you are amazing people.
But i want to make somethings working on TClientDataSet crossplatform with raw data from MS SQL Server.

Is there any way to run TSynBinaryDataSet.From(const BinaryData: RawByteString;
DataRowPosition: PCardinalDynArray) function?
How can get data as a RawByeteString from Rest Server and set TSynBinaryDataSet.From function?

Maybe we can write a TClientDataSet working with Rest on crossplatform.
I said before i need to more time to understand and learn mORMot much.
I know you prefer ORM not TClientDataSet, but I combine DataSet and ORM together .

ab · 2015-01-03 14:24:37

By itself, the TDataSet is an horrible performance bottleneck.
An ORM over TDataSet would be working of course, since both are holding data, but IMHO this leads to slow performance.
In fact, we use the JSON buffer as TDataSet.

About the update, what is the SQL content?

itSDS · 2015-01-03 14:59:34

Hi AB i think you are an performance enthusiast and me too. But the ClientDataSet itself is something for the clicker under the programmer who want's to use "LiveBindings".
Correct me if i am wrong. Here does not performance count but RAD.

Murat Ak · 2015-01-03 16:00:30

Hi ab,

about update, TClientDataSet generates update sql command.
But on sql server profiler there is not sql because it is interruted by error.
On PiClientDataSet, when i looking profiler, i can see that sql.

exec sp_executesql N'update Categories set
CategoryName = @P1
where
CategoryID = @P2 and
CategoryName = @P3',N'@P1 nvarchar(4000),@P2 bigint,@P3 nvarchar(4000)',N'Beveragesx',1,N'Beverages'

I dont send that sql by me, it is generated by TClientDataSet ApplyUpdates function.
I can make examples, and i like to that, because without example find solution is difficult always.
But i can not find send files here.

I understand you about performance. But i am writing a ERP system, and i use GUI.
All gui working over dataset, grid, edits, reportings... If i use without gui i understand you i can do without dataset.
When i want to make edit able inputs, i have to convert TSQLRecord to DataSet Always, And for reporting tools.
Otherwise i have to do everything myself over TSQLRecord.
I know DataSet has performance problem, but not so so bad. On client side computers have good performance.
and on ios ve android have good performance too.

I dont want to make you convince. (sorry my bad english )
Just i want to find good solutions for going to right way.
What i need;
I need TField: Display Label, display format, edit format, ReadOnly, ProviderFlags and more.
I need TDataSet: DataSource, IndexName, IndexFieldNames, SetRange, FindKey (all over from indexed data), CloneCursor (so important for me), and using db grids, db reportings directly.

I know if i try to use TSQLRecord, i have to add all functions that have TClientDataSet.
And over TSQLDBWinHTTPConnectionProperties connection;
IRows := FSQLProps.Execute(SQL, [])
and
TSynDBDataSet is very near performance over internet connection on my test.
But there is difference from TMS.
I wish i can use mORMot on crossplatform.

And May be i can share my ORM, SQL Object and Business Rules codes over TDataSet.
It is like devexpress XAF framework. There is a DataModule (for data) and TPiApplicationModule (for gui) frameworks.

Thanks all,
Best Regards
Murat Ak

ab · 2015-01-03 21:06:07

Please check http://synopse.info/fossil/info/87d596ed6e
On our side, now ApplyUpdates() works - at least with a SQlite3 backend.

I would like to enhance TSQLTableJSON based TDataSet, able to implement ApplyUpdates(), without the need of using the TClientDataSet.
It would ease the REST/ORM based UI generation.

mORMot Open Source

#1 2015-01-01 11:07:11

mORMot working as a DataSnap Replacement

#2 2015-01-01 15:53:13

Re: mORMot working as a DataSnap Replacement

#3 2015-01-02 08:09:29

Re: mORMot working as a DataSnap Replacement

#4 2015-01-02 10:49:42

Re: mORMot working as a DataSnap Replacement

#5 2015-01-02 13:39:49

Re: mORMot working as a DataSnap Replacement

#6 2015-01-02 17:50:33

Re: mORMot working as a DataSnap Replacement

#7 2015-01-03 08:58:19

Re: mORMot working as a DataSnap Replacement

#8 2015-01-03 09:06:25

Re: mORMot working as a DataSnap Replacement

#9 2015-01-03 13:57:16

Re: mORMot working as a DataSnap Replacement

#10 2015-01-03 14:24:37

Re: mORMot working as a DataSnap Replacement

#11 2015-01-03 14:59:34

Re: mORMot working as a DataSnap Replacement

#12 2015-01-03 16:00:30

Re: mORMot working as a DataSnap Replacement

#13 2015-01-03 21:06:07

Re: mORMot working as a DataSnap Replacement

Board footer