An application must not use a System.IO.TextReader or a System.IO.Stream object as a LOB value when the DataAdapter.ContinueUpdateOnError property is set to true and Batch Update feature is enabled (TdDataAdapter.UpdateBatchSize property is greater 1).

Releases Affected

This issue occurs with all .NET Data Provider for Teradata releases. It occurs for INSERT operations on Teradata Database version 13.0 or earlier and it also occurs for UPDATE or DELETE operations on all the Advaned SQL Engine versions.

Conditions When Issue Occurs

When one of the statements in a batch fails due to input data errors, the SQL Engine rejects the whole batch and the Data Provider resubmits the batch by removing the erroneous statement. During resubmission, the Data Provider cannot rewind the LOB values to the original position. The System.IO.TextReader does not support rewind to the original position and not all implementations of the System.IO.Stream type support Seek to the original position.

Example Of Code That Causes Issue To Occur

Below code illustrates a Batch Update with LOB value as System.IO.TextReader (column c2) for 3 rows with TdDataAdapter.UpdateBatchSize set to 3 DataAdapter.ContinueUpdateOnError set to true and one of the row is erroneous(row with column c1 value 1). During Batch Update, rows(column c1 value 2 and 3) are executed ahead of erroneous row but batch will be rejected due to Duplicate Primary Key error. The Data Provider resubmits request removing the erroneous row, due to which System.IO.TextReader cannot be repositioned back to original position and incorrect values are stored into column c2 for rows with column c1 value 2 and 3.

Note: Create table statement and data to be present for issue replication is added as comments in example

C#	Copy Code
public static void Example1() { // The definition of the table used in this example is // CREATE TABLE ProblemTable( // C1 INTEGER NOT NULL, // C2 CLOB(20), // C3 VARCHAR(20), // Primary key(c1)) // // data to be present in table // INSERT INTO ProblemTable VALUES (1,'sampleData','SampleData'); // Setting up the log on string TdConnection cn = new TdConnection(); TdConnectionStringBuilder tsb = new TdConnectionStringBuilder(); tsb.DataSource = "teradata"; tsb.UserId = "user"; tsb.Password = "password"; // Open a Connection to the SQL Engine cn.ConnectionString = tsb.ConnectionString; cn.Open(); TextReader tReader1 = new StringReader("sampleText-1"); TextReader tReader2 = new StringReader("sampleText-2"); TextReader tReader3 = new StringReader("SampleText-3"); TdCommand cmd = cn.CreateCommand(); cmd.UpdatedRowSource = UpdateRowSource.None; // Populating the DataTable DataTable dt = new DataTable(); dt.Columns.Add("c1", typeof(Int32)); dt.Columns.Add("c2", typeof(TextReader)); dt.Columns.Add("c3", typeof(String)); DataRow row = dt.NewRow(); // the batch will be rejected due to Duplicate Primary Key error. row["c1"] = 1; row["c2"] = tReader1; row["c3"] = "temp"; dt.Rows.Add(row); row = dt.NewRow(); row["c1"] = 2; row["c2"] = tReader2; row["c3"] = "temp"; dt.Rows.Add(row); row = dt.NewRow(); row["c1"] = 3; row["c2"] = tReader3; row["c3"] = "temp"; dt.Rows.Add(row); // Setting up the Insert command which will be used to update the table cmd.CommandText = "insert into ProblemTable values (?, ?, ?)"; cmd.Parameters.Add(new TdParameter("c1", TdType.Integer)); cmd.Parameters.Add(new TdParameter("c2", TdType.Clob)); cmd.Parameters.Add(new TdParameter("c3", TdType.VarChar)); cmd.Parameters["c1"].SourceColumn = "c1"; cmd.Parameters["c2"].SourceColumn = "c2"; cmd.Parameters["c3"].SourceColumn = "c3"; TdDataAdapter adapter = new TdDataAdapter(); adapter.ContinueUpdateOnError = true; adapter.UpdateBatchSize = 3; adapter.InsertCommand = cmd; try { adapter.Update(dt); } finally { // Disposing all the objects used tReader1.Dispose(); tReader2.Dispose(); tReader3.Dispose(); cn.Close(); } }

Copy Code

public static void Example1()
{
    // The definition of the table used in this example is
    //    CREATE TABLE ProblemTable(
    //        C1 INTEGER NOT NULL,
    //        C2 CLOB(20),
    //        C3 VARCHAR(20),
    //        Primary key(c1))
    //        
    //    data to be present in table
    //        INSERT INTO ProblemTable VALUES (1,'sampleData','SampleData');

    // Setting up the log on string
    TdConnection cn = new TdConnection();
    TdConnectionStringBuilder tsb = new TdConnectionStringBuilder();
    tsb.DataSource = "teradata";
    tsb.UserId = "user";
    tsb.Password = "password";

    // Open a Connection to the SQL Engine
    cn.ConnectionString = tsb.ConnectionString;
    cn.Open();

    TextReader tReader1 = new StringReader("sampleText-1");
    TextReader tReader2 = new StringReader("sampleText-2");
    TextReader tReader3 = new StringReader("SampleText-3");

    TdCommand cmd = cn.CreateCommand();
    cmd.UpdatedRowSource = UpdateRowSource.None;

    // Populating the DataTable
    DataTable dt = new DataTable();
    dt.Columns.Add("c1", typeof(Int32));
    dt.Columns.Add("c2", typeof(TextReader));
    dt.Columns.Add("c3", typeof(String));


    DataRow row = dt.NewRow();
   // the batch will be rejected due to Duplicate Primary Key error.
     row["c1"] = 1;
    row["c2"] = tReader1;
    row["c3"] = "temp";
     dt.Rows.Add(row);

    row = dt.NewRow();
    row["c1"] = 2;
    row["c2"] = tReader2;
    row["c3"] = "temp";
    dt.Rows.Add(row);

    row = dt.NewRow();
    row["c1"] = 3;
    row["c2"] = tReader3;
    row["c3"] = "temp";
    dt.Rows.Add(row);

    // Setting up the Insert command which will be used to update the table
    cmd.CommandText = "insert into ProblemTable values (?, ?, ?)";

    cmd.Parameters.Add(new TdParameter("c1", TdType.Integer));
    cmd.Parameters.Add(new TdParameter("c2", TdType.Clob));
    cmd.Parameters.Add(new TdParameter("c3", TdType.VarChar));

    cmd.Parameters["c1"].SourceColumn = "c1";
    cmd.Parameters["c2"].SourceColumn = "c2";
    cmd.Parameters["c3"].SourceColumn = "c3";

    TdDataAdapter adapter = new TdDataAdapter();
    adapter.ContinueUpdateOnError = true;
    adapter.UpdateBatchSize = 3;
    adapter.InsertCommand = cmd;
    try
    {
        adapter.Update(dt);
    }
    finally
    {
        // Disposing all the objects used
        tReader1.Dispose();
        tReader2.Dispose();
        tReader3.Dispose();
        cn.Close();
    }
}

Workarounds

An application can use STRING, BYTE[] and CHAR[] types for LOB columns when DataAdapter.ContinueUpdateOnError set to true, Batch Update is enabled and the input data has errors.