8.3.2 TVirualQuery + TvirtualTable = BUG...

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
vampirekb
Posts: 9
Joined: Mon 20 Oct 2014 19:30

8.3.2 TVirualQuery + TvirtualTable = BUG...

Post by vampirekb » Sun 16 May 2021 18:55

Hello all !

Problem:
SQL QUERY in {} - doesnt' WORK (but sentence is correct ! Update table SET field = where field= AND field=)

I made it in a russian way..i made a crutch with double work.. (1st SQL - search , 2nd table - edit..) but it's NOT right !






Form Example:
unit Menulocal_change;

interface
uses
Math, ExtCtrls,DB, Classes,SysUtils, MemDS, Uni, VirtualTable, CRBatchMove,VirtualQuery,StdCtrls, memData,VirtualDataSet,Dialogs;

procedure CALC_consumants(var mytable:TVirtualTable;var myconsumats:TVirtualTable);

implementation

procedure CALC_consumants(var mytable:TVirtualTable;var myconsumats:TVirtualTable);
var
Q_consumants,Q_sum:TVirtualQuery;
procent:int64;
begin
Q_consumants:=TVirtualQuery.Create(nil);
Q_consumants.SourceDataSets.Clear;
Q_consumants.SourceDataSets.Add(mytable,'','vt_menu');
Q_consumants.SourceDataSets.Add(myconsumats,'','vt_cons');

Q_sum:=TVirtualQuery.Create(nil);
Q_sum.SourceDataSets.Clear;
Q_sum.SourceDataSets.Add(mytable,'','vt_menu');
Q_sum.SourceDataSets.Add(myconsumats,'','vt_cons');

{$IFDEF DEBUG}
mytable.SaveToXML('mytable_BEFORE.xml');
{$ENDIF}
Q_consumants.SQL.Text:='SELECT * FROM vt_menu WHERE dopinfo_type=1';
Q_consumants.Open;
while not Q_consumants.Eof do
begin
{
Q_sum.SQL.Text:='UPDATE vt_menu SET dopinfo_price=('+
' (SELECT price FROM vt_menu WHERE unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')/'+
'(SELECT COUNT(*) FROM vt_menu WHERE dopinfo_type=1 AND dopinfo_unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')'+
'*(SELECT procent FROM vt_cons WHERE id = '+Q_consumants.FieldByName('dopinfo_id').AsString+')/100 '+
') WHERE dopinfo_id='+Q_consumants.FieldByName('dopinfo_id').AsString+' AND unum='+Q_consumants.FieldByName('unum').AsString;
Q_sum.ExecSQL;
}
Q_sum.SQL.Text:='SELECT ((SELECT price FROM vt_menu WHERE unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')/'+
'(SELECT COUNT(*) FROM vt_menu WHERE dopinfo_type=1 AND dopinfo_unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')'+
'*(SELECT procent FROM vt_cons WHERE id = '+Q_consumants.FieldByName('dopinfo_id').AsString+')/100 '+
') as dopinfo_price';
Q_sum.open;
Q_consumants.Edit;
Q_consumants.FieldByName('dopinfo_price').AsFloat:=Q_sum.FieldByName('dopinfo_price').AsFloat;
Q_consumants.Post;

Q_consumants.Next;
end;
{$IFDEF DEBUG}
mytable.SaveToXML('mytable_AFTER.xml');
{$ENDIF}
Q_consumants.Free;
Q_sum.Free;
end;

end.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: 8.3.2 TVirualQuery + TvirtualTable = BUG...

Post by Stellar » Tue 01 Jun 2021 06:12

Thank you for the information. To investigate this behavior of VirtualDAC, please compose a full sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Post Reply