Devart Forums

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

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.

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.

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?

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

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

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

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