Devart Forums

Hey
I just did some tests with Sqlite and i found serious bug
with types mismatch when
aggregation functions are used:

How to reproduce:

1) We have example sqlite db with :
table1:
field A: integer
field B: date
Now fill some records with values

2. New form. Put Uniconnection, UniQuery with
"select max(A) as maxA , max(B) as maxB from table1",

3. Design : On UniQuery: Active = true and Add All Fields.

4) Design : On UniQuery: Active = false

5) Now delete everything from table1

6) Turn on UniQuery: Active: True

Bang ! Error:
---------------------------
UniQuery1: Type mismatch for field 'maxA', expecting: LargeInt actual: String.
---------------------------

Delphi 2010 , Unidac 4.1.5

Any workarounds ?

greets
sk

Hello,

This problem is due to SQLite itself. When calling the Prepare method, SQLite incorrectly returns the "aggregate" field type, and when opening DataSet, the "aggregate" field type is defined by the first record in this field. Therefore we cannot change this behaviour. For such fields we don't recommend creating fields in design time. However, in the design time, you can create Data-fields, whose names coincide with the field name in the query, in this case, the data type, that you assign when creating the field, will be used.

-- you can create Data-fields, whose names coincide with the field name in the query

Could you please clarify more about what is Data-fields meaning?

In your first sentence you are not recommending create fields in design time but in second you recommending to create field in design time.

We have the same situation and it does not work in both cases.

more details:

in TUniQuery in property SQL I have:

select pkey, MAIN_KEY, height, cast(1 as integer) BldType from bldrectangular
where Main_Key=:PKey
union
select pkey, MAIN_KEY, height, cast(2 as integer) BldType from bldcircular
where Main_Key=:PKey
union
select pkey, MAIN_KEY, height, cast(3 as integer) BldType from bldpolygonal
where Main_key=:PKey

and I have:

TiersBLDTYPE: TIntegerField;

When I run .Open; I have message: Type mismatch for field 'BLDTYPE', expecting: Integer actual: String

Hello,

To avoid this problem, you should open Fields Editor of your UniQuery in design-time, add all fields, delete the BldType fields (created as ftString), add a new field NewField with the BldType name, Integer type, and Data field type. In this case, the field type will be the same in both design-time and run-time.

--did you read my post ?

TiersBLDTYPE: TIntegerField;

Hello,

When creating a field with a necessary type, this error doesn't occur. Please send your project demonstrating the problem and the DB file to alexp*devart*com