How use TMSQuery with STP and In/Out-Parameters

[asc]

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]

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]

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]

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]

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]

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]

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";