|
Null Values
Undefined values, or undef, can be used to indicate null values.
However, care must be taken in the particular case of trying to use null values
to qualify a SELECT statement. Consider:
SELECT description FROM products WHERE product_code = ? | |
Binding an undef (NULL) to the placeholder will not
select rows which have a NULL product_code! Refer to the SQL manual
for your database engine or any SQL book for the reasons for this. To explicitly
select NULLs you have to say "WHERE product_code IS NULL" and to
make that general you have to say:
... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL)) | |
and bind the same value to both placeholders.
Generell zu Type Infos:
======================================
type_info_all
NEW
Warning: This method is experimental and may change.
$type_info_all = $dbh->type_info_all; | |
Returns a reference to an array which holds information about each data type
variant supported by the database and driver. The array and its contents should
be treated as read-only.
The first item is a reference to an 'index' hash of Name =>
Index pairs. The items following that are references to arrays, one
per supported data type variant. The leading index hash defines the names and
order of the fields within the arrays that follow it. For example:
$type_info_all = [ { TYPE_NAME => 0, DATA_TYPE => 1, COLUMN_SIZE => 2, # was PRECISION originally LITERAL_PREFIX => 3, LITERAL_SUFFIX => 4, CREATE_PARAMS => 5, NULLABLE => 6, CASE_SENSITIVE => 7, SEARCHABLE => 8, UNSIGNED_ATTRIBUTE=> 9, FIXED_PREC_SCALE => 10, # was MONEY originally AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally LOCAL_TYPE_NAME => 12, MINIMUM_SCALE => 13, MAXIMUM_SCALE => 14, NUM_PREC_RADIX => 15, }, [ 'VARCHAR', SQL_VARCHAR, undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef ], [ 'INTEGER', SQL_INTEGER, undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10 ], ]; | |
Note that more than one row may have the same value in the
DATA_TYPE field if there are different ways to spell the type name
and/or there are variants of the type with different attributes (e.g., with and
without AUTO_UNIQUE_VALUE set, with and without
UNSIGNED_ATTRIBUTE, etc).
The rows are ordered by DATA_TYPE first and then by how closely
each type maps to the corresponding ODBC SQL data type, closest first.
The meaning of the fields is described in the documentation for the /type_info method. The index values shown above (e.g.,
NULLABLE => 6) are for illustration only. Drivers
may define the fields with a different order.
This method is not normally used directly. The /type_info method provides a more useful interface to the
data.
Even though an 'index' hash is provided, all the field names in the index
hash defined above will always have the index values defined above. This is
defined behaviour so that you don't need to rely on the index hash, which is
handy because the lettercase of the keys is not defined. It is usually
uppercase, as show here, but drivers are free to return names with any
lettercase. Drivers are also free to return extra driver-specific columns of
information - though it's recommended that they start at column index 50 to
leave room for expansion of the DBI/ODBC specification.
type_info NEW
Warning: This method is experimental and may change.
@type_info = $dbh->type_info($data_type); | |
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 data type, 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 data type 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:
- TYPE_NAME (string)
- Data type name for use in CREATE TABLE statements etc.
- DATA_TYPE (integer)
- SQL data type number.
- COLUMN_SIZE (integer)
-
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.
- LITERAL_PREFIX (string)
- 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 data types for which
this is not applicable.
- LITERAL_SUFFIX (string)
- Characters used to suffix a literal. Typically "
'" for
characters. NULL (undef) is returned for data types where this is
not applicable.
- CREATE_PARAMS (string)
- Parameter names for data type 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 data types for which this is not
applicable.
- NULLABLE (integer)
- Indicates whether the data type accepts a NULL value:
0 or an
empty string = no, 1 = yes, 2 = unknown.
- CASE_SENSITIVE (boolean)
- Indicates whether the data type is case sensitive in collations and
comparisons.
- SEARCHABLE (integer)
-
Indicates how the data type can be used in a WHERE clause, as follows:
0 - Cannot be used in a WHERE clause 1 - Only with a LIKE predicate 2 - All comparison operators except LIKE 3 - Can be used in a WHERE clause with any comparison operator | |
- UNSIGNED_ATTRIBUTE
(boolean)
- Indicates whether the data type is unsigned. NULL (
undef) is
returned for data types for which this is not applicable.
- FIXED_PREC_SCALE
(boolean)
- Indicates whether the data type always has the same precision and scale
(such as a money type). NULL (
undef) is returned for data types
for which this is not applicable.
- AUTO_UNIQUE_VALUE
(boolean)
- Indicates whether a column of this data type is automatically set to a
unique value whenever a new row is inserted. NULL (
undef) is
returned for data types for which this is not applicable.
- LOCAL_TYPE_NAME (string)
- 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).
- MINIMUM_SCALE (integer)
- The minimum scale of the data type. If a data type has a fixed scale, then
MAXIMUM_SCALE holds the same value. NULL (undef) is
returned for data types for which this is not applicable.
- MAXIMUM_SCALE (integer)
- The maximum scale of the data type. If a data type has a fixed scale, then
MINIMUM_SCALE holds the same value. NULL (undef) is
returned for data types for which this is not applicable.
- SQL_DATA_TYPE (integer)
- This column is the same as the
DATA_TYPE column, except for
interval and datetime data types. For interval and datetime data types, 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 data type. If
this field is NULL, then the driver does not support or report on interval or
date subtypes.
- SQL_DATETIME_SUB
(integer)
- For interval or datetime data types, 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 data type.
Otherwise it will be NULL (undef).
- NUM_PREC_RADIX (integer)
- The radix value of the data type. 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 data types for which
this is not applicable or if the driver cannot report this information.
- INTERVAL_PRECISION
(integer)
- The interval leading precision for interval types. NULL is returned either
for data types for which this is not applicable or if the driver cannot report
this information.
For example, to find the type name for the fields in a select statement you
can do:
@names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} } | |
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, you can
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 data types 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.
|
