I see a big difference in what $sth->{TYPE} returns (and the name) and
what column_info () - if implemented - is returning.
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:
--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.
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
• column_info () is not always available (sth is undef then)
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
?
--
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/
