Devart Forums

Good evening support. I'm trying to use components of access to the DBMS FireBird By UniDac . And I have 2 problems that can not find any forum or in help files.

Baselines:
1. The table on the server D_MED_REASON
2. 3 procedures on the server implementing the operation changes the data without the use of transactions on the client level (AUTOCOMMIT = true)

Med_add_D_MED_REASON
Med_Edit_D_MED_REASON
Med_Del_D_MED_REASON

Client Delphi Code

QuD_MED_REASON: = TUniQuery.Create (Self);
QuD_MED_REASON.Options.RequiredFields: = False;
QuD_MED_REASON.Options.StrictUpdate: = False;
QuD_MED_REASON.Options.ReturnParams: = True;
QuD_MED_REASON.Connection: = DB;

QuD_MED_REASON.SQL.Clear;
QuD_MED_REASON.SQL.Add ('SELECT');
QuD_MED_REASON.SQL.Add ('a.ID,');
QuD_MED_REASON.SQL.Add ('a.NAME,');
QuD_MED_REASON.SQL.Add ('a.IS_DELETE');
QuD_MED_REASON.SQL.Add ('FROM D_MED_REASON a');

QuD_MED_REASON.Name: = 'QuD_MED_REASON';
QuD_MED_REASON.SQLInsert.Clear;
QuD_MED_REASON.SQLInsert.Add ('execute block (');
QuD_MED_REASON.SQLInsert.Add ('p_DEFINE_ID type of column D_MED_REASON.ID =: ID,');
QuD_MED_REASON.SQLInsert.Add ('p_NAME type of column D_MED_REASON.NAME =: NAME)');
QuD_MED_REASON.SQLInsert.Add ('returns (ID type of column D_MED_REASON.ID) as');
QuD_MED_REASON.SQLInsert.Add ('begin');
QuD_MED_REASON.SQLInsert.Add ('execute procedure Med_add_D_MED_REASON (');
QuD_MED_REASON.SQLInsert.Add ('p_DEFINE_ID,');
QuD_MED_REASON.SQLInsert.Add ('p_NAME');
QuD_MED_REASON.SQLInsert.Add (') returning_values ID;');
QuD_MED_REASON.SQLInsert.Add ('suspend;');
QuD_MED_REASON.SQLInsert.Add ('end;');

QuD_MED_REASON.SQLDelete.Clear;
QuD_MED_REASON.SQLDelete.Add ('execute procedure Med_del_D_MED_REASON (: ID)');

QuD_MED_REASON.SQLUpdate.Clear;
QuD_MED_REASON.SQLUpdate.Add ('execute procedure Med_edit_D_MED_REASON (: ID,: NAME,: IS_DELETE);');

I have 2 problems

1. Post DataSet after the insert (AutoCommit By default = true) does not occur Commit transaction script QuD_MED_REASON.SQLInsert (I see this the second session running to the database), although the SQLUpdate work correctly and fix transaction by update record. Perhaps the difference is that the Insert block contains "execute block". How do AutoCommit to occur in the case of Insert?

2. In the Server-specific features described
# Support for the Firebird 2 EXECUTE BLOCK syntax
# Support for the Firebird 2 RETURNING clause
Is it possible to return the output parameters in the field
QuD_MED_REASON.SQL? If yes, how? Judging by the example of an experienced value of returns (ID type of column D_MED_REASON.ID) does not return to the field ID

Thanks in advance. If this information is described in any part of the documentation, please indicate this source

This behaviour occurs because for this SQLInsert query InterBase server returns a result set for returning values. In this case IBDAC doesn't commit transaction.
To solve the problem don't use the EXECUTE BLOCK statement to execute a stored procedure. In this case IBDAC creates parameters for returning values and sets these values to the corresponding fields on its own.

WIth commit problem is solved, thank you. But transmission OUT parameters in the DataSet does not work. Can you give a small example? This procedure on FireBird Side

create or alter procedure MED_ADD_D_MED_REASON (
P_DEFINE_ID type of column D_MED_REASON.ID,
- Ask a primary key
P_NAME type of column D_MED_REASON.NAME
- Name
)
returns (
ID type of column D_MED_REASON.ID - Primary key
)
as
begin
execute procedure NEWID
returning_values: ID;
insert into D_MED_REASON (ID, NAME, IS_DELETE)
values (: ID,: P_NAME, 'N');
SUSPEND;
end;

call this procedure through the script with (OUT parameters) with no return DataSet as the only possible (in SQLInsert) as

execute procedure MED_ADD_D_MED_REASON (1,: P_NAME);

keyword "returning_values" in this case is not allowed. So, the return parameter :ID in the body of the script can not be write.
If you look at DebugMode the script, then the OUT parameter ID is not defined.

And Delphi Code
//---------\/---------------------
procedure TMedDbDsUniDm.QuD_MED_REASONBeforeUpdateExecute (Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
inherited;
if stInsert in StatementTypes then
begin
Params.ParamByName ('ID'). ParamType: = ptOutput;
end;
end;
//---------/\---------------------
Also does not help and gives me a message "parameter not found".

What can I to do? Can you write me an example which call procedure MED_ADD_D_MED_REASON by SQLInsert and would return the OUT parameter in the field of the original query SQLInsert?

To solve the problem you should handle the BeforeUpdateExecute event and add a new out parameter, like this:

Hello Dmitry. Unfortunately your example did not give a positive result. The problem was not in fact that the resulting field does not get the value, but the fact that the output parameter does not get value, and is always null even if a create parameter with the given name.
Maybe you can help with information that the output parameters in FireBird is record returned DataSet in fields which contain value of OUT parameters.
Experimentally, I was able to get a positive result if the solution to the problem in the class TCustomIBCDataSetUpdater (IBCServicesUni) override RetunParamsAsFields

function TCustomIBCDataSetUpdater.RetunParamsAsFields: boolean;
begin
Result: = True;
end;

and SQLInsert rewrite
select * from MED_ADD_D_MED_REASON (1,: P_NAME)

In this case (similar PGServicesUni) the resulting parameters are returned in the original DataSet. However, if there is a problem because AutoCommit SQLInsert returns Recordset and commit not happening.
Perhaps there are more elegant solution without changing the source. Waiting for your help ...

What the problem is when you set SQLInsert to the SQL: and use the following code to handle the BeforeUpdateExecute event:

Maybe you did not understand me! Exactly what you advise, I did! The parameter is created, but it does not return a value from a procedure. To verify the existence of an error , try to return the OUT parameter to any procedure if it is a challenge to put in SQLInsert. Read please carefully my previous post.

The point is that I can not reproduce the problem. In my test application when out parameter is created then the ID field is updated in the dataset automatically.
Please, try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

Send to
[email protected]

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next UniDAC build.

Thank you, and you as well. Waiting for the next Build