How use TMSQuery with STP and In/Out-Parameters

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
asc
Posts: 13
Joined: Wed 03 Feb 2010 10:32
Location: Germany, Siegen (NRW)

How use TMSQuery with STP and In/Out-Parameters

Post by asc » Fri 07 Jul 2017 12:41

It is the first time i use the TMSQuery with Insert/Update-Statement. I have an Save-STP (for Insert and Update) that modify a few parameters on success (Identity...). The call is successful, but how get i the modified values back to the DataSet?

Insert/Update-Statement:
{:RETURN_VALUE = CALL <Save-STP>;1 (:id, <...many fields...>, :createdAt, :createdFrom, :changedAt, :changedFrom)}

the last 4 fields are allways output, the id are created on the Insert-Call.

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

Re: How use TMSQuery with STP and In/Out-Parameters

Post by azyk » Mon 10 Jul 2017 10:33

To assign the out-parameters values of a stored procedure to the dataset fields, you can use the AfterUpdateExecute event handler. For example:

Code: Select all

procedure TForm1.MSQuery1AfterUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
var id:integer;
begin
    if stInsert in StatementTypes then
    begin
        // get Identity
        id := Integer(Params.ParamByName('RETURN_VALUE').Value);
        Sender.FieldByName('id').ReadOnly := False;
        try
          Sender.FieldByName('id').NewValue := vid;
        finally
          Sender.FieldByName('id').ReadOnly := True;
        end;

        // get out-param value
        Sender.FieldByName('field1').NewValue := Params.ParamByName('createdAt').Value;
        Sender.FieldByName('field2').NewValue := Params.ParamByName('createdFrom').Value;
        ...
    end;
end;
More details about AfterUpdateExecute in our online documentation: https://www.devart.com/sdac/docs/?devar ... xecute.htm

asc
Posts: 13
Joined: Wed 03 Feb 2010 10:32
Location: Germany, Siegen (NRW)

Re: How use TMSQuery with STP and In/Out-Parameters

Post by asc » Mon 10 Jul 2017 13:18

azyk wrote:To assign the out-parameters values of a stored procedure to the dataset fields, you can use the AfterUpdateExecute event handler...
I had test AfterUpdateExecute before without any result, i have try it again and doesn't get any new value here (In SQL Server Management Studio i get the correct output-values).

Code: Select all

void __fastcall TdmZeitraster::
m_tblZeitraster_dsAfterUpdateExecute(
    TCustomMSDataSet * sender,
    TStatementTypes statementTypes,
    TMSParams * params)
{
    if(statementTypes.Contains(stInsert) || statementTypes.Contains(stUpdate)) {
        // Each "params->ParamByName(<ParamName>)->As..." [b]doesn't return any new value[/b] here
    }
}
Now i try any Option in the TMSQuery... i hope i find the problem.

My Workaround: First read the Id from "RETURN_VALUE" and then do a RefreshRecord.

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

Re: How use TMSQuery with STP and In/Out-Parameters

Post by azyk » Tue 11 Jul 2017 07:38

Please compose a small test project, which demonstrates how you used AfterUpdateExecute and send us using the contact form at our site: http://www.devart.com/company/contactform.html . In the project also include CREATE script of the stored procedure and the table that it uses.

asc
Posts: 13
Joined: Wed 03 Feb 2010 10:32
Location: Germany, Siegen (NRW)

Re: How use TMSQuery with STP and In/Out-Parameters

Post by asc » Fri 14 Jul 2017 14:32

azyk wrote:Please compose a small test project, which demonstrates how you used AfterUpdateExecute and send us using the contact form at our site: http://www.devart.com/company/contactform.html . In the project also include CREATE script of the stored procedure and the table that it uses.
The login doesn't work (last week was all okay - i have successfully download the newest version of sdac with the account); have you problems with your servers?

Now i need a solution (The workaround doesn't work if one WideMemoField exists - "EAssertionFailed...TCRBlobData.Free RefCount = 0..."), and i have a exampleproject with a little db but can't use the contactform...

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

Re: How use TMSQuery with STP and In/Out-Parameters

Post by azyk » Mon 17 Jul 2017 10:35

Last week you may have experienced authorization failures on the site caused by the issues with hosting. The issue was temporary and has now been fixed.

1. In the provided sample, it is necessary to additionally specify that the parameter with the 'id' name (from the TMSQuery.SQLInsert property) is the OUTPUT parameter. To do this, add the TMSQuery.BeforeUpdateExecute event handler and set the TDAParam.ParamType parameter property to the ptOutput value. For example:

Code: Select all

void __fastcall TForm1::MSQuery1BeforeUpdateExecute(TCustomMSDataSet *Sender, TStatementTypes StatementTypes,
		  TMSParams *Params)
{
	if(StatementTypes.Contains(stInsert) || StatementTypes.Contains(stUpdate)) {
		Params->ParamByName(L"id")->ParamType = ptOutput;
	}
}
2. To avoid error, use the solution from the above example

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

Re: How use TMSQuery with STP and In/Out-Parameters

Post by azyk » Mon 17 Jul 2017 11:18

Supplement to the point 1.

Another way to specify that the 'id' parameter is the OUTPUT parameter is to use the OUT keyword when calling a stored procedure:

Code: Select all

MSQuery1->SQLInsert->Text = "EXEC :RETURN_VALUE = STP_SaveMemotext :id out, :uid out, 
:text, :description";

Post Reply