The Schema collections return metadata for the SQL Engine objects. The notable metadata columns for User Defined Array Types are:
Column Name | Description |
---|---|
COLUMN_TYPE or DATA_TYPE |
Set to the user specified type name, for example ArrayOfVarChars. |
PROVIDERDBTYPE | Set to DBNull.Value |
The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for an Array User Defined Type transformed to VarChar 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.VarChar. |
Format | Format assigned to the object. |
ProviderSpecificDataType | System.Type object for the System.String type. |
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. |
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.
C# |
Copy Code |
---|---|
// // 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.
C# |
Copy Code |
---|---|
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)"; |
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]
The following methods and properties return the column value as a System.Char[] object.
The following methods and properties return the column or parameter value as a System.String object.
Accessor Methods for Retrieving Data
Configuring Parameters and Parameter Data Types