type_info (NEW )
@type_info = $dbh->type_info($data_type);
Warning: This method is experimental and may change.
Returns
a
list of hash references holding information about one or more
variants of $data_type. The list is ordered by
DATA_TYPE first and then by how closely each type
maps to the corresponding ODBC SQL datatype, closest first. If called
in a scalar context then only the first (best) element is returned.
If $data_type is undefined or
SQL_ALL_TYPES, then the list will contain hashes
for all datatype variants supported by the database and driver.
If $data_type is an array reference, then
type_info returns the information for the
first type in the array that has any matches.
The keys of the hash follow the same letter case conventions as the rest of the DBI (see "Naming Conventions and Name Space”). The following items should exist:
Datatype name for use in CREATE
TABLE statements, etc.
SQL datatype number.
For numeric types, this is either the total number of digits (if the
NUM_PREC_RADIX value is 10) or
the total number of bits allowed in the column (if
NUM_PREC_RADIX is 2).
For string types, this is the maximum size of the string in bytes.
For date and interval types, this is the maximum number of characters needed to display the value.
Characters used to prefix a literal. A typical prefix is
"'" for characters, or possibly
"0x" for binary values passed as
hexadecimal. NULL (undef) is returned for
datatypes for which this is not applicable.
Characters used to suffix a literal. Typically
"'" for characters. NULL
(undef) is returned for datatypes where this is
not applicable.
Parameters for a datatype definition. For example,
CREATE_PARAMS for a DECIMAL
would be "precision,scale" if the
DECIMAL type should be declared as
DECIMAL(
precision
,
scale
)
where precision and
scale are integer values. For a
VARCHAR it would be "max
length“. NULL (undef) is
returned for datatypes for which this is not applicable.
Indicates whether the datatype accepts a NULL value:
0 = no, 1 = yes,
2 = unknown.
Indicates whether the datatype is case-sensitive in collations and comparisons.
Indicates how the datatype can be used in a WHERE
clause, as follows:
Cannot be used in a WHERE clause
Only with a LIKE predicate
All comparison operators except LIKE
Can be used in a WHERE clause with any comparison
operator
Indicates whether the datatype is unsigned. NULL
(undef) is returned for datatypes for which this
is not applicable.
Indicates whether the datatype always has the same precision and
scale (such as a money type). NULL (undef) is
returned for datatypes for which this is not applicable.
Indicates whether a column of this datatype is automatically set to a
unique value whenever a new row is inserted. NULL
(undef) is returned for datatypes for which this
is not applicable.
Localized version of the TYPE_NAME for use in
dialog with users. NULL (undef) is returned if a
localized name is not available (in which case
TYPE_NAME should be used).
The minimum scale of the datatype. If a datatype has a fixed scale,
then MAXIMUM_SCALE holds the same value. NULL
(undef) is returned for datatypes for which this
is not applicable.
The maximum scale of the datatype. If a datatype has a fixed scale,
then MINIMUM_SCALE holds the same value. NULL
(undef) is returned for datatypes for which this
is not applicable.
This column is the same as the DATA_TYPE column,
except for interval and datetime datatypes. For interval and datetime
datatypes, the SQL_DATA_TYPE field will return
SQL_INTERVAL or SQL_DATETIME,
and the SQL_DATETIME_SUB field below will return
the subcode for the specific interval or datetime datatype. If this
field is NULL, then the driver does not support or report on interval
or date subtypes.
For interval or datetime datatypes, where the
SQL_DATA_TYPE field above is
SQL_INTERVAL or SQL_DATETIME,
this field will hold the subcode for the specific interval or
datetime datatype. Otherwise it will be NULL
(undef).
The radix value of the datatype. For approximate numeric types,
NUM_PREC_RADIX contains the value
2 and COLUMN_SIZE holds the
number of bits. For exact numeric types,
NUM_PREC_RADIX contains the value
10 and COLUMN_SIZE holds the
number of decimal digits. NULL (undef) is returned
either for datatypes for which this is not applicable or if the
driver cannot report this information.
The interval leading precision for interval types. NULL is returned either for datatypes for which this is not applicable or if the driver cannot report this information.
Since DBI and ODBC drivers vary in how they map their types into the ISO standard types, you may need to search for more than one type. Here’s an example looking for a usable type to store a date:
$my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] );
Similarly, to more reliably find a type to store small integers, you
could use a list starting with SQL_SMALLINT,
SQL_INTEGER, SQL_DECIMAL, etc.
For more detailed information about these fields and their meanings, refer to:
| http://msdn.microsoft.com/library/psdk/dasdk/odch6yy7.htm |
If that URL ceases to work, then use the MSDN search facility at:
| http://search.microsoft.com/us/dev/ |
and search the MSDN library for SQLGetTypeInfo
returns using the exact phrase option. The link
you want will probably just be called
SQLGetTypeInfo (there may be more than one).
The individual datatypes are currently described here:
| http://msdn.microsoft.com/library/psdk/dasdk/odap8fcj.htm |
If that URL ceases to work, or to get more general information, use
the MSDN search facility as described above, and search for
SQL
Data
Types.