Re: Defining type_info Was: DBIstcf_DISCARD_STRING and DBIstcf_STRICT

On 05/02/2013 14:33, Martin J. Evans wrote:
> I personally have loads of code which would potentially break if the 
> result-set from type_info_all was changed in any way. Also, as I said 
> above, DBD::ODBC does not change any result-sets and there is no 
> ability currently to support doing that.
>
> Other DBDs have greater flexibility since some of them emulate 
> SQLGetTypeInfo with a SQL statement whereas DBD::ODBC is stuck with 
> SQLGetTypeInfo. Even if we went down the SQL route, there are so many 
> variations in databases under ODBC it would be impossible.

I got the feeling that might be the case for DBD::ODBC.

>> It would help a lot, and what I think is needed, is a clear
>> definition as to the purpose of type_info_all, and more guidance for
>> DBD developers on how mappings should be carried out.
>>
>> In the DBI docs it states: Since DBI and ODBC drivers vary in how
>> they map their types into the ISO standard types you may need to
>> search for more than one type.
>>
>> Should this really be something put on the user, as opposed to giving
>> them the best mapping for that type if there is one? It seems like
>> it's adding extra search effort for them that they shouldn't
>> necessarily need to do. As you said, it appears the only guidance DBD
>> authors get is to pull the information from the equivalent ODBC
>> driver:
>>
>> Metadata.pm#Generating_a_TypeInfo_package_for_a_driver
>>
>> And from DBI::DBD with regards to type_info it states: Writing
>> DBD::Driver::db::type_info The guidelines on writing this method are
>> still not really clear. No sample implementation is available.
>>
>> A final bit from the documentation, in DBI.pm's POD with regard to
>> type_info_all it suggests:
>>
>> Drivers are also free to return extra driver-specific columns of
>> information - though it's recommended that they start at column index
>> 50 to leave room for expansion of the DBI/ODBC specification.
>>
>> Although I've yet to find a driver that has followed this guidance.
>> They all appear to have just adding driver specific columns into the
>> next available slot (19, 20, etc.).
>
> ODBC added a new column just recently:
>
> perl -le 'use DBI; my $h = DBI->connect; my $x =h->type_info_all; use 
> Data::Dumper; print Dumper($x)'
> $VAR1 = [
>           {
>             'UNSIGNED_ATTRIBUTE' => 9,
>             'MAXIMUM_SCALE' => 14,
>             'INTERVAL_PRECISION' => 18,
>             'CREATE_PARAMS' => 5,
>             'NUM_PREC_RADIX' => 17,
>             'SEARCHABLE' => 8,
>             'USERTYPE' => 19, <----------------- see here
>             'LOCAL_TYPE_NAME' => 12,
>             'AUTO_INCREMENT' => 11,
>             'MONEY' => 10,
>             'LITERAL_PREFIX' => 3,
>             'COLUMN_SIZE' => 2,
>             'MINIMUM_SCALE' => 13,
>             'TYPE_NAME' => 0,
>             'NULLABLE' => 6,
>             'DATA_TYPE' => 1,
>             'SQL_DATA_TYPE' => 15,
>             'CASE_SENSITIVE' => 7,
>             'LITERAL_SUFFIX' => 4,
>             'SQL_DATETIME_SUB' => 16
>           },
>
>> I think at this point, a big question is: Does the DBI just want to
>> emulate ODBC's SQLSetTypeInfo function, or does it want to have
>> something that goes a bit further and possibly does it a bit better?
>
> I'm ok with someone defining better but I've never had a case where I 
> needed something better. I have code that looks at most of the 
> type_info_all fields and uses this data to work out how to create 
> tables but it was really just a port of some old C code. Mostly, I'm 
> not using any code which creates schemas now, just using existing ones.

I think I'll develop some ideas in a separate module, then report my 
findings and we can see if there is anything that might be useful to the 
DBDs.

Thanks for your feedback, much appreciated!


Lyle