Problem with calculated fields in DBSQL and ZEOS !
lazarus / 16-Databases

----------------------------------------------------------------------
mingodad · 2008-03-23 20:40

I found a problem with calculated fields with DBSQL and ZEOS, DBSQL
accepts everithing but shows nothing, ZEOS make the IDE hangs.

I'm testing with a PostgreSQL connection doing the folowing query:

select 123 as ID, 3 as Quantity, 1.234 as Price, (3*1.234) as Total;

No tables used, the ZEOS version rounds all fields to nearest integer,
DBSQL shows then correctly.

The test program follows:
---  Sorry I couldn't find a way to attach it here ---

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
  ZConnection, ZDataset, db, DBGrids, pqconnection, sqldb;

type

  { TForm1 }

  TForm1 = class(TForm)
    Datasource1: TDatasource;
    Datasource2: TDatasource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    PQConnection1: TPQConnection;
    SQLQuery1: TSQLQuery;
    SQLQuery1calcTotal1: TCurrencyField;
    SQLQuery1id1: TLongintField;
    SQLQuery1price1: TBCDField;
    SQLQuery1quantity1: TLongintField;
    SQLQuery1total1: TBCDField;
    SQLTransaction1: TSQLTransaction;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    procedure FormCreate(Sender: TObject);
    procedure SQLQuery1CalcFields(DataSet: TDataSet);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  with ZQuery1 do
  begin
       Active := False;
       Active := True;
  end;
end;

procedure TForm1.SQLQuery1CalcFields(DataSet: TDataSet);
begin
    with DataSet do
    begin
         FieldByName('calcTotal').AsCurrency :=
 FieldByName('quantity').AsCurrency * FieldByName('price').AsCurrency;
    end;
end;

initialization
  {$I unit1.lrs}

end.

----------------------------------------------------------------------
mingodad · 2008-03-23 20:43

And here is the form (.lfm)
-----------------------------

object Form1: TForm1
  Left = 305
  Height = 465
  Top = 161
  Width = 624
  HorzScrollBar.Page = 623
  VertScrollBar.Page = 464
  Caption = 'Form1'
  ClientHeight = 465
  ClientWidth = 624
  OnCreate = FormCreate
  object DBGrid1: TDBGrid
    Left = 8
    Height = 144
    Top = 64
    Width = 584
    DataSource = Datasource1
    FixedColor = clBtnFace
    FixedHotColor = cl3DLight
    Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize,
dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection,
dgConfirmDelete, dgCancelOnExit]
    OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn]
    ParentColor = False
    TabOrder = 0
    TabStop = True
  end
  object DBGrid2: TDBGrid
    Left = 8
    Height = 152
    Top = 288
    Width = 584
    DataSource = Datasource2
    FixedColor = clBtnFace
    FixedHotColor = cl3DLight
    Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize,
dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection,
dgConfirmDelete, dgCancelOnExit]
    OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn]
    ParentColor = False
    TabOrder = 1
    TabStop = True
  end
  object ZConnection1: TZConnection
    Protocol = 'postgresql-8'
    HostName = 'localhost'
    Database = 'dadbiz'
    User = 'dadbiz'
    Password = 'dadbiz2'
    Connected = True
    left = 29
    top = 22
  end
  object ZQuery1: TZQuery
    Connection = ZConnection1
    Active = True
    SQL.Strings = (
      'select 123 as ID, 3 as Quantity, 1.234 as Price, (3*1.234) as
Total'
    )
    Params = <>
    left = 91
    top = 22
  end
  object Datasource1: TDatasource
    DataSet = ZQuery1
    left = 152
    top = 24
  end
  object PQConnection1: TPQConnection
    Connected = True
    Streamedconnected = True
    DatabaseName = 'dadbiz'
    Password = 'dadbiz2'
    Transaction = SQLTransaction1
    UserName = 'dadbiz'
    HostName = 'localhost'
    left = 64
    top = 248
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = PQConnection1
    left = 120
    top = 250
  end
  object Datasource2: TDatasource
    DataSet = SQLQuery1
    left = 232
    top = 250
  end
  object SQLQuery1: TSQLQuery
    Active = True
    OnCalcFields = SQLQuery1CalcFields
    Database = PQConnection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'select 123 as ID, 3 as Quantity, 1.234 as Price, (3*1.234) as
Total'
    )
    Params = <>
    Upda]"]>Blockedde = upWhereKeyOnly
    UsePrimaryKeyAsKey = True
    ParseSQL = True
    left = 178
    top = 250
    object SQLQuery1id1: TLongintField
      AlignMent = taRightJustify
      DisplayWidth = 10
      FieldName = 'id'
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object SQLQuery1quantity1: TLongintField
      AlignMent = taRightJustify
      DisplayWidth = 10
      FieldName = 'quantity'
      Index = 1
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object SQLQuery1price1: TBCDField
      AlignMent = taRightJustify
      DisplayWidth = 15
      FieldName = 'price'
      Index = 2
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 15
      Size = 65535
    end
    object SQLQuery1total1: TBCDField
      AlignMent = taRightJustify
      DisplayWidth = 15
      FieldName = 'total'
      Index = 3
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 15
      Size = 65535
    end
    object SQLQuery1calcTotal1: TCurrencyField
      AlignMent = taRightJustify
      DisplayWidth = 10
      FieldKind = fkCalculated
      FieldName = 'calcTotal'
      Index = 4
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
  end
end