How to get mORMot to work with FastReport and Express Quantum Grid?

BrentG · 2012-06-21 15:02:50

There are a few TDataset oriented controls that the customer simply "must have" in my application.
So what is the best way to use FastReport and DevEx Quantum Grid with mORMot?

Do I use a TClientDataset and fill it with data from mORMot? Are there methods available to do this?
I'm searching for this "missing link"? (link from mORMot to TDataset)

I know from a purest standpoint, using code will be faster and more efficient, but I need these TDataset grid & report components so I can allow the user to sort, group, summarize data at will. I also want the user to be able to create his own reports.

What's the solution?

TIA
Brent

ab · 2012-06-21 15:16:27

There is no TDataSet support yet.
So you won't be able to use all the DB.pas related features of those components.
What you can to is using a memory TDataSet, which is to be filled from mORMot.

For the Grid point of view, it could be filled from a TSQLTable result.
You have to use the "GetValue" event handler, and let it be filled by TSQLTable, via the FillStringGrid() function.
See http://synopse.info/forum/viewtopic.php?pid=3360#p3360

BrentG · 2012-06-21 16:50:29

Thanks for the quick reply. I wish other forums were this responsive with their software.

::There is no TDataSet support yet.
::So you won't be able to use all the DB.pas related features of those components.
::What you can to is using a memory TDataSet, which is to be filled from mORMot.

Ok. I'm wondering if I should stick to a Sqlite memory table so the datatypes are the same?
Can anyone recommend a good (inexpensive) Sqlite TDataset component for Delphi?

::For the Grid point of view, it could be filled from a TSQLTable result.
::You have to use the "GetValue" event handler, and let it be filled by TSQLTable, via the FillStringGrid() function.
::See http://synopse.info/forum/viewtopic.php?pid=3360#p3360

Sounds feasible. I'll give it a try. Of course if the user edits any of the cell values, I'll have to write it back to mORMot. Shouldn't be too difficult.

In some future version, it would be nice if there were methods that allowed users to create adhoc "queries". This would be easy enough for single table "queries". Not sure about joining tables though.

TIA
Brent

P.S. How's the SQL implementation coming along. VBG

lele9 · 2012-06-21 17:32:51

Hi BrentG,
when i find mormot i forget TDataset! I "think Object"! Right AB?:D

for SQL oint of view, when i have complex joined query i use ExecuteList command
i.e.

 Self.Elenco := ControllerMain.Client.ExecuteList([TSQLPersona, TSQLComune, TSQLProvincia, TSQLRegione],
                'SELECT PERSONA.ID, PERSONA.RAGIONESOCIALE, PERSONA.DESCRIZIONE, PERSONA.TITOLO, PERSONA.CODICEFISCALE,   PERSONA.PARTITAIVA, PERSONA.DATANASCITA, PERSONA.SESSO, PERSONA.INDIRIZZO, C.NOME AS COMUNE, C.PROVINCIA, C.CAP, C.REGIONE ' +
                                                    'FROM PERSONA LEFT OUTER JOIN (SELECT COMUNE.ID, COMUNE.NOME, P.SIGLA as Provincia, P.Regione as Regione, COMUNE.CAP ' +
                                                    'FROM COMUNE LEFT OUTER JOIN (SELECT PROVINCIA.ID, PROVINCIA.NOME, PROVINCIA.SIGLA, REGIONE.NOME as Regione FROM PROVINCIA LEFT OUTER JOIN REGIONE on PROVINCIA.REGIONE = REGIONE.ID) P ' +
                                                    'on COMUNE.PROVINCIA = P.ID) C ON PERSONA.COMUNE = C.ID;');

for fastReport see onGetValue event of Report and CompareTextCommand
i.e.

procedure TControllerAssemblea.RepAssembleaGetValue(const VarName: string;
  var Value: Variant);
...
   if CompareText(VarName, 'NomeCondominio') = 0 then //in the report i have a MemoText with value like this "Nome Condominio: [NomeCondominio]"
    Value := TSQLAssemblea(self.ObjectsList[i]).Condominio.Nome;
