firebird procedure insert with return values not commit
firebird procedure insert with return values not commit
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
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
Re: firebird procedure insert with return values not commit
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.
As a workaround, you can call the CommitRetaining method after calling the Open method.
Re: firebird procedure insert with return values not commit
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.
Re: firebird procedure insert with return values not commit
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?
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?
Re: firebird procedure insert with return values not commit
To solve the issue, please try to set the the DescribeParams option to True:
Code: Select all
UniQuery.SpecificOptions.Values['DescribeParams'] := 'True';
Re: firebird procedure insert with return values not commit
So, in this case I can use TUNISTOREDPROC instead of UNIQUERY, since it tests without separating parameters and commits, is it reliable?
Re: firebird procedure insert with return values not commit
Yes, you can use TUniStoredProc without any changes to solve the issue. Also, you can use TUniQuery, but by making changes we specified above.
Re: firebird procedure insert with return values not commit
Yes, you can use TUniStoredProc without any changes to solve the issue. Also, you can use TUniQuery, but by making changes we specified above.