Issue Details (XML | Word | Printable)

Key: ODBC-122
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Alexander Potapchenko
Reporter: Martin J. Evans
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
ODBC Driver

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

Created: 21/Apr/11 07:55 AM   Updated: 21/Apr/11 07:55 AM
Component/s: None
Affects Version/s: 2.1 Beta
Fix Version/s: None

Time Tracking:
Not Specified

Environment:
Linux Ubuntu
Perl 5.10.1
DBI 1.617
DBD::ODBC 1.29
Firebird ODBC driver reporting itself as 02.01.0100 via SQLGetInfo (CVS head as of 20-Apr-2011)
SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database = 2.5.0
SELECT RDB$CHARACTER_SET_NAME FROM RDB$DATABASE returns NONE


 Description  « Hide
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
          ]
        ];



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.