> DBD::Oracle currently sets $sth->{TYPE} to 9 (SQL_DATE) for DATE fields.
> Seems logical at first but Oracle DATE fields are really date+time fields.
> Oracle's own ODBC drivers return 11 (SQL_TIMESTAMP) for DATE fields and
> I'm planning to change DBD::Oracle to do likewise.
>
> Tim.
>
That's only part of the story!
- Up to version 8.1.7, Oracle supports only one temporal datatype:
DATE, which has a granularity of second.
The following datatypes are new for Oracle9i:
TIMESTAMP
TIMESTAMP WITH [LOCAL]TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
- DBI's statement handle returns: TYPE=9.
This is SQL_DATETIME in ODBC and SQL/CLI.
- DBI's type_info returns: DATA_TYPE=11.
This is SQL_TIMESTAMP in ODBC >= 3.0 and not defined in SQL/CLI.
- Oracle's ODBC driver 8.1.6 returns (SQLGetTypeInfo): DATA_TYPE=11,
if SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC2.
Same for SQLDescribeCol.
- Oracle's ODBC driver 8.1.6 returns (SQLGetTypeInfo): DATA_TYPE=93,
if SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3.
This is SQL_TYPE_TIMESTAMP in ODBC and SQL/CLI.
Same for SQLDescribeCol.
Steffen
SQLGetTypeInfo:
In: StatementHandle = 0x00D31870, DataType = SQL_ALL_TYPES=0
Return: SQL_SUCCESS=0
Get Data All:
"TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX",
"CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE",
"FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE",
"MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX",
"INTERVAL_PRECISION"
"BLOB", -4, 2147483647, "'", "'", <Null>, 1, 1, 0, <Null>, 0, <Null>, "BLOB", <Null>,
<Null>, -4, <Null>, <Null>, <Null>
"LONG RAW", -4, 2147483647, "'", "'", <Null>, 1, 0, 0, <Null>, 0, <Null>, "LONG RAW",
<Null>, <Null>, -4, <Null>, <Null>, <Null>
"BFILE", -4, 2147483647, "'", "'", <Null>, 1, 1, 0, <Null>, 0, <Null>, "BFILE",
<Null>, <Null>, -4, <Null>, <Null>, <Null>
"RAW", -3, 2000, "'", "'", "max length", 1, 0, 3, <Null>, 0, <Null>, "RAW", <Null>,
<Null>, -3, <Null>, <Null>, <Null>
"CLOB", -1, 2147483647, "'", "'", <Null>, 1, 1, 0, <Null>, 0, <Null>, "CLOB", <Null>,
<Null>, -1, <Null>, <Null>, <Null>
"LONG", -1, 2147483647, "'", "'", <Null>, 1, 1, 0, <Null>, 0, <Null>, "LONG", <Null>,
<Null>, -1, <Null>, <Null>, <Null>
"CHAR", 1, 2000, "'", "'", "max length", 1, 1, 3, <Null>, 0, 0, "CHAR", <Null>,
<Null>, 1, <Null>, <Null>, <Null>
"DECIMAL", 3, 38, <Null>, <Null>, "precision,scale", 1, 0, 3, 0, 0, 0, "DECIMAL", 0,
38, 3, <Null>, 10, <Null>
"DOUBLE PRECISION", 8, 15, <Null>, <Null>, <Null>, 1, 0, 3, 0, 0, 0, "DOUBLE
PRECISION", <Null>, <Null>, 8, <Null>, 10, <Null>
"DATE", 93, 19, "'", "'", <Null>, 1, 0, 3, <Null>, 0, 0, "DATE", 0, 0, 9, 3, <Null>,
<Null>
"VARCHAR2", 12, 4000, "'", "'", "max length", 1, 1, 3, <Null>, 0, 0, "VARCHAR2",
<Null>, <Null>, 12, <Null>, <Null>, <Null>
11 rows fetched from 19 columns.
SQLDescribeCol:
In: StatementHandle = 0x00D31870, ColumnNumber = 10,
ColumnName = 0x0014F860, BufferLength = 300, NameLengthPtr =
0x0014AF70,
DataTypePtr = 0x0014E960, ColumnSizePtr = 0x0014E978, DecimalDigits =
0x0014E990,
NullablePtr = 0x0014E9A8
Return: SQL_SUCCESS=0
Out: *ColumnName = "CDATE", *NameLengthPtr = 5,
*DataTypePtr = SQL_TYPE_TIMESTAMP=93, *ColumnSizePtr = 19,
*DecimalDigits = 0, *NullablePtr = SQL_NULLABLE=1
