#1 2016-05-11 17:59:31

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Is a good idea mix ORM access and SynDBRemote?

Hi

I was thinking about use TSynDBDataSet.CommandText, for some ClientDataSet/DBGrid/dataware access, because is the best way that I know to use mORMot with a RAD/Desktop with SQLite3.

But for some reports and some another operations, I was thinking that ORM or interface based approach would be better.

Whats is the best approach? How to serve two connections to the same SQLite3 database, one with SynDB access and other with ORM/Interface access?

I would like to use mORMotMidasVCL, but I don't know how to save the delta/modifications of ClientDataSet back to mORMot.

Thank you.

Last edited by Junior/RO (2016-05-11 18:01:21)

Offline

#2 2016-05-11 19:09:52

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Is a good idea mix ORM access and SynDBRemote?

Give a look to SynRestDataset too, SQLite3\Samples\ThirdPartyDemos\EMartin\TSynRestDataset, this approach not require the RDBMS client installed on local machine.

Best regards.


Esteban

Offline

#3 2016-05-11 19:56:22

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: Is a good idea mix ORM access and SynDBRemote?

Thank you, Esteban. I tried TSynRestDataset yesterday, worked well.  I read your code and liked what I saw.

But I also want to use the ORM part of mORMot for some more complex reports and services, which select SQL can be up to tens of lines.

Can you tell if TSynRestDataset could handle huge, multiline SELECTs?

And some selects with expressions like

select *, Cidade||'', ''||DDD||'' ''||Telefone as CidadeTelefone from Funcionario 

At this point, the only option that I see is to create services on the server and return only the serialization of the results as JSON.

Last edited by Junior/RO (2016-05-11 19:59:46)

Offline

#4 2016-05-11 20:02:34

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Is a good idea mix ORM access and SynDBRemote?

For complex reports is better to use interface based service and TSynRestDataset can work with them, just return array of JSON objects as SynDB return from a select.

Best regards.


Esteban

Offline

#5 2016-05-12 03:27:32

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: Is a good idea mix ORM access and SynDBRemote?

@EMartin, how does TSynRestDataset  determine the field types? I'm asking this question because the built-in ToClientDataset() function determine the field types by guessing, which is  not always accurate.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#6 2016-05-12 05:48:48

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: Is a good idea mix ORM access and SynDBRemote?

@edwinsn

I am using this:

T:=TSQLTableJSON.CreateWithColumnTypes([sftID,sftUTF8Text,sftUTF8Text,sftFloat,sftFloat,sftFloat,sftFloat,sftFloat,sftFloat,sftFloat,sftFloat],'',jsonresult);
LDS.DataSet:=TSynSQLTableDataSet.CreateOwnedTable(Owner,T);

Perhaps it helps.

Offline

#7 2016-05-23 01:20:25

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Is a good idea mix ORM access and SynDBRemote?

@EMartin,  I have found the reason why TSynRestSQLDataSet does not work under XE3 and above.  The commented function PSExecuteStatement SHOULD BE IMPLEMENTED, just like in TSynDBDataSet.  After the modification, The demo FishFactSyn can do insert, delete, and update, though it still does not work correctly. I will give the reasons in the following post.

  TSynRestSQLDataSet = class(TSynBinaryDataSet)
  protected
    fBaseURL: RawUTF8;
    ......
    {$ifdef ISDELPHIXE3}
    function PSExecuteStatement(const ASQL: string; AParams: TParams): Integer; overload; override;
    function PSExecuteStatement(const ASQL: string; AParams: TParams; var ResultSet: TDataSet): Integer; overload; override;
    {$else}
    function PSExecuteStatement(const ASQL: string; AParams: TParams; ResultSet: Pointer=nil): Integer; overload; override;
    {$endif}
    ......
  end;

  ......

{$ifdef ISDELPHIXE3}
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
  AParams: TParams): Integer;
var DS: TDataSet;
begin
  DS := nil;
  result := PSExecuteStatement(ASQL,AParams,DS);
  DS.Free;
end;

...........

Offline

#8 2016-05-23 01:27:26

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Is a good idea mix ORM access and SynDBRemote?

@EMartin, there is a memory leakage problem in TSynRestDataSet (SynRestMidasVCL), as it does not release the objects created in the constructor. There should be a corresponding destructor in TSynRestDataSet, just like this:

destructor TSynRestDataSet.Destroy;
begin
  fProvider.DataSet := nil;
  FreeAndNil(fDataSet);
  FreeAndNil(fProvider);

  inherited;
end;

@ab, there should be a destructor in TSynDBDataSet (SynDBMidasVCL) as well.

Offline

#9 2016-05-23 02:02:28

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Is a good idea mix ORM access and SynDBRemote?

@EMartin,  finally goes to the problem why the demo of SynRestMidasVCL, FishFactSyn, does not work properly.

In TSynRestSQLDataSet (SynRestVCL),  the ID is got by the following statement:

      lID := aParams[0].Value;    // soDelete:  about line 750
             or
      lID := aParams.ParamByName('ID').Value;  // soUpdate: about line 769


In both state, the lID is not the real row ID of TSQLBiolife, but the value of Species_No. The reason is that the ID column is not exist in the SELECT statement. So we should add the ID as the first column of the SELECT statement, like this:

  SynRestDataset.CommandText := 'http://LocalHost:8080/root/BioLife?'
      + 'select=ID,Species_No,Category,Common_Name,Species_Name,'
      + 'Length_cm,Length_in,Graphic,Notes,Som&sort=Species_No';


The memory leakage still exists in the demo FishFactSyn, because of there is not a destructor for the unit FFactWin, like this one
(declared fSQLModel as a private variable of TForm1):

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FreeAndNil(fSQLModel);
  FreeAndNil(SynRestDataset);
end;

Last edited by houdw2006 (2016-05-23 22:07:49)

Offline

#10 2016-05-23 14:16:05

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Is a good idea mix ORM access and SynDBRemote?

Thanks @houdw2006, I added your modifications.

With regard to Delphi XE3, I work with Delphi 7 and I not have any other version of Delphi so thank you for the feedback.

With regard to ID field, except for the insert command, the same is added after where clause because ORM of mORMot use it as primary key, creating it or mapping.

@ab I made  the @houdw2006 corrections:

https://drive.google.com/open?id=0Bx7LP … jJhay1YN0E

Can you apply this ?

Thanks.


Esteban

Offline

#11 2016-05-26 10:33:22

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Is a good idea mix ORM access and SynDBRemote?

@ab don' t forget this patch:

https://drive.google.com/open?id=0Bx7LP … jJhay1YN0E

Thanks.


Esteban

Offline

Board footer

Powered by FluxBB