unidac, SqLite and Quickreport group band

[dragonstar]

Forgive me if I'm not allowed to post this kind of entry - this is not for the Unidac developers but for Unidac USERS who may have run into this problem and can share a solution with me.

Delphi 7, Unidac, QuickReports, MySql and SqLite.

I have 2 identical tables in both MySql and SqLite to UNION together for a report, each table has a type and description.

The report consists of a group band whose expression is the type data field, and a detail band with a single qrdbtext for the description.

When I run the report against MySql it's perfect - the group band breaks twice and the descriptions are organized by type.

When I run it against SqLite the group band only fires once for the first value of the type data field and never again so I end up with all records listed under the same type.

QUESTION - has anyone run into this oddity when using QuickReports against an SqLite db via Unidac where a group band did not fire and if so do you know why and what is the solution?

Thanks in advance.

drc
[email protected]

[AlexP]

Hello,

Please send a small sample demonstrating the problem (including the SQLite database file) to alexp*devart*com

[dragonstar]

Actually I found the problem.

When doing the union between the 2 data sets I select a string literial I can use to sort each of the two subsets by, for example...

Select 'A' as SortKey,
Desc1 as Description
from Table 1
UNION
Select 'B' as SortKey,
Desc2 as Description
Order By SortKey, Description

... from which I set the QuickReports group band expression to field SortKey.

The problem is that SortKey despite only being a few characters long is coming back as field data type ftMemo which QuickReports doesn't recognize as a valid type for a group band.

I tried using Cast to make the field a Varchar(1) (to use the above single letter example) and the field still came back as ftMemo.

In the end I tweaked the QuickReport code so that it treats ftMemo fields the same as strings and all my reports work perfectly, but it is most annoying that a string literial in a Select comes back with what I consider an incorrect data field type.

drc

[AlexP]

Hello,

When using the UNION construction,aggregate functions, etc., SQLite returns field type as Unknown, since it cannot define the result field type, therefore we map such fields as TMemoField. To solve the problem, you can use the Data Type Mapping technology in order to explicitly specify the type, to which such fields should be mapped for DataSet

[dragonstar]

> you can use the Data Type Mapping technology in order to explicitly
> specify the type, to which such fields should be mapped for DataSet

Can you point me in the direction of where I would find such technology and how to use it?


drc

[AlexP]

Hello,

You can find the information about DataTypeMapping in the UniDAC documentation: http://www.devart.com/unidac/docs/data_type_mapping.htm