FirebirdSQL
Firebird 6.0. Requires adding SEARCH_PATH parameter to EXECUTE STATEMENT statement.
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.
This is what SET SEARCH_PATH exists for. Just execute it using exactly the same EXECUTE STATEMENT.
This is what
SET SEARCH_PATHexists for. Just execute it using exactly the sameEXECUTE STATEMENT.
And how do you propose to execute that with "the same EXECUTE STATEMENT"? You can't, as you execute individual statements.
Just add another EXECUTE STATEMENT one line up from this one. Connection pooling will do the rest.
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.
Then connection pooling must take into account not only connection string, user and role but search path as well. Otherwise surprises are ensured.
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?..
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.
Since SET SEARCH_PATH TO uses a list of names (X, Y) and not a string ('X, Y'), how should this be represented in the EXECUTE STATEMENT command?
With list of names or string?
A list of names may make one to need to enclose an EXECUTE STATEMENT inside an EXECUTE STATEMENT to use dynamic names...
I see two options:
- a comma-separated list of identifiers; I'd prefer that to be honest
- a string; I don't think I'd like that option, but it could offer flexibility if a variable is allowed in its place
On the other hand, does EXECUTE STATEMENT really need this? You'd just need to construct queries with fully-qualified names.
As an aside, I don't see how enclosing EXECUTE STATEMENT in an EXECUTE STATEMENT could work (other than constructing a whole execute block), as it only accepts DSQL.
The problem with comma separated identifiers is that you can't verify these identifiers. The schemas in the remote DB may not be the same as the current DB. So I would prefer a simple string that can be a variable, like in the case of roles.
I understand that you can always write a fully qualified name, but I also think that if we can specify a parameter in a regular connection, we should be able to specify it in EXECUTE STATEMENT ON EXTERNAL. This gives additional convenience to users.
The problem with comma separated identifiers is that you can't verify these identifiers. The schemas in the remote DB may not be the same as the current DB. So I would prefer a simple string that can be a variable, like in the case of roles.
That is not a problem at all, it is just a list of identifiers (i.e.: names); identifiers don't need to validated at that point at all. Even SET SEARCH_PATH TO itself doesn't validate identifiers as schemas: you can specify non-existent schema names, and they will be used, and when searching, it will basically ignore non-existent schemas.
I understand that you can always write a fully qualified name, but I also think that if we can specify a parameter in a regular connection, we should be able to specify it in
EXECUTE STATEMENT ON EXTERNAL. This gives additional convenience to users.
That is not a good argument IMHO. If you want that, then you should ask for support to be able to use all DPB items in EXECUTE STATEMENT, using some key-value list extension, not ask for a single feature to be added to execute statement.
That is not a problem at all, it is just a list of identifiers (i.e.: names); identifiers don't need to validated at that point at all. Even
SET SEARCH_PATH TOitself doesn't validate identifiers as schemas: you can specify non-existent schema names, and they will be used, and when searching, it will basically ignore non-existent schemas.
Let's still maintain consistency in the EXECUTE STATEMENT syntax. We don't pass identifiers anywhere. All additional attributes are strings, which allows them to be calculated dynamically, or, for example, stored in some table.
That is not a good argument IMHO. If you want that, then you should ask for support to be able to use all DPB items in EXECUTE STATEMENT, using some key-value list extension, not ask for a single feature to be added to execute statement.
Not everything in DPB needs to be made EXECUTE STATEMENT ON EXTERNAL parameters, for example, it makes no sense for CHARSET, because the encodings must be consistent. But for those parameters that may differ significantly from the calling DB, it is necessary to do this, for example for SEARCH_PATH. I would also add the ability to enable/disable network compression.
That is not a problem at all, it is just a list of identifiers (i.e.: names); identifiers don't need to validated at that point at all. Even
SET SEARCH_PATH TOitself doesn't validate identifiers as schemas: you can specify non-existent schema names, and they will be used, and when searching, it will basically ignore non-existent schemas.Let's still maintain consistency in the
EXECUTE STATEMENTsyntax. We don't pass identifiers anywhere. All additional attributes are strings, which allows them to be calculated dynamically, or, for example, stored in some table.
Now, that is an argument I can accept.
That is not a good argument IMHO. If you want that, then you should ask for support to be able to use all DPB items in EXECUTE STATEMENT, using some key-value list extension, not ask for a single feature to be added to execute statement.
Not everything in DPB needs to be made
EXECUTE STATEMENT ON EXTERNALparameters, for example, it makes no sense for CHARSET, because the encodings must be consistent. But for those parameters that may differ significantly from the calling DB, it is necessary to do this, for example forSEARCH_PATH. I would also add the ability to enable/disable network compression.
That contradicts your earlier statement that "if we can specify a parameter in a regular connection, we should be able to specify it in EXECUTE STATEMENT ON EXTERNAL."
Besides, as far as I'm aware, your reasoning about CHARSET is not sound, because 1) I think the server would do conversion if needed (e.g. consider NONE connections to a database with varying column character sets, there it would need to happen as well) and 2) for some databases you will probably need to specify an explicit character set to avoid logical corruption due to (incorrect) use of NONE as a column character set.
List of identifiers sounds OK at the first glance, but its limitation is that it must be defined statically, i.e. the search path cannot be passed as a parameter to a routine invoking EXECUTE STATEMENT. Not sure how important is this in practice, but potentially looks like unnecessary PITA.
I think the server would do conversion if needed
As a side note: IMHO, ESonEDS must explicitly send expected types of parameters and fields instead of accepting them from remote side. It should avoid extra type coercion which may lead to data losses.
I think the server would do conversion if needed
As a side note: IMHO, ESonEDS must explicitly send expected types of parameters and fields instead of accepting them from remote side. It should avoid extra type coercion which may lead to data losses.
@aafemt, this is beyond the scope of this ticket.
this is beyond the scope of this ticket.
Yes. It just an argument why CHARSET parameter may be not needed for ES.