How use TMSQuery with STP and In/Out-Parameters
How use TMSQuery with STP and In/Out-Parameters
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.
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.
Re: How use TMSQuery with STP and In/Out-Parameters
To assign the out-parameters values of a stored procedure to the dataset fields, you can use the AfterUpdateExecute event handler. For example:
More details about AfterUpdateExecute in our online documentation: https://www.devart.com/sdac/docs/?devar ... xecute.htm
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;
Re: How use TMSQuery with STP and In/Out-Parameters
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).azyk wrote:To assign the out-parameters values of a stored procedure to the dataset fields, you can use the AfterUpdateExecute event handler...
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
}
}
My Workaround: First read the Id from "RETURN_VALUE" and then do a RefreshRecord.
Re: How use TMSQuery with STP and In/Out-Parameters
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.
Re: How use TMSQuery with STP and In/Out-Parameters
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?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.
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...
Re: How use TMSQuery with STP and In/Out-Parameters
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:
2. To avoid error, use the solution from the above example
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;
}
}
Re: How use TMSQuery with STP and In/Out-Parameters
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:
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";