Something wrong in my test because 'sintax error' when I try role name without username/password.

Can you help me?

EXECUTE BLOCK

returns (result_value Integer)

AS

declare variable lid_cv bigint=3;

declare variable lcoditem varchar(30)='(C120P2AC)';

declare variable lvl_base NUMERIC(18,2)=            100.00;

declare variable lid_cv_item bigint;

declare variable lid_cv_itens_sub1 bigint;

declare variable lid_cv_itens_sub2 bigint;

declare variable lcvi_coditem varchar(30);

declare variable lsub1_coditem varchar(30);

declare variable lsub2_coditem varchar(30);

declare variable lsql_update_cv_itens varchar(4096);

declare variable lsql_update_cv_itens_sub1 varchar(4096);

declare variable lsql_update_cv_itens_sub2 varchar(4096);

begin

  lsql_update_cv_itens='update cv_itens set vl_base=? where (id_cv_item=?);';

  lsql_update_cv_itens_sub1=' update cv_itens_sub1 set vl_base=? where (id_cv_itens_sub1=?);';

  lsql_update_cv_itens_sub2=' update cv_itens_sub2 set vl_base=? where (id_cv_itens_sub2=?);';

  result_value=0;

  for select id_cv_item, coditem from cv_itens

  where id_cv=:lid_cv 

  into :lid_cv_item, :lcvi_coditem do

  begin

    if (:lcvi_coditem=:lcoditem) then 

    begin

      execute statement (lsql_update_cv_itens)

        (:lvl_base, :lid_cv_item)

        as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password

      result_value=(result_value+1);

    end

    for select id_cv_itens_sub1, coditem from cv_itens_sub1

    where id_cv_item=:lid_cv_item 

    into :lid_cv_itens_sub1, :lsub1_coditem  do

    begin

      if (:lsub1_coditem=:lcoditem) then 

      begin

        execute statement (lsql_update_cv_itens_sub1)

          (:lvl_base, :lid_cv_itens_sub1)

          as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password

        result_value=(result_value+1);

      end

      for select id_cv_itens_sub2, coditem from cv_itens_sub2

      where id_cv_itens_sub1=:lid_cv_ itens_sub1

      into :lid_cv_itens_sub2, :lsub2_coditem do

      begin

        if (:lsub2_coditem=:lcoditem) then 

        begin

          execute statement (lsql_update_cv_itens_sub2)

            (:lvl_base, :lid_cv_itens_sub2)

            as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password

          result_value=(result_value+1);

        end

      end

    end

  end

  suspend;

end

2017-08-30 16:33 GMT-03:00 Dimitry Sibiryakov sd@... [firebird-support] <firebird-support@yahoogroups. com>:

30.08.2017 21:15, hamacker sirhamacker@... [firebird-support] wrote:
> Here in fb3, to put role name in execute, Its mandatory put username and password too.

   I was wrong a little, but still you should read the documentation:

> - if ON EXTERNAL DATA SOURCE clause is omitted then
>       a) statement will be executed against current (local) database
>       b) if AS USER clause is omitted or <user_name> equal to CURRENT_USER
>       and if ROLE clause is omitted or <role_name> equal to CURRENT_ROLE
>       then the statement is executed in current connection context
>       c) if <user_name> is not equal to CURRENT_USER or <role_name> not equal to CURRENT_ROLE
>       then the statement is executed in separate connection established inside the same
>       engine instance (i.e. created new internal connection without Y-Valve and remote layers).