SQL_ALL_TYPES and SQL_UNKNOWN_TYPE share the same type code number

[rwfranks]

ranscribed verbatim from CPAN RT#83238, warts and all.

Sat Feb 09 10:09:47 2013 COSMICNET [...] cpan.org - Ticket created
Subject: SQL_ALL_TYPES and SQL_UNKNOWN_TYPE share the same type code number

Hi All,
These two type constants sharing the same type code leads to incorrect
results when one requests type info:

C:\>perl -MData::Dumper -e "use 5.16.0; use DBI qw(:sql_types); say
'SQL_ALL_TYPES=' . SQL_ALL_TYPES; say 'SQL_UNKNOWN_TYPE=' .
SQL_UNKNOWN_TYPE;"

#SQL_ALL_TYPES=0
#SQL_UNKNOWN_TYPE=0

C:\>perl -MData::Dumper -e "use DBI qw(:sql_types); my $dbh =
DBI->connect('DBI:mysql:database=test;host=localhost;','test','test');
print Dumper( $dbh->type_info( SQL_UNKNOWN_TYPE ) )"

#returns all types

C:\>perl -MData::Dumper -e "use DBI qw(:sql_types); my $dbh =
DBI->connect('DBI:Pg:database=test;host=localhost;','test','test');
print Dumper( $dbh->type_info( SQL_UNKNOWN_TYPE ) )"

#returns all types

SQL_ALL_TYPES does appear in the standard.
SQL_UNKNOWN_TYPE doesn't appear in the standard.

Googling seems to suggest that SQL_UNKNOWN_TYPE is used by a number of
DBMSs, with type code 0.

I can't think of a solution other than to re-code SQL_UNKNOWN_TYPE to
something within the DBI specific permissible range, such as 9000. But
then that would probably break several DBDs.

The DBI docs don't mention SQL_UNKNOWN_TYPE. So maybe the only workable
solution is to add a caveat?

Lyle

[rwfranks]

Thu Sep 26 11:01:00 2013 bohica [...] ntlworld.com - Correspondence added

On Sat Feb 09 10:09:47 2013, COSMICNET wrote:

Hi All,
These two type constants sharing the same type code leads to incorrect
results when one requests type info:

C:>perl -MData::Dumper -e "use 5.16.0; use DBI qw(:sql_types); say
'SQL_ALL_TYPES=' . SQL_ALL_TYPES; say 'SQL_UNKNOWN_TYPE=' .
SQL_UNKNOWN_TYPE;"

#SQL_ALL_TYPES=0
#SQL_UNKNOWN_TYPE=0

C:>perl -MData::Dumper -e "use DBI qw(:sql_types); my $dbh =
DBI->connect('DBI:mysql:database=test;host=localhost;','test','test');
print Dumper( $dbh->type_info( SQL_UNKNOWN_TYPE ) )"

#returns all types

C:>perl -MData::Dumper -e "use DBI qw(:sql_types); my $dbh =
DBI->connect('DBI:Pg:database=test;host=localhost;','test','test');
print Dumper( $dbh->type_info( SQL_UNKNOWN_TYPE ) )"

#returns all types

SQL_ALL_TYPES does appear in the standard.
SQL_UNKNOWN_TYPE doesn't appear in the standard.

Googling seems to suggest that SQL_UNKNOWN_TYPE is used by a number of
DBMSs, with type code 0.

I can't think of a solution other than to re-code SQL_UNKNOWN_TYPE to
something within the DBI specific permissible range, such as 9000. But
then that would probably break several DBDs.

The DBI docs don't mention SQL_UNKNOWN_TYPE. So maybe the only workable
solution is to add a caveat?

Lyle

I don't see at all why SQL_UNKNOWN_TYPE or SQL_ALL_TYPES should be renumbered just because they are the same. The type_info method in DBI I guess is based on ODBC's SQLGetTypeInfo and you are only supposed to pass certain types (and SQL_ALL_TYPES) to SQLGetTypeInfo (see http://msdn.microsoft.com/en-us/library/ms714632%28v=vs.85%29.aspx). SQL_UNKNOWN_TYPE is returned by SQLDescribeCol if the column type is unknown (see http://msdn.microsoft.com/en-us/library/ms716289%28v=vs.85%29.aspx).

As far as I'm aware SQL_UNKNOWN_TYPE could just be removed from DBI's export list but it is far easier to just not use it.

Martin

Martin J. Evans
Wetherby, UK

Thu Sep 26 11:01:01 2013 The RT System itself - Status changed from 'new' to 'open'