Teradata using DBD::ODBC

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