Firebird 6.0. Requires adding SEARCH_PATH parameter to EXECUTE STATEMENT statement.

[sim1984]

The EXECUTE STATEMENT statement currently has the following syntax:

<execute_statement> ::=
  EXECUTE STATEMENT <argument>
    [<option> ...]
    [INTO <variables>]

<argument> ::=
    <paramless_stmt>
  | (<paramless_stmt>)
  | (<stmt_with_params>) (<param_values>)

<param_values> ::= <named_values> | <positional_values>

<named_values> ::=
    [EXCESS] paramname := <value_expr>
    [, [EXCESS] paramname := <value_expr> ...]

<positional_values> ::= <value_expr> [, <value_expr> ...]

<option> ::=
    WITH {AUTONOMOUS | COMMON} TRANSACTION
  | WITH CALLER PRIVILEGES
  | AS USER user
  | PASSWORD password
  | ROLE role
  | ON EXTERNAL [DATA SOURCE] <connect_string>

It is necessary to expand the <option> list by adding the SEARCH_PATH option to it.

This is especially useful in conjunction with the ON EXTERNAL option.

If the remote connection does not support search paths, they can simply be ignored.

[aafemt]

This is what SET SEARCH_PATH exists for. Just execute it using exactly the same EXECUTE STATEMENT.

[mrotteveel]

This is what SET SEARCH_PATH exists for. Just execute it using exactly the same EXECUTE STATEMENT.

And how do you propose to execute that with "the same EXECUTE STATEMENT"? You can't, as you execute individual statements.

[aafemt]

Just add another EXECUTE STATEMENT one line up from this one. Connection pooling will do the rest.

[mrotteveel]

Not if you'd use WITH AUTONOMOUS TRANSACTION.

[sim1984]

Just add another EXECUTE STATEMENT one line up from this one. Connection pooling will do the rest.

Even when it works, it's inconvenient. Let's make it convenient for users to work with.

[aafemt]

Then connection pooling must take into account not only connection string, user and role but search path as well. Otherwise surprises are ensured.

[aafemt]

Not if you'd use WITH AUTONOMOUS TRANSACTION.

AFAIU SET SEARCH_PATH is attachment-wide, not transaction wide so it should not be affected by transactions. Or do you refer some pooling nuances which I'm not aware of?..

[mrotteveel]

As far as I know, the connection will be returned to the pool in this case, which means ALTER SESSION RESET will be called, and there is also no guarantee that the next one will be the same connection.