Issue with "ALTER SESSION RESET" and implicit cursor close

[dmitry-lipetsk]

Firebird 4.0.3.2948.

Here are two scenarios of using "ALTER SESSION RESET" within transaction with one active cursor.

In both cases fbclient.dll was used.

1. Work with server through TCP/IP (INET)

Here "alter session reset" closes active cursor (stmt00000002).

Client gets the error "invalid request handle" when he tries to fetch from stmt00000002.

Client closes and opens this statement stmt00000002 and gets the error "Attempt to reopen an open cursor"

If these operations execute through IBProvider (OLE DB provider) builtin client for Firebird, before "Attempt to reopen an open cursor" will be an error about cursor close (operation 000012).

ISC_API_IN [#000001]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000000)
ISC_API_OUT[#000001]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000001) return OK.
ISC_API_IN [#000002]: isc_dsql_prepare_m(...,tr_handle=&tr00000000,stmt_handle=&stmt00000001,...)
[stmt_text len:0]
set transaction
[/stmt_text]
ISC_API_OUT[#000002]: isc_dsql_prepare_m(...,tr_handle=&tr00000000,stmt_handle=&stmt00000001,...) return OK.
ISC_API_IN [#000003]: isc_dsql_execute2_m(...,tr_handle=&tr00000000,stmt_handle=&stmt00000001,...)
ISC_API_OUT[#000003]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000001,...) return OK.
ISC_API_IN [#000004]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000000)
ISC_API_OUT[#000004]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000002) return OK.
ISC_API_IN [#000005]: isc_dsql_prepare_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...)
[stmt_text len:0]
select * from rdb$fields
[/stmt_text]
ISC_API_OUT[#000005]: isc_dsql_prepare_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...) return OK.
ISC_API_IN [#000006]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...)
ISC_API_OUT[#000006]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...) return OK.
ISC_API_IN [#000007]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000000)
ISC_API_OUT[#000007]: isc_dsql_allocate_statement(...,db_handle=&db00000001,stmt_handle=&stmt00000003) return OK.
ISC_API_IN [#000008]: isc_dsql_prepare_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000003,...)
[stmt_text len:0]
alter session reset
[/stmt_text]
ISC_API_OUT[#000008]: isc_dsql_prepare_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000003,...) return OK.
ISC_API_IN [#000009]: isc_dsql_free_statement(...,stmt_handle=&stmt00000001,2)
ISC_API_OUT[#000009]: isc_dsql_free_statement(...,stmt_handle=&stmt00000000,2) return OK.
ISC_API_IN [#000010]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000003,...)
ISC_API_OUT[#000010]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000003,...) return OK.
ISC_API_IN [#000011]: isc_dsql_fetch_m(...,stmt_handle=&stmt00000002,...)
ISC_API_OUT[#000011]: isc_dsql_fetch_m(...,stmt_handle=&stmt00000002,...) return ERROR [335544327]
invalid request handle
ISC_API_IN [#000012]: isc_dsql_free_statement(...,stmt_handle=&stmt00000002,1)
ISC_API_OUT[#000012]: isc_dsql_free_statement(...,stmt_handle=&stmt00000002,1) return OK.
ISC_API_IN [#000013]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...)
ISC_API_OUT[#000013]: isc_dsql_execute2_m(...,tr_handle=&tr00000001,stmt_handle=&stmt00000002,...) return OK.
ISC_API_IN [#000014]: isc_dsql_fetch_m(...,stmt_handle=&stmt00000002,...)
ISC_API_OUT[#000014]: isc_dsql_fetch_m(...,stmt_handle=&stmt00000002,...) return ERROR [335544436]
SQL error code = -502
Attempt to reopen an open cursor
ISC_API_IN [#000015]: isc_dsql_free_statement(...,stmt_handle=&stmt00000003,2)
ISC_API_OUT[#000015]: isc_dsql_free_statement(...,stmt_handle=&stmt00000000,2) return OK.

2. Work with embedded server

"alter session reset" closes stmt0000000000000002.

[#000012, #000013] - it is an attemt to close this cursor with "invalid request handle" error.

[#000014] - it is an attempt to drop this statement with "invalid request handle" error.

ISC_API_IN [#000001]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000000)
ISC_API_OUT[#000001]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000001) return OK.
ISC_API_IN [#000002]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000000,stmt_handle=&stmt0000000000000001,...)
[stmt_text len:0]
set transaction
[/stmt_text]
ISC_API_OUT[#000002]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000000,stmt_handle=&stmt0000000000000001,...) return OK.
ISC_API_IN [#000003]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000000,stmt_handle=&stmt0000000000000001,...)
ISC_API_OUT[#000003]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000001,...) return OK.
ISC_API_IN [#000004]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000000)
ISC_API_OUT[#000004]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000002) return OK.
ISC_API_IN [#000005]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000002,...)
[stmt_text len:0]
select * from rdb$fields
[/stmt_text]
ISC_API_OUT[#000005]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000002,...) return OK.
ISC_API_IN [#000006]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000002,...)
ISC_API_OUT[#000006]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000002,...) return OK.
ISC_API_IN [#000007]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000000)
ISC_API_OUT[#000007]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000003) return OK.
ISC_API_IN [#000008]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000003,...)
[stmt_text len:0]
alter session reset
[/stmt_text]
ISC_API_OUT[#000008]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000003,...) return OK.
ISC_API_IN [#000009]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000001,2)
ISC_API_OUT[#000009]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000000,2) return OK.
ISC_API_IN [#000010]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000003,...)
ISC_API_OUT[#000010]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000003,...) return OK.
ISC_API_IN [#000011]: isc_dsql_fetch_m(...,stmt_handle=&stmt0000000000000002,...)
ISC_API_OUT[#000011]: isc_dsql_fetch_m(...,stmt_handle=&stmt0000000000000002,...) return ERROR [335544327]
invalid request handle
ISC_API_IN [#000012]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,1)
ISC_API_OUT[#000012]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,1) return ERROR [335544327]
invalid request handle
ISC_API_IN [#000013]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,1)
ISC_API_OUT[#000013]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,1) return ERROR [335544327]
invalid request handle
ISC_API_IN [#000014]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,2)
ISC_API_OUT[#000014]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000002,2) return ERROR [335544327]
invalid request handle
ISC_API_IN [#000015]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000000)
ISC_API_OUT[#000015]: isc_dsql_allocate_statement(...,db_handle=&db0000000000000002,stmt_handle=&stmt0000000000000004) return OK.
ISC_API_IN [#000016]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000004,...)
[stmt_text len:0]
select * from rdb$fields
[/stmt_text]
ISC_API_OUT[#000016]: isc_dsql_prepare_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000004,...) return OK.
ISC_API_IN [#000017]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000004,...)
ISC_API_OUT[#000017]: isc_dsql_execute2_m(...,tr_handle=&tr0000000000000001,stmt_handle=&stmt0000000000000004,...) return OK.
ISC_API_IN [#000018]: isc_dsql_fetch_m(...,stmt_handle=&stmt0000000000000004,...)
ISC_API_OUT[#000018]: isc_dsql_fetch_m(...,stmt_handle=&stmt0000000000000004,...) return OK.
ISC_API_IN [#000019]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000003,2)
ISC_API_OUT[#000019]: isc_dsql_free_statement(...,stmt_handle=&stmt0000000000000000,2) return OK.

[dmitry-lipetsk]

Another example for the current issue.

execute block
returns (c integer)
as
begin
 c=1;suspend;
 alter session reset;
 c=2;suspend;
end;