...

this code substitute value with [] in report with my object properties.

I hope this help you.
Emanuele.

ab · 2012-06-21 18:42:39

I suspect I'll implement tomorrow a new unit, able to publish any TSQLTable into a TDataSet component.

It will fill the gap between SynDB/mORMot and the VCL DB oriented components.

BrentG · 2012-06-21 19:35:52

ab wrote:

I suspect I'll implement tomorrow a new unit, able to publish any TSQLTable into a TDataSet component.
It will fill the gap between SynDB/mORMot and the VCL DB oriented components.

That would be great, thanks!
That would allow me to use mORMot for more applications. :-)

Brent

Junior/RO · 2012-06-22 04:28:48

BrentG wrote:

That would be great, thanks!
That would allow me to use mORMot for more applications. :-)
Brent

+1

ab · 2012-06-23 10:04:59

That's it: I've added SynDBVCL unit to handle DB VCL dataset using SynDB data access.
http://synopse.info/fossil/finfo?name=SynDBVCL.pas
This is for direct connection to the DB using our SynDB classes. May be handy.

And see also the newly added SQLite3VCL unit to fill a DB VCL dataset using TSQLTable/TSQLTableJSON data access.
http://synopse.info/fossil/finfo?name=S … te3VCL.pas
This can be used from the ORM part of mORMot, since most results are returned as TSQLTableJSON.

This first draft uses a TClientDataSet in memory to set the data.
Not perfect, but working.
Hope it helps.

BrentG · 2012-06-24 21:38:58

ab,
Hey that's great! I didn't think you'd get it done this fast. I'll try it out.

Thanks again.

Brent

ab · 2012-06-25 06:27:54

See http://blog.synopse.info/post/2012/06/2 … t-or-SynDB article for further information.

mpv · 2012-06-25 10:03:55

To @ab

As I remember when I use TClientDataSet (long time ago in Delphi 3) in "inmemory" way, setting TClientDataSet.LogChanges := False give GREAT performance increase. I think it's true in other Delphi versions too and must be used in TSQLTableToDataSet.

ab · 2012-06-25 13:07:58

@mpv

Thanks for the idea.
I've taken it in account.
http://synopse.info/fossil/info/ee05d4b6c1

coblongpamor · 2012-07-20 01:55:43

hi, i got this error when trying to fill DB VCL dataset with TSQLTable.

EDatabaseError with message 'Cannot access field 'ID' as type Variant

i use SQLite3VCL.

one more: how to save back from Dataset to the framework?

many thanks

coblongpamor · 2012-07-20 02:30:33

i also got exception class EDBClient with message 'Invalid Parameter' at line 140

TClientDataSet(result).CreateDataSet;

this is how i call the function:

fTableSchedule:= fClient.List([TSQLSchedule],'*');
fDataSetSchedule:= TSQLTableToDataSet(Self,fTableSchedule,fClient);

