Did you remember to also remove AS? As far as I can see from the syntax
description, AS should be used before USER, but not before ROLE, so try:
execute statement (lsql_update_cv_itens_sub1)
(:lvl_base, :lid_cv_itens_sub1)
role 'perfil_vendas';
HTH,
Set
2017-08-30 23:08 GMT+02:00 hamacker [email protected]
[firebird-support] <[email protected]>:
>
>
> 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 [email protected]
> [firebird-support] <[email protected]>:
>
>> 30.08.2017 21:15, hamacker [email protected] [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).
>>
>>
>
>
>
