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.
> > 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.
> 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.
>
> typeinfo_all:
> xml, -152
> sql_variant, -150
> uniqueidentifier, -11
> ntext, -10
> nvarchar, -9
> sysname, -9
> nchar, -8
> bit, -7
> tinyint, -6
> tinyint identity, -6
> bigint, -5
> bigint identity, -5
> image, -4
> varbinary, -3
> binary, -2
> timestamp, -2
> text, -1
> char, 1
> numeric, 2
> numeric() identity, 2
> decimal, 3
> money, 3
> smallmoney, 3
> decimal() identity, 3
> int, 4
> int identity, 4
> smallint, 5
> smallint identity, 5
> float, 6
> real, 7
> varchar, 12
> datetime, 93
> smalldatetime, 93
> PSDATE, 93
> PSDATETIME, 93
>
> create table mje (a1 xml,a2 sql_variant,a3 uniqueidentifier,a4 ntext,a5
> nvarchar,a6 sysname,a7 nchar,a8 bit,a9 tinyint,a10 tinyint identity,a11
> bigint,a12 image,a13 varbinary,a14 binary,a15 timestamp,a16 text,a17
> char,a18 numeric,a19 decimal,a20 money,a21 smallmoney,a22 int,a23
> smallint,a24 float,a25 real,a26 varchar,a27 datetime,a28
> smalldatetime,a29 PSDATE,a30 PSDATETIME)
>
> column_info:
> a1, xml, -152
> a2, sql_variant, -150
> a3, uniqueidentifier, -11
> a4, ntext, -10
> a5, nvarchar, -9
> a6, sysname, -9
> a7, nchar, -8
> a8, bit, -7
> a9, tinyint, -6
> a10, tinyint identity, -6
> a11, bigint, -5
> a12, image, -4
> a13, varbinary, -3
> a14, binary, -2
> a15, timestamp, -2
> a16, text, -1
> a17, char, 1
> a18, numeric, 2
> a19, decimal, 3
> a20, money, 3
> a21, smallmoney, 3
> a22, int, 4
> a23, smallint, 5
> a24, float, 6
> a25, real, 7
> a26, varchar, 12
> a27, datetime, 93
> a28, smalldatetime, 93
> a29, PSDATE, 93
> a30, PSDATETIME, 93
>
> sth->{TYPE}:
> $VAR1 = [
> -10, <--- suspicious entry
> -150,
> -11,
> -10,
> -9,
> -9,
> -8,
> -7,
> -6,
> -6,
> -5,
> -4,
> -3,
> -2,
> -2,
> -1,
> 1,
> 2,
> 3,
> 3,
> 3,
> 4,
> 5,
> 6,
> 7,
> 12,
> 93,
> 93,
> 93,
> 93
> ];
>
> 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.
--
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/