and this is TSQLSchedule class declaration;

  TSQLBaseSigned = class(TSQLRecordSigned)
  private
    fDocumentNumber: RawUTF8;
    fDate: TTimeLog;
    fData: TSQLRawBlob;
    fNote: TSQLRawBlob;
    fCreatedTime: TCreateTime;
    fModifiedTime: TModTime;
    fTimeLog: TTimeLog;
  protected
  public
  published
    property DocumentNumber: RawUTF8 read fDocumentNumber write fDocumentNumber stored False;
    property Date: TTimeLog read fDate write fDate;
    property Data: TSQLRawBlob read fData write fData;
    property Note: TSQLRawBlob read fNote write fNote;
    property CreatedTime: TCreateTime read fCreatedTime write fCreatedTime;
    property ModifiedTime: TModTime read fModifiedTime write fModifiedTime;
    property TimeLog: TTimeLog read fTimeLog write fTimeLog;
    property SignatureTime;
    property Signature;
  end;
	
  TSQLBaseSchedule = class(TSQLBaseSigned)
  private
    // internal fields
    fActualFinish: Integer;
    fActualStart: Integer;
    fCaption: RawUTF8;
    fEventType: Integer;
    fFinish: TTimeLog;
    fLabelColor: Integer;
    fLocation: RawUTF8;
    fMessage: RawUTF8;
    fOptions: Integer;
    fParentID: TSQLBaseSchedule;
    fRecurrenceIndex: Integer;
    fRecurrenceInfo: TSQLRawBlob;
    fReminderDate: TTimeLog;
    fReminderMinutesBeforeStart: Integer;
    fResourceID: TSQLRawBlob;
    fStart: TTimeLog;
    fState: Integer;
    // additional for tasks
    fTaskComplete: Integer;
    fTaskIndex: Integer;
    fTaskLinks: TSQLRawBlob;
    fTaskStatus: Integer;
    // additional for reminders
    fReminderResourcesData: TSQLRawBlob;
  protected
  public
  published
    // internal fields
    property ActualFinish: Integer read fActualFinish write fActualFinish;
    property ActualStart: Integer read fActualStart write fActualStart;
    property Caption: RawUTF8 read fCaption write fCaption;
    property EventType: Integer read fEventType write fEventType;
    property Finish: TTimeLog read fFinish write fFinish;
    property LabelColor: Integer read fLabelColor write fLabelColor;
    property Location: RawUTF8 read fLocation write fLocation;
    property Message: RawUTF8 read fMessage write fMessage;
    property Options: Integer read fOptions write fOptions;
    property ParentID: TSQLBaseSchedule read fParentID write fParentID;
    property RecurrenceIndex: Integer read fRecurrenceIndex write fRecurrenceIndex;
    property RecurrenceInfo: TSQLRawBlob read fRecurrenceInfo write fRecurrenceInfo;
    property ReminderDate: TTimeLog read fReminderDate write fReminderDate;
    property ReminderMinutesBeforeStart: Integer read fReminderMinutesBeforeStart write fReminderMinutesBeforeStart;
    property ResourceID: TSQLRawBlob read fResourceID write fResourceID;
    property Start: TTimeLog read fStart write fStart;
    property State: Integer read fState write fState;
    // additional for tasks
    property TaskComplete: Integer read fTaskComplete write fTaskComplete;
    property TaskIndex: Integer read fTaskIndex write fTaskIndex;
    property TaskLinks: TSQLRawBlob read fTaskLinks write fTaskLinks;
    property TaskStatus: Integer read fTaskStatus write fTaskStatus;
    // additional for reminders
    property ReminderResourcesData: TSQLRawBlob read fReminderResourcesData write fReminderResourcesData;
  end;

  TSQLSchedule = class(TSQLBaseSchedule)
  private
  protected
  public
  published
  end;

thanks for your support.

ab · 2012-07-20 13:42:35

Is there data in all columns of the incoming list (at least one value in each column)?

Issues comes perhaps from the fact that aTable.FieldLengthMax() returns 0 in some cases.

coblongpamor · 2012-07-20 16:56:31

Yes, there one row data, but not all column have a value.
then, i delete the row data, and exception still ocured.

ab · 2012-07-20 18:59:24

Try changing aTable.FieldLengthMax() into aTable.FieldLengthMax()+1 in the unit.

Feedback is welcome!

coblongpamor · 2012-07-21 09:12:47

Yes, changing aTable.FieldLengthMax() into aTable.FieldLengthMax()+1 in the unit SQLite3VCL solved the problem.
and abaut

EDatabaseError with message 'Cannot access field 'ID' as type Variant

i just add 2 lines of code to handle TLargeintField, and so far it's work.

...
if result.Fields[F] is TLargeintField then
  result.Fields[F].AsInteger := aTable.GetAsInteger(i,F)
else
   result.Fields[F].AsVariant := aTable.GetVariant(i,F,aClient);

will you add method in the future to Fill TSQLTable Back from updated conten of TDataset?

thank you.

