Error in exception handling with CREATE USER statement [CORE2346]

[firebird-automations]

Submitted by: Andre Berten (berten)

EXECUTE BLOCK AS BEGIN EXECUTE STATEMENT 'CREATE USER TEST PASSWORD ''test'''; WHEN ANY DO
BEGIN
END END

returns: SQL Message : -901 Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements

Engine Code : 336723987 Engine Message : add record error

if executed a second time (user TEST exists). I think the error should be ignored with the WHEN ANY statement and build 22686 (Alpha 1) did what i expect.

[firebird-automations]

Modified by: @dyemanov

assignee: Alexander Peshkov \[ alexpeshkoff \]

[firebird-automations]

Commented by: @AlexPeshkoff

There were some known (and already fixed) bugs in user management in Alpha1. Currently all real job (like in many other DDL) is performed at commit time, and the fact that errors from DDL from EXECUTE STATEMENT are ignored is not user management specific. This is what we will get with beta1 (I use manual commit to make it better visible, DatabaseAccess is restricted in firebird.conf):

⁠ ./isql employee

Database: employee SQL> set term ^; SQL> EXECUTE BLOCK AS BEGIN CON> EXECUTE STATEMENT 'create shadow 1 ''/tmp/qq'''; CON> WHEN ANY DO BEGIN END END^ -- no error SQL> commit^ Statement failed, SQLSTATE = 28000 Access to additional database file "/tmp/qq" is denied by server administrator -- error when doing commit SQL> rollback^

But there is a way to handle errors - using new Autonomous Transactions:

SQL> EXECUTE BLOCK AS BEGIN CON> in autonomous transaction do CON> EXECUTE STATEMENT 'CREATE USER TEST PASSWORD ''test'''; CON> WHEN ANY DO BEGIN END END^ SQL> commit^ -- no errors displayed

Without error handler error is certainly visible:

SQL> EXECUTE BLOCK AS BEGIN CON> in autonomous transaction do CON> EXECUTE STATEMENT 'CREATE USER TEST PASSWORD ''test'''; CON> END^ Statement failed, SQLSTATE = HY000 add record error SQL> commit^ -- pay attention - error displayed not in commit, but in EXECUTE BLOCK

Be careful - using autonomous transaction you will not be able to rollback user creation (like any other statement).

[firebird-automations]

Modified by: @AlexPeshkoff

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \]

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]