| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect for AS/400 |
![[-]](/dwicons/i_colpse.gif)
| Chapter 12 Accessing catalog information with CSPs |
|
| sp_columns |
Returns information about the type of data that can be stored in one or more columns.
sp_columns table_name [, table_owner] [, table_qualifier] [, column_name]
is the table name. Use the wildcard character to request information about more than one table.
table_owneris the name of the collection if the CSPCatalogQualifier property value is QSYS2. Otherwise, this parameter can be ignored, set to NULL, or set to the value of the CSPCatalogQualifier property.
table_qualifieris ignored. Leave blank or set to NULL.
column_nameis the name of the column for which you want information. Use the wildcard character to request information about more than one column. Leave empty or set to NULL to request information about all columns in the table or tables.
If column_name is provided, sp_columns returns information only for the column or columns that match.
This function corresponds to the ODBC function SQLColumns.
Information is based on the SYSCOLUMNS system catalog table.
sp_columns returns one row containing a description of each column in a table. Results are ordered by the following columns:
TABLE_OWNER
TABLE_NAME
The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.
The following table shows the result set.
Column | Datatype | Description |
TABLE_QUALIFIER | varchar(128) | Always NULL |
TABLE_OWNER | varchar(128) | Collection name |
TABLE_NAME | varchar(128) NOT NULL | Table name |
COLUMN_NAME | varchar(128) NOT NULL | Column name |
DATA_TYPE | smallint NOT NULL | Integer code for the ODBC datatype |
TYPE_NAME | varchar(128) NOT NULL | String representing the datatype name in the target database |
PRECISION | int | Number of significant digits of the column on the target database |
LENGTH | int | Length of the column in bytes |
SCALE | smallint | Number of digits to the right of the decimal point |
RADIX | smallint | Base for numeric types |
NULLABLE | smallint NOT NULL | Indicates whether the column accepts NULL values: 0 SQL_NO_NULLS if the column does not accept NULL values 1 SQL_NULLABLE if the column accepts NULL values 2 SQL_NULLABLE_UNKNOWN if it is not known if the column accepts NULL values |
REMARKS | varchar(254) | A description of the column |
SS_DATA_TYPE | smallint | The SQL Server datatype name |
COLID | smallint | The column ID number |
REMOTE_DATA_TYPE | int | An integer representing the underlying target database datatype (composite value) |
The following table describes the AS/400 datatypes and matching ODBC integer identifiers that are returned in the TYPE_NAME and DATA_TYPE columns of the sp_columns, sp_datatype_info, sp_special_columns, and sp_sproc_columns result sets.
AS/400 Datatype (TYPE_NAME) | Target DatatypeMaximum Physical Length | ODBC Type | ODBC Integer ID (DATA_ TYPE) | AS/400 Datatype Description |
Fixed or Variable Length Character for Bit Data Datatypes | ||||
CHARACTER() FOR BIT DATA | 32766 | SQL_LONGVARBINARY | -4 | Fixed length character for bit data |
VARCHAR() FOR BIT DATA | 255 | SQL_VARBINARY | -3 | Variable length character for bit data |
VARCHAR() FOR BIT DATA | 32740 | SQL_LONGVARBINARY | -4 | Variable length character for bit data |
Fixed or Variable Length Character Datatypes | ||||
CHARACTER() | 254 | SQL_CHAR | 1 | Fixed length character |
CHARACTER() | 32766 | SQL_LONGVARCHAR | -1 | Fixed length character |
CHARACTER() FOR SBCS DATA | 254 | SQL_CHAR | 1 | Fixed length character |
CHARACTER() FOR SBCS DATA | 32766 | SQL_LONGVARCHAR | -1 | Fixed length character |
VARCHAR() | 254 | SQL_VARCHAR | 12 | Variable length character |
VARCHAR() | 32740 | SQL_LONGVARCHAR | -1 | Variable length character |
VARCHAR() FOR SBCS DATA | 254 | SQL_VARCHAR | 12 | Variable length character |
VARCHAR() FOR SBCS DATA | 32740 | SQL_LONGVARCHAR | -1 | Variable length character |
Fixed or Variable Length Graphic Datatypes | ||||
GRAPHIC() | 255 | SQL_BINARY | -2 | Fixed length graphic (DBCS) |
GRAPHIC() | 16383 | SQL_LONGVARBINARY | -4 | Fixed length graphic (DBCS) |
VARGRAPHIC() | 255 | SQL_VARBINARY | -3 | Variable length graphic (DBCS) |
Binary Integer, Floating Point, or Decimal Number Datatypes | ||||
SMALLINT | 2 | SQL_SMALLINT | 5 | 2-byte binary integer |
INTEGER | 4 | SQL_INTEGER | 4 | 4-byte binary integer |
REAL | 4 | SQL_REAL | 7 | 4-byte floating point |
FLOAT() | 4 | SQL_REAL | 7 | 4-byte floating point with a precision less than 22 |
FLOAT() | 8 | SQL_DOUBLE | 8 | 8-byte floating point with a precision equal to or greater than 22 |
DOUBLE PRECISION | 8 | SQL_DOUBLE | 8 | 8-byte floating point |
DECIMAL() | 31 | SQL_DECIMAL | 3 | Packed decimal number |
NUMERIC | 31 | SQL_NUMERIC | 2 | Zoned decimal number |
Date and TIme Datatypes | ||||
DATE | 10 | SQL_DATE | 9 | Date |
TIME | 8 | SQL_TIME | 10 | Time |
TIMESTAMP | 26 | SQL_DATETIME | 11 | Timestamp |
The REMOTE_DATATYPE column contains a 32-bit composite datatype value that represents the target database datatype.
The following table describes the datatype value.
Bit(s) | Description |
Bits 0-7 | ODBC (target) datatype (can be extended for types not defined in ODBC) |
Bit 8 | Returns 1 if nullable, 0 if not nullable |
Bit 9 | Returns 1 if case sensitive, 0 if not case sensitive |
Bits 10, 11 | Always returns 10 (binary) meaning updatability unknown |
Bits 12, 13 | Reserved, always returns 00 (binary) |
Bits 14, 15 | Returns the following: 01 (binary) meaning NEWODBCDATATYPE (used for all except REAL) 10 (binary) meaning NEWUSERTYPE (used for REAL) |
For numeric types: Bits 16-23 Bits 24-31 | Precision Scale |
For non-numeric types: Bits 16-31 | Length |
|
|