ab · 2012-07-21 10:32:16

Issues may be fixed with http://synopse.info/fossil/info/5fdb5e0e5e

About handling writing back to database, it is not planned yet.
It is some work to implement, which does not make much sense with the "stateless" approach of the RESTful architecture implemented via mORMot.
And it would let it use RAD components, which we would like not to do for design reasons.

But it is still on the road map, as "briefcase" component.
No time yet.

coblongpamor · 2012-07-21 11:13:57

Thanks for the quick response.
Actually I also do not want to use the RAD approach, I prefer to wear such a way that mORMot do (generate control from the code).
But I'm trying to work with mORMot to make scheduling application, that I use cxScheduler for the user interface.

I really do not want to use cxScheduler in BoundMode that requires TDataSet and TDataSource.
Working in UnboundMode also actually very easy, TcxScheduleStorage have LoadFromFile and SaveToFile method.
But I have trouble when trying to save it to mORMot.
Then I go back to the BoundMode, with TSQLTableToDataSet facilities of mORMot. And I still tend to want to use cxSchedulre in UnboundMode.

mpv · 2012-07-22 08:49:30

As for me, the easiest way to write TClientDataSet back to database in current implementation is handle BeforeApplyUpdates event and put dataset Delta back to TSQLRecord class. It's just 4 lines of code.....

About issue with FieldLength: in my implementation I use Columnus property of TSQLDBStatementWithParamsAndColumns (do not filleld for OleDB now) to determinate real size of string columns (it's not a biggest length of string in result data as in current mORMot implementation - field may be varchar(1000) but all row contains small string, and not possible to calculate max length via index, because I may write select fieldA+Left(fieldB, 2) from SomeTable, and real field size determinated by database.

coblongpamor · 2012-07-22 10:09:38

@mpv: i don't understand about

put dataset Delta back to TSQLRecord class

.
i'll try to implement OnBeforeApplyUpdate as you suggest.
thnk you.

another issue, i never succes with TSQLRawBlob field. the field never added to TCLientDataset.

mpv · 2012-07-22 10:40:53

@coblongpamor. Oh, I work with ClientDataSet many years ago - every thing is changed as I see. Now this parameter named OwnerData BeforeApplyUpdates(Sender: TObject; var OwnerData: OleVariant);
You must find corresponded TSQLRecord by ID from OwnerData and move modified fields from OwnerData to TSQLRecord instance.
In Delphy3 after this operation you must return NULL in OwnerData to avoid passing data to ClientData.RemoteServer - in current implementation I don't know.
And you must turn on ClientDataSet.LogChanges after call TSQLTableToDataSet (it Turned off for performance reason when fill dataSet with start data).

coblongpamor · 2012-07-22 13:23:39

@mpv. i never use ClientDataSet before. thnks you for the explanation.

mORMot Open Source

#1 2012-06-21 15:02:50

How to get mORMot to work with FastReport and Express Quantum Grid?

#2 2012-06-21 15:16:27

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#3 2012-06-21 16:50:29

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#4 2012-06-21 17:32:51

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#5 2012-06-21 18:42:39

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#6 2012-06-21 19:35:52

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#7 2012-06-22 04:28:48

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#8 2012-06-23 10:04:59

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#9 2012-06-24 21:38:58

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#10 2012-06-25 06:27:54

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#11 2012-06-25 10:03:55

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#12 2012-06-25 13:07:58

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#13 2012-07-20 01:55:43

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#14 2012-07-20 02:30:33

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#15 2012-07-20 13:42:35

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#16 2012-07-20 16:56:31

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#17 2012-07-20 18:59:24

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#18 2012-07-21 09:12:47

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#19 2012-07-21 10:32:16

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#20 2012-07-21 11:13:57

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#21 2012-07-22 08:49:30

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#22 2012-07-22 10:09:38

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#23 2012-07-22 10:40:53

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

#24 2012-07-22 13:23:39

Re: How to get mORMot to work with FastReport and Express Quantum Grid?

Board footer