TMSQuery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
nijazicitaku
Posts: 7
Joined: Tue 15 Mar 2016 20:05

TMSQuery

Post by nijazicitaku » Tue 15 Mar 2016 20:10

Hi there,


is there any possibility to apply MSQuery calculated fields to a table with no calculate dfields?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSQuery

Post by azyk » Fri 18 Mar 2016 09:39

You can write the value of the dataset calculated field to a table field using the BeforeUpdateExecute event handler by passing the value via query parameter. The below sample demonstrates how to do this on record adding.

Generate an INSERT query:

Code: Select all

INSERT INTO TestTable
  (... , TestField)
VALUES
  (... , :TestField)
...
Set the query as a value for the TMSQuery.SQLInsert.Text property. In the BeforeUpdateExecute event handler, set the calculated field of the dataset as a value for the parameter.

Code: Select all

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if (StatementTypes = [stInsert]) then
    Params.ParamByName('TestField').Value := MSQueryCalcField.Value;
...

nijazicitaku
Posts: 7
Joined: Tue 15 Mar 2016 20:05

Re: TMSQuery

Post by nijazicitaku » Fri 18 Mar 2016 20:05

Hi there,


thank you very much for your prompt reply, but my question was is it possible to
do on MSQuery.ApplyUpdates?




Thanks

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSQuery

Post by AlexP » Mon 21 Mar 2016 13:55

Hello,

When calling the ApplyUpdates method, the BeforeUpdateExecute event will be triggered for each changed record.

nijazicitaku
Posts: 7
Joined: Tue 15 Mar 2016 20:05

Re: TMSQuery

Post by nijazicitaku » Mon 21 Mar 2016 21:11

Hi,


ok but what if updated record already exists in table?



Cheers

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSQuery

Post by azyk » Thu 24 Mar 2016 11:09

Generate an UPDATE query:

Code: Select all

UPDATE TestTable SET
...
  TestField = :TestField
WHERE
...
Set the retrieved query as a value for the TMSQuery.SQLUpdate.Text property in the BeforeUpdateExecute handler, add code that would set a value for the parameter on editing an existing record;

Code: Select all

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin

...
  if (StatementTypes = [stUpdate]) then
    Params.ParamByName('TestField').Value := MSQueryCalcField.Value;
...

nijazicitaku
Posts: 7
Joined: Tue 15 Mar 2016 20:05

Re: TMSQuery

Post by nijazicitaku » Sun 27 Mar 2016 19:41

Hi,

As first HAPPY EASTER.

Here is the code I'm using.
Calculated fields are not recognized as parameters on BeforeUpdateExecute.

with QryMallDetal do begin
SQL.Clear;
SQL.Add('SELECT TOP 1 MHDUI, MHDViti, MHDNjOrg, MHDLlDok, MHDNum, MHDRen, MHDLldokt, MHDData, MHdKomId, MHDDudLloji, MHDDudZyra, MHDDudNum, MHDDudData, MHDKursi, MHDArtId,');
SQL.Add('MHDArtKodi, MHDArtEmri, MHDArtRap, MHDNrSerik, MHDDatSkad, MHDPeshaSpec, MHDRafti, MHDSasiPor, MHDSasia, MHDCmimFatV, MHDRabPrc, MHDRabVleV, MHDShpePrc, MHDShpeVleV,');
SQL.Add('MHDRivPrc, MHDRivVle, MHDTranPrc, MHDTranVle, MHDDogPrc, MHDDogVle, MHDAkcPrc, MHDAKcVle,');
SQL.Add('MHDTVShPrc, MHDTVShVle, MHDTVShKred, MHDTvsh, MHDShpenPrc, MHDShpenVle, MHDTatShit, MHDPakMarPrc, MHDPakMarVle, MHDPakCmim, MHDShumMarPrc,');
SQL.Add('MHDShumMarVle, MHDShumCmim, MHDProj, MHDShtoUser, MHDShtoKoha, MHDEditUser, MHDEDitKoha, MHDAktiv FROM tblMallHyrDetajet');
Prepared;
Open;
end;
QryMallDetal.Close;

QryMallDetal.FieldDefs.Update;
for i := 0 to QryMallDetal.FieldDefList.Count - 1 do
with QryMallDetal.FieldDefList do
if (DataType <> ftUnknown) and not (DataType in ObjectFieldTypes) then
CreateField( QryMallDetal, nil, QryMallDetal.FieldDefList.Strings);

with QryMallDetal do begin
FieldDefs.Update;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmimiV';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin FieldKind := fkCalculated;
FieldName := 'MHDCmimiFat';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDRabVle';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDShpeVle';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmimi';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDBazaTVSh';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmKost';
DataSet := QryMallDetal;
Calculated:= true;
end;

end;

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSQuery

Post by azyk » Wed 30 Mar 2016 11:48

Compose a small test project to demonstrate how you attempt to pass the value of the calculated field via parameter to BeforeUpdateExecute. Include CREATE scripts for test tables into the project and send the project to andreyz*devart*com .

nijazicitaku
Posts: 7
Joined: Tue 15 Mar 2016 20:05

Re: TMSQuery

Post by nijazicitaku » Wed 25 May 2016 20:45

Hi there,

does TMSQuery supports InternalCalc fields?

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: TMSQuery

Post by ViktorV » Thu 26 May 2016 11:24

SDAC doesn't support FieldKind "fkInternalCalc". If you want us to implement the feature, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... components. If the suggestion gets a lot of votes, we will consider the possibility to implement it.

Post Reply