Introducing NUMBER support in the 14.0 release of the .Net Data Provider for Teradata.

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

This is a brief introduction to the new provider specific type called TdNumber .  It will be released in the 14.0 version of the .Net Data Provider for Teradata.  This new type corresponds to the NUMBER data type introduced in 14.0 release of the Teradata Database.  

TdNumber will have similar features/behaviors as the NUMBER data type.  The primary purpose of TdNumber is to allow an application to send and retrieve NUMBER data to and from a Teradata 14.0 or greater database.  It will have the following features and behaviors:

  • Arithmetic operations
  • Comparison operators
  • Conversion to other numeric types and string
  • Conversion from other numeric types.
  • Support of up to 40 digits
  • Range of plus/minus 9.999...9E+125 (mantissa can support up to 40 digits)
  • Exponent can range from -130 to 125
  • Normalized representation is one digit to left of decimal point and up to 39 digits to the right of decimal point.  Example:  5.244215E-10

Here are several examples on how to created a TdNumber:

// 1.2321221E+3
TdNumber example1 = new TdNumber(1232.1221m);

//9.032324E+4
TdNumber example2 = new TdNumber(9032.324e50);

// 5.0E-9
TdNumber example3 = new TdNumber(0x00000032, 0, 0, 0, 0, -10);

As of .Net Framework 3.5, there does not exist a System type that can be used to retrieve all numbers that are supported by the Teradata NUMBER data type.  System.Decimal cannot support the range of a NUMBER.  System.Double can be used, however, the mantissa only supports up to 15 digits --NUMBER can support up to 40 digits.  If a Double is used to retrieve data of a column defined as NUMBER, the mantissa of the NUMBER will be rounded at the 15th digit.

The following is an example on how to use TdNumber to retrieve data from a column that is declared as a NUMBER:

// This example retrieves data from the table with the following definition
// CREATE TABLE numberTable (c1 INTEGER, c2 NUMBER(*))

public void NumberExample(TdCommand cmd)
{
// A decimal is implicitly converted to a TdNumber
TdNumber numberData = 345382.121m;

// Going to insert a row into the table
cmd.CommandText = "INSERT INTO numberTable VALUES (?, ?)";

// Setting up the parameters
cmd.Parameters.Clear();
cmd.Parameters.Add(null, TdType.Integer, 0, ParameterDirection.Input, true,
0, 0, null, DataRowVersion.Current, 1);
cmd.Parameters.Add(null, TdType.Number, 0, ParameterDirection.Input, true,
0, 0, null, DataRowVersion.Current, numberData);

// Executing the insert statement
cmd.ExecuteNonQuery();

// going to retrieve the row that was just inserted
cmd.CommandText = "SELECT c1, c2 FROM numberTable";
cmd.Parameters.Clear();

using (TdDataReader rd = cmd.ExecuteReader())
{
rd.Read();
// retrieving a 1
Int32 c1 = rd.GetInt32(0);

// retrieving the number 3.45382121E+05
TdNumber c2 = rd.GetTdNumber(1);
}
}