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
