This issue occurs when inserting a row from the DataTable into the corresponding table contained in the Advanced SQL Engine. The DataTable contains a column defined as a TdDecimal. The underlying database table contains a decimal column that is defined as a NOT NULL GENERATED ALWAYS AS IDENTITY. When a row from the DataTable is inserted into the database table the Teradata Provider will return a TdException that indicates that a Null value cannot be inserted.

Releases Affected

This issue occurs with all Teradata Provider releases. However there are two exceptions to this when using the 13.0 release:

• This issue will occur when the Connection String Attribute UseEnhancedSchemaTable is set to false.
• It will also occur when connected to a Teradata Database 6.1 or earlier release.  In these releases of Teradata, UseEnhancedSchemaTable is disabled.

Conditions When Issue Occurs

When the following conditions exist a TdException will be thrown when Update has been called by an application:

1. The database table corresponding to the DataTable contains a column defined as a DECIMAL, NOT NULL, and as an IDENTITY. The IDENTITY is defined as "GENERATED ALWAYS".
2. The Connection String Attribute UseEnhancedSchemaTable has been set to false.
3. ReturnProviderSpecificTypes  has been set to true.
4. A new row has been added to the DataTable.
5. The data in the column in the DataTable that corresponds to the IDENTITY column is the Null value.
6. Fill is called to fill the DataTable.

This issue is caused by the schema information returned when the GetSchemaTable method is called by TdCommandBuilder. The information returned does not include the AutoIncrement property of a column. Therefore, it will default to false which causes the TdCommandBuilder to generate the incorrect INSERT statement when TdDataAdapter.Update is called.

Example Of Code That Causes Issue To Occur

The following is an example of code that duplicates this problem:

 public static void Example1()
{
    // The definition of the table used in this example is
    //    CREATE TABLE ProblemTable(
    //        C1 DECIMAL(5, 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
    //        C2 VARCHAR(50),
    //        C3 DECIMAL(20, 4))

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

    // UseEnhancedSchemaTable only needs to be set if using
    // the 13.0 or higher release of the provider
    tsb.UseEnhancedSchemaTable = false;

    // Logging into a the SQL Engine
    cn.ConnectionString = tsb.ConnectionString;
    cn.Open();

    // Setting up the SELECT statement that will be used to fill the DataTable
    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "select c1, c2 from ProblemTable";

    // Setting the data adapter and command builder
    TdDataAdapter da = new TdDataAdapter(cmd);
    da.ReturnProviderSpecificType = true;

    TdCommandBuilder cb = new TdCommandBuilder(da);

    DataTable dt = new DataTable("ProblemTable");

    // Filling the DataTable
    da.Fill(dt);

    // Adding a row to the DataTable
    // Since C1 is auto-generated, a value does not have to be specified for this column.
    DataRow dr = dt.NewRow();
    dr["C2"] = "This row will not be inserted";
    dr["C3"] = new TdDecimal(321.32);
    dt.Rows.Add(dr);

    try
    {
        // A TdException will get thrown when Update is called
        da.Update(dt);
    }
        catch (TdException e)
    {
        // This exception was expected to be thrown
        Console.WriteLine(e.Message)
    }
}

Workarounds

There are several workarounds for this issue:

• Upgrade the Teradata Provider to the 13.0 release and set the UseEnhancedSchemaTable connection string attribute to true (this is the default setting). When the TdCommandBuilder retrieves meta-data about the columns contained in the SELECT statement that are defined as GENERATED ALWAYS AS IDENTITY, the autoincrement will be set to true.
• After filling the DataTable, set the AutoIncrement property of the column in the DataTable that corresponds to the IDENTITY column of the table contained in the SQL Engine to true. In the above example, this would be the column c1 which is the first column of the DataTable:

      da.Fill(dt)
    dt.Columns[0].AutoIncrement = true;

• If it is possible, specify a value for the column in the DataTable that corresponds to the IDENTITY column. By specifying a value, the data will match the TdParameter.IsNullable property which was set to false by the TdDataAdapter. The value specified in the column of the DataTable will be ignored since the IDENTITY was defined as GENERATED ALWAYS.
• If it is possible, set the ReturnProviderSpecificTypes property to false. In this situation the TdCommandBuilder will generate the correct INSERT statement even though the autoincrement property is false.

See Also

DataTable
TdDataReader.GetSchemaTable
TdConnectionStringBuilder.UseEnhancedSchemaTable
TdParameter
TdCommandBuilder
TdDataAdapter