firebird procedure insert with return values not commit

[gss200610]

I have the following situation:
1 - a procedure in which I insert and returns me a sequential number.
2 - uniconnection settings are to autocommit = true, I'm not using
UNITRANSACTION no object for explicit transactions.
3 - Using latest version of unidac.
the procedure is as follows:

create or alter procedure SP_INSERT_ORCAMENTO (
DATA date,
CODCLI integer,
CODTPV integer,
CODEMP integer,
CODVEND integer,
CAIXA integer,
NOMECLIENTE varchar(40),
CNPJ varchar(14),
ENDERECO varchar(40),
BAIRRO varchar(40),
CIDADE varchar(40),
ESTADO varchar(2),
TELEFONE varchar(10))
returns (
CODIGO integer,
SEQUENCIA bigint)
as
BEGIN
CODIGO = GEN_ID(GNT_COD_ORCAMENTO, 1);
SEQUENCIA = GEN_ID(GNT_SEQUENCIA_DAV, 1);
IF (SEQUENCIA > 9999999999) THEN
BEGIN
EXECUTE STATEMENT 'SET GENERATOR GNT_SEQUENCIA_DAV TO 0';
SEQUENCIA = GEN_ID(GNT_SEQUENCIA_DAV, 1);
END

if (:codcli IS NULL) then
BEGIN
INSERT INTO ORCAMENTO (CODIGO, DATA, COD_TPV, COD_VEND, TOTAL, SEQUENCIA_DAV,
COD_EMP, STATUS, COD_CAI, NOME_CLIENTE, CPF_CNPJ, STATUS_EXPORTA,
ENDERECO, BAIRRO, CIDADE, ESTADO, FONE )
VALUES(:CODIGO, :DATA, :CODTPV, :CODVEND, 0, :SEQUENCIA,
:CODEMP, 0, :CAIXA, :NOMECLIENTE, :CNPJ, 0, :ENDERECO, :BAIRRO,
:CIDADE, :ESTADO, :TELEFONE );

END
ELSE
BEGIN

INSERT INTO ORCAMENTO (CODIGO, DATA, COD_CLI, COD_TPV, COD_VEND, TOTAL, SEQUENCIA_DAV,
COD_EMP, STATUS, COD_CAI, NOME_CLIENTE, CPF_CNPJ, STATUS_EXPORTA,
ENDERECO, BAIRRO, CIDADE, ESTADO, FONE )
VALUES(:CODIGO, :DATA, :CODCLI, :CODTPV, :CODVEND, 0, :SEQUENCIA,
:CODEMP, 0, :CAIXA, :NOMECLIENTE, :CNPJ, 0, :ENDERECO, :BAIRRO,
:CIDADE, :ESTADO, :TELEFONE );
END
SUSPEND;
END;


4 - uniQuery use with select statement as it is a procedure return, something like:

uniquery.close;
uniquery.parambyname......
uniquery.prepare;
uniquery.open;


edtCodigo.Text:. = uniquery.fieldbyname ('CODIGO') AsInteger (. I have set the value of the code field and if is not in this bank recorded);
   this table is master, there is throw any detail record and is in the bank already listed there. or if you uniconnection.commitretaining (recorded in the bd).
  
   I did tests with firedac and this does not happen (I migrated from firedac to unidac).
   I wonder what I can do for this autocommit = true commit it to the bank after making the query open, as is the insertion procedure
   and not use cached updates

[ViktorV]

Thank you for the information. We will investigate the behavior of UniDAC when working with Firebird and inform you about the results.
As a workaround, you can call the CommitRetaining method after calling the Open method.

[ViktorV]

The AutoCommit property in UniDAC is not applicable to SELECT statements. To execute stored procedures, that return parameters, in UniDAC, you should use the EXECUTE PROCEDURE <procedure> expression instead of SELECT * FROM <procedure>, and call the TUniQuery.Execute method instead of TUniQuery.Open. In such case, to retrieve the value of an OUT parameter, you should use the TUniQuery.ParamByName method instead of TUniQuery.FieldByName.

[gss200610]

I did the tests as speech, and after running adoquery.execute not return the out parameter defined in the procedure returns.
I tried to get something like:. uniquery.parambyname ('out').asstring after the execute method

NOT WORK!

the recercod is commited and not returned value to edit1.text := uniquery.parambyname ('out').asstring

any alternative solution?

[ViktorV]

To solve the issue, please try to set the the DescribeParams option to True:

Code: Select all

  UniQuery.SpecificOptions.Values['DescribeParams'] := 'True';

[gss200610]

So, in this case I can use TUNISTOREDPROC instead of UNIQUERY, since it tests without separating parameters and commits, is it reliable?

[ViktorV]

Yes, you can use TUniStoredProc without any changes to solve the issue. Also, you can use TUniQuery, but by making changes we specified above.

[ViktorV]

Yes, you can use TUniStoredProc without any changes to solve the issue. Also, you can use TUniQuery, but by making changes we specified above.