TYPE_NAME length returned by SQLDescribeCol/SQLColAttribute is too short for types -9 and -10 [ODBC122]

[firebird-automations]

Submitted by: Martin J. Evans (bohica)

Calling SQLGetTypeInfo for all types and them querying SQLDescribeCol/SQLColAttribute to find the size of the TYPE_NAME column returns a display size of 34 but the type -9 'VARCHAR(x) CHARACTER SET UNICODE_' and -10 'BLOB SUB_TYPE TEXT CHARACTER SET ' seem to want to return 36 chrs so they are truncated as shown here. This is not a serious issue for me personally but others using systems that need to map the type name internally will struggle especially in Perl via DBI since by default data truncation is an error so any call to retrieve type information will fail. It can be worked around by setting DBI's LongTruncOk but the TYPE_NAME fields are still truncated.

e.g.,

#⁠!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect('dbi:ODBC:Driver=Firebird;Dbname=/var/lib/firebird/2.5/data/hlaghdb.fdb',
'SYSDBA', 'XXX', { RaiseError => 1, PrintError => 0 });
print DBI::neat($dbh->get_info(7)), "\n";
my $r;
eval {
$r = $dbh->type_info_all;
};
print "err = ", $dbh->err ,"\n";
print "state = ", $dbh->state ,"\n";
$dbh->{LongTruncOk} = 1;
$r = $dbh->type_info_all;
print Dumper($r);
$dbh->disconnect;

errors the first time and works the second time when LongTruncOk is set. The output is below (note the -9 and -10 output).

The problem arises from a SQLDescribeCol call on the TYPE_NAME field which reports the length as:

DescribeCol column = 1, name = TYPE_NAME, namelen = 9, type = VARCHAR(12), precision/column size = 33, scale = 0, nullable = 1
SQL_COLUMN_DISPLAY_SIZE = 33
SQL_COLUMN_LENGTH = 33

'02.01.0100'
err = 1
state = 01004
$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
},
[
'CHAR',
'1',
'32767',
'\'',
'\'',
'length',
'1',
'1',
'3',
undef,
'0',
undef,
'CHAR',
undef,
undef,
'1',
undef,
undef,
undef
],
[
'VARCHAR',
'12',
'32765',
'\'',
'\'',
'length',
'1',
'1',
'3',
undef,
'0',
undef,
'VARCHAR',
undef,
undef,
'12',
undef,
undef,
undef
],
[
'BLOB SUB_TYPE TEXT',
'-1',
'2147483647',
'\'',
'\'',
undef,
'1',
'1',
'0',
undef,
'0',
undef,
'BLOB SUB_TYPE TEXT',
undef,
undef,
'-1',
undef,
undef,
undef
],
[
'CHAR(x) CHARACTER SET UNICODE_FSS',
'-8',
'10922',
'\'',
'\'',
'length',
'1',
'1',
'3',
undef,
'0',
undef,
'CHAR(x) CHARACTER SET UNICODE_FSS',
undef,
undef,
'-8',
undef,
undef,
undef
],
[
'VARCHAR(x) CHARACTER SET UNICODE_', <--------------------------------- note truncated
'-9',
'10921',
'\'',
'\'',
'length',
'1',
'1',
'3',
undef,
'0',
undef,
'VARCHAR(x) CHARACTER SET UNICODE_',
undef,
undef,
'-9',
undef,
undef,
undef
],
[
'BLOB SUB_TYPE TEXT CHARACTER SET ', <--------------------------------- note truncated
'-10',
'715827882',
'\'',
'\'',
undef,
'1',
'1',
'0',
undef,
'0',
undef,
'BLOB SUB_TYPE TEXT CHARACTER SET ',
undef,
undef,
'-10',
undef,
undef,
undef
],
[
'BLOB SUB_TYPE 0',
'-4',
'2147483647',
undef,
undef,
undef,
'1',
'0',
'0',
undef,
'0',
undef,
'BLOB SUB_TYPE 0',
undef,
undef,
'-4',
undef,
undef,
undef
],
[
'BLOB SUB_TYPE 0',
'-3',
'2147483647',
undef,
undef,
undef,
'1',
'0',
'0',
undef,
'0',
undef,
'BLOB SUB_TYPE 0',
undef,
undef,
'-3',
undef,
undef,
undef
],
[
'BLOB SUB_TYPE 0',
'-2',
'2147483647',
undef,
undef,
undef,
'1',
'0',
'0',
undef,
'0',
undef,
'BLOB SUB_TYPE 0',
undef,
undef,
'-2',
undef,
undef,
undef
],
[
'NUMERIC',
'2',
'18',
undef,
undef,
'precision,scale',
'1',
'0',
'2',
'0',
'0',
'0',
'NUMERIC',
'0',
'18',
'2',
undef,
'10',
undef
],
[
'DECIMAL',
'3',
'18',
undef,
undef,
'precision,scale',
'1',
'0',
'2',
'0',
'0',
'0',
'DECIMAL',
'0',
'18',
'3',
undef,
'10',
undef
],
[
'INTEGER',
'4',
'10',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'INTEGER',
'0',
'0',
'4',
undef,
'10',
undef
],
[
'SMALLINT',
'-6',
'5',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'SMALLINT',
'0',
'0',
'-6',
undef,
'10',
undef
],
[
'SMALLINT',
'5',
'5',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'SMALLINT',
'0',
'0',
'5',
undef,
'10',
undef
],
[
'DOUBLE PRECISION',
'6',
'15',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'DOUBLE PRECISION',
undef,
undef,
'6',
undef,
'2',
undef
],
[
'FLOAT',
'7',
'7',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'FLOAT',
undef,
undef,
'7',
undef,
'2',
undef
],
[
'DOUBLE PRECISION',
'8',
'15',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'DOUBLE PRECISION',
undef,
undef,
'8',
undef,
'2',
undef
],
[
'BIGINT',
'-5',
'18',
undef,
undef,
undef,
'1',
'0',
'2',
'0',
'0',
'0',
'BIGINT',
'0',
'18',
'-5',
undef,
'10',
undef
],
[
'DATE',
'91',
'10',
'{d\'',
'\'}',
undef,
'1',
'0',
'2',
undef,
'0',
undef,
'DATE',
undef,
undef,
'9',
'1',
undef,
undef
],
[
'TIME',
'92',
'13',
'{t\'',
'\'}',
undef,
'1',
'0',
'2',
undef,
'0',
undef,
'TIME',
'0',
'4',
'9',
'2',
undef,
undef
],
[
'TIMESTAMP',
'93',
'24',
'{ts\'',
'\'}',
undef,
'1',
'0',
'2',
undef,
'0',
undef,
'TIMESTAMP',
'0',
'4',
'9',
'3',
undef,
undef
]
];