Subject Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
Author
Post date 2015-03-02T09:41:02Z
  Tim,

your question:

(iii) I can't find any documentation of when the connection on database
B get closed, and when and under what circumstances the transaction on
database B gets committed and when and under what circumstances it gets
rolled back

documentation http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans

answer:

WITH {AUTONOMOUS|COMMON} TRANSACTION

...

Any new transactions started under the “COMMON” regime are committed or rolled back with the current transaction.



Then you ask again:
(ii) Each time the statement run in (b) completes the transaction in
database B will be committed, unless there was an error in which case it
will be rolled back
...
isn't it rather expensive to reopen the connection to database B every time?

Of course reopen connection is more expensive then reuse existing connection ;) But...

...read the docs
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-on-external


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 the connect string is exactly the same, including case.

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

  • Notice that statements WITH AUTONOMOUS TRANSACTION can and will reuse 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 uncommitted transaction!)


So, please, read the documentation !

Regards,
Vlad


---In firebird-support@yahoogroups.com, <tdw@...> wrote :

Yes, I did, of course, you will see that not only do I quote the documentation but also that my questions are on points that the documentation doesn't answer, which is why I asked them here.

On 27/02/2015 18:57, hvlad@... [firebird-support] wrote:
 

Read the documentation first
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html

 

Regards,
Vlad


-- 
Tim Ward