Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the CLOB data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to CLOB.
CHARACTER_MAXIMUM_LENGTH	Returns the maximum length of the column in Characters.
CHARACTER_OCTET_LENGTH	Returns the maximum length of the column in Bytes. This field is set to "CHARACTER_MAXIMUM_LENGTH * 2" for Unicode Character columns.
FORMAT	Format assigned to the SQL Engine object.
CHAR_TYPE	Indicates whether the columns is of type: LATIN UNICODE
PROVIDERDBTYPE	It is set to TdType.Clob.

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Clob data type are:

Column Name	Description
ColumnSize	Returns the maximum length of the column in Characters.
DataType	System.Type object for the System.String type.
ProviderType	Set to TdType.Clob.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdClob class.

Configuring Parameters

The following example shows how to configure a Clob parameter using DbType type declaration, Size property and a BCL value. The value is set to a System.String instance.

C#	Copy Code
TdParameter biography = new TdParameter(); biography.ParameterName = "biography"; biography.DbType = DbType.String; biography.Size = 64001; biography.Value = "John Smith";

Note
The Data Provider maps DbType.String to the SQL Engine VarChar Data Type when Size is less than or equal to 64,000 characters. The Data Provider maps DbType.String to the SQL Engine CLOB Data Type when Size is greater than 64,000.

The following example shows how to configure a Clob parameter using TdType type declaration and a BCL value.

C#	Copy Code
TdParameter biography = new TdParameter(); biography.ParameterName = "biography"; biography.TdType = TdType.Clob; biography.Value = "John Smith";

The TdParameter.Value should be set to an Array of Characters (Char[]), String object or an instance of System.IO.TextReader.

Set TdParameter.Size Property for InputOutput or Output Parameters

The TdParameter.Size property must be set to a number greater than Zero for InputOutput or Output Parameters. The Data Provider will throw an exception when TdParameter.Direction property is set to ParameterDirection.Output or ParameterDirection.InputOutput and TdParameter.Size property is set to Zero. The TdParameter.Size property specifies the maximum number of Unicode Characters the Data Provider will send to the SQL Engine and/or receive from the SQL Engine. We recommend to set the TdParameter.Size property to the corresponding SQL Engine object size (e.g. Column or Stored Procedure Parameter size) for Input, InputOutput or Output parameters.

Specifying Character Literal

The syntax for the Character Literal is 'string'. A zero-length character literal (i.e. Empty String) is represented by two consecutive single-quotes.

Note
We recommend to always use Parameters in order to take advantage of the SQL Engine's Request Cache.

The following example shows "John Smith" value represented as Character-Literal in the Command Text.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT customerId, customerName from Customer where customerName = 'John Smith' "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Customer Id={0}, Name={1}", reader.GetString(0), reader.GetString(1)); } } } }

Copy Code

using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;"))
{
    cn.Open();
    TdCommand cmd = cn.CreateCommand();

    cmd.CommandText = "SELECT customerId, customerName from Customer where customerName = 'John Smith' ";

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("Customer Id={0}, Name={1}", reader.GetString(0), reader.GetString(1));
            }
        }
    }
}

Retrieving Clob Data

The following methods return the column or parameter value as a System.Char[] object.

The following methods and properties return the column or parameter value as a System.String object.

The following methods and properties return the column or parameter value as a TdClob class.

Note
The CommandBehavior (see TdCommand.ExecuteReader(CommandBehavior)) must not include CommandBehavior.SequentialAccess.

The following method returns the column or parameter value as a System.TextReader object.

Note
The CommandBehavior.SequentialAccess can potentially result in best overall performance. However in this mode CLOB data type cannot be transformed to TdClob class. The CommandBehavior.SequentialAccess reduces the overall messages between the Data Provider and the SQL Engine because LOBs are transmitted to the Data Provider in Inline-mode.