Hi,
I use DBD::ODBC to connect to Teradata. I need to get column data type
back. when using $sth->{TYPE}. I got IDs not in $dbh->type_info_all. It
appears to happen to only CHAR and VARCHAR types. Does anyone know how
to get the data type information for a column (in a query)? Thanks.
Here is the code. Note that -8 in $sth->{TYPE} is not defined in
$dbh->type_info_all. It is the ID for both CHAR and VARCHAR columns
defined in the table. I tried the same using DBD::ADO and got similar
issue.
#!perl
#
use strict;
use DBI;
use DBD::ODBC;
#use Carp;
use Data::Dumper;
my $dsn = 'Production Teradata';
my $dbh = DBI->connect("DBI:ODBC:$dsn", 'user', 'passwd', {AutoCommit
=> 0,PrintError => 0,RaiseError => 0,} ) or die $DBI::errstr;
#CREATE SET TABLE foo
# (
# fooID INTEGER,
# LineNum INTEGER,
# foo char(10),
# foo2 date,
# foo3 smallint,
# foo4 bigint,
# foo5 byte,
# foo6 time,
# Haiku VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
# RecModTS TIMESTAMP(0))
#PRIMARY INDEX ( fooID );
my $sql = 'select * from foo';
#$dbh->{ado_ti_ver} = 1;
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
my $rv = $sth->execute() or die $sth->errstr;
my $rows = $sth->fetchall_arrayref();
print Dumper($dbh->type_info_all), Dumper($sth->{TYPE});
$dbh->commit;
$dbh->disconnect();
1;
$VAR1 = [
{ 'UNSIGNED_ATTRIBUTE' => 9, 'MAXIMUM_SCALE' => 14,
'INTERVAL_PRECISION' => 18, 'CREATE_PARAMS' => 5, 'NUM_PREC_RADIX' =>
17, 'SEARCHABLE' => 8, 'LOCAL_TYPE_NAME' => 12, 'LITERAL_PREFIX' => 3,
'COLUMN_SIZE' => 2, 'MINIMUM_SCALE' => 13, 'TYPE_NAME' => 0,
'AUTO_UNIQUE_VALUE' => 11, 'NULLABLE' => 6, 'DATA_TYPE' => 1,
'SQL_DATA_TYPE' => 15, 'CASE_SENSITIVE' => 7, 'LITERAL_SUFFIX' => 4,
'FIXED_PREC_SCALE' => 10, 'SQL_DATETIME_SUB' => 16 },
[ 'PERIOD(DATE)', '-1049', '28', 'PERIOD \'', '\'', undef,
'1', '0', '2', undef, '0', '0', 'PERIOD(DATE)', '0', '0', '-1049',
undef, '0', undef ],
[ 'PERIOD(TIME())', '-1048', '38', 'PERIOD \'', '\'', 'scale',
'1', '0', '2', undef, '0', '0', 'PERIOD(TIME())', '0', '6', '-1048',
undef, '0', undef ],
[ 'PERIOD(TIME() WITH TIME ZONE)', '-1047', '50', 'PERIOD \'',
'\'', 'scale', '1', '0', '2', undef, '0', '0', 'PERIOD(TIME() WITH TIME
ZONE)', '0', '6', '-1047', undef, '0', undef ],
[ 'PERIOD(TIMESTAMP())', '-1046', '60', 'PERIOD \'', '\'',
'scale', '1', '0', '2', undef, '0', '0', 'PERIOD(TIMESTAMP())', '0',
'6', '-1046', undef, '0', undef ],
[ 'PERIOD(TIMESTAMP() WITH TIME ZONE)', '-1045', '72', 'PERIOD
\'', '\'', 'scale', '1', '0', '2', undef, '0', '0', 'PERIOD(TIMESTAMP()
WITH TIME ZONE)', '0', '6', '-1045', undef, '0', undef ],
[ 'BYTEINT', '-6', '3', undef, undef, undef, '1', '0', '2',
'0', '0', '0', 'BYTEINT', '0', '0', '-6', undef, '10', undef ],
[ 'BIGINT', '-5', '19', undef, undef, undef, '1', '0', '2',
'0', '0', '0', 'BIGINT', '0', '0', '-5', undef, '10', undef ],
[ 'VARBYTE', '-3', '64000', '\'', '\'XB', 'max length', '1',
'0', '0', undef, '0', '0', 'VARBYTE', undef, undef, '-3', undef, '0',
undef ],
[ 'BYTE', '-2', '64000', '\'', '\'XB', 'max length', '1', '0',
'0', undef, '0', '0', 'BYTE', undef, undef, '-2', undef, '0', undef ],
[ 'CHAR', '1', '64000', '\'', '\'', 'max length', '1', '1',
'3', undef, '0', '0', 'CHAR', undef, undef, '1', undef, '0', undef ],
[ 'DECIMAL', '3', '38', undef, undef, 'precision, scale', '1',
'0', '2', '0', '0', '0', 'DECIMAL', '0', '38', '3', undef, '10', undef
],
[ 'INTEGER', '4', '10', undef, undef, undef, '1', '0', '2',
'0', '0', '0', 'INTEGER', '0', '0', '4', undef, '10', undef ],
[ 'SMALLINT', '5', '5', undef, undef, undef, '1', '0', '2',
'0', '0', '0', 'SMALLINT', '0', '0', '5', undef, '10', undef ],
[ 'FLOAT', '6', '15', undef, undef, undef, '1', '0', '2', '0',
'0', '0', 'FLOAT', undef, undef, '6', undef, '2', undef ],
[ 'VARCHAR', '12', '64000', '\'', '\'', 'max length', '1',
'1', '3', undef, '0', '0', 'VARCHAR', undef, undef, '12', undef, '0',
undef ],
[ 'DATE', '91', '10', undef, undef, undef, '1', '0', '2',
undef, '0', '0', 'DATE', '0', '0', '9', '1', '0', undef ],
[ 'TIME', '92', '8', undef, undef, undef, '1', '0', '2',
undef, '0', '0', 'TIME', '0', '0', '9', '2', '0', undef ],
[ 'TIMESTAMP', '93', '26', 'TIMESTAMP \'', '\'', 'scale', '1',
'0', '2', undef, '0', '0', 'TIMESTAMP', '0', '6', '9', '3', '0', undef
],
[ 'INTERVAL YEAR', '101', '4', 'INTERVAL \'', '\' YEAR',
'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL YEAR', undef,
undef, '10', '1', '0', '2' ],
[ 'INTERVAL MONTH', '102', '4', 'INTERVAL \'', '\' MONTH',
'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL MONTH', undef,
undef, '10', '2', '0', '2' ],
[ 'INTERVAL DAY', '103', '4', 'INTERVAL \'', '\' DAY',
'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL DAY', undef,
undef, '10', '3', '0', '2' ],
[ 'INTERVAL HOUR', '104', '4', 'INTERVAL \'', '\' HOUR',
'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL HOUR', undef,
undef, '10', '4', '0', '2' ],
[ 'INTERVAL MINUTE', '105', '4', 'INTERVAL \'', '\' MINUTE',
'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL MINUTE', undef,
undef, '10', '5', '0', '2' ],
[ 'INTERVAL SECOND', '106', '11', 'INTERVAL \'', '\' SECOND',
'precision, scale', '1', '0', '2', undef, '0', '0', 'INTERVAL SECOND',
'0', '6', '10', '6', '0', '2' ],
[ 'INTERVAL YEAR TO MONTH', '107', '7', 'INTERVAL \'', '\'
YEAR TO MONTH', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL
YEAR TO MONTH', undef, undef, '10', '7', '0', '2' ],
[ 'INTERVAL DAY TO HOUR', '108', '7', 'INTERVAL \'', '\' DAY
TO HOUR', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL DAY TO
HOUR', undef, undef, '10', '8', '0', '2' ],
[ 'INTERVAL DAY TO MINUTE', '109', '10', 'INTERVAL \'', '\'
DAY TO MINUTE', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL
DAY TO MINUTE', undef, undef, '10', '9', '0', '2' ],
[ 'INTERVAL DAY TO SECOND', '110', '20', 'INTERVAL \'', '\'
DAY TO SECOND', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL
DAY TO SECOND', '0', '6', '10', '10', '0', '2' ],
[ 'INTERVAL HOUR TO MINUTE', '111', '7', 'INTERVAL \'', '\'
HOUR TO MINUTE', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL
HOUR TO MINUTE', undef, undef, '10', '11', '0', '2' ],
[ 'INTERVAL HOUR TO SECOND', '112', '17', 'INTERVAL \'', '\'
HOUR TO SECOND', 'precision', '1', '0', '2', undef, '0', '0', 'INTERVAL
HOUR TO SECOND', '0', '6', '10', '12', '0', '2' ],
[ 'INTERVAL MINUTE TO SECOND', '113', '14', 'INTERVAL \'', '\'
MINUTE TO SECOND', 'precision', '1', '0', '2', undef, '0', '0',
'INTERVAL MINUTE TO SECOND', '0', '6', '10', '13', '0', '2' ]
];
$VAR1 = [ 4, 4, -8, 91, 5, -5, -2, 92, -8, 93 ]
LJ
