Re: column_info () vs type_info () (Summary)

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.