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