Issue Details (XML | Word | Printable)

Key: CORE-4747
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 12/Apr/15 11:27 AM   Updated: 28/May/15 03:43 PM
Component/s: Engine
Affects Version/s: 3.0 Beta 1, 2.5.4
Fix Version/s: 3.0 Beta 2, 2.5.5

QA Status: Done successfully


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 16/Apr/15 02:35 AM
Patch for v3 is committed, please confirm

Pavel Zotov added a comment - 16/Apr/15 03:09 AM
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

Vlad Khorsun added a comment - 16/Apr/15 07:44 AM
Please, verify v2.5 too

Pavel Zotov added a comment - 17/Apr/15 05:39 PM
Checked on WI-V2.5.5.26860 - works fine.