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

Tim Bunce wrote:
> 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?

From my now so heavily used copy of the ODBC spec that it is falling
apart it says:

SQLColumns:
  DATA_TYPE: SQL data type. this can be an ODBC SQL data type or a
  driver-specific SQL data type. For datetime and interval data types,
  this column returns the concise data type (such as SQL_TYPE_DATE ot
  SQL_INTERVAL_YEAR_TO_MONTH, rather than the non-concise data type such
  as SQL_DATETIME or SQL_INTERVAL).

  The definition of "driver-specific SQL data types" is later in the
  book and they should be ones registered with the standard.

  TYPE_NAME: Data source dependent data type name; for example, "CHAR",
  "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR() FOR BIT DATA".

SQLGetTypeInfo:
  DATA_TYPE: exactly the same as SQLColumns above.

  TYPE_NAME: exactly the same as SQLColumns above except it adds
  "Applications must use this name in CREATE TABLE and ALTER TABLE
  statements."

In ODBC the {TYPE} attribute in a statement comes from SQLDescribeCol
which says:

  DATA_TYPE: the SQL data type of the column. This value is read from
  the SQL_DESC_CONCISE_TYPE field of the IRD (Implementation Record
  Descriptor - mje). This will be one of the values in the "SQL Data
  Types" section of Appendix D, "Data Types," or a driver-specific SQL
  data type. If the data type cannot be determined, the driver returns
  SQL_UNKNOWN_TYPE.

So I believe this means TYPE_NAME in type_info MUST be the name
recognised by the database (as usable in create table etc) but DATA_TYPE
in type_info/column_info/{TYPE} should match.

>> 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.

revision - if the DATA_TYPEs match (which they don't for all drivers
currently) there is no absolute reason for the names to match with
non-ODBC drivers. To go from type_info to column_info to {TYPE} and back
you'd use the DATA_TYPE field not the TYPE_NAME field.

>> 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.

Done.

>> 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.

That was one of my arguments to Merijn although I did not why it did not
work for some DBDs. If we can't change it then at least we should
document it - which I will happily do if this is the path chosen.

> Tim.
> 
> 

I will try and address Merijn's additional posting later today as he
says he in currently in a motivational window :-) although on first
reading I don't see any problems with providing a map of names to types
and vice versa (I use one internally in DBD::ODBC anyway).

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com