FirebirdSQL logo

ON EXTERNAL [DATA SOURCE]

With ON EXTERNAL [DATA SOURCE], the SQL statement is executed in a separate connection to the same or another database, possibly even on another server.If connection_string is NULL or “''” (empty string), the entire ON EXTERNAL [DATA SOURCE] clause is considered absent, and the statement is executed against the current database.

Connection Pooling
  • External connections made by statements WITH COMMON TRANSACTION (the default) will remain open until the current transaction ends.They can be reused by subsequent calls to EXECUTE STATEMENT, but only if connection_string is identical, including case

  • External connections made by statements WITH AUTONOMOUS TRANSACTION are closed as soon as the statement has been executed

  • Statements using WITH AUTONOMOUS TRANSACTION can and will re-use connections that were opened earlier by statements WITH COMMON TRANSACTION.If this happens, the reused connection will be left open after the statement has been executed.(It must be, because it has at least one active transaction!)

Transaction Pooling
  • If WITH COMMON TRANSACTION is in effect, transactions will be reused as much as possible.They will be committed or rolled back together with the current transaction

  • If WITH AUTONOMOUS TRANSACTION is specified, a fresh transaction will always be started for the statement.This transaction will be committed or rolled back immediately after the statement’s execution

Exception Handling

When ON EXTERNAL is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database.One of the consequences is that exceptions cannot be caught in the usual way.Every exception caused by the statement is wrapped in either an eds_connection or an eds_statement error.To catch them in your PSQL code, you have to use WHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement or WHEN ANY.

Note

Without ON EXTERNAL, exceptions are caught in the usual way, even if an extra connection is made to the current database.