Transaction Level Query Bands are active only during a transaction that has been started when TdConnection.BeginTransaction has been called. The transaction inherits all Connection Level Query Bands. If the same Query Band has been defined at both the Connection and Transaction levels, the one defined at the Transaction level will have precedence.

TdConnection.ChangeQueryBand must not be called when a transaction is open. If it is called, the method will succeed. However, commands cannot be successfully executed. The reason for this is that a "SET QUERY_BAND … FOR SESSION" is executed when TdConnection.ChangeQueryBand is called. This is a Data Definition Language (DDL) statement. The Advanced SQL Engine only allows an end transaction statement to follow a DDL. Therefore, when a command is executed a TdException gets thrown indicating that an end transaction must follow a Data Definition Language Statement.

TdTransaction.ChangeQueryBand can be called several time while a transaction is open.

Defining Query Bands at the Transaction Level

Query Bands are defined at the Transaction level by calling one of two overloaded methods from the TdConnection object:

• TdConnection.BeginTransaction(TdQueryBand)
• TdConnection.BeginTransaction(IsolationLevel, TdQueryBand)

If the connected SQL Engine does not support Query Bands (versions earlier than 12.0), a TdException will be thrown indicating that the SQL Engine does not support Query Bands.

Once the transaction is closed the Transaction Level Query Bands are no longer valid. The Query Bands defined at the Connection level remain in effect. Query Bands that were re-defined at the Transaction Level will be returned to their Connection level values.

Transaction Level Query Bands can be defined either at the time that a transaction is started or during the transaction. If Query Bands are to be created at the time that the transaction is started, an instance of TdQueryBand is passed in as a parameter to the call to TdConnection.BeginTransaction. The following is an example of code that specifies that the Query Bands are to be defined when the transaction is started:

TdQueryBand qb = new TdQueryBand();
qb["ProxyUser"] = "user1";
qb.Add("SpecialName", "SpecialUserName");

// tc is the TdConnection object
TdTransaction trans = tc.BeginTransaction(qb);

Transaction Level Query Bands are modified in the same way as described in Modifying Connection Level Query Bands.

If Query Bands are to be modified or added after the transaction has been started, it is important to retrieve an instance of TdQueryBand from the TdTransaction.QueryBand property. When the TdQueryBand is retrieved from this property, a new instance of TdQueryBand is created and all key=value pairs defined when the transaction was started is copied into this new instance.

After the Query Bands have been created or modified, the TdTransaction.ChangeQueryBand method must be called to apply the changes contained in the TdQueryBand instance. When the Query Bands are applied, the old setting are removed from the transaction and the setting contained in the TdQueryBand instance are set in the transaction.

An example of setting up Query Bands after the transaction has been started is as follows:

// tc is the TdConnection object
TdTransaction trans = Tc.BeginTransaction()

TdQueryBand qb = trans.QueryBand;
// The proxy user can also be defined at the Transaction level
qb["ProxyUser"] = "user1";

// Creating a custom Query Band
qb.Add("SpecialNames", "SpecialUserName");

// Applying the changes
trans.ChangeQueryBand(qb);

Guidelines for Transaction Level Query Bands

The following are guidelines related to Transaction Level Query Bands:

1. If the Teradata provider’s capabilities to manage Query Bands are used by an application, it is important that an application does not execute "SET QUERY_BAND ... FOR TRANSACTION" statements to the SQL Engine.
2. If the same Query Band has been defined at both the Connection and Transaction levels, the setting at the Transaction level has precedence.
3. When Query Bands have been added or modified the TdConnection.ChangeQueryBand must be called to apply the changes contained in the TdQueryBand instance.
4. When Query Bands are applied, the old Query Bands are removed and are no longer valid.
5. A TdQueryBand instance should first be retrieved from the TdTransaction.QueryBand property if the Query Bands are to be modified or created after the transaction has started. This object will contain the existing Query Band definitions. The reason for this is that when TdTransaction.ChangeQueryBand is called, all the existing Query Bands are deleted. Then the Query Bands contained in the TdQueryBand instance passed in as a parameter are defined for the Transaction.
6. After the transaction has closed, the Transaction Level Query Bands are removed. The Query Bands defined at the Connection Level are still valid. Query Bands overridden at the Transaction Level are returned to the value defined at the Connection Level.
7. Query Bands defined at the Connection Level are not set in the TdQueryBand instance returned from TdTransaction.QueryBand.
8. In a transaction, the method TdConnection.ChangeQueryBand must not be called by an application. If this method is called while in a transaction it will succeed. However, any command that is executed after will fail. The TdException that is thrown will indicate that a Data Definition Language statement (DDL) must be followed by an end transaction.

Example of Transaction Level Query Bands

Public void TransactionLevelDemo()
{
   TdConnection tc = new TdConnection(
       "Data Source=tdata1;User Id=user1;Password=pass1;QueryBand='Group=gp1;JobId=1;'");

   tc.Open();

   //
   // Application performs its required tasks
   //

   // Create the Transaction level Query Band object
   TdQueryBand qbTrans = new TdQueryBand();

   // The ProxyUser can be reset for the transaction
   qbTrans["ApplicationName"] = "newapp1";

   // Since SpecialName is a custom Query Band, it must be added to qbTrans.
   qbTrans.Add("SpecialName", "AnotherName");

   // Starting the transaction.
   TdTransaction trans = tc.BeginTransaction(qb);

   // After the transaction has been started, Query Bands can be modified or added.

   // Once the transaction has been started, it is important that the Query Bands be
   // retrieved from the TdTransaction object.
   qbTrans = trans.QueryBand;

   // Modifying Query Band
   qbTrans["SpecialName"] = "YetAnotherName";

   // Adding a Query Band
   qbTrans.Add("TransactionName", "Trans1");

   // The changes to the Query Bands must be applied
   Trans.ChangeQueryBand(qbTrans);

   //
   // Application performs it required tasks
   //

   trans.Commit();

   // The Transaction level Query Bands are removed after the transaction has
   // been closed. The Query Bands defined at the Connection level are still valid.
   // Query Bands that have been redefined in the transaction revert back to what
   // they were defined in the Connection level.

   //
   // Application continues to perform required tasks
   //

   tc.Close();
}