Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Apr 21, 2011 · 0 comments

Comments

@firebird-automations
Copy link

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
]
];

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants