On Wed, 10 Mar 2010 13:57:07 +0000, Tim Bunce <tim.bu...@pobox.com>
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?
It maps perfect, as we propose to document in the column_info ()
section that those fields are to return ODBC types in the declared
fields, and that the DBD is free to add internal types with it's own
prefix as pg_name or ora_type
I'm still undecided if we should promote pg_type_name or that we leave
that completely open. Currently neither Oracle nor Postgres return a
field like that.
> > 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.
Subject to standard is very much OK, but I think it won't harm
mentioning that in the method documentation instead of only with the
ODBC SQL/CLI Standards section. I must admit I missed that section when
browsing te manual for column_info ().
> > 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.
Oh man, this is one valid nice overlooked remark!
Neither did I. Now fixed.
column_info () for DBD::Unify now does:
my @col_name = qw(
TABLE_CAT TABLE_SCHEM TABLE_NAME
COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE
REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH
ORDINAL_POSITION IS_NULLABLE
CHAR_SET_CAT CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM
COLLATION_NAME UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM
DOMAIN_NAME SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY
DTD_IDENTIFIER IS_SELF_REF
uni_type uni_type_name
uni_display_length uni_display_scale uni_rdonly uni_primry
uni_uniq uni_logged uni_ordered
);
DBI->connect ("dbi:Sponge:", "", "", {
RaiseError => $sth->{RaiseError},
PrintError => $sth->{PrintError},
ChopBlanks => 1,
FetchHashKeyName => $sth->{FetchHashKeyName} || "NAME",
})->prepare ("select column_info $where", {
rows => \...@fki,
NAME => \...@col_name,
});
are there more that should be passed?
> Even if we wanted to change it there's a fairly high probability of
> such a change breaking code.
>
> Tim.
--
H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
