On Wed, Mar 10, 2010 at 10:25:45AM +0000, Martin Evans wrote:
> 1. the TYPE attribute on a statement is clearly documented as to what it
> should contain - "The values correspond to the international standards
> (ANSI X3.135 and ISO/IEC 9075) which, in general terms, means ODBC".
>
> However, it is not clear from the docs what TYPE_NAME and DATA_TYPE
> columns should be in the column_info method and how they compare with
> the same named columns returned by the type_info method.
>
> e.g., for the column_info method:
>
> DATA_TYPE: The concise data type code
> this could be the database internal type number or the ODBC type
> the current opinion is that it should be the ODBC type and extra
> keys added for the internal type (e.g., ora_type, uni_type depending
> on DBD prefix) NOTE mysql already adds "mysql_is_auto_increment",
> "mysql_is_pri_key", "mysql_type_name", "mysql_values" keys.
> TYPE_NAME: A data source dependent data type name.
>
> for type_info:
>
> DATA_TYPE (integer) SQL data type number.
> TYPE_NAME (string) Data type name for use in CREATE TABLE statements
>
> Proposed Solution: document column_info DATA_TYPE as being the same as
> {TYPE} but allow DBDs to add other keys to the column_info result (some
> already do).
The column_info() method maps to ODBC SQLColumns() function, and the
type_info() method maps to the SQLGetTypeInfo() function.
http://search.cpan.org/~timb/DBI-1.609/DBI.pm#ODBC_and_SQL/CLI_Standards_Reference_Information
How does the proposed solution fit with that model, and is a better fit
possible?
> 2. There is no guarantee that if you find a TYPE_NAME in column_info you
> can map it successfully to the type in type_info - this is annoying and
> difficult to workaround.
>
> Proposed Solution: document they should be the same
Subject to the standard - see above. An underlying issue here may be
(I'm guessing) that unlike ODBC, the DBI doesn't parse and rewrite
'standard sql' statements to the drivers dialect.
> 3. column_info is not always provided by a DBD and the documentation
> fails to mention that in this case the returned statement handle is undef.
>
> Proposed Solution: update documentation
Yeap.
> 4. It appears FetchHashKeyName is not honoured in the results of
> column_info though how this occurs does appear to depend on the DBD.
> Merijn got all upper case keys from MySQL when FetchHashKeyName =
> name_lc e.g.,
>
> { BUFFER_LENGTH => undef, # <-- should have been lowercase
> CHAR_OCTET_LENGTH => undef,# <-- should have been lowercase
> .
> .
> }
>
> and with DBD::ODBC I find the column names follow FetchHashKeyName but
> not the key columns passed to fetchXXX_arrayref e.g.,
>
> with NAME_lc I get:
>
> $VAR1 = {
> 'PSDATE' => { # <-- these are as the database returns them
> # and not lowercased here
> 'char_octet_length' => undef, # correctly cased
>
> and name_uc I get:
>
> $VAR1 = {
> 'PSDATE' => { # correctly cased by accident - as db returned
> 'DECIMAL_DIGITS' => '3', # correctly cased
>
> This does not altogether surprise me since in DBD::ODBC's case the
> result-set is generated by the ODBC driver not by DBD::ODBC.
>
> Proposed Solution: The MySQL case is plain wrong (needs fixing), the
> DBD::ODBC is only wrong if case of the data_type_name is supposed to
> follow FetchHashKeyName as well. What should it be?
The cause is probably that some drivers use DBD::Sponge but neglect to
pass the value of FetchHashKeyName through. Even if we wanted to change
it there's a fairly high probability of such a change breaking code.
Tim.
