H.Merijn Brand wrote:
> On Mon, 08 Mar 2010 10:13:02 +0000, Martin Evans
> <martin.ev...@easysoft.com> wrote:
>
> large original chunks snipped ...
>
>> H.Merijn Brand wrote:
>>> I see a big difference in what $sth->{TYPE} returns (and the name) and
>>> what column_info () - if implemented - is returning.
>> I don't think I do with DBD::ODBC (results below).
>>
>>> DATA_TYPE has no specification of what type of code that is. It can be
>>> either the code the type is internally known by with the database, or
>>> it can be the ODBC equivalent.
>>>
>>> TYPE_NAME has no guarantee whatsoever to be like what type_info ()
>>> returns with code like:
>> I thought it should.
>>
>>> --8<---
>>> { my %types; # Cache for types
>>>
>>> # Convert numeric to readable
>>> sub _type_name
>>> {
>>> my $type = shift;
>>>
>>> unless (exists $types{$dbh}{$type}) {
>>> my $tpi = $type =~ m/^-?[0-9]+$/ ? $dbh->type_info ($type) : undef;
>>> $types{$dbh}{$type} = $tpi ? $tpi->{TYPE_NAME} : $type // "?";
>>> }
>>> return $types{$dbh}{$type};
>>> } # type_name
>>> }
>>> -->8---
>>>
>>> The keys in the hashref returned from column_info () often do not honor
>>> the {FetchHashKeyName} dbh attribute, which makes it quite a bit harder
>>> to write database-independent code. I think either document that the
>>> sth returned from column_info () doesn't have to follow this attribute,
>>> or make the authors alter the code so it does.
>> I guess you are mostly referring to the 'COLUMN_NAME', 'TABLE_NAME',
>> 'TABLE_SCHEM' and 'TABLE_CAT' keys - yes?
>
> Yes, but esp the *extra* fields returned. FetchHaskKeyNames refers to
> the date returned in the hashref. The 4 you name are normally provided
> to column_info () and not the ones you want to examine. What I mean is
> *all* the keys, so also 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. etc
>
> For example, in MySQL, a hash like this is returned:
>
> { BUFFER_LENGTH => undef,
> CHAR_OCTET_LENGTH => undef,
> CHAR_SET_CAT => undef,
> CHAR_SET_NAME => undef,
> CHAR_SET_SCHEM => undef,
> COLLATION_CAT => undef,
> COLLATION_NAME => undef,
> COLLATION_SCHEM => undef,
> COLUMN_DEF => undef,
> COLUMN_NAME => 'xbb',
> COLUMN_SIZE => 20,
> DATA_TYPE => 4,
> DECIMAL_DIGITS => undef,
> DOMAIN_CAT => undef,
> DOMAIN_NAME => undef,
> DOMAIN_SCHEM => undef,
> DTD_IDENTIFIER => undef,
> IS_NULLABLE => 'NO',
> IS_SELF_REF => undef,
> MAX_CARDINALITY => undef,
> NULLABLE => 0,
> NUM_PREC_RADIX => 10,
> ORDINAL_POSITION => 1,
> REMARKS => undef,
> SCOPE_CAT => undef,
> SCOPE_NAME => undef,
> SCOPE_SCHEM => undef,
> SQL_DATA_TYPE => 4,
> SQL_DATETIME_SUB => undef,
> TABLE_CAT => undef,
> TABLE_NAME => 'xbb',
> TABLE_SCHEM => undef,
> TYPE_NAME => 'BIGINT',
> UDT_CAT => undef,
> UDT_NAME => undef,
> UDT_SCHEM => undef,
> mysql_is_auto_increment => 1,
> mysql_is_pri_key => 1,
> mysql_type_name => 'bigint(20) unsigned',
> mysql_values => undef
> }
>
> and FetchHashKeyName was set to "NAME_lc", which IMHO should have
> returned ALL keys lowercase.
I see what you mean now.
I don't see why FetchHashKeyName should affect those keys - I believed
it was for the cases where keys are column names. Those keys are part of
the DBI spec not some variable thing depending on database e.g., whether
case is maintained on a column name or not.
>>> Extra fun comes from databases that store type names instead of type
>>> codes in their data-dictionary (like Unify and SQLite), and reversing
>>> that process to make column_info () return both TYPE_NAME and DATA_TYPE
>>> makes it a different pair than TYPE and the derived counterpart from
>>> type_info ().
>>>
>>> My real question is, should the docs be enhanced to
>>>
>>> • make clear that these two return different things
>> or make them return the same things. Obviously for ODBC this is simple
>> as they are the same things but for other DBDs I think it is useful to
>> know a single type that can be used across all databases and the real
>> type implemented in the database (and be able to map between them) -
>> from your results mysql looks closest in this respect.
>
> indeed, and I was planning to make the two columns identical for Unify
> too. But the docs did not force me to do so, and making translation
> tables is causing hardcoded code, which is harder to maintain.
I can see this is not ideal for you.
>> People writing bugzilla, open LDAP etc backend support in databases are
>> having to hand code the schema for each database but in many cases it
>> may be possible (if the DBDs returned a single set of types) to code
>> this generically (although I'd guess it would be still quite hard).
>>
>>> • column_info () is not always available (sth is undef then)
>> I guess so.
>>
>> It should not be difficult to add column_info to DBD::Oracle - I know
>> this has come up in the past. I think I even provided some SQL that
>> would do it but I cannot find it right now.
>
> I did the initial implementation for Unify in about 30 minutes, but
> then I ended up finding these strange behaviours and digged into all
> the other databases.
>
>>> Here's my findings so far ...
>>>
>>> PostgreSQL
>>> Create as sth attributes column_info ()
>>> ----------------------- --------------------------
>>> -----------------------------------
>>> bigint ? -5 bigint
>>> -5
>>> bigserial ? -5 bigint
>>> -5
>>> :
>>>
>>> DBD::Oracle does not support column_info ()
>>>
>>> Oracle
>>> Create as sth attributes column_info ()
>>> ----------------------- --------------------------
>>> -------------------------
>>> bfile ? -9114 -
>>> blob ? 30 -
>>> :
>>>
>>> DBD::Unify will have column_info () in the next release. I'm all open
>>> for changes.
>>>
>>> Unify
>>> Create as sth attributes column_info ()
>>> ----------------------- --------------------------
>>> -----------------------------------
>>> amount FLOAT 6 AMOUNT
>>> -206
>>> amount (5, 2) FLOAT 6 AMOUNT
>>> -206
>>> :
>>>
>>> MySQL
>>> Create as sth attributes column_info ()
>>> ----------------------- --------------------------
>>> -----------------------------------
>>> serial bigint -5 BIGINT
>>> 4
>>> tinyint tinyint -6 TINYINT
>>> 4
>>> :
>>>
>>> SQLite does not return handle TYPE attribute attributes :(
>>>
>>> SQLite
>>> Create as sth attributes column_info ()
>>> ----------------------- --------------------------
>>> -----------------------------------
>>> int ? 0 int
>>> ?
>>> integer ? 0 integer
>>> ?
>>> :
>> and here are mine with MS SQL Server (and our driver) - note there may
>> be a small issue with the XML type here - I have passed this on to the
>> person responsible. I've skipped multiple identity columns but could
>> provide more if you really want it.
>>
<snipped my sql server results>
>>
>> I seem to remember a similar issue with the lack of column_info came up
>> on Perl monks recently with DBD::Interbase. With the increasing use of
>> modules like DBIx::Class etc the meta data methods are becoming a lot
>> more important than they perhaps used to be.
>
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
