Thanks Steffen. I realised after I sent it that I meant to ask if
anyone was sure what the situation was with the new types in Oracle 9i.
If you'll accept it then I'll happily nominate you 'chief resolver of
date issues in DBD::Oracle' and await your patch :-)
Tim.
On Mon, Aug 27, 2001 at 09:42:48AM +0200, Steffen Goeldner wrote:
> > 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
