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).
>
>
