Schema Collections

The Schema collections return metadata for the SQL Engine objects. The notable metadata columns for User Defined Array Types are:

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for an Array User Defined Type transformed to VarChar data type are:

Note
The SQL Engine automatically transforms all user defined Array data types to/from VarChar(length). The length depends on the array element type and the maximum number of elements defined for the array.

Configuring Parameters

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

//
// DDL: CREATE TYPE IntegerArray AS INTEGER ARRAY[10]
// The SQL Engine will transform the IntegerArray type to/from VarChar(121).
//

TdParameter arrayOfIntegers = new TdParameter();
arrayOfIntegers.ParameterName = "ArrayOfIntegers";

arrayOfIntegers.DbType = DbType.String;
arrayOfIntegers.Size = 121;

arrayOfIntegers.Value = "(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)";

Note
The SQL Engine transforms VarChar input parameters to user define Array data type.

The following example shows how to configure a user defined array parameter as TdType.VarChar and a BCL value.

TdParameter arrayOfIntegers = new TdParameter();
arrayOfIntegers.ParameterName = "ArrayOfIntegers";

arrayOfIntegers.TdType = TdType.VarChar;
arrayOfIntegers.Size = 121;

arrayOfIntegers.Value = "(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)";

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.

HELP TYPE command returns the attributes for a user defined array including the maximum number of characters (Max length) when the SQL Engine transforms the user defined array to/from VahrChar data type.  The "SQL Data Definition Language - Syntax and Examples" manual describe the HELP TYPE command and the output. The following example shows how to retrieve the maximum number for characters for an array of Integers and an array of VarChars:

CREATE TYPE ArrayOfIntegers as INTEGER ARRAY[10];
CREATE TYPE ArrayOfVarChars as VARCHAR(100) ARRAY[20];

HELP TYPE ArrayOfIntegers;
        Name:                   ArrayOfIntegers
        Internal Type:          A1
        External Type:         CV
        Max Length:             121
        ...
        Array (Y/N):            Y
        Dimensions:             1
        Element Type:           I
        UDT Name:               [NULL]
        Array Scope:            [1:10]

HELP TYPE ArrayOfVarChars;
        Name:                   ArrayOfVarChars
        Internal Type:          A1
        External Type:         CV
        Max Length:             4,061
        ...
        Array (Y/N):            Y
        Dimensions:             1
        Element Type:           CV
        UDT Name:               [NULL]
        Array Scope:            [1:20]

Retrieving Array Data

The following methods and properties return the column value as a System.Char[] object.

1. TdDataReader.GetChars

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

1. TdDataReader.GetString
2. TdDataReader.GetValue
3. TdDataReader.GetProviderSpecificValue
4. TdParameter.ProviderSpecificValue
5. TdParameter.Value

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types