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
