Is a good idea mix ORM access and SynDBRemote?

Junior/RO · 2016-05-11 17:59:31

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)

EMartin · 2016-05-11 19:09:52

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

Best regards.

Junior/RO · 2016-05-11 19:56:22

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)

EMartin · 2016-05-11 20:02:34

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.

edwinsn · 2016-05-12 03:27:32

@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.

AOG · 2016-05-12 05:48:48

@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.

houdw2006 · 2016-05-23 01:20:25

@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;

...........

houdw2006 · 2016-05-23 01:27:26

@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.

houdw2006 · 2016-05-23 02:02:28

@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)

EMartin · 2016-05-23 14:16:05

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.

EMartin · 2016-05-26 10:33:22

@ab don' t forget this patch:

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

Thanks.

mORMot Open Source

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

Is a good idea mix ORM access and SynDBRemote?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Board footer