mORMot Open Source

AOG

Member

Registered: 2014-02-24

Posts: 490

CreateAndFillPrepareJoined SQL table

I am using CreateAndFillPrepareJoined to get a SQL table.
With this table, a dataset is filled.

aProduct:=TSQLProduct.CreateAndFillPrepareJoined(aClient,'',[],[]);
fProductDataSet := TSynSQLTableDataSet.Create(Self,aProduct.FillTable);

Some of the joined fields are TDateTime and boolean.

Under FPC, a TRxDBGrid is used to display dataset-tables.
TRxDBGrid use some "magic" to display the right field-format: e.g boolean is displayed as checkbox.

This works as expected on normal datasets.

However, it seems that the field properties of the PrepareJoined joined-fields are not preserved.
Booleans and DateTime values are displayed as numbers.

Question: are all datatypes preserved with a PrepareJoined, or are these joined fields converted to text ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,671

Website

Re: CreateAndFillPrepareJoined SQL table

Edit:
I've found an issue and fixed it by http://synopse.info/fossil/info/e7198bd6bd
So forget about the orginal message below:

AFAIR in TSynSQLTableDataSet.GetRowFieldData, boolean are converted into integer and TDateTime as double values.
BUT the fields are defined with the expected type in TSynSQLTableDataSet.InternalInitFieldDefs, i.e. ftBoolean and ftDateTime.

So TSynSQLTableDataSet expect the field types to be specified as sftBoolean and sftDateTime from fTable.FieldType().
I guess there is perhaps an issue with fTable.FieldType() for *FillPrepareJoined().

In TSQLRecord.CreateAndFillPrepareJoined(), the following line gives all TSQLRecord classes (the main table + the joined tables) to the

T := aClient.ExecuteList(props.props.JoinedFieldsTable,SQL);

So, normally, the function FieldPropFromTables() should retrieve the field name as expected...

Normally the following line:

SQLType := fTable.FieldType(F,@EnumType);

should call fTable.InitFieldTypes which should call FieldPropFromTables() to retrieve the field name as expected...

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: CreateAndFillPrepareJoined SQL table

Thank you !
Joined fields now show up in TRxDBGrid according to their original definition !!

Thanks again Ab.

wai-kit

Member

From: Amsterdam, the Netherlands

Registered: 2012-11-27

Posts: 90

Re: CreateAndFillPrepareJoined SQL table

@Ab, so if I understand this thread right then a possible way to work with datasets and ORM is with CreateAndFillPrepareJoined and TSynSQLTableDataSet?
I mean if I want to avoid working with SQL, this is the way to go?

Last edited by wai-kit (2019-03-22 14:25:16)

---

fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...