Problem with INSERT (...) RETURNING (...) and using dbExpress with Delphi [CORE5592]

[firebird-automations]

Submitted by: Luiz Fernando Trevisan (luizztrevisan)

Attachments:
Insert_Returning.zip

using dbExpress components (TSQLConnection and TSQLDataSet)

running this command:
insert into CAD_CULTURAS (EMPRESA,
CODIGO,
NOME)
values (1,
null,
'soja')
returning CODIGO

The following error message is generated
Cursor is not open.

In Firebird 2.5.7 the code works perfectly

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Luiz Fernando,

Where is no error message.

[firebird-automations]

Commented by: Luiz Fernando Trevisan (luizztrevisan)

this is source code

In Firebird 2.5.7 the code works perfectly

I am doing tests for migration to Firebird 3.0, and this command is no longer working

[firebird-automations]

Modified by: Luiz Fernando Trevisan (luizztrevisan)

Attachment: Insert_Returning.zip [ 13171 ]

[firebird-automations]

Commented by: @ibaseru

this is dbExpress problem, not Firebird.
dbExpress does not understand that insert/update/delete may return anything more than result of operation. So, you have 2 possibilities
1. do not use insert ... returning while working with dbExpress
2. do not use dbExpress at all, use another components that understands insert... returning.

[firebird-automations]

Commented by: Luiz Fernando Trevisan (luizztrevisan)

ok, I got it

Some time ago, in Firebird 2.5, there was a same request for this "problem" where it was solved in build 2.5.1

Follow the link:
CORE3203

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Commented by: @asfernandes

It's looking like a regression, no? Why won't fix?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I was acting based on @kdv's comments.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Commented by: @asfernandes

Luiz Fernando, please check if the problems happens when using Firebird embedded.

[firebird-automations]

Commented by: Luiz Fernando Trevisan (luizztrevisan)

Adriano dos Santos, com o Firebird embedded acontece a mesma coisa.

entrei em contato com os desenvolvedores da DevExpress, eles sugeriram fazer a execução dos comandos com insert ... returning de uma outra forma.
dessa nova forma o valor é retornado sem problemas.

fiz a abertura do chamado pois, utilizando a fbclient 2.5 e o firebird 2.5, ao executar o comando ".Open" o resultado é retornado sem problemas,
mas, se eu utilizar somente a fbclient 3.0, com o firebird 2.5, recebo a mesma mensagem "Cursor is not open" ou "Cursor not returned from query"

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Luiz Fernando,

Please post comments in English.

[firebird-automations]

Commented by: Alison Dennis (alisondv)

Hi,

I'm using Firebird 3.0.4 with DBExpress and the same error occurs: "SQL Server Error: Cursor is not open."

Insert Into TBCTENFELOG
(
C2, C3
)
Values
(
'007', 152223
) Returning C1

The Sql above works fine with FB 2.1.

I'm working on the migration to Firebird 3.0.4 and I like to know if this "bug" will be fixed on a future version?