Who is using what? TIBCConnection.DefaultTransaction, .ExecSQL and .SQL

[RSE]

Hello,

we just bought IBDAC for our new application. I'm just implementing it and wondered how these 3 mentioned objects relate to each other.

I configured the DefaultTransaction to be our standard read-transaction that stays open for as long as it's needed (as it's iblReadOnlyReadCommitted it shouldn't slow down anything). For any updates and inserts we want to use explicit other transactions with TCustomDADataSet or TCustomDASQL descendents.

When doing a DELETE (or any other operation I don't need a result for), I want to use the most lightweight and fastest option possible:

• Which transaction is used when calling TIBCConnection.ExecSQL? I assume it's TIBCConnection.DefaultTransaction, so this is not the right way to do it (please confirm this). I further assume TIBCConnection.ExecSQL uses TIBCConnection.SQL object internally (please confirm or refute).
• TIBCConnection has a built-in TIBCSQL in TIBCConnection.SQL. Is TIBCConnection.SQL.Transaction different to TIBCConnection.DefaultTransaction?
  • If yes (please confirm or refute), I assume this would be the most lightweight and fastest option (instantiate new TIBCTransaction, configure it, use it with TIBCConnection.SQL, set TIBCConnection.SQL.Transaction to nil, free newly instantiated TIBCTransaction).
  • If not, I would temporally instantiate an Instance of TIBCSQL to do the operation.

[ZEuS]

Yes, when calling the TIBCConnection.ExecSQL method, a built-in TIBCSQL object that is available via the TIBCConnection.SQL property is used internally for executing a SQL-statement, and the TIBCConnection.DefaultTransaction is used in this case.
Usually a separate TIBCSQL component has to be used to execute a SQL-statement when a different transaction is needed. But, you can use the approach that you describe: assign a separate TIBCTransaction to the TIBCConnection.SQL.Transaction property. In this case, the transaction will be used when executing the TIBCConnection.ExecSQL method.

[RSE]

Thank you for the quick Answer.

But, you can use the approach that you describe: assign a separate TIBCTransaction to the TIBCConnection.SQL.Transaction property. In this case, the transaction will be used when executing the TIBCConnection.ExecSQL method.

What happens to TIBCConnection.DefaultTransaction in this case? Is it then different to TIBCConnection.SQL.Transaction? Does this influence the Queries working with TIBCConnection.DefaultTransaction in any way?
If they are different and Queries working with TIBCConnection.DefaultTransaction are not influenced in any way, then this would be my favourite approach. After Calling TIBCConnection.ExecSQL, I'd set TIBCConnection.SQL.Transaction property to nil to recreate "initial Situation".

What are other uses of TIBCConnection.SQL, that could be influenced by this procedure? I plan to use it widely and often. Or is it better to use a separate TIBCSQL instance to avoid all possible influences? I'd then instantiate TIBCSQL for every single SQL operation done with this procedure (the used TIBCTransaction instance may live longer).

[ZEuS]

When you set a separate transaction to the TIBCConnection.SQL.Transaction property, the TIBCConnection.SQL object uses only this transaction.
No influence on TIBCTransaction.DefaultTransaction is made in this case. Other datasets continue to use TIBCConnection.DefaultTransaction.
The TIBCConnection.SQL object is used only to implement the TIBCConnection.ExecSQL, ExecSQLEx, ExecProc and ExecProcEx methods.
So, you can use TIBCConnection.SQL without any fear to harm a default transaction.

[RSE]

Thank you very much for this clarification.