How can I assign a declare-value interactively?

Question

I can run the following SQL:

--Pseudo Full text search
--derived from https://stackoverflow.com/questions/75172712/how-to-find-a-value-in-all-tables-on-firebird
--TODO: The searched string (and condition) should be queried on execution, not be hardcoded.

execute block
returns (
    table_name varchar(40),
    table_desc varchar(63),
    column_name varchar(40),
    column_desc varchar(63),
    drilldown_SQL varchar(255)
    )
as
declare search_value varchar(30) = 'search_string';
--declare search_condition varchar(30) = '=';
--declare search_condition varchar(30) ='like';
declare search_condition varchar(30) ='containing';
declare has_result boolean;
begin
    for select 
              trim(r.rdb$relation_name)
              , substring(r.rdb$description from 1 for 63)
              , trim(f.rdb$field_name)
              , substring(f.rdb$description from 1 for 63)
              , 'select '||trim(f.rdb$field_name)|| ', '||trim(r.rdb$relation_name)||
                '.* from ' || trim(r.rdb$relation_name) || 
                ' where ' || trim(f.rdb$field_name) || ' ' || :search_condition || ' ''' || :search_value || ''''
        from rdb$relation_fields f
        join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
        and r.rdb$view_blr is null
        and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
        order by r.rdb$relation_name, f.rdb$field_position
        into :table_name, :table_desc, :column_name, :column_desc, :drilldown_SQL
    do
    begin
        execute statement ('select exists(select * from "' || table_name || '" where "' || column_name || '" ' || search_condition || ' ?) from rdb$database') (search_value)
            into has_result;
        if (has_result) then
            suspend;
        when any do
        begin
            /* value not comparable with varchar, skip */
        end
    end
end

However, I can't set the declare of search_value interactively.

This standalone example works: select 'abc' as row1 from RDB$DATABASE where 1 like :string I get a prompt for string.

Trying the same in the SQL above fails (-104 Token unknown :):

declare search_value varchar(30) = :searchstring;

I guess it has to do with the environment that I am in, and/or with the restrictions that apply to my user.

It's a dialogue in an ERP software, where I can run SQL on the DB. Only read access is given here, so only select's (and obviously everything in the above, like execute block) are allowed.

Also, for example, I can't do two select's in a row, the following throws an error (unknown token select on line 2):

select * from table1;
select * from table2;

Answer 1

You can't run declare on its own, and you cannot just parametrize PSQL code randomly. The proper way to do this, is to add input parameters to the EXECUTE BLOCK:

execute block (search_value varchar(30) = ?)
returns (
    table_name varchar(40),
    table_desc varchar(63),
    column_name varchar(40),
    column_desc varchar(63),
    drilldown_SQL varchar(255)
    )
as
-- (Now as parameter) declare search_value varchar(30) = 'search_string';
--declare search_condition varchar(30) = '=';
--declare search_condition varchar(30) ='like';
declare search_condition varchar(30) ='containing';
declare has_result boolean;
begin
  ...
end

It is possible that you can use a named parameter (Firebird itself doesn't actually have named parameters in this context, but some access components provide transparent mapping to positional parameters):

execute block (search_value varchar(30) = :searchstring)
...