Re: SQLGetTypeInfo does not return COLUMN_SIZE

On 04/03/14 14:29, Heikki Linnakangas wrote:
> (sorry for the late reply, I'm spring-cleaning my inbox..)

> On 10/04/2013 08:01 PM, Martin J. Evans wrote:
>> I may have discovered an issue with the postgres ODBC Driver.
>>
>> Postgres ODBC Driver version:
>> perl -MDBI -le 'my $h = DBI->connect; print $h->get_info(7);'
>> 09.00.0310
>>
>> Platform: Linux, Ubuntu
>>
>> I maintain Perl DBD::ODBC and the test suite shows a problem because when calling SQLGetTypeInfo some types return
>>
>> 'CREATE_PARAMS' => 'max. length'
>>
>> but then the COLUMN_SIZE is missing. e.g.:
>>
>> $VAR1 = {
>> 'UNSIGNED_ATTRIBUTE' => undef,
>> 'MAXIMUM_SCALE' => undef,
>> 'INTERVAL_PRECISION' => '0',
>> 'CREATE_PARAMS' => 'max. length',
>> 'NUM_PREC_RADIX' => undef,
>> 'PRECISION' => 255,
>> 'SEARCHABLE' => '3',
>> 'MONEY' => '0',
>> 'AUTO_INCREMENT' => undef,
>> 'LOCAL_TYPE_NAME' => undef,
>> 'LITERAL_PREFIX' => '\'',
>> 'MINIMUM_SCALE' => undef,
>> 'TYPE_NAME' => 'varchar',
>> 'NULLABLE' => '1',
>> 'DATA_TYPE' => 12,
>> 'SQL_DATA_TYPE' => '12',
>> 'LITERAL_SUFFIX' => '\'',
>> 'CASE_SENSITIVE' => '1',
>> 'SQL_DATETIME_SUB' => undef
>> };
>>
>> See http://msdn.microsoft.com/en-us/library/ms714632%28v=vs.85%29.aspx where COLUMN_SIZE was introduced in ODBC 2 (decades ago) and should exist in the columns returned. Without it, the application cannot know what to put in varchar (size_here) after a column in a create table.
>>
>> I've put in a special case for now to work around this problem but it issues a warning too. If you need anything further from me please let me know.
>
> It's there with the old name "PRECISION". That Microsoft page says that:
>
> "The following columns have been renamed for ODBC 3.x. The column name changes do not affect backward compatibility because applications bind by column number."
>
> So technically, I think the driver is not doing anything wrong when it's returning the old name, although I agree it would be about time to switch to the new names...
>
> - Heikki
>

Agreed, the ODBC driver is not really doing anything wrong as such. The problem is that interfaces on top of ODBC (like Perl's DBI module) dictate the fields are accessed by the name of the column (see http://search.cpan.org/~timb/DBI-1.631/DBI.pm#type_info) and they name COLUMN_SIZE and not PRECISION.

ODBC 2 is more than 15 years old now. The result for the postgres driver is still confusing as it is as the same MS page states for ODBC 3.x the columns SQL_DATA_TYPE, INTERVAL_PRECISION, SQL_DATETIME_SUB and NUM_PREC_RADIX were added. I note the postgres result has all 4 but still refers to the old ODBC 2 PRECISION. At a minimum this is inconsistent.

Martin