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). > From the DBI docs: > > Handle attributes: > > "TYPE" (array-ref, read-only) > > Returns a reference to an array of integer values for each column. > The value indicates the data type of the corresponding column. > > The values correspond to the international standards (ANSI X3.135 > and ISO/IEC 9075) which, in general terms, means ODBC. Driver-specific > types that don't exactly match standard types should generally return > the same values as an ODBC driver supplied by the makers of the > database. That might include private type numbers in ranges the > vendor has officially registered with the ISO working group: > > ftp://sqlstandards.org/SC32/SQL_Registry/ > > Where there's no vendor-supplied ODBC driver to be compatible with, > the DBI driver can use type numbers in the range that is now > officially reserved for use by the DBI: -9999 to -9000. > > All possible values for "TYPE" should have at least one entry in the > output of the "type_info_all" method (see "type_info_all"). > > column_info: > > DATA_TYPE: The concise data type code. > > TYPE_NAME: A data source dependent data type name. > > 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? > > 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. 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. > Here's my findings so far ... > > PostgreSQL > Create as sth attributes column_info () > ----------------------- -------------------------- > ----------------------------------- > bigint ? -5 bigint > -5 > bigserial ? -5 bigint > -5 > bit unknown 0 bit > 0 > bit (9) unknown 0 bit > 0 > bit varying unknown 0 bit varying > 0 > bit varying (35) unknown 0 bit varying > 0 > bool bool 16 boolean > 16 > boolean bool 16 boolean > 16 > box unknown 0 box > 0 > bytea bytea -3 bytea > -3 > character bpchar 1 character > 1 > character (5) bpchar 1 character > 1 > character varying text 12 character varying > 12 > character varying (21) text 12 character varying > 12 > cidr unknown 0 cidr > 0 > circle unknown 0 circle > 0 > date date 91 date > 91 > decimal numeric 3 numeric > 3 > decimal (15, 2) numeric 3 numeric > 3 > decimal (8) numeric 3 numeric > 3 > double precision float4 6 double precision > 6 > float4 unknown 0 real > 0 > float8 float4 6 double precision > 6 > inet unknown 0 inet > 0 > int int4 4 integer > 4 > int2 int2 5 smallint > 5 > int4 int4 4 integer > 4 > int8 ? -5 bigint > -5 > integer int4 4 integer > 4 > interval unknown 0 interval > 0 > interval (3) unknown 0 interval > 0 > line unknown 0 line > 0 > lseg unknown 0 lseg > 0 > macaddr unknown 0 macaddr > 0 > money unknown 0 money > 0 > numeric numeric 3 numeric > 3 > numeric (15, 2) numeric 3 numeric > 3 > numeric (8) numeric 3 numeric > 3 > path unknown 0 path > 0 > point unknown 0 point > 0 > polygon unknown 0 polygon > 0 > real unknown 0 real > 0 > serial int4 4 integer > 4 > serial4 int4 4 integer > 4 > smallint int2 5 smallint > 5 > text ? -1 text > -1 > time time 92 time without time zone > 92 > time (3) time 92 time without time zone > 92 > timestamp timestamp 11 timestamp without time > zone 11 > timestamp (3) timestamp 11 timestamp without time > zone 11 > timestamptz timestamptz 95 timestamp with time > zone 95 > timestamptz (3) timestamptz 95 timestamp with time > zone 95 > timetz unknown 0 time with time zone > 0 > timetz (3) unknown 0 time with time zone > 0 > tsquery unknown 0 tsquery > 0 > tsvector unknown 0 tsvector > 0 > txid_snapshot unknown 0 txid_snapshot > 0 > uuid unknown 0 uuid > 0 > varchar text 12 character varying > 12 > varchar (21) text 12 character varying > 12 > xml unknown 0 xml > 0 > > > DBD::Oracle does not support column_info () > > Oracle > Create as sth attributes column_info () > ----------------------- -------------------------- > ------------------------- > bfile ? -9114 - > blob ? 30 - > char CHAR 1 - > char (19) CHAR 1 - > clob ? 40 - > date DATE 93 - > float DOUBLE PRECISION 8 - > float (12) DOUBLE PRECISION 8 - > integer DECIMAL 3 - > interval day to second INTERVAL DAY TO SECOND 110 - > interval day (3) to second (2) INTERVAL DAY TO SECOND 110 - > long LONG -1 - > long raw LONG RAW -4 - > Field 1 has an Oracle type (106) which is not explicitly supported at > /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/DBD/Oracle.pm line 284, <DATA> > line 122. > DBD::Oracle::st execute failed: ORA-00932: inconsistent datatypes: expected > NUMBER got LABEL (DBD ERROR: error possibly near <*> indicator at char 7 in > 'select <*>* from xbb where 0 = 1') [for Statement "select * from xbb where 0 > = 1"] at /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/PROCURA/DBD.pm line > 421, <DATA> line 122. > mlslabel ? -9106 - > nchar CHAR 1 - > nchar (13) CHAR 1 - > nclob ? 40 - > number DOUBLE PRECISION 8 - > number (8) DECIMAL 3 - > number (15, 2) DECIMAL 3 - > number (9, -2) DECIMAL 3 - > nvarchar2 (3) VARCHAR2 12 - > raw (58) ? -2 - > rowid ? -9104 - > timestamp DATE 93 - > timestamp (3) DATE 93 - > urowid ? -9104 - > varchar (46) VARCHAR2 12 - > varchar2 (46) VARCHAR2 12 - > xmltype ? -9108 - > > > 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 > huge amount REAL 7 HUGE AMOUNT > -207 > huge amount (5, 2) REAL 7 HUGE AMOUNT > -207 > huge amount (15, 2) REAL 7 HUGE AMOUNT > -207 > byte BYTE -2 BYTE > -2 > byte (512) BYTE -2 BYTE > -2 > char CHAR 1 CHARACTER > 1 > char (12) CHAR 1 CHARACTER > 1 > currency HUGE AMOUNT 0 - > currency (9) HUGE AMOUNT 0 - > currency (7,2) HUGE AMOUNT 0 - > date DATE 9 DATE > 9 > huge date HUGE DATE 11 HUGE DATE > 11 > decimal NUMERIC 2 NUMERIC > 2 > decimal (2) NUMERIC 2 NUMERIC > 2 > decimal (8) NUMERIC 2 NUMERIC > 2 > double precision DOUBLE PRECISION 8 DOUBLE > 8 > float DOUBLE PRECISION 8 FLOAT > 6 > huge integer HUGE INTEGER -5 - > integer NUMERIC 2 NUMERIC > 2 > numeric NUMERIC 2 NUMERIC > 2 > numeric (2) SMALLINT 5 NUMERIC > 2 > numeric (6) NUMERIC 2 NUMERIC > 2 > real REAL 7 REAL > 7 > smallint SMALLINT 5 NUMERIC > 2 > text TEXT -1 TEXT > -1 > time TIME 10 TIME > 10 > > MySQL > Create as sth attributes column_info () > ----------------------- -------------------------- > ----------------------------------- > serial bigint -5 BIGINT > 4 > tinyint tinyint -6 TINYINT > 4 > tinyint unsigned tinyint -6 TINYINT > 4 > tinyint (2) tinyint -6 TINYINT > 4 > tinyint (2) unsigned tinyint -6 TINYINT > 4 > bool tinyint -6 TINYINT > 4 > boolean tinyint -6 TINYINT > 4 > smallint smallint 5 SMALLINT > 4 > smallint unsigned smallint 5 SMALLINT > 4 > smallint (2) smallint 5 SMALLINT > 4 > smallint (2) unsigned smallint 5 SMALLINT > 4 > mediumint integer 4 MEDIUMINT > 4 > mediumint unsigned integer 4 MEDIUMINT > 4 > mediumint (2) integer 4 MEDIUMINT > 4 > mediumint (2) unsigned integer 4 MEDIUMINT > 4 > int integer 4 INT > 4 > int unsigned integer 4 INT > 4 > int (2) integer 4 INT > 4 > int (2) unsigned integer 4 INT > 4 > integer integer 4 INT > 4 > integer unsigned integer 4 INT > 4 > integer (2) integer 4 INT > 4 > integer (2) unsigned integer 4 INT > 4 > bigint bigint -5 BIGINT > 4 > bigint unsigned bigint -5 BIGINT > 4 > bigint (2) bigint -5 BIGINT > 4 > bigint (2) unsigned bigint -5 BIGINT > 4 > float float 7 FLOAT > 6 > float unsigned float 7 FLOAT > 6 > float (15) float 7 FLOAT > 6 > float (15) unsigned float 7 FLOAT > 6 > float (15, 2) float 7 FLOAT > 6 > float (15, 2) unsigned float 7 FLOAT > 6 > double double 8 DOUBLE > 8 > double unsigned double 8 DOUBLE > 8 > double (15, 2) double 8 DOUBLE > 8 > double (15, 2) unsigned double 8 DOUBLE > 8 > double precision double 8 DOUBLE > 8 > double precision unsigned double 8 DOUBLE > 8 > double precision (15, 2) double 8 DOUBLE > 8 > double precision (15, 2) unsigned double 8 DOUBLE > 8 > decimal decimal 3 DECIMAL > 3 > decimal unsigned decimal 3 DECIMAL > 3 > decimal (15) decimal 3 DECIMAL > 3 > decimal (15) unsigned decimal 3 DECIMAL > 3 > decimal (15, 2) decimal 3 DECIMAL > 3 > decimal (15, 2) unsigned decimal 3 DECIMAL > 3 > date date 9 DATE > 9 > datetime timestamp 11 DATETIME > 11 > timestamp timestamp 11 TIMESTAMP > 11 > time time 10 TIME > 10 > year smallint 5 YEAR > 4 > year (2) smallint 5 YEAR > 4 > year (4) smallint 5 YEAR > 4 > char char 1 CHAR > 1 > char (3) char 1 CHAR > 1 > nchar char 1 CHAR > 1 > nchar (3) char 1 CHAR > 1 > national char char 1 CHAR > 1 > national char (3) char 1 CHAR > 1 > char character set utf8 char 1 CHAR > 1 > varchar (3) varchar 12 VARCHAR > 12 > nvarchar (3) varchar 12 VARCHAR > 12 > national varchar (3) varchar 12 VARCHAR > 12 > varchar (3) character set utf8 varchar 12 VARCHAR > 12 > binary (35) char 1 BINARY > 1 > varbinary (17) varchar 12 VARBINARY > 12 > tinyblob blob -4 TINYBLOB > 12 > tinytext blob -4 TINYTEXT > 12 > tinytext character set utf8 blob -4 TINYTEXT > 12 > blob blob -4 BLOB > 12 > blob (123) blob -4 TINYBLOB > 12 > text blob -4 TEXT > 12 > text (123) blob -4 TINYTEXT > 12 > text character set utf8 blob -4 TEXT > 12 > text (123) character set utf8 blob -4 TEXT > 12 > mediumblob blob -4 MEDIUMBLOB > 12 > mediumtext blob -4 MEDIUMTEXT > 12 > mediumtext character set utf8 blob -4 MEDIUMTEXT > 12 > longblob blob -4 LONGBLOB > 12 > longtext blob -4 LONGTEXT > 12 > longtext character set utf8 blob -4 LONGTEXT > 12 > enum ('a','b') char 1 ENUM > 12 > enum ('a','b') character set utf8 char 1 ENUM > 12 > set ('a','b') char 1 SET > 12 > set ('a','b') character set utf8 char 1 SET > 12 > > > SQLite does not return handle TYPE attribute attributes :( > > SQLite > Create as sth attributes column_info () > ----------------------- -------------------------- > ----------------------------------- > int ? 0 int > ? > integer ? 0 integer > ? > integer (3) ? 0 integer > ? > tinyint ? 0 tinyint > ? > smallint ? 0 smallint > ? > mediumint ? 0 mediumint > ? > bigint ? 0 bigint > ? > unsigned big int ? 0 unsigned big int > ? > int2 ? 0 int2 > ? > int8 ? 0 int8 > ? > character (2) ? 0 character > ? > varchar (20) ? 0 varchar > ? > varying character (3) ? 0 varying character > ? > nchar (13) ? 0 nchar > ? > native character (7) ? 0 native character > ? > nvarchar (11) ? 0 nvarchar > ? > text ? 0 text > ? > clob ? 0 clob > ? > blob ? 0 blob > ? > real ? 0 real > ? > double ? 0 double > ? > double precision ? 0 double precision > ? > float ? 0 float > ? > numeric ? 0 numeric > ? > numeric (3) ? 0 numeric > ? > decimal (10, 5) ? 0 decimal (10, 5) > ? > boolean ? 0 boolean > ? > date ? 0 date > ? > datetime ? 0 datetime > ? > > 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. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
