Error "invalid BLOB ID" can occur when retrieving MON$STATEMENTS.MON$SQL_TEXT using ES/EDS and db_connect argument is not specified [CORE4747]

[firebird-automations]

Submitted by: @pavel-zotov

Test-1 (works correct):

set list on;
set blob all;

recreate table test(sid int, txt blob);
commit;

set term ^;
execute block returns( msg varchar(10), running_stt_id int, running_stt_text blob) as
declare v_dbname varchar(255);
declare v_stt1 varchar(1024) = 'select t.sid, t.txt from test t';
declare v_stt2 varchar(1024) = 'select s.mon$statement_id, s.mon$sql_text from mon$statements s rows 1';
declare v_usr rdb$user = 'sysdba';
declare v_pwd varchar(20) = 'masterke';
declare v_trn int;
begin

\-\- NOTE: v\_dbname is NOT initialized with database connection string\.

in autonomous transaction do
insert into test\(sid, txt\) select s\.mon$statement\_id, s\.mon$sql\_text from mon$statements s rows 1;

msg = 'point\-1';
execute statement \(v\_stt1\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_stt\_text;
suspend;

msg = 'point\-2';
execute statement \(v\_stt2\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_stt\_text ;
suspend;

end
^
set term ;^
rollback;

Output:

MSG point-1
RUNNING_STT_ID 108
RUNNING_STT_TEXT b0:0
<execute block>

MSG point-2
RUNNING_STT_ID 227
RUNNING_STT_TEXT 0:1
<execute block>

Test-2:

set list on;
set blob all;

recreate table test(sid int, txt blob);
commit;
insert into test(sid, txt) select s.mon$statement_id, s.mon$sql_text from mon$statements s rows 1;
commit;

set term ^;
execute block returns( msg varchar(10), running_stt_id int, running_stt_text blob) as
declare v_dbname varchar(255);
declare v_stt1 varchar(1024) = 'select t.sid, t.txt from test t';
declare v_stt2 varchar(1024) = 'select s.mon$statement_id, s.mon$sql_text from mon$statements s rows 1';
declare v_usr rdb$user = 'sysdba';
declare v_pwd varchar(20) = 'masterke';
declare v_trn int;
begin
-- NOTE: v_dbname is NOT initialized with database connection string.

msg = 'point\-1';
execute statement \(v\_stt1\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_stt\_text;
suspend;

msg = 'point\-2';
execute statement \(v\_stt2\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_stt\_text ;
suspend;

end
^
set term ;^
rollback;

Output:

MSG point-1
RUNNING_STT_ID 105
RUNNING_STT_TEXT b1:0
insert into test(sid, txt) select s.mon$statement_id, s.mon$sql_text from mon$statements s rows 1

MSG point-2
RUNNING_STT_ID 227
RUNNING_STT_TEXT 0:1
Statement failed, SQLSTATE = 42000
invalid BLOB ID

No such error in the test-2 if change there MON$DATABASE.MON$SQL_TEXT to RDB$DATABASE.RDB$DESCRIPTION (and create some non-empty comment for database, of course).

---------
PS.
WI-V2.5.4.26856
WI-T3.0.0.31780

Commits: 4307b08 8edeb97 FirebirdSQL/fbt-repository@7a164ab FirebirdSQL/fbt-repository@07cdffe

[firebird-automations]

Modified by: @dyemanov

Version: 2.5.4 [ 10585 ]

Version: 3.0 Beta 1 [ 10332 ]

[firebird-automations]

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

[firebird-automations]

Commented by: @hvlad

Patch for v3 is committed, please confirm

[firebird-automations]

Commented by: @pavel-zotov

As of LI-T3.0.0.31789 (rev. 61328) it seems to me that all works fine.

Slightly modified test-2 from starting message:

set list on;
set blob all;

recreate table test(sid int, tid int, txt blob);
commit;
insert into test(sid, tid, txt) select s.mon$statement_id, s.mon$transaction_id, s.mon$sql_text from mon$statements s rows 1;
commit;

set term ^;
execute block returns( msg varchar(10), running_stt_id int, running_trn_id int, running_stt_text blob) as
declare v_dbname varchar(255);
declare v_stt1 varchar(1024) = 'select t.sid, t.tid, t.txt from test t';
declare v_stt2 varchar(1024) = 'select s.mon$statement_id, s.mon$transaction_id, s.mon$sql_text from mon$statements s where s.mon$transaction_id = current_transaction rows 1';
declare v_usr rdb$user = 'sysdba';
declare v_pwd varchar(20) = 'masterke';
declare v_trn int;
begin
-- NOTE: v_dbname is NOT initialized with database connection string.

msg = 'point\-1';
execute statement \(v\_stt1\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_trn\_id, running\_stt\_text;
suspend;

msg = 'point\-2';
execute statement \(v\_stt2\)
on external \(v\_dbname\)
as user :v\_usr password :v\_pwd
into running\_stt\_id, running\_trn\_id, running\_stt\_text ;
suspend;

end
^
set term ;^
rollback;

Output:

MSG point-1
RUNNING_STT_ID 111
RUNNING_TRN_ID 219
RUNNING_STT_TEXT 91:0
insert into test(sid, tid, txt) select s.mon$statement_id, s.mon$transaction_id, s.mon$sql_text from mon$statements s rows 1

MSG point-2
RUNNING_STT_ID 140
RUNNING_TRN_ID 224
RUNNING_STT_TEXT 0:1
select s.mon$statement_id, s.mon$transaction_id, s.mon$sql_text from mon$statements s where s.mon$transaction_id = current_transaction rows 1

[firebird-automations]

Commented by: @hvlad

Please, verify v2.5 too

[firebird-automations]

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 2.5.5 [ 10670 ]

[firebird-automations]

Commented by: @pavel-zotov

Checked on WI-V2.5.5.26860 - works fine.

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Done successfully