16.04.2012 7:58, Piers Smith wrote:

>
> We are trying to migrate some existing users to the new RDB$ADMIN using
> some dynamic SQL. We have the following SQL
>
> for
> --Gets a list of users from our system
> select ib_username from user_group_users where user_group_no = 1
> into :v_username
> do
> begin
> --Give the user admin rights in our database
> v_grant_statement = 'GRANT RDB$ADMIN TO ' || :v_username || ' GRANTED BY
> SYSDBA ';
> execute statement v_grant_statement;
>
> --Give the user admin rights in the security database
> --The security database MUST be Dialect 3. This should be handled by the
> installer
> v_grant_statement = 'ALTER USER ' || :v_username || ' GRANT ADMIN ROLE ';
> execute statement v_grant_statement;
> end
>
> However if the value in :v_username does not exist in the Security2.fdb
> then we get an exception. We tried a "when any do" to add the user if
> there was an error;
>
> when any do
> begin
> --user is not in the security2.fdb, so add them
> declare variable v_grant_statement2 varchar(51);
> v_grant_statement2 = 'CREATE USER ' || :v_username || ' password ''' ||
> :v_username || ''' ';
> execute statement v_grant_statement2;
> end
>
> however this brings up syntax errors when run.

DDL commands are actually executed at the commit time, hence your error
handling does not work. You could try wrapping your loop body with an
autonomous transaction, i.e.:

for select ...
do begin
in autonomous transaction begin
...
end
when any do
...
end

This should allow you to process the exceptions properly.


Dmitry