Now I remove 'AS ' and sintaxe error message is gone, thanks.
But another problem, my role 'perfil_vendas' have all access to all objects
and 'perfil_vendas', but not for 'for select...' :(
when I try to execute my sample code, IBExpert says:
'no permition for SELECT access TABLE <TABLE_NAME> at block line....'
as expected.
So, to my sample run file, all block code need to be a unique execute
statement... and you know it´s a mess.
Better I think right and start over.
Thanks a lot.
Code sample:
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 -- no role for this select
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)
role 'perfil_orcamentos';
result_value=(result_value+1);
end
for select id_cv_itens_sub1, coditem from cv_itens_sub1 -- no role for
this select
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)
role 'perfil_orcamentos';
result_value=(result_value+1);
end
for select id_cv_itens_sub2, coditem from cv_itens_sub2 --- no role
for this select
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)
role 'perfil_vendas';
result_value=(result_value+1);
end
end
end
end
suspend;
end
2017-08-31 3:58 GMT-03:00 Svein Erling Tysvær [email protected]
[firebird-support] <[email protected]>:
>
>
> 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).
>>>
>>>
>>
>>
>
>
