Changing Data Using DML Statements - OLE DB Provider for Teradata

OLE DB Provider for Teradata User Guide

Product
OLE DB Provider for Teradata
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-2498
Product Category
Teradata Tools and Utilities

Changing Data Using DML Statements

When using DML, the consumer does the following:

  • Generates the correct SQL statements
  • Specifies the correct data values
  • To change data using DML statements

    After generating the SQL and specifying the data values, follows these steps:

    1 (Optional) Start a transaction using ITransactionLocal::StartTransaction.

    Note: If the consumer does not start a transaction, then OLE DB Provider for Teradata automatically commits the work after each call to ICommand::Execute.

    2 If parameters are used in the DML statement, create the Accessor using IAccessor::CreateAccessor.

    3 If parameters are used in the DML statement, bind the data values.

    4 Set the command text using ICommandText::SetCommandText.

    5 Prepare the command using ICommandPrepare::Prepare.

    Note: Preparing the command, is also optional. A DML statement can be immediately executed after the text of the command object has been set.

    6 Execute the command using ICommand::Execute.

    7 (Optional) Commit the transaction using ITransaction::Commit.

    Note: If the consumer does not start a transaction, then OLE DB Provider for Teradata automatically commits the work after each call to ICommand::Execute.

    Performance Considerations

    For optimal performance:

  • Prepare the request before execution. Teradata Database will parse the SQL request and store this information in the request cache. When the consumer repeatedly calls ICommand::Execute to process the same request but with different data values, the Teradata Database reuses the cached request. For more information about the request cache, see SQL Request and Transaction Processing.
  • Manually start a transaction by calling ITransactionLocal::StartTransaction. After executing the statement with data for the last set of parameters, end the transaction by calling ITransaction::Commit. This prevents Teradata Database from performing a commit after each call to ICommand::Execute.
  • Note: A more efficient way to insert, update, and delete multiple rows is to use Parameter Sets. See “Using Multiple Parameter Sets” on page 66 for more information.