Page 1 of 1
How use TMSQuery with STP and In/Out-Parameters
Posted: Fri 07 Jul 2017 12:41
by 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.
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Mon 10 Jul 2017 10:33
by 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
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Mon 10 Jul 2017 13:18
by asc
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.
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Tue 11 Jul 2017 07:38
by 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.
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Fri 14 Jul 2017 14:32
by asc
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...
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Mon 17 Jul 2017 10:35
by 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
Re: How use TMSQuery with STP and In/Out-Parameters
Posted: Mon 17 Jul 2017 11:18
by 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";