Issue with SQLite Query Field Type
Posted: 23.05.2024, 00:17
I am upgrading a VCL Win32 application from Delphi 11.3 using Zeos 7.2 to Delphi 12.1 using Zeos 8.0 with SQLlite.
I have a number of Queries using TZReadOnlyQuery, where aggregate fields created with the SUM() function are creating a TZUnicodeCLobField, rather than a TFloatField (or the appropriate Zeos Field equivalent). As I access the field value using the AsFloat property, I get an EDatabaseError Error "Cannot Access Field 'xxx' as type float".
My investigations have found that found that with prepared queries, if the First execution of the query results in an empty Dataset, then the re-opening the query with a populated dataset causes the SUM() fields to be the incorrect TZUnicodeCLobField class.
Zeos 7.2 operates appropriately and creates the fields as TFloatField.
I have mocked up an example of the problem.
I have a number of Queries using TZReadOnlyQuery, where aggregate fields created with the SUM() function are creating a TZUnicodeCLobField, rather than a TFloatField (or the appropriate Zeos Field equivalent). As I access the field value using the AsFloat property, I get an EDatabaseError Error "Cannot Access Field 'xxx' as type float".
My investigations have found that found that with prepared queries, if the First execution of the query results in an empty Dataset, then the re-opening the query with a populated dataset causes the SUM() fields to be the incorrect TZUnicodeCLobField class.
Zeos 7.2 operates appropriately and creates the fields as TFloatField.
I have mocked up an example of the problem.
Code: Select all
Writeln( 'Prepare Query ...');
ROQuery := TZReadOnlyQuery.Create(nil);
ROQuery.Connection := Connection;
ROQuery.SQL.Add( 'SELECT');
ROQuery.SQL.Add( 'Category,');
ROQuery.SQL.Add( 'SUM(Amount) AS TotalAmount');
ROQuery.SQL.Add( 'FROM TableA');
ROQuery.SQL.Add( 'WHERE Category = :Category');
ROQuery.SQL.Add( 'GROUP BY Category');
ROQuery.Prepare;
Writeln( 'Open Query where there are no records...');
ROQuery.ParamByName('Category').AsString := 'EmptyCat';
ROQuery.Open;
if ROQuery.RecordCount > 0 then
Writeln( 'Will not be executed as no records')
else
Writeln( 'Query resulted in no Records Found');
Writeln( 'Close Query where there were no records');
ROQuery.Close;
Writeln( 'Re-Open Query where there are now matching records');
ROQuery.ParamByName('Category').AsString := 'Cat2';
ROQuery.Open;
if ROQuery.RecordCount > 0 then
begin
ROQuery.First;
While NOT ROQuery.Eof do
begin
Writeln( '>>> Errors trying to access FieldByName(''TotalAmount'').AsFloat as it is type ' + ROQuery.FieldByName('TotalAmount').ClassName);
Writeln( ' Amount is ' + FloatToStr( ROQuery.FieldByName('TotalAmount').AsFloat));
ROQuery.Next;
end;
end
else
Writeln( 'No Cat2 Records Found');