Using LOB's as Input, InputOutput and Output Parameters

Using Large Objects (LOB) with the .NET Teradata Provider for Teradata is not much different than any other data types that are supported. The Data Provider supports Binary (BLOB), Character (CLOB) and XML Large Objects. The data type (TdType) specified when creating a TdParameter object for a LOB are:

TdType.Blob -- BLOB
TdType.Clob -- CLOB or JSON
TdType.Xml -- XML

When specifying an Input parameter, the TdParameter.Value property can be set to one of the following System types:

Teradata Type	System Type
BLOB	System.IO.Stream or any object that derives from Stream. System.Byte[]
CLOB or JSON	System.IO.TextReader or any object that derives from TextReader System.String System.Char[]
XML	System.Xml.XmlReader or any object that derives from XmlReader System.String System.Char[]

Corresponding System type for BLOB or CLOB

The provider will also use LOB's when an application sets the TdParameter.DbType property using either DbType.Binary or DbType.String. The provider will send the data to Teradata as a Blob or Clob when the size exceeds 64,000 bytes.

Using a Byte Array, Char array, or String to contain the data of a LOB and setting up a parameter is a straight forward operation to perform. Using a Stream or TextReader is more complicated. It is easier to use an existing System object that derives from either Stream or TextReader. For example, the parameter setup when a System.IO.FileStream object is used to access the data a Blob contained in a file will be as follows:

C#	Copy Code
System.IO.FileStream fs = new FileStream("picture.jpg", FileMode.Open, FileAccess.Read); System.IO.FileInfo fi = new FileInfo("picture.jpg"); command.Parameters.Add(null, TdType.Blob, fi.Length); command.Parameters[0].Value = fs;

Copy Code

System.IO.FileStream fs = new FileStream("picture.jpg", FileMode.Open, FileAccess.Read);

System.IO.FileInfo fi = new FileInfo("picture.jpg");
command.Parameters.Add(null, TdType.Blob, fi.Length);
command.Parameters[0].Value = fs;

When a LOB has been specified as an InputOutput or Output parameter one of the following objects is returned in the TdParameter.Value property after the stored procedure has been executed:

TdBlob -- returned when TdType is set to TdType.Blob
TdClob -- returned when TdType is set to TdType.Clob
TdXml -- returned when TdType is set to TdType.Xml

Refer to the .NET Data Provider for Teradata help files for more information on TdBlob, TdClob and TdXml.

Alternatively the data may be retrieved as a different compatible type by using the GetValue<T> method. Depending on the data type, T may be any of:

Blob -- TdBlob, Stream, String, Byte[]
Clob -- TdClob, TextReader, String, Char[]
Xml -- TdXml, XmlReader, String, Char[]

Note that only TdBlob, TdClob and TdXml support null values. The method IsDBNull() should therefore be called prior to calling GetValue<T> in order to avoid a potential InvalidCast exception.

For example the setup up of a parameter object and accessing the data of a CLOB is as follows:

C#	Copy Code
System.IO.StreamReader sr = new StreamReader("textfile.txt"); System.IO.FileInfo fs = new FileInfo("textfile.txt"); command.Parameters.Add(null, TdType.Clob, fs.Length); command.Parameters[0].Direction = ParameterDirection.InputOutput; command.Parameters[0].Value = sr; //executes a stored procedure that only contains 1 parameter command.ExecuteNonQuery(); TdClob clob1 = (TdClob)command.Parameter[0].Value; clob1.Read(charBuffer, 0, 25); //Process buffer... clob1.Close(); //need to close command when done command.Dispose();

Copy Code

System.IO.StreamReader sr = new StreamReader("textfile.txt");
System.IO.FileInfo fs = new FileInfo("textfile.txt");
command.Parameters.Add(null, TdType.Clob, fs.Length);
command.Parameters[0].Direction = ParameterDirection.InputOutput;
command.Parameters[0].Value = sr;

//executes a stored procedure that only contains 1 parameter
command.ExecuteNonQuery();
TdClob clob1 = (TdClob)command.Parameter[0].Value;
clob1.Read(charBuffer, 0, 25);

//Process buffer...

clob1.Close();
//need to close command when done
command.Dispose();

It is important to dispose the command object after the processing of all the LOBs have been completed by the application and the command is no longer needed. When the data type of an InputOutput or Output parameter is a LOB, the provider will allocate internal objects to manage the data of the LOBs. These objects do not get disposed until the command object gets disposed.

Note
External stored procedures do not support inline return of LOB data for INOUT or OUT parameters. DBS will return "6950 - Returning LOB inline in multipart Record mode is not supported for XSP", if an application tries to retrieve LOB data for INOUT/OUT parameter inline by calling an external stored procedure. Refer to General Guidelines for Using Stored Procedures for additional